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

Limits

The SLPM manages permissions on schemas in a strict manner. When you use this model to grant permissions, take note of the following limits:
  • Permissions are not shared between schemas. If you reference two or more tables in different schemas when you create a view or a rule, the created view or rule 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 the "Grant permissions by using the SLPM" section of this topic. 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 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. 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 only the permissions on existing tables and objects. To enable users to manage objects and tables created in the future, 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 objects and tables of these types created in the future. 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 rather than executing the alter rename schema statement. slpm_rename_schema
    rename database To rename a database, you must call the slpm_rename_database function rather than executing the alter rename database statement. slpm_rename_database
    drop database To delete a database, you must execute the drop database statement and then call the slpm_cleanup('dbName') function to delete default users. To delete a database, you must execute the drop database statement and then call the slpm_cleanup('dbName') function to delete default users.

Grant permissions by using the SLPM

After you use a development tool to connect to a Hologres instance, 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 create extension statement is executed only once in a database.
    create extension slpm;
  2. Enable the SLPM.
    By default, the SLPM is disabled. You must 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, the SLPM cannot be enabled and the service may be affected.
    call slpm_enable (); // Enable the SLPM for the database. 
  3. Optional:Switch from the standard PostgreSQL authorization model to SLPM for existing objects.
    If your database uses the standard PostgreSQL authorization model and contains objects such as tables, views, and foreign tables, you can call the slpm_migrate function to switch from the standard PostgreSQL authorization model to 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 SLPM for these objects, take note of the following information:

    By default, each time you call the slpm_migrate function, you can transfer the ownership of up to database 64 objects to the developers that are specified in the SLPM. 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 grant permissions to a user, you must create the user in the Hologres instance. If you grant permissions to 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 about user groups, see Overview.

    call slpm_create_user ('Alibaba Cloud account ID/Alibaba Mail address/RAM user UID'); // Create a user in the Hologres instance. Alibaba Mail addresses must be enclosed 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 in a database. 
    Note
    • When you call the slpm_create_user function to add a RAM user to a Hologres instance, you must prefix the UID of the RAM user with p4_. Example: p4_uid. You can go to the Users page in the RAM console to obtain the UID of the RAM user. For more information about the display names of RAM users in Hologres, see Accounts.
  5. Add a user to a user group.
    After you create a user in the Hologres instance, you must add the user to a user group in a database to grant permissions to the user. If you have already specified a user group for a user when you create the user, 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 about user groups, 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 named subuser of the Alibaba Cloud account named 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

You can execute the following statement to remove a user from a user group. After a user is removed from a user group, 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 about user groups, 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 named subuser of the Alibaba Cloud account named 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. If a superuser disables the SLPM for a database, the user groups of the database are not deleted. 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, or 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. To facilitate user management, we recommend that you retain user groups.
    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 user groups cannot be deleted and the service 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 as a superuser. In this example, execute the statement in the postgres database.
      postgres=# call slpm_cleanup ( 'mydb' );

Re-enable the SLPM

You can perform the following operations to re-enable the SLPM for a database.

  1. Revoke permissions from users.
    Before you enable the SLPM, we recommend that you execute the following statement to revoke all permissions of users in a database:
    call slpm_cleanup ( 'dbname' );
  2. Re-enable the SLPM.
    After the permissions of users are revoked, execute the following statements to re-enable the SLPM:
     call slpm_enable ('t');
     call slpm_migrate ();
  3. Grant permissions to users.
    After the SLPM is re-enabled, you can grant permissions to users by using the Hologres console or executing SQL statements. For more information, see Grant permissions to a user.