postgresql: user vs role

2020-02-20

 | 

~2 min read

 | 

290 words

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: user and group. As a result, ALTER USER and ALTER GROUP are just aliases for ALTER ROLE.

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 USER and CREATE GROUP which are both aliases for CREATE ROLE. If you write CREATE USER, the LOGIN permission 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!