~2 min read|
I was working in Postgres recently and realized that I was confused about whether or not to use the
alter user or
alter role command.
Heading to StackOverflow, I found an interesting conversation: What is the difference between a user and a role.
The top answer was fascinating. The tl:dr; in PostgreSQL a
role combines two other concepts:
group. As a result,
ALTER USER and
ALTER GROUP are just aliases for
Full answer copied below:
Previous versions of Postgres, and some other DB systems, have separate concepts of “groups” (which are granted access to database objects) and “users” (who can login, and are members of one or more groups).
In modern versions of Postgres, the two concepts have been merged: a “role” can have the ability to login, the ability to “inherit” from other roles (like a user being a member of a group, or a group being a member of another group), and access to database objects.
For convenience, many tools and manuals refer to any user with login permission as a “user” or “login role”, and any without as a “group” or “group role”, since it is useful and common practice to keep roughly to that structure. This is entirely a convention of terminology, and to understand the permissions, you need only understand the options available when creating roles and granting them access.
Again purely for convenience, Postgres still accepts commands using the old terminology, such as
CREATE GROUPwhich are both aliases for
CREATE ROLE. If you write
CREATE USER, the
LOGINpermission will be added to the new role by default, to emulate the old behaviour when that was a separate command.
Hi there and thanks for reading! My name's Stephen. I live in Chicago with my wife, Kate, and dog, Finn. Want more? See about and get in touch!