This topic describes how to use the schema-level permission model (SLPM) in Hologres by executing SQL statements.
Limits on the SLPM
- Permissions are not shared among schemas. Assume that you reference two or more tables in different schemas when you create a view or a rule. In this case, the view or rule that you create cannot be accessed. We recommend that you do not create a view or rule by using tables from different schemas in a database for which the SLPM is enabled.
- After the SLPM is enabled, only specific permissions can be granted. For more information
about the specific permissions, see Grant permissions by using the SLPM. After you enable the SLPM for a database, you cannot perform operations by executing
data definition language (DDL) statements described in the following table. You must
perform relevant operations by calling the SLPM functions that correspond to the DDL
statements. For more information about the functions, see Functions of the SLPM.
DDL statement Description SLPM function alter table owner to xx After you enable the SLPM for a database, the ownership of all tables in a schema of the database is transferred to the members of the {db}.{schema}.developer group. You are not allowed to transfer the ownership of these tables. The ownership of tables is automatically transferred. grant After you add a user to a user group, the user is automatically granted permissions of the user group. In this case, you do not need to call the slpm_grant function to grant the user permissions of the user group. slpm_grant revoke You cannot call the slpm_revoke function to revoke a specific permission from a user. Instead, you must remove the user from the specific user group. slpm_revoke alter default privileges In the standard PostgreSQL authorization model, you can grant users the permissions only on existing tables and objects. To enable users to manage new tables and objects, you must grant them permissions again. In the SLPM, you can add users to user groups to grant them the required permissions on all tables or objects of the specified types, including the existing ones and new ones. Users are granted the required permissions when they are added to user groups. create/drop/alter/rename default user groups After the SLPM is enabled, the {db}.admin, {db}.{schema}.developer, {db}.{schema}.writer, and {db}.{schema}.viewer groups are generated by the system. All users including a superuser are not allowed to create, modify, or delete the user groups. N/A rename schema To rename a schema, you must call the slpm_rename_schema
function in a database but not execute therename schema
statement.slpm_rename_schema rename database To rename a database, you must call the slpm_rename_database
function but not execute therename database
statement.slpm_rename_database drop database To delete a database, you must execute the drop database
statement and call theslpm_cleanup('dbName')
function to delete default users.To delete a database, you must execute the drop database
statement and call theslpm_cleanup('dbName')
function to delete default users.
Grant permissions by using the SLPM
After you connect a Hologres instance to a development tool, you can enable the SLPM to grant users permissions by executing SQL statements. This way, the users are granted the required permissions on schemas.
Remove a user from a user group
To remove a user from a user group, execute the following statement. After a user is removed, the user has no permission of the user group.
In the following statement, the {dbname}.[admin|{schemaname}.developer|{schemaname}.writer|{schemaname}.viewer] clause is used to specify a user group in the current database. For more information, see Overview.
call slpm_revoke ('{dbname}.[admin|{schemaname}.developer|{schemaname}.writer|{schemaname}.viewer]', 'Alibaba Cloud account ID/Alibaba Mail address/RAM user UID'); // Revoke permissions from a user.
// Remove a user from the {db}.admin group of a database.
call slpm_revoke ('dbname.admin', 'p4_564306222995xxx');// Remove the RAM user whose UID is 564306222995xxx from the {db}.admin group.
call slpm_revoke ('dbname.admin', '197006222995xxx');// Remove the user whose Alibaba Cloud account ID is 197006222995xxx from the {db}.admin group.
call slpm_revoke ('dbname.admin', '"xxx@aliyun.com"');
// Remove a user from the {db}.{schema}.developer group of a database.
call slpm_revoke ('mydb.lisa.developer', 'RAM$mainaccount:subuser'); // Remove the RAM user subuser of the Alibaba Cloud account mainaccount from the mydb.lisa.developer group.
call slpm_revoke ('mydb.public.developer', 'p4_564306222995xxx');// Remove the RAM user whose UID is 564306222995xxx from the mydb.public.developer group.
// Remove a user from the {db}.{schema}.viewer group of a database.
call slpm_revoke ('"MYDB.SCHEMA1.viewer"', 'p4_564306222995xxx'); // Remove the RAM user whose UID is 564306222995xxx from the MYDB.SCHEMA1.viewer group.
Delete a user from a Hologres instance
DROP ROLE "Alibaba Cloud account ID/Alibaba Mail address/RAM user UID"; // Delete a user from a Hologres instance.
Disable the SLPM
When you no longer need the SLPM, you can perform the following operations to disable it.