Switch between permission models - Hologres - Alibaba Cloud Documentation Center

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 the permission model that best suits your business requirements. However, you may find the need to switch between permission models for your Hologres instances. This topic describes how to switch between permission models.

Check the permission model of a database

You can use one of the following methods to check the permission model of a database:

Use the HoloWeb console
Check the permission model of a database on the Database Authorization page of the Security Center tab in the HoloWeb console. For more information, see Manage databases.

Use SQL statements

Execute one of the following statements to check whether the database has the SPM or SLPM enabled:

--Check whether the SPM is enabled.
show hg_experimental_enable_spm;

--Check whether the SLPM is enabled.
show hg_enable_slpm;

Switch from the SPM to the standard PostgreSQL authorization model

Usage notes
- The SPM can be disabled for a database only by a superuser.
- After the SPM is disabled, the PUBLIC group is granted the USAGE and CREATE permissions on the public schema of the database.
- After the SPM is disabled, the PUBLIC group is granted the CONNECT and TEMPORARY permissions on the database.
- After the SPM is disabled, the PUBLIC group is granted the EXECUTE permission on the functions and procedures of the database.
- After the SPM is disabled, the PUBLIC group is granted the USAGE permission on the languages and data types (including domains) of the database.
- After the SPM is disabled, the PUBLIC group has no permission on the objects other than the preceding ones. For example, the PUBLIC group has no permission on tables, views, materialized views, columns, sequences, foreign data wrappers, foreign servers, or schemas (excluding the public schema).
- After the SPM is disabled, the <db>_admin, <db>_developer, <db>_writer, and <db>_viewer groups have the following permissions:
  - <db>_admin: retains obtained permissions on existing objects and has no permission on new objects.
  - <db>_developer: retains obtained permissions on existing objects and has no permission on new objects.
  - <db>_writer: retains obtained permissions on existing objects and has no permission on new objects.
  - <db>_viewer: retains obtained permissions on existing objects and has no permission on new objects.
Switch from the SPM to the standard PostgreSQL authorization model
Execute the following SQL statement to disable the SPM. After the SPM is disabled, the permission model is switched to the standard PostgreSQL authorization model.
```
--Disable the SPM.
call spm_disable ();
--Optional. Delete user groups.
call spm_cleanup ('dbname');
```
Note
After the SPM is disabled, corresponding user groups are not deleted. To facilitate management, we recommend that you do not delete user groups. If you want to delete user groups, make sure that no SQL statement is being executed in the database. Otherwise, the user groups may fail to be deleted and the service may be affected.

Switch from the standard PostgreSQL authorization model to the SPM

Execute the following statements to switch from the standard PostgreSQL authorization model to the SPM:

--Enable the SPM for the database.
call spm_enable ();
--Transfer the ownership of existing objects in the database to the developers that are specified in the SPM. 
call spm_migrate ();

If the database has the SPM enabled and the system roles in the SPM exist, the ERROR: cannot enable Simple Privilege Model for db=[xxxxxx] because roles conflict error is reported. In this case, execute the following statements to enable the SPM in the recovery model:

--Enable the SPM again.
call spm_enable ('t');

--Transfer the ownership of existing objects in the database to the developers that are specified in the SPM. 
call spm_migrate ();

Note

If the ownership of existing objects in a database is not fully transferred after the permission model is switched to the SPM, an error of no permissions may be reported for new users even if the new users have the required permissions. You must repeatedly execute the call spm_migrate (); statement until the ownership of all objects is transferred.

Switch from the SPM to the SLPM

The permission model of a database cannot be directly switched from the SPM to the SLPM. You must execute the following statements to switch the permission model from the SPM to the standard PostgreSQL authorization model and then from the standard PostgreSQL authorization model to the SPM:

--Disable the SPM to switch the permission model from the SPM to the standard PostgreSQL authorization model.
call spm_disable ();
--Delete the system roles in the SPM.
call spm_cleanup ('dbname');
--Enable the SLPM.
call slpm_enable ();
--Transfer the ownership of existing objects in the database to the developers that are specified in the SLPM. This statement must be executed.
call slpm_migrate ();

If the database has the SLPM enabled and the system roles in the SLPM exist, the ERROR: cannot enable Simple Privilege Model for db=[xxxxxx] because roles conflict error is reported. In this case, execute the following statements to enable the SLPM in the recovery model:

--Disable the SPM.
call spm_disable ();
--Delete the system roles in the SPM.
call spm_cleanup ('dbname');
--Enable the SLPM in the recovery model.
call slpm_enable ('t');
--Transfer the ownership of existing objects in the database to the developers that are specified in the SLPM. This statement must be executed.
call slpm_migrate ();

Note

If the ownership of existing objects in a database is not fully transferred after the permission model is switched to the SLPM, an error of no permissions may be reported for new users even if the new users have the required permissions. You must repeatedly execute the call slpm_migrate (); statement until the ownership of all objects is transferred.

Switch from the SLPM to the SPM

The permission model of a database cannot be directly switched from the SLPM to the SPM. You must execute the following statements to switch the permission model from the SLPM to the standard PostgreSQL authorization model and then from the standard PostgreSQL authorization model to the SPM:

--Disable the SLPM.
call slpm_disable ();
--Delete the system roles in the SLPM.
call slpm_cleanup ('dbname');
--Enable the SPM for the database.
call spm_enable ();
--Transfer the ownership of existing objects in the database to the developers that are specified in the SPM. 
call spm_migrate ();

--Disable the SLPM.
call slpm_disable ();
--Delete the system roles in the SLPM.
call slpm_cleanup ('dbname');
--Enable the SPM in the recovery model.
call spm_enable ('t')
--Transfer the ownership of existing objects in the database to the developers that are specified in the SPM. 
call spm_migrate ();

Note