Help Center> GaussDB> Distributed_2.x> Database Security Management> Managing Users and Their Permissions> Roles
Updated on 2023-10-23 GMT+08:00
View PDF

Roles

A role is a set of users. After a role is granted to a user through GRANT, the user will have all the permissions of the role. It is recommended that roles be used to efficiently grant permissions. For example, you can create different roles of design, development, and maintenance personnel, grant the roles to users, and then grant specific data permissions required by different users. When permissions are granted or revoked at the role level, these changes take effect on all members of the role.

GaussDB provides an implicitly defined group PUBLIC that contains all roles. By default, all new users and roles have the permissions of PUBLIC. For details about the default permissions of PUBLIC, see GRANT. To revoke permissions of PUBLIC from a user or role, or re-grant these permissions to them, add the PUBLIC keyword in the REVOKE or GRANT statement.

To view all roles, query the system catalog PG_ROLES.

1
 
SELECT * FROM PG_ROLES;

Adding, Modifying, and Deleting Roles

In non-separation-of-duties scenarios, a role can be created, modified, and deleted only by a system administrator or a user with the CREATEROLE attribute. In separation-of-duties scenarios, a role can be created, modified, and deleted only by an initial user or a user with the CREATEROLE attribute.

  • To create a role, use CREATE ROLE.
  • To add or delete users in an existing role, use ALTER ROLE.
  • To delete a role, use DROP ROLE. DROP ROLE deletes only a role, rather than member users in the role.

Built-in Roles

GaussDB provides a group of default roles whose names start with gs_role_. These roles are provided to access to specific, typically high-privileged operations. You can grant these roles to other users or roles within the database so that they can use specific functions. These roles should be given with great care to ensure that they are used where they are needed. Table 1 describes the permissions of built-in roles.

Table 1 Permission description of built-in roles

Roles

Permission

gs_role_copy_files

Permission to run the copy... to/from filename command. However, the GUC parameter enable_copy_server_files must be set first to enable the function of copying server files.

gs_role_signal_backend

Permission to call the pg_cancel_backend, pg_terminate_backend, and pg_terminate_session functions to cancel or terminate other sessions. However, this role cannot perform operations on sessions of the initial user or PERSISTENCE user.

gs_role_tablespace

Permission to create a tablespace.

gs_role_replication

Permission to call logical replication functions, such as kill_snapshot, pg_create_logical_replication_slot, pg_create_physical_replication_slot, pg_drop_replication_slot, pg_replication_slot_advance, pg_create_physical_replication_slot_extern, pg_logical_slot_get_changes, pg_logical_slot_peek_changes, pg_logical_slot_get_binary_changes, and pg_logical_slot_peek_binary_changes.

gs_role_account_lock

Permission to lock and unlock users. However, this role cannot lock or unlock the initial user or PERSISTENCE user.

gs_role_pldebugger

Permission to debug functions in dbe_pldebugger.

gs_role_directory_create

Permission to create directory objects. However, this role needs to enable the GUC parameter enable_access_server_directory first.

gs_role_directory_drop

Permission to delete directory objects. However, this role needs to enable the GUC parameter enable_access_server_directory first.
The restrictions on built-in roles are as follows:
  • The role names starting with gs_role_ are reserved for built-in roles in the database. Do not create users or roles starting with gs_role_ or rename existing users or roles starting with gs_role_.
  • Do not perform ALTER or DROP operations on built-in roles.
  • By default, built-in roles do not have the LOGIN permission and do not have preset passwords.
  • The gsql meta-commands \du and \dg do not display information about built-in roles. However, if pattern is set to a specific built-in role, the information is displayed.
  • When separation-of-duty is disabled, the initial user, users with the SYSADMIN permission, and users with the ADMIN OPTION built-in role permission have the permission to perform GRANT and REVOKE operations on built-in roles. When separation of duty is enabled, the initial user and users with the ADMIN OPTION built-in role permission have the permission to perform GRANT and REVOKE operations on built-in roles. Example: 
    1
2
    		 
    GRANT gs_role_signal_backend TO user1;
REVOKE gs_role_signal_backend FROM user1;