Hologres:Use the SLPM

Limits

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 as the superuser. 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 your business may be affected.

   call slpm_enable ();  // Enable the SLPM for the current database.

3. Optional: Switch from the standard PostgreSQL authorization model to the SLPM for existing objects.

   If the 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 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, each time you call the slpm_migrate function, the function transfers the ownership of up to 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 repeatedly call 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 current 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 of a database.

   Note

   • When you call the slpm_create_user function to add a Resource Access Management (RAM) user to a Hologres instance, you must prefix the user ID (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 Overview.

   • The SLPM does not support custom RAM user names that end with admin, developer, writer, viewer, or all_users.

5. Add the user to a user group.

   After you create the 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 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 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 user group of a database.
   call slpm_grant ('mydb.admin', '197006222995xxx', ); // Add the user whose Alibaba Cloud account ID is 197006222995xxx to the mydb.admin user group.
   call slpm_grant ('mydb.admin', 'ALIYUN$xxx'); // Add the user whose Alibaba Mail address is xxx@aliyun.com to the mydb.admin user group.
   
   // Add a user to the {db}.{schema}.developer user 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 user 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 user group.
   
   // Add a user to the {db}.{schema}.viewer user 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 user 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 user 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 permissions 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 specific user.

// Remove a user from the {db}.admin user group of a database.
call slpm_revoke ('dbname.admin', 'p4_564306222995xxx');// Remove the RAM user whose UID is 564306222995xxx from the {db}.admin user group.
call slpm_revoke ('dbname.admin', '197006222995xxx');// Remove the user whose Alibaba Cloud account ID is 197006222995xxx from the {db}.admin user group.
call slpm_revoke ('dbname.admin', '"xxx@aliyun.com"');

// Remove a user from the {db}.{schema}.developer user 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 user group.
call slpm_revoke ('mydb.public.developer', 'p4_564306222995xxx');// Remove the RAM user whose UID is 564306222995xxx from the mydb.public.developer user group.

// Remove a user from the {db}.{schema}.viewer user 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 user group.

Delete a user

DROP ROLE "Alibaba Cloud account ID/Alibaba Mail address/RAM user UID"; // Delete a user from a Hologres instance.

Disable the SLPM

If you no longer need the SLPM, you can perform the following steps to disable it.

1. Disable the SLPM.

   After the SLPM is enabled, the superuser can execute the following statement to disable the SLPM for a database. After the superuser disables the SLPM for the 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 the 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 permissions on the objects other than the preceding ones. For example, the PUBLIC group has no permissions on tables, views, materialized views, table columns, sequences, foreign data wrappers, foreign servers, or schemas. The public schema is excluded. Contact the superuser to obtain these permissions based on your business requirements.

   • After the SLPM is disabled, members in the {db}.admin, {db}.{schemaname}.developer, {db}.{schemaname}.writer, and {db}.{schemaname}.viewer user groups retain the obtained permissions on existing objects but do not have the permissions 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 the user groups of an existing database

     If you want 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, you may fail to delete the user groups and your business may be affected.

     The slpm_cleanup function is used to transfer the ownership of objects to the current user. However, the ownership of up to 64 objects can be transferred by using this function each time. You can modify the batch_size parameter in this function. As a result, you may need to repeatedly call 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 the user groups of a deleted database

     To delete the user groups of a deleted database, execute the following statement in another database as a superuser. For this example, execute the statement in the postgres database.

     postgres=# call slpm_cleanup ( 'mydb' );

Enable the SLPM again

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

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. Enable the SLPM again.

   After the permissions of users are revoked, execute the following statements to enable the SLPM again:

    call slpm_enable ('t');
    call slpm_migrate ();

3. Grant permissions to users.

   After the SLPM is enabled again, you can grant permissions to users by using the Hologres console or executing SQL statements. For more information, see Grant permissions to a user.

Create a view across schemas by using the SLPM (beta)

Only Hologres V1.3.36 and later allow you to create views across schemas. If the version of your Hologres instance is earlier than V1.3.36, manually upgrade your Hologres instance in the Hologres console or join a Hologres DingTalk group to apply for an instance upgrade. For more information about how to manually upgrade a Hologres instance, see Instance upgrades. For more information about how to join a Hologres DingTalk group, see Obtain online support for Hologres.

Scenario description

CREATE VIEW ads.customer_total_order_price_view AS
SELECT
    c_name,
    sum(o_totalprice)
FROM
    ods.orders AS o
INNER JOIN dwd.customer AS c
ON o.o_custkey = c.c_custkey
GROUP BY
    1;

Enable the cross-schema view creation feature

• Usage notes

  • By default, the cross-schema view creation feature is disabled.

  • When you create a view across schemas, you must have the developer permissions on the schemas to which the view belongs and the permissions of the viewer or a higher-level user group on tables that are used in the schemas.

    For example, if you want to use the ads_dev_user account to create a view named customer_total_order_price_view in a schema named ads, the ads_dev_user account must be granted the developer permissions on the ads schema and the permissions of the viewer user group on the ods and dwd schemas.

  • If you want to view a view that is created across schemas, the account that you use must be granted only the permissions of the viewer or a higher-level user group on the schema to which the view belongs.

    For example, if you want to use the ads_view_user account to query a view named ads.customer_total_order_price_view, the account must be granted only the permissions of the viewer or a higher-level user group on a schema named ads.

  • After you enable the cross-schema view creation feature, the owner of the created view is the user who created the view. Only the owner of a view can modify or delete the view.

    For example, in the preceding scenario, the owner of the ads.customer_total_order_price_view view is the ads_dev_user account. If you want to delete the ads_dev_user account from a database, execute the following SQL statements to transfer the ownership of views. Make sure that the account to which the ownership is transferred has the permissions of the viewer or a higher-level user group on the schemas of tables that are used by the views and the developer permissions on the schemas to which the views belong.

    -- Sample syntax
    call slpm_alter_view_owner('View name', 'Alibaba Cloud account ID/Alibaba Mail address/RAM user UID');
    
    -- Example: Transfer the ownership of the ads.customer_total_order_price_view view to the p4_xxxxx user.
    call slpm_alter_view_owner ('ads.customer_total_order_price_view', 'p4_xxxxx');

• Syntax

  To enable the cross-schema view creation feature, execute the following SQL statement as a superuser:

  call slpm_enable_multi_schema_view();

  After the preceding statement is executed, you can create a view across schemas.

Disable the cross-schema view creation feature

-- Disable the cross-schema view creation feature.
call slpm_disable_multi_schema_view();
-- Transfer the ownership of all views to the developer of the schemas to which the views belong.
call slpm_migrate();