Best Practices for Permission Configuration

Context

A database may be used by many users, and users are grouped into a database role for easy management. A database role can be regarded as one or a group of database users.

For databases, users and roles are basically the same. The difference is that when CREATE ROLE is used to create a role, no schema with the same name is created and the user does not have the LOGIN permission by default. When CREATE USER is used to create a user, a schema with the same name is automatically created. By default, the user has the LOGIN permission. That is, a role with the LOGIN permission can be considered to be a user. In service design, you are advised to use a role to manage permissions rather than accessing databases.

Overview

Improper permission configuration may cause permission exploitation. This section describes the functions of each permission role.

Solution

Database user

Database users are used to connect databases, access database objects, and run SQL statements. Only an existing database user can be used to connect databases. Therefore, a database administrator must plan a database user for each user who wants to connect to a database.

Specify at least the following attributes for a database user:

By default, database users can be classified into two types, as listed in Table 1.

**Table 1** User types
Type	Description
Initial user	Has the highest-level database rights, that is, has all system and object permissions. Initial users are not affected by the settings of the object permissions. This is comparable to the permissions of root in a Unix system. For security purposes, you are advised not to operate as an initial user unless necessary. When installing or initializing a database, you can specify the initial username and password. If you do not specify the username, an initial user with the same name as the OS user who installs the database is automatically generated. If no password is specified, the initial user password is empty after the installation. You need to set the initial user password on the GSQL client before performing other operations. Note: For security purposes, remote login to GaussDB Kernel in trust mode is prohibited for all users, and remote login in any mode is prohibited for the initial user.
Common user	By default, a user can access the default database system catalogs (excluding pg_authid, pg_largeobject, pg_user_status, and pg_auth_history) and views and connect to the default database postgres, as well as the objects in the public schema, including tables, views, and functions. You can run CREATE USER and ALTER USER to specify system permissions, or run GRANT ALL PRIVILEGE to grant the SYSADMIN permission. You can run the GRANT statement to assign object permissions to a common user. The user can run the GRANT statement to assign other user permissions to a common user.

Database permission types

Permissions and roles work together to specify accessible data and executable SQL statements. For details, see Table 2.

System permissions are specified by using the CREATE USER/ALTER USER and CREATE ROLE/ALTER ROLE statements and cannot be inherited from roles. The SYSADMIN permission can be granted or revoked by using the GRANT/REVOKE ALL PRIVILEGES statement.

**Table 2** Permission types
Type	Description
System permission	System permissions are also regarded as user attributes, which can be specified when a user is being created or modified. System permissions include SYSADMIN, MONADMIN, OPRADMIN, POLADMIN, CREATEDB, CREATEROLE, AUDITADMIN, and LOGIN. They can be specified only by the CREATE USER or ALTER USER statement. System permissions except SYSADMIN, cannot be granted or revoked by the GRANT or REVOKE statement. In addition, system permissions cannot be inherited from roles.
Object permission	Object permissions are operation permissions for tables, views, indexes, sequences, and functions. These permissions include SELECT, INSERT, UPDATE, and DELETE. Only an object owner or system administrator can use the GRANT/REVOKE statement to grant or revoke object permissions.
Roles	A role is a group of permissions. If a role consists of system permissions, these permissions cannot be granted to other users or roles. If a role consists of object permissions, these permissions can be granted to other users or roles.

Database permission model

System permission model
- Default permission mechanism
  Figure 1 Permission architecture
  
  Figure 1 shows the permission architecture. In the default permission mechanism, the sysadmin user has most permissions.
  - Initial installation user: an account automatically generated during cluster installation. This account has the highest permissions in the system and can perform all operations.
  - SYSADMIN: system administrator permissions, which are only inferior to those of the initial installation user. By default, the system administrator has the same permissions as the object owner, excluding the permissions of the monitor administrator and O&M administrator.
  - MONADMIN: monitor administrator permissions, including the permissions to access and grant views and functions in the monitor schema DBE_PERF.
  - OPRADMIN: O&M administrator permissions, including the permission to use Roach to perform backup and restoration
  - CREATEROLE: security administrator permissions, including the permissions to create, modify, and delete users and roles
  - AUDITADMIN: audit administrator permissions, including the permissions to view and maintain database audit logs
  - CREATEDB: permission to create databases.
  - POLADMIN: security policy administrator permissions, including the permissions to create resource labels, dynamic data masking policies, and unified audit policies.
