Updated on 2024-06-03 GMT+08:00

Separation of Duties

Descriptions in Default Permission Mechanism and Administrators are about the initial situation after the database system is created. By default, the system administrator with the SYSADMIN attribute has the highest permission in the system.

To avoid risks caused by centralized permissions, you can enable separation of duties to assign the system administrator's user creation permission to security administrators and audit management permission to audit administrators.

After separation of duties, the system administrator does not have the CREATEROLE attribute (security administrator) or the AUDITADMIN attribute (audit administrator). That is, the system administrator can neither create roles or users, nor view or maintain database audit logs. For details about the CREATEROLE and AUDITADMIN attributes, see CREATE ROLE.

Separation of duties does not take effect for an initial user. Therefore, you are advised to use an initial user as a database administrator only for database management other than service running.

To enable separation of duties, set the GUC parameter enableSeparationOfDuty to on.

If you need to use the separation of duties model, specify it during database initialization. You are advised not to switch the permission management model back and forth. In particular, if you want to switch from a non-separation-of-duties permission management model to the separation-of-duties permission management model, you need to review the permission set of existing users. If a user has the system administrator permission and audit administrator permission, the permissions need to be tailored.

After separation of duties, the system administrator does not have permissions for non-system schemas of other users. Therefore, the system administrator cannot access the objects in other users' schemas before being granted the permissions. For details about permission changes before and after enabling separation of duties, see Table 1 and Table 2.

Table 1 Default user permissions

Object Name

Initial User (ID: 10)

System Administrator

Security Administrator

Audit Administrator

Common User

Tablespaces

Has all permissions.

Can create, modify, delete, access, or grant permissions for tablespaces.

Cannot create, modify, delete, or grant permissions for tablespaces and can access tablespaces if the access permission is granted.

Schemas

Has all permissions for all schemas except dbe_perf.

Has all permissions for their own schemas, but does not have permissions for non-system schemas of other users.

User-defined functions

Has all permissions for all user-defined functions.

Has all permissions for their own functions, and has only the call permission for other users' functions.

User-defined tables or views

Has all permissions for all user-defined tables or views.

Has all permissions for their own tables or views, but does not have permissions for other users' tables or views.

Table 2 Changes in permissions after separation of duties

Object Name

Initial User (ID: 10)

System Administrator

Security Administrator

Audit Administrator

Common User

Tablespaces

N/A

Has all permissions.

N/A

N/A

Schemas

Permissions reduced

Has all permissions for their own schemas, but does not have permissions for non-system schemas of other users.

N/A

User-defined functions

Cannot access functions in non-system schemas of other users before being granted the permissions.

N/A

User-defined tables or views

Cannot access tables or views in non-system schemas of other users before being granted the permissions.

N/A

PG_STATISTIC and PG_STATISTIC_EXT store sensitive information about statistical objects, such as high-frequency MCVs. After separation of duties is enabled, the system administrator can still access the two system catalogs to obtain sensitive information in the statistics.