How to quickly and easily switch between different permission models - Hologres

Hologres provides three permission models: the standard PostgreSQL authorization model, the simple permission model (SPM), and the schema-level permission model (SLPM). You can choose a model based on your business needs. However, you may need to switch between models while using an instance. This topic describes how to quickly and easily switch between these permission models.

View the current permission model of a database

Use one of the following methods to view the permission model of the current database.

HoloWeb

In HoloWeb, view the current permission model on the DB Authorization page of the Security Center module. For more information, see DB Management.

SQL statement

Use the following commands to check whether SPM or SLPM is enabled for the database.

--Check whether SPM is enabled.
SHOW hg_experimental_enable_spm;

--Check whether SLPM is enabled.
SHOW hg_enable_slpm;

Switch from the simple permission model (SPM) to the standard PostgreSQL authorization model

Notes
- Only a superuser can disable the simple permission model.
- The public role has USAGE and CREATE permissions on the public schema.
- The public role has CONNECT and TEMPORARY permissions on the DB.
- The public role has EXECUTE permissions on functions and procedures.
- The public role has USAGE permissions on language, data types (include domains).
- The public role does not have permissions on other objects, such as tables, views, materialized views, table columns, sequences, foreign data wrappers, foreign servers, or schemas (except for the public schema).
- After you disable the simple permission model, the permissions of user groups are as follows.
  - admin: Retains permissions on existing objects, but these permissions do not apply to new database objects.
  - developer user group: Retains permissions on existing objects, but these permissions do not apply to new database objects.
  - writer user group: Retains permissions on existing objects, but these permissions do not apply to new database objects.
  - viewer user group: Retains permissions on existing objects, but these permissions do not apply to new database objects.
Switch the permission model
Execute the following SQL command to disable the simple permission model. The database then uses the standard PostgreSQL authorization model.
```
--Disable the simple model.
CALL spm_disable ();
--Clean up user groups (optional).
CALL spm_cleanup ('dbname');
```
Note
The corresponding user groups are not deleted after you disable the simple permission model. For easier management, do not delete the user groups. To clean up the user groups, make sure that no SQL statements are running on the DB. Otherwise, the cleanup may fail and affect your service.

Switch from the standard PostgreSQL authorization model to the simple permission model (SPM)

Use the following commands to switch from the standard PostgreSQL authorization model to the simple permission model.

-- Enable the simple permission model for the current DB.
CALL spm_enable ();
-- Update the owner of existing objects in the DB to 'developer' to be managed by SPM.
CALL spm_migrate ();

If the error ERROR: cannot enable Simple Privilege Model for db=[xxxxxx] because roles conflict is reported, it indicates that SPM was previously enabled and the system roles still exist. In this case, enable SPM in recovery mode by executing the following command.

--Enable the simple permission model again.
CALL spm_enable ('t');

--Update the owner of existing objects in the DB to 'developer' to be managed by SPM.
CALL spm_migrate ();

Note

If you switch to SPM, a new account may report a permission error even if it has the required permissions. This indicates that the previous object migration was incomplete. Execute the call spm_migrate (); command multiple times.

Switch from the simple permission model (SPM) to the schema-level permission model (SLPM)

There is no direct method to switch from SPM to SLPM. You must first switch from SPM to the standard PostgreSQL authorization model, and then switch from the standard PostgreSQL authorization model to SLPM. The commands are as follows.

-- Disable SPM to revert to the standard model.
CALL spm_disable ();
-- Clean up SPM system roles.
CALL spm_cleanup ('dbname');
-- Enable schema-level permissions.
CALL slpm_enable ();
-- Update the owner of existing objects in the DB to 'developer' to be managed by SLPM. This step is required.
CALL slpm_migrate ();

If the error cannot enable slpm for database xxxxx because roles conflict. is reported, it indicates that SLPM was previously enabled and the system roles still exist. In this case, enable SLPM in recovery mode by executing the following commands.

-- Disable SPM.
CALL spm_disable ();
-- Clean up SPM system roles.
CALL spm_cleanup ('dbname');
-- Enable schema-level permissions (enable SLPM in recovery model).
CALL slpm_enable ('t');
-- Update the owner of existing objects in the DB to 'developer' to be managed by SLPM. This step is required.
CALL slpm_migrate ();

Note

If you switch to SLPM, a new account may report a permission error even if it has the required permissions. This indicates that the previous object migration was incomplete. Execute the call slpm_migrate (); command multiple times.

Switch from the schema-level permission model (SLPM) to the simple permission model (SPM)

There is no direct method to switch from SLPM to SPM. You must first switch from SLPM to the standard PostgreSQL authorization model, and then switch from the standard PostgreSQL authorization model to SPM. The commands are as follows.

-- Disable SLPM.
CALL slpm_disable ();
-- Clean up SLPM system roles.
CALL slpm_cleanup ('dbname');
-- Enable the simple permission model for the current DB.
CALL spm_enable ();
-- Update the owner of existing objects in the DB to 'developer' to be managed by SPM.
CALL spm_migrate ();

If the error cannot enable spm for database xxxxx because roles conflict. is reported, it indicates that SPM was previously enabled and the system roles still exist. In this case, enable SPM in recovery mode by executing the following commands.

-- Disable SLPM.
CALL slpm_disable ();
-- Clean up SLPM system roles.
CALL slpm_cleanup ('dbname');
-- Enable SPM (enable SPM in recovery model).
CALL spm_enable ('t')
-- Update the owner of existing objects in the DB to 'developer' to be managed by SPM.
CALL spm_migrate ();

Note