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 keywords.
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.
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) |
|
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 |
|
CREATEDB |
The role has a permission to create databases |
|
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 |
|
REPLICATION |
A role has a permission to initiate streaming replication. This role must also have the LOGIN permission |
|
PASSWORD |
Use this attribute if the client authentication method requires a password. For more information, see Password authentication |
|
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
.
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