Hologres:Functions of the SLPM

Overview

slpm_enable

• Description

  slpm_enable: enables the SLPM.

  After you call the slpm_enable function, the system automatically creates the following user groups: {db}.admin, {db}.{schema}.developer, {db}.{schema}.writer, and {db}.{schema}.viewer.

• Syntax

  CALL slpm_enable ();

  Note

  To call the slpm_enable function, you must be a superuser of the Hologres instance.

• Usage notes

  After you call the slpm_enable function to enable the SLPM for a database, the following changes occur to the permissions of users:

  • All permissions on the database are revoked from the PUBLIC group. This prevents unauthorized users from connecting to the database.

  • All permissions on all schemas of the database are revoked from the PUBLIC group.

  • Users in the {db}.admin, {db}.{schema}.developer, {db}.{schema}.writer, and {db}.{schema}.viewer groups have the permission to connect to the database.

  • Users in the {db}.admin group become the owners of the database and all schemas of the database.

  • Users in the {db}.{schema}.developer, {db}.{schema}.writer, and {db}.{schema}.viewer groups have the USAGE permission on the schema. Users in the {db}.{schema}.developer group have the CREATE permission on the schema.

  • After users in the {db}.admin and {db}.{schema}.developer groups create objects based on a schema, users in the {db}.{schema}.developer group are owners of these objects. Users in the {db}.{schema}.writer group have the read and write permissions on these objects. Users in the {db}.{schema}.viewer group have the read-only permissions on these objects.

  • After users in the {db}.admin and {db}.{schema}.developer groups create objects that do not have a schema, such as foreign servers, foreign data wrappers, and languages, users in the {db}.admin group are owners of these objects. Users in the {db}.{schema}.developer, {db}.{schema}.writer, and {db}.{schema}.viewer groups have the USAGE permission on these objects.

slpm_migrate

• Description

  slpm_migrate: migrates existing objects such as tables, views, and foreign tables to the SLPM.

• Syntax

  CALL slpm_migrate ( [ batch_size ] );

  The following table describes the parameters in the syntax.

  Parameter	Description	Value range
  batch_size	The maximum number of objects that can be migrated at a time. If you set this parameter to 0, the maximum number of objects that can be migrated at a time is specified by the max_locks_per_transaction parameter.	[1, 64]. This parameter is invalid if its value is beyond this range. If your database contains many objects, such as hundreds of objects, you must call the slpm_migrate function multiple times until all objects are migrated. If your database contains a large number of objects, such as more than thousands or even tens of thousands of objects, we recommend that you contact technical support in the DingTalk group (ID 32314975) to increase the value of the max_locks_per_transaction parameter before you migrate objects.

• Usage notes

  • If the DONE BUT NOT COMPLETED message is returned after you call the slpm_migrate function, you still have some objects to migrate. In this case, call the slpm_migrate function again.

  • If the COMPLETED message is returned, all objects are migrated.

• Examples

  CALL slpm_migrate (); // Migrate objects to the SLPM. The maximum number of objects that can be migrated is specified by the max_locks_per_transaction parameter. 
  
  CALL slpm_migrate (64); // Migrate a maximum of 64 objects to the SLPM. 

slpm_create_user

• Description

  slpm_create_user: creates a user in the SLPM. After you call this function to create a user, the user has only the logon permission. To allow the user to perform data analytics, you must grant specific permissions to the user.

  Note

  To call the slpm_create_user function, you must be a superuser or a member of the {db}.admin group of the database.

• Syntax

  CALL slpm_create_user ( user_name [, role_name] );

  The following table describes the parameters in the syntax.

  Parameter	Description
  user_name	The name of the user to be created. You can specify the name in one of the following formats: The ID or display name of an Alibaba Cloud account, such as 13432193xxxx or xx@aliyun.com. The UID of a RAM user prefixed with p4_ or the display name of the RAM user, such as p4_202338382183xxx or RAM$mainaccount:subuser.
  role_name	The group to which the user is to be added. You can add the user to one of the following groups based on your business needs: {db}.admin {db}.{schema}.developer {db}.{schema}.writer {db}.{schema}.viewer For more information about the permissions of user groups, see User groups and permissions in SLPM.

