This topic describes how to use the schema-level permission model (SLPM) in Hologres by executing SQL statements.

Limits on the SLPM

The SLPM manages permissions on schemas in a strict manner. When you enable this model to grant permissions, take note of the following limits:
  • 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 the rename schema statement. slpm_rename_schema
    rename database To rename a database, you must call the slpm_rename_database function but not execute the rename database statement. slpm_rename_database
    drop database To delete a database, you must execute the drop database statement and call the slpm_cleanup('dbName') function to delete default users. To delete a database, you must execute the drop database statement and call the slpm_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.

  1. Enable function calls.
    Before you enable the SLPM, you must execute the following statement to enable function calls: In this example, the database-level create extension statement needs to be executed only once in a database.
    create extension slpm;
  2. Enable the SLPM.
    By default, the SLPM is disabled. You can execute the following statement to enable the SLPM for a database: When you enable the SLPM for a database, make sure that no SQL statement is being executed in the database. Otherwise, you may fail to enable the SLPM and the business may be affected.
    call slpm_enable (); // Enable the SLPM for the current database.
    Note After you enable the SLPM for a database, add all the database users to user groups. Otherwise, the users cannot access the database and the business may be affected. For more information, see Add a user to a user group.
  3. Optional:Switch from the standard PostgreSQL authorization model to the SLPM for existing objects.
    You can log on to the Hologres console, go to the details page of an instance, and then view the permission model that is enabled for the database on the Databases tab. If the database uses the standard PostgreSQL authorization model and the database contains objects such as tables, views, and foreign tables, you can call the slpm_migrate function to switch from standard PostgreSQL authorization model to the SLPM for these objects.
    call slpm_migrate (); // Transfer the ownership of existing objects in the database to the developers that are specified in the SLPM.
    When you switch from the standard PostgreSQL authorization model to the SLPM for these objects, take note of the following information:

    By default, you can transfer the ownership of a maximum of 64 objects in the database to the developers that are specified in the SLPM each time you call the slpm_migrate function. You can modify the batch_size parameter in this function. If the number of objects exceeds 64, you must repeat calling the slpm_migrate function until the ownership of all objects is transferred. For more information about this function, see slpm_migrate.

  4. Create a user in the Hologres instance.
    Before you authorize a user, you must create the user in the Hologres instance. If you need to authorize an existing user in the instance, skip this step.

    In the following statements, 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_create_user ('Alibaba Cloud account ID/Alibaba Mail address/RAM user UID'); // Create a user in the Hologres instance. For an Alibaba Mail address, enclose the Alibaba Mail address in double quotation marks (" ").
call slpm_create_user ('Alibaba Cloud account ID/Alibaba Mail address/RAM user UID', '{dbname}.[admin|{schemaname}.developer|{schemaname}.writer|{schemaname}.viewer]');  // Create a user in the Hologres instance and add the user to a user group of a database.
    Note
    • When you call the slpm_create_user function to add a RAM user to a Hologres instance, prefix the UID of the RAM user with p4_ and enclose it in double quotation marks (" "), such as "p4_uid". To obtain the UID of the RAM user, go to the Users page in the RAM console. For more information about the formats of display names for RAM users in Hologres, see Account overview.
  5. Add a user to a user group.
    After you create a user in the Hologres instance, add the user to a user group in a database to authorize the user. If you have specified a user group for a user when you create the user, skip this step.

    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_grant ('{dbname}.[admin|{schemaname}.developer|{schemaname}.writer|{schemaname}.viewer]', 'Alibaba Cloud account ID/Alibaba Mail address/RAM user UID'); // Add a user to a user group.
    You can execute one of the following statements to add a user to a specified user group:
    // Add a user to the {db}.admin group of a database.
call slpm_grant ('mydb.admin', '197006222995xxx', ); // Add the user whose Alibaba Cloud account ID is 197006222995xxx to the mydb.admin group.
call slpm_grant ('mydb.admin', 'ALIYUN$xxx'); // Add the user whose Alibaba Mail address is xxx@aliyun.com to the mydb.admin group.

// Add a user to the {db}.{schema}.developer group of a database.
call slpm_grant ('mydb.public.developer', '197006222995xxx'); // Add the user whose Alibaba Cloud account ID is 197006222995xxx to the mydb.public.developer group.
call slpm_grant ('mydb.public.developer', 'RAM$mainaccount:subuser');// Add the RAM user subuser of the Alibaba Cloud account mainaccount to the mydb.public.developer group.

// Add a user to the {db}.{schema}.viewer group of a database.
call slpm_grant ('"MYDB.lisa.viewer"', '197006222995xxx'); // Add the user whose Alibaba Cloud account ID is 197006222995xxx to the MYDB.lisa.viewer group.
call slpm_grant ('mydb.lisa.viewer', '"xxx@aliyun.com"'); // Add the user whose Alibaba Mail address is xxx@aliyun.com to the mydb.lisa.viewer group.

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.
You can execute one of the following statements to remove a user from a specified user group:
// 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

You can delete a user as needed. After a user is deleted from a Hologres instance, the user has no permission on the instance. Proceed with caution.
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.

  1. Disable the SLPM.
    After the SLPM is enabled, a superuser can execute the following statement to call the slpm_disable function to disable the SLPM for a database. The user groups of a database are not deleted if a superuser disables the SLPM for the database. For more information about the permissions of the members in the user groups, see Functions of the SLPM.
    call slpm_disable ();
    When you disable the SLPM, take note of the following items:
    • Only a superuser can disable the SLPM for a database.
    • After the SLPM is disabled, the PUBLIC group is granted the USAGE and CREATE permissions on the public schema of the database, the CONNECT and TEMPORARY permissions on the database, the EXECUTE permission on the functions and procedures of the database, and the USAGE permission on the languages and data types (including domains) of the database.
    • After the SLPM 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, and schemas (excluding the public schema). Contact the superuser to obtain these permissions.
    • After the SLPM is disabled, members in the {db}.admin, {db}.{schema}.developer, {db}.{schema}.writer, and {db}.{schema}.viewer groups retain the obtained permissions on existing objects and have no permission on new objects.
  2. Delete the user groups of a database. Generally, we recommend that you retain user groups to facilitate user management.
    After the SLPM is disabled, you can call the slpm_cleanup function to delete the user groups of a database in one of the following cases:
    • Case 1: Delete user groups of an existing database
      To delete user groups but retain the database, execute the following statement as a superuser:
      call slpm_cleanup ( '<dbname>' );
      Note When you call the slpm_cleanup function, make sure that no SQL statement is being executed in the database. Otherwise, the function may fail and the business may be affected.

      The slpm_cleanup function transfers the ownership of objects to the current user. However, this function transfers the ownership of a maximum of 64 objects each time you call this function. You can modify the batch_size parameter in this function. As a result, you may need to repeat calling the slpm_cleanup function until the ownership of all objects is transferred and all user groups of the database are deleted. We recommend that you repeat calling this function no more than five times. For more information about this function, see slpm_cleanup.

    • Case 2: Delete user groups of a deleted database
      To delete user groups of a deleted database, execute the following statement in another database such as postgres as a superuser:
      postgres=# call slpm_cleanup ( 'mydb' );