Database roles and privileges

ADPG uses roles to manage database access permissions. A role represents a database user or a group of database users. Roles can own database objects (for example, tables and functions) and allow access and actions on these objects (assign privileges). It is also possible to grant membership in a role to another role to use its privileges.

Database roles are separate from operating system users, but you can create roles corresponding to operating system users. Roles are global to the entire ADPG cluster, not to a single database.

The ADPG cluster contains a predefined superuser role postgres. Connect as postgres to create additional roles.

Create a role

To create a role, use the CREATE ROLE SQL command. You can run commands in the ADCM UI or psql command line.

CREATE ROLE user1 WITH LOGIN PASSWORD 'password1';

The command above creates the role user1 with the password password1. The LOGIN parameter allows the role to log in: user1 can be an initial session authorization name during client connection.

If you need to use special characters in a role name, enclose the name in double quotes. For additional information about SQL identifiers, refer to Identifiers and key words.

You can also execute the createuser command from the shell command line to create a role. createuser is a wrapper for the CREATE ROLE SQL command. Connect to the ADPG host as a superuser or a user with the CREATEROLE privilege to run the createuser command.

$ createuser user2

The pg_roles view provides access to information about database roles. You can execute the following query to check if a role is added:

SELECT rolname FROM pg_roles;

The user1 and user2 items should be in the result with PostgreSQL predefined roles :

pg_database_owner
pg_read_all_data
pg_write_all_data
pg_monitor
pg_read_all_settings
pg_read_all_stats
pg_stat_scan_tables
pg_read_server_files
pg_write_server_files
pg_execute_server_program
pg_signal_backend
postgres
user1
user2

The \du psql meta-command also lists existing roles.

NOTE
Only a user with superuser privileges can create a new superuser role or grant superuser privileges.

Role attributes

A database role can have attributes that define its privileges and interact with the client authentication system.

Role attributes
Name Description Example

LOGIN

The role can be used as the initial role name for a database connection (an equivalent of the CREATE USER command)

CREATE ROLE name LOGIN;

SUPERUSER

A database superuser bypasses all permission checks, except the right to log in. Since this role has high-level privileges, minimize the use of this role for security reasons

CREATE ROLE name SUPERUSER;

CREATEDB

The role has a permission to create databases

CREATE ROLE name CREATEDB;

CREATEROLE

The role has a permission to create roles. A role with CREATEROLE privilege can alter and drop other roles, grant or revoke membership in them

CREATE ROLE name CREATEROLE;

REPLICATION

A role has a permission to initiate streaming replication. This role must also have the LOGIN permission

CREATE ROLE name REPLICATION LOGIN;

PASSWORD

Use this attribute if the client authentication method requires a password. For more information, see Password authentication

CREATE ROLE name PASSWORD 'password_string';
NOTE
We recommend you to create a role that has the CREATEDB or CREATEROLE privileges instead of a superuser role and use this role for routine operations. This approach restricts superuser access and reduces security risks.

Use the ALTER ROLE command to modify role attributes after creation. The following command changes the password of the user1 role:

ALTER ROLE user1 WITH PASSWORD 'password2';

You can also add the CREATEDB or CREATEROLE privileges to the user2 role:

ALTER ROLE user2 CREATEROLE CREATEDB;

Role membership

You can group users to manage their privileges. To do this, create a group role:

CREATE ROLE group1 CREATEROLE CREATEDB;

There is no difference between a group role and individual user role, except the way of use. Group roles usually do not have the LOGIN attribute.

Execute the GRANT command to add members to the group role. The command below adds user1 and user2 to group1.

GRANT group1 TO user1, user2;

To remove a group member, utilize the REVOKE command.

REVOKE group1 FROM user1;

To use group privileges, call the SET ROLE command or add the INHERIT attribute to a group member role.

The SET ROLE command

Log in as a group member and execute the SET ROLE command:

SET ROLE 'group1';

As a result, the group member temporarily "becomes" group1. The database session has access to group role privileges and creates database objects on behalf of a group role: the owner of all created objects is group1.

The INHERIT attribute

Group member roles that have the INHERIT attribute automatically use group privileges. In the example below, user3 can utilize the group1 privileges after logging in.

CREATE ROLE user3 LOGIN INHERIT;
GRANT group1 TO user3;

Drop a role

To drop a role, drop or reassign all objects that the role owns. Granted permissions also must be revoked.

You can use the ALTER command to reassign an object. The command below assigns the database table table1 to the role user2.

ALTER TABLE table1 OWNER TO user2;

Alternatively, execute the REASSIGN OWNED command to reassign an entire role ownership to another role. The REASSIGN OWNED command transfers database objects to a new owner, but it does not affect granted privileges for objects that do not belong to the role. Call the DROP OWNED command to drop these privileges.

REASSIGN OWNED BY user1 TO user2;
DROP OWNED BY user1;

Since the commands above cannot access objects in other databases, run them for each database that contains objects owned by the role.

After all dependencies are removed, call the DROP ROLE command to remove a role:

DROP ROLE user1;

You can also execute the dropuser command from the shell command line to drop a role. dropuser is a wrapper for the DROP ROLE SQL command. Connect to the ADPG host as a superuser or a user with the CREATEROLE privilege to run the dropuser command.

$ dropuser user1
Found a mistake? Seleсt text and press Ctrl+Enter to report it