All Products
Search
Document Center

Hologres:Use the SLPM

Last Updated:Sep 12, 2024

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 items:

  • 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. In this case, the error message ERROR: permission denied for table is displayed. We recommend that you do not create a view or a rule by using tables in different schemas in a database for which the SLPM is enabled. For more information about how to create a view across schemas, see Create a view across schemas by using the SLPM (beta) in this topic.

  • 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 in this topic. After you enable the SLPM for a database, you cannot perform operations by executing the DDL statements that are 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 cannot transfer the ownership of these tables.

    The ownership of the tables is automatically transferred.

    grant

    After you add a user to a user group, the user is automatically granted the permissions of the user group. You do not need to call the slpm_grant function to grant the user the 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 can remove the user from the specific user group to revoke the specific permission from the user.

    slpm_revoke

    alter default privileges

    In the standard PostgreSQL authorization model, you can grant users only the permissions on existing tables and objects. To manage the objects and tables that will be created in the future, you must grant them the required permissions again. In the SLPM, you can add users to user groups to grant the users the required permissions on all tables or objects of the specified types, including the objects and tables of these types that will be 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 user groups are generated by the system by default. All users including the superuser cannot 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 do not execute the alter rename schema statement.

    slpm_rename_schema

    rename database

    To rename a database, you must call the slpm_rename_database function but do not execute the alter 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.

  • 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 Upgrade instances. For more information about how to join a Hologres DingTalk group, see Obtain online support for Hologres.

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 Cloud email address/RAM user ID'); // Create a user in the Hologres instance. Alibaba Cloud email addresses must be enclosed in double quotation marks ("). 
    call slpm_create_user ('Alibaba Cloud account ID/Alibaba Cloud email address/RAM user ID', '{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, 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 the 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 Cloud email address/RAM user ID'); // 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 UID is 197006222995xxx to the mydb.admin user group.
    call slpm_grant ('mydb.admin', 'ALIYUN$xxx'); // Add the user whose Alibaba Cloud email 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 UID 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 UID is 197006222995xxx to the MYDB.lisa.viewer user group.
    call slpm_grant ('mydb.lisa.viewer', '"xxx@aliyun.com"'); // Add the user whose Alibaba Cloud email 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 Cloud email address/RAM user ID'); // Revoke permissions from a specific 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 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 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

You can delete a user based on your business requirements. After a user is deleted from a Hologres instance, the user has no permissions on the instance. Proceed with caution.

DROP ROLE "Alibaba Cloud account ID/Alibaba Cloud email address/RAM user ID"; // 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 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 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. In this example, execute the statement in the postgres database.

      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:

    -- Enable the SLPM in recovery mode. 
    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 ();
  3. Grant permissions to users.

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

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 Upgrade instances. For more information about how to join a Hologres DingTalk group, see Obtain online support for Hologres.

Scenarios

The SLPM manages permissions on schemas. In some business scenarios, you may need to create views across schemas. For example, in specific business scenarios, you can create schemas for data layers including ODS, DWD, DWS, and ADS of a data warehouse, and then create tables at these data layers. However, sometimes you need to create views across schemas. For example, you need to create an ADS view by using tables that are created at the DWS and DWD data layers to meet the business requirements, as described in the following table.

Database

Schema

Table

View

erp_db

ods

orders

N/A

dwd

customer

N/A

ads

N/A

View name: customer_total_order_price

DDL for creating the view:

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 schema to which the view belongs and the permissions of the viewer or a higher-level user group on tables that are used in the schema.

      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 query 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 Cloud email address/RAM user ID');
      
      -- 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

If you no longer need to use the cross-schema view creation feature, execute the following SQL statements to disable the 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();

After the preceding statements are executed, views that are not created across schemas can be queried, the SLPM-based capabilities are restored, and cross-schema views cannot be queried.