• Examples

  CALL slpm_create_user ('my_test@aliyun.com');
  CALL slpm_create_user ('RAM$my_test:mysubuser', 'mydb.public.developer');
  CALL slpm_create_user ('13532313103042xxx');
  CALL slpm_create_user ('p4_23319103042xxx', 'mydb.admin');

slpm_grant

• Description

  slpm_grant: adds a user to the {db}.admin, {db}.{schema}.developer, {db}.{schema}.writer, or {db}.{schema}.viewer group.

  Note

  To call the slpm_grant function, you must be a superuser or a member of the {db}.admin group of the database.

• Syntax

  CALL slpm_grant ( role_name, user_name );

  The following table describes the parameters in the syntax.

  Parameter	Description
  user_name	The name of the user to which you want to grant permissions. You can specify the name in one of the following formats: The ID or display name of an Alibaba Cloud account, such as 13432193xxxx or xx@aliyun.com. The UID of a RAM user prefixed with p4_ or the display name of the RAM user, such as p4_202338382183xxx or RAM$mainaccount:subuser.
  role_name	The group to which the user is to be added. You can add the user to one of the following groups based on your business needs: {db}.admin {db}.{schema}.developer {db}.{schema}.writer {db}.{schema}.viewer For more information about the permissions of user groups, see User groups and permissions in SLPM.

• Usage notes

  • You can call this function only after the SLPM is enabled.

  • The value of the user_name parameter must be the account ID or display name of an Alibaba Cloud account or a RAM user.

  • The value of the role_name parameter must be in one of the following formats: {db}.admin, {db}.{schema}.developer, {db}.{schema}.writer, or {db}.{schema}.viewer.

• Examples

  CALL slpm_grant ('mydb.public.developer', 'p4_202338382183xxx'); // Add the specified RAM user to the mydb.public.developer group. 
  CALL slpm_grant ('mydb.admin', 'RAM$my_test:xxx'); // Add the specified RAM user to the mydb.admin group. 
  CALL slpm_grant ('otherdb.admin', '13532313103042xxx'); // This function fails because you can add a user only to a group of the current database. To add a user to a group of another database, connect to the database and call the slpm_grant function. 

slpm_revoke

• Description

  slpm_revoke: removes a user from the {db}.admin, {db}.{schema}.developer, {db}.{schema}.writer, or {db}.{schema}.viewer group.

  Note

  To call the slpm_grant function, you must be a superuser or a member of the {db}.admin group of the database.

• Syntax

  CALL slpm_revoke ( role_name, user_name );

• Usage notes

  • You can call this function only after the SLPM is enabled.

  • The value of the user_name parameter must be the account ID or display name of an Alibaba Cloud account or a RAM user.

  • The value of the role_name parameter must be in one of the following formats: {db}.admin, {db}.{schema}.developer, {db}.{schema}.writer, or {db}.{schema}.viewer.

• Examples

  CALL slpm_revoke ('mydb.public.developer', 'p4_202338382183xxx'); // Remove the specified RAM user from the mydb.public.developer group. 
  CALL slpm_revoke ('mydb.admin', 'RAM$my_test:xxx'); // Remove the specified RAM user from the mydb.admin group. 
  CALL slpm_revoke ('otherdb.admin', '13532313103042xxx'); // This function fails because you can remove a user only from a group of the current database. To remove a user from a group of another database, connect to the database and call the slpm_revoke function. 

slpm_disable

• Description

  slpm_disable: disables the SLPM for a database.

  Note

  To call the slpm_disable function, you must be a superuser.

• Syntax

  CALL slpm_disable ();

