Help Center/ GaussDB/ Developer Guide(Distributed_3.x)/ Database Security/ Users and Permissions/ Roles
Updated on 2024-05-07 GMT+08:00
View PDF
Share

Roles

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 scenarios other than separation of duties, 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 Built-in roles and their permissions

Roles

Permission

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, or to call the pg_terminate_active_session_socket function to close active sessions and the socket connection of the client. 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 users with the PERSISTENCE attribute.

gs_role_pldebugger

Permission to debug functions in dbe_pldebugger.

gs_role_public_dblink_drop

Permission to delete public database links.

gs_role_public_dblink_alter

Permission to modify public database links.
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, roles, or schemas starting with gs_role_ or rename existing users, roles, or schemas to names 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 duties 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-duties 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;
Parent topic: Users and Permissions