Hologres:Functions to manage the SPM

Overview of functions

spm_enable

• Description

  spm_enable: enables the SPM.

  After you call the spm_enable function to enable the SPM for a database, the system creates the <db>_admin, <db>_developer, <db>_writer, and <db>_viewer groups for the database.

• Syntax

  CALL spm_enable();

  Note

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

• Usage notes

  After you call the spm_enable function to enable the SPM 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 who are added to the <db>_admin, <db>_developer, <db>_writer, and <db>_viewer groups have permissions to connect to the database.

  • Users who are added to the <db>_admin group become the owners of the database and all the schemas of the database.

  • Users who are added to the <db>_developer, <db>_writer, and <db>_viewer groups have the USAGE permission on all the schemas of the database. Users who are added to the <db>_developer group also have the CREATE permission on all the schemas of the database.

  • Users who are added to the <db>_admin and <db>_developer groups have permissions to create objects. Users who are added to the <db>_developer group are the owners of these objects. Users who are added to the <db>_writer group have read and write permissions on these objects, and users who are added to the <db>_viewer group have read-only permissions on these objects.

spm_migrate

• Description

  spm_migrate: transfers the ownership of existing objects such as internal tables, views, and foreign tables in a database to the developers who are specified in the SPM.

• Syntax

  CALL spm_migrate( [ batch_size ] );

  The following table describes the parameter in the syntax.

  Parameter	Description	Valid value
  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 value of the batch_size parameter must fall into the closed interval [0, max_locks_per_transaction]. Otherwise, this parameter is invalid. The maximum value of the max_locks_per_transaction parameter is 64. If the ownership of thousands of or even tens of thousands of objects needs to be transferred, you must repeatedly call the spm_migrate function until the ownership of all the objects is transferred. In addition, we recommend that you join the Hologres DingTalk group to ask engineers to increase the maximum value of the max_locks_per_transaction parameter before you call the function. For more information about how to join the Hologres DingTalk group, see Obtain online support for Hologres.

• Usage notes

  • After you call the spm_migrate function in a database, if the DONE BUT NOT COMPLETED message is returned, the ownership of part of objects is not transferred. You must call the spm_migrate function again.

  • When the COMPLETED message is returned, the ownership of all the objects is transferred.

• Sample statements

  CALL spm_migrate(); // Transfer the ownership of objects of which the number is no more than the value of the max_locks_per_transaction parameter at a time. 
  
  CALL spm_migrate(128); // Transfer the ownership of 128 objects at a time.

spm_create_user

• Description

  spm_create_user: creates a user to connect to a Hologres instance. After you call this function to create a user, the user has only logon permissions. To allow the user to perform data development, you must grant specific permissions to the user.

  Note

  To call the spm_create_user function, you must be a superuser or a member of the <db>_admin group of the database.

• Syntax

  CALL spm_create_user( user_name [, role_name] );

  The following table describes the parameters in the syntax.

  Parameter	Description
  user_name	The name of the user that you want to create. The name must meet the following format requirements: 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 requirements: {db}_admin {db}_developer {db}_writer {db}_viewer

• Sample statements

  CALL spm_create_user('my_test@aliyun.com');
  CALL spm_create_user('RAM$my_test:mysubuser', 'mydb_developer');
  CALL spm_create_user('13532313103042xxx');
  CALL spm_create_user('p4_23319103042xxx', 'mydb_admin');

spm_grant

• Description

  spm_grant: adds a user to one of the following user groups of a database: <db>_admin, <db>_developer, <db>_writer, and <db>_viewer.

  Note

  To call the spm_grant function, you must be a superuser or a member of the <db>_admin group of the database.

• Syntax

  CALL spm_grant( role_name, user_name );

  The following table describes the parameters in the syntax.

  Parameter	Description
  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 requirements: {db}_admin {db}_developer {db}_writer {db}_viewer For more information about the permissions of user groups, see Overview.
  user_name	The name of the user that you want to add to the specified user group. The name must meet the following format requirements: 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.

