Creating and Managing Users/Roles

The entity of the user is almost the same as the entity of the role in PostgreSQL. The commands to create these are CREATE USER and CREATE RULE. CREATE USER is now an alias for CREATE ROLE. The only difference is that when the command is spelled CREATE USER, LOGIN is assumed by default, whereas NOLOGIN is assumed when the command is spelled CREATE ROLE. So, a role having the LOGIN attribute can be thought of as a user.

There is also an operating system level wrapper createuser to create the command.

A user or role is created at the PostgreSQL cluster level and it is available or visible inside all the databases. By default, the new role will be able to connect to all existing and new databases and will also be able to create new tables and other objects in those databases. But, by default, it won’t be select data from tables created by other users.

Creating users/roles using SQL command

Syntax of CREATE USER is as below. Full details are available here.

CREATE USER name [ [ WITH ] option [ ... ] ]

where option can be:

      SUPERUSER | NOSUPERUSER
    | CREATEDB | NOCREATEDB
    | CREATEROLE | NOCREATEROLE
    | INHERIT | NOINHERIT
    | LOGIN | NOLOGIN
    | REPLICATION | NOREPLICATION
    | BYPASSRLS | NOBYPASSRLS
    | CONNECTION LIMIT connlimit
    | [ ENCRYPTED ] PASSWORD 'password' | PASSWORD NULL
    | VALID UNTIL 'timestamp'
    | IN ROLE role_name [, ...]
    | IN GROUP role_name [, ...]
    | ROLE role_name [, ...]
    | ADMIN role_name [, ...]
    | USER role_name [, ...]
    | SYSID uid

Important options for CREATE DATABASE are:

• NAME – the name of the user to be created. This is the only mandatory option.
• SUPERUSER | NOSUPERUSER – It specifies whether new role should be superuser. It defaults to NOSUPERUSER.
• LOGIN | NOLOGIN – If the role is created with LOGIN option, then the role gets the initial session authorization name during client connection. It defaults to NOLOGIN.
• CONNECTION LIMIT – It specifies how many concurrent connections the role can make. It defaults to -1 (unlimited).
• [ENCRYPTED] PASSWORD – It sets a password for the role. Useful only when also specifiying LOGIN option. The password is stored in the systems catalogs in encrypted form all the time. SO, ENCRYPTED option has not effect but it is there only for backward compatibility. PASSWORD defaults to NULL which can be changed later also.

The following examples will help explain this better.

# CREATE ROLE john LOGIN;

# CREATE USER doe WITH PASSWORD 'xdftry034';

# CREATE ROLE mike WITH LOGIN PASSWORD 'fsf4gs3' VALID UNTIL '2022-01-01';

# CREATE ROLE dbadmin WITH CREATEDB CREATEROLE;

You can list the roles using psql command \du and its variations.

# \du

# \du+

# \du john

# \du+ john

Altering users/roles

Syntax of ALTER USER is as below. Full details are available here.

ALTER ROLE role_specification [ WITH ] option [ ... ]

where option can be:

      SUPERUSER | NOSUPERUSER
    | CREATEDB | NOCREATEDB
    | CREATEROLE | NOCREATEROLE
    | INHERIT | NOINHERIT
    | LOGIN | NOLOGIN
    | REPLICATION | NOREPLICATION
    | BYPASSRLS | NOBYPASSRLS
    | CONNECTION LIMIT connlimit
    | [ ENCRYPTED ] PASSWORD 'password' | PASSWORD NULL
    | VALID UNTIL 'timestamp'

ALTER ROLE name RENAME TO new_name

ALTER ROLE { role_specification | ALL } [ IN DATABASE database_name ] SET configuration_parameter { TO | = } { value | DEFAULT }
ALTER ROLE { role_specification | ALL } [ IN DATABASE database_name ] SET configuration_parameter FROM CURRENT
ALTER ROLE { role_specification | ALL } [ IN DATABASE database_name ] RESET configuration_parameter
ALTER ROLE { role_specification | ALL } [ IN DATABASE database_name ] RESET ALL

where role_specification can be:

    role_name
  | CURRENT_USER
  | SESSION_USER

There are 3 types of actions possible on a role:

Modify attributes of a role – Database superusers can change any of the settings for any role. Roles having CREATEROLE privilege can change any of these settings except SUPERUSER, REPLICATION, and BYPASSRLS; but only for non-superuser and non-replication roles. Ordinary roles can only change their own password.

Rename the role – Roles having CREATEROLE privilege can rename non-superuser roles. The current session user cannot be renamed. Renaming a role clears its password if the password is MD5-encrypted.

The following examples will help explain this better.

Change role’s session default for a configuration variable – One can also change a role’s session default for a configuration variable, either for all databases or, when the IN DATABASE clause is specified, only for sessions in the named database.

# ALTER ROLE john WITH PASSWORD 'sdgsd#sgsd';

# ALTER ROLE doe WITH PASSWORD NULL;

# ALTER ROLE manj VALID UNTIL 'Aug 14 12:00:00 2022 +1';

# ALTER ROLE hemd VALID UNTIL 'infinity';

# ALTER ROLE tryd CREATEROLE CREATEDB;

# ALTER ROLE john RENAME TO doe;

# ALTER ROLE nightjobs SET maintenance_work_mem = 100000;

# ALTER ROLE groud IN DATABASE testdb SET client_min_messages = DEBUG;

# ALTER USER john SUPERUSER;

Dropping users/roles

A user can not be dropped if it is not connected and doesn’t own any objects. It can be done via SQL command DROP USER or wrapper dropuser;

# DROP USER john;

$ dropuser john;