“create user”, “create role”, what should be used in PostgreSQL?

Recently we’ve seen a video on YouTube which recommends not to use the “create user” command in PostgreSQL and instead always use the “create role” command. It was also mentioned that “create group” should not be used as well. We’ve been a bit surprised by this and this is the reason for this post.

While we never use the “create group” command, there is nothing in the documentation which recommends to avoid it. What is written there is, that “create group” is an alias for “create role”. This changed in version 8.1 of PostgreSQL and if you take a look at the release notes you will find this:

“Create a new role system that replaces users and groups (Stephen Frost)

Roles are a combination of users and groups. Like users, they can have login capability, and like groups, a role can have other roles as members. Roles basically remove the distinction between users and groups. For example, a role can:

• Have login capability (optionally)
• Own objects
• Hold access permissions for database objects
• Inherit permissions from other roles it is a member of

Once a user logs into a role, she obtains capabilities of the login role plus any inherited roles, and can use SET ROLE to switch to other roles she is a member of. This feature is a generalization of the SQL standard’s concept of roles. This change also replaces pg_shadow and pg_group by new role-capable catalogs pg_authid and pg_auth_members. The old tables are redefined as read-only views on the new role tables.”

This means that the only concept from there on are roles, users and groups internally are gone. This also explains why “create group” and “create user” are aliases for “create role” since then.

There is, however, no warning in the documentation to avoid those commands. Internally a role will be created, no matter which command you use. On the other hand we like to use “create user” when we want to create a role with login privileges, it is just easier to read. The only difference between “create role” and “create user” is, that “create user” grants the login privilege by default, while “create role” does not:

postgres=# create user u;
CREATE ROLE
postgres=# select rolcanlogin from pg_roles where rolname = 'u';
 rolcanlogin 
-------------
 t
(1 row)
postgres=# create role r;
CREATE ROLE
postgres=# select rolcanlogin from pg_roles where rolname = 'r';
 rolcanlogin 
-------------
 f
(1 row)

From a pure PostgreSQL internals point of view it might make sense to recommend not to use “create group” and “create user”. From an end user or DBA perspective these commands are still valid and if you want to use them, go ahead. If you want to only stick with “create role”, this is also fine.