• Usage notes

  When you disable the SLPM, take note of the following items:

  • The {db}.admin, {db}.{schema}.developer, {db}.{schema}.writer, and {db}.{schema}.viewer groups are retained. You can execute authorization statements in the standard PostgreSQL authorization model to add users to these user groups. Users in the {db}.{schema}.developer group are still the owners of database objects.

  • The CONNECT and TEMPORARY permissions on the database are granted to the PUBLIC group.

  • The USAGE and CREATE permissions on the public schema of the database are granted to the PUBLIC group.

  • The EXECUTE permission on the functions and procedures of the public schema in the database is granted to the PUBLIC group.

  • The USAGE permission on the user-defined languages of the database is granted to the PUBLIC group.

  • The USAGE permission on the user-defined types of the database is granted to the PUBLIC group.

• Examples

  CALL slpm_disable ();

slpm_cleanup

• Description

  slpm_cleanup: deletes the {db}.admin, {db}.{schema}.developer, {db}.{schema}.writer, and {db}.{schema}.viewer groups that are retained for the SLPM.

  Note

  To call the slpm_cleanup function, you must be a superuser.

• Syntax

  CALL slpm_cleanup ( db_name [, batch_size ] );

  The following table describes the parameters in the syntax.

  Parameter	Description	Value range
  db_name	The name of the database for which you want to delete user groups.	If the name contains special characters or uppercase letters, enclose it in double quotation marks (" "), such as "MYDB".
  batch_size	The maximum number of objects that can be migrated at a time. If you set this parameter to 0, the maximum number of objects that can be migrated at a time is specified by the max_locks_per_transaction parameter. The default value of this parameter is 64.	[0, 64]. This parameter is invalid if its value is beyond this range. If your database contains many objects, such as hundreds of objects, you must call the slpm_cleanup function multiple times until all objects are migrated. If your database contains a large number of objects, such as more than thousands or even tens of thousands of objects, we recommend that you contact technical support in the DingTalk group (ID 32314975) to increase the value of the batch_size parameter before you migrate objects.

• Usage notes

  • If the DONE BUT NOT COMPLETED message is returned after you call the slpm_cleanup function, you still have some objects to migrate, and the user groups are not deleted. In this case, call the slpm_cleanup function again.

  • If the COMPLETED message is returned, all objects are migrated and the user groups are deleted.

• Examples

  CALL slpm_cleanup ('mydb'); // Change the owner of objects to the current user. The maximum number of objects for which you can change the owner is specified by the max_locks_per_transaction parameter. 
  CALL slpm_cleanup ('mydb', 64); // Change the owner of up to 64 objects to the current user. 

  • Case 1: Delete a database and then user groups of the database.

    drop database mydb;
    CALL slpm_cleanup ('mydb'); // You can delete user groups of a deleted database at a time. 

  • Case 2: Delete user groups of an existing database. In this case, you must connect to the database.

     CALL slpm_cleanup ('otherdb');
    ERROR:  Permission Denied. execute in database otherdb, or drop database before call slpm_cleanup.

slpm_rename_schema

• Description

  slpm_rename_schema: renames a schema.

  After you rename a database for which the SLPM is enabled, you must also rename the four user groups. The slpm_rename_schema function automatically renames the user groups. Therefore, you must call this function to rename a schema.

  Note

  To call the slpm_rename_database function, you must be a superuser or a member of the {db}.admin group of the database.

• Syntax

  CALL slpm_rename_schema ( old_name, new_name );

  The following table describes the parameters in the syntax.

  Parameter	Description
  old_name	The original name of the schema to be renamed. If the name contains special characters or uppercase letters, enclose it in double quotation marks (" "), such as "MYDB".
  new_name	The new name that you want to specify for the schema. If the name contains special characters or uppercase letters, enclose it in double quotation marks (" "), such as "MYDB".

• Examples

  CALL slpm_rename_schema ('oldschema', 'newschema');