• Usage notes

  • You can call the spm_grant function only in a database for which the SPM 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>_developer, <db>_writer, and <db>_viewer.

• Sample statements

  CALL spm_grant('mydb_developer', 'p4_202338382183xxx');// Add the specified RAM user to the mydb_developer group. 
  CALL spm_grant('mydb_admin', 'RAM$my_test:xxx');// Add the specified RAM user to the mydb_admin group. 
  CALL spm_grant('otherdb_admin', '13532313103042xxx'); // This function fails to call 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 spm_grant function.

spm_revoke

• Description

  spm_revoke: removes a user from the <db>_admin, <db>_developer, <db>_writer, or <db>_viewer group of a database.

  Note

  To call the spm_revoke function, you must be a superuser or a member of the <db>_admin group of the database.

• Syntax

  CALL spm_revoke( role_name, user_name );

• Usage notes

  • You can call the spm_revoke function only in a database for which the SPM 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>_developer, <db>_writer, and <db>_viewer.

• Sample statements

  CALL spm_revoke('mydb_developer', 'p4_202338382183xxx');// Remove the specified RAM user from the mydb_developer group. 
  CALL spm_revoke('mydb_admin', 'RAM$my_test:xxx');// Remove the specified RAM user from the mydb_admin group. 
  CALL spm_revoke('otherdb_admin', '13532313103042xxx'); // This function fails to call 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 spm_revoke function.

spm_disable

• Description

  spm_disable: disables the SPM for a database.

  Note

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

• Syntax

  CALL spm_disable();

• Usage notes

  After you disable the SPM for a database, the following changes occur to the permissions of users:

  • The <db>_admin, <db>_developer, <db>_writer, and <db>_viewer groups are retained. You can execute authorization statements in the standard PostgreSQL authorization model to add users to these user groups. Members in the <db>_developer group are still the owners of the database.

  • 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.

• Sample statement

  CALL spm_disable();

spm_cleanup

• Description

  spm_cleanup: deletes the <db>_admin, <db>_developer, <db>_writer, and <db>_viewer groups of a database after the SPM is disabled.

  Note

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

• Syntax

  CALL spm_cleanup( db_name [, batch_size ] );

  The following table describes the parameters in the syntax.

  Parameter	Description	Valid value
  db_name	The name of the database for which you want to delete user groups. If the database name contains special characters or uppercase letters, enclose the name by using double quotation marks (" "). Example: "MYDB".	N/A
  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 value of the batch_size parameter must fall into the closed interval [0, max_locks_per_transaction]. Otherwise, this parameter is invalid. If the ownership of thousands of or even tens of thousands of objects needs to be transferred, you must repeatedly call the spm_migrate function until the ownership of all the objects is transferred. In addition, we recommend that you join the Hologres DingTalk group to ask engineers to increase the maximum value of the max_locks_per_transaction parameter before you call the function. For more information about how to join the Hologres DingTalk group, see Obtain online support for Hologres.

• Usage notes

  • After you call the spm_cleanup function in a database, if the DONE BUT NOT COMPLETED message is returned, the ownership of part of objects is not transferred, and no user groups are deleted. You must call the spm_cleanup function again.

  • When the COMPLETED message is returned, the ownership of all the objects is transferred, and the user groups are deleted.

• Sample statements

  CALL spm_cleanup('mydb'); // Delete all user groups of the specified database and transfer the ownership of objects of which the number is no more than the value of the max_locks_per_transaction parameter at a time to the current user who calls this function. 
  CALL spm_cleanup('mydb', 128);// Delete all user groups of the specified database and transfer the ownership of 128 objects at a time to the current user who calls this function.

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

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

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

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

Permissions on calling SPM functions

The following table describes the permissions on calling SPM functions that are granted to different user groups.

Restricted statements in the SPM

The SPM limits the usage of part of statements related to permissions, as described in the following table.