- Separation of duties
  Figure 2 Separation of duties
  - SYSADMIN: system administrator permission. The user with this attribute no longer has the permissions to create, modify, delete users or roles, or view or maintain database audit logs.
  - CREATEROLE: security administrator permissions, including the permissions to create, modify, and delete users and roles
  - AUDITADMIN: audit administrator permissions, including the permissions to view and maintain database audit logs
  - A user or role can have only one of the system permissions SYSADMIN, CREATEROLE, and AUDITADMIN.
Object permission model
- Object permissions refer to the permissions to perform operations for database objects (such as databases, schemas, and tables), including SELECT, INSERT, UPDATE, DELETE, and CONNECT.
- The permissions vary by object. Object permissions can be granted to users or roles.
- You can use GRANT or REVOKE to grant permissions to a user or revoke them from the user. Object permissions can be inherited by a role.

Role permission model

GaussDB Kernel 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 3 describes the permissions of built-in roles.

**Table 3** Permissions of built-in roles
Role	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 users with the PERSISTENCE attribute.
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_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.

System permission configuration
- Configuring the default permission mechanism
  - Initial user
    The account automatically generated during database installation is called an initial user. The initial user is also the system administrator, monitor administrator, O&M administrator, and security policy administrator. It has the highest permissions in the system and can perform all operations. If the initial username is not specified during installation, the username is the same as the name of the OS user who installs the database. If the password of the initial user is not specified during the installation, the password is empty after the installation. In this case, you need to change the password of the initial user on the gsql client before performing other operations. If the initial user password is empty, you cannot perform other SQL operations, such as upgrade, capacity expansion, and node replacement, except changing the password.
    
    An initial user bypasses all permission checks. You are advised to use an initial user as a database administrator only for database management other than service running.
  - System administrator
```
gaussdb=#CREATE USER u_sysadmin WITH SYSADMIN password '********';
-- Alternatively, run the following SQL statement when the user already exists:
gaussdb=#ALTER USER u_sysadmin01 SYSADMIN;
```
  - Monitor administrator
```
gaussdb=#CREATE USER u_monadmin WITH MONADMIN password '********';
-- Alternatively, run the following SQL statement when the user already exists:
gaussdb=#ALTER USER u_monadmin01 MONADMIN;
```
  - O&M administrator
```
gaussdb=#CREATE USER u_opradmin WITH OPRADMIN password "xxxxxxxxx";
-- Alternatively, run the following SQL statement when the user already exists:
gaussdb=#ALTER USER u_opradmin01 OPRADMIN;
```
  - Security policy administrator
```
gaussdb=#CREATE USER u_poladmin WITH POLADMIN password "xxxxxxxxx";
-- Alternatively, run the following SQL statement when the user already exists:
gaussdb=#ALTER USER u_poladmin01 POLADMIN;
```
- Configuring the separation of duties
  To configure this mode, you need to set the GUC parameter enableSeparationOfDuty to on. This is a POSTMASTER parameter. After this parameter is modified, you need to restart the database.
```
gs_guc set -Z datanode -N all -I all -c "enableSeparationOfDuty=on"
gs_om -t stop
gs_om -t start
```
  The syntax for creating and configuring user permissions is the same as that for default permissions.

Role permission configuration

-- Create the database test.
gaussdb=#CREATE DATABASE test;
-- Create role1 and user1.
gaussdb=#CREATE ROLE role1 PASSWORD '********';
gaussdb=#CREATE USER user1 PASSWORD '********';
-- Grant the CREATE ANY TABLE permission to role1.
gaussdb=#GRANT CREATE ON DATABASE test TO role1;

-- If role1 is assigned to user1, user1 belongs to group role1 and inherits the permissions of role1 to create schemas in the database test.
gaussdb=#GRANT role1 TO user1;

-- Query user and role information.
gaussdb=#\du role1|user1;
            List of roles
 Role name |  Attributes  | Member of 
-----------+--------------+-----------
 role1     | Cannot login | {}
 user1     |              | {role1}