Use the SPM - Hologres - Alibaba Cloud Documentation Center

This topic describes how to use the simple permission model (SPM) in Hologres.

Use the SPM to authorize users

When you use the SPM to authorize users of an instance in Hologres, you can use the following method:

Execute SQL statements to authorize users
After you connect a Hologres instance to a development tool, you can execute SQL statements to authorize a user by using the SPM. This way, the user is granted the required permissions on the instance. To authorize a user by using the SPM, perform the following steps:

Enable function calls.
Before you enable the SPM, you must execute the following statement to enable function calls:
```
create extension spm;
```
Enable the SPM.
By default, the SPM is disabled. You can execute the following statement to enable the SPM for a database as a superuser. For information about the spm_enable function, see spm_enable.
```
call spm_enable ();  // Enable the SPM for the current database.
```
Note
After you enable the SPM for a database, users in the developer user group are granted permissions on tables and table-like objects in all schemas of the database.
Optional: Switch from the standard PostgreSQL authorization model to the SPM for existing objects.
If the database uses the standard PostgreSQL authorization model and the database contains objects such as tables, views, and foreign tables, you must transfer the ownership of these objects to the developers that you specify in the SPM. Otherwise, users do not have the permissions to manage the objects, and the business may be affected. Execute the following statement to transfer the ownership of objects:
```
call spm_migrate(); // Transfer the ownership of existing objects in the database to the developers that are specified in the SPM.
```
If the database contains no objects, skip this step.
Note
When you enable the SPM for a database, make sure that no SQL statement is being executed in the database. Otherwise, you may fail to enable the SPM, and the business may be affected.
The spm_migrate function may transfer the ownership of a large number of objects at a time. This may be limited by PostgreSQL. The number of objects for which you can transfer ownership at a time by calling the spm_migrate function is no more than the value of the max_locks_per_transaction parameter. As a result, you may need to call the spm_migrate function multiple times until the ownership of all objects is transferred. For information about the spm_migrate function, see spm_migrate.
Create a user.
Before you authorize a user, you must create the user in the Hologres instance. If you need to authorize an existing user in the instance, skip this step.
- Alibaba Cloud account or RAM user:
```
call spm_create_user ('UID of an Alibaba Cloud account/Alibaba Mail address/RAM user'); // If you create a user by using an Alibaba Mail address, you must enclose the address in double quotation marks (").
call spm_create_user ('UID of an Alibaba Cloud account/Alibaba Mail address/RAM user', '<dbname>_[admin|developer|writer|viewer]'); // Add the user to the corresponding user group when you create the user.
```
  The dbname parameter in the preceding statement indicates the name of the database on the current Hologres instance.
  For example, you can add the RAM user xxx.onaliyun.com to the developer user group of the testdb database.
```
call spm_create_user ('xxx.onaliyun.com', 'testdb_developer');
```
- Custom user:
```
create user "BASIC$<user_name>" with password '<password>';
```
Note
- When you call the spm_create_user function to add a RAM user to a Hologres instance, prefix the UID of the RAM user with p4_ and enclose the prefix and the UID in double quotation marks ("), such as "p4_uid".
- If you use a custom account, the username cannot end with admin, developer, writer, viewer, or all_users.

Add the user to a user group.

After you create the user in the Hologres instance, add the user to a user group in a database to authorize the user. After you authorize a RAM user, you can use a development tool to connect to the current database and develop the database as the RAM user. If you specified a user group for a user when you created the user, skip this step. For information about the spm_grant function, see spm_grant.

In the following statement, the {dbname}_[admin|developer|writer|viewer] clause is used to specify a user group in the current database. For more information, see Overview.


call spm_grant('{dbname}_[admin|developer|writer|viewer]', 'UID of an Alibaba Cloud account/Alibaba Mail address/RAM user'); // 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 spm_grant('mydb_admin', 'p4_564306222995xxx'); // Add the RAM user whose UID is 564306222995xxx to the mydb_admin user group. 
call spm_grant('mydb_admin', '197006222995xxx'); // Add the user whose Alibaba Cloud account UID is 197006222995xxx to the mydb_admin user group. 
call spm_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>_developer user group of a database. 
call spm_grant('mydb_developer', 'p4_564306222995xxx'); // Add the RAM user whose UID is 564306222995xxx to the mydb_developer user group. 
call spm_grant('mydb_developer', '197006222995xxx'); // Add the user whose Alibaba Cloud account UID is 197006222995xxx to the mydb_developer user group. 
call spm_grant('mydb_developer', 'RAM$mainaccount:subuser');// Add the RAM user named subuser of the Alibaba Cloud account named mainaccount to the mydb_developer user group. 

// Add a user to the <db>_viewer user group of a database. 
call spm_grant('"MYDB_viewer"', 'p4_564306222995xxx'); // Add the RAM user whose UID is 564306222995xxx to the MYDB_viewer user group. 
call spm_grant('"MYDB_viewer"', '197006222995xxx'); // Add the user whose Alibaba Cloud account UID is 197006222995xxx to the MYDB_viewer user group. 
call spm_grant('mydb_viewer', '"xxx@aliyun.com"'); // Add the user whose Alibaba Mail address is xxx@aliyun.com to the mydb_viewer user group.

Remove a user from a user group

If you need to remove a user from a user group of a specified database in Hologres, you can use the following method based on the SPM:

Execute SQL statements to remove a user from a user group.

You can execute the following statement to remove a user from a user group of a database. For information about the spm_revoke function, see spm_revoke.

call spm_revoke('<dbname>_[admin|developer|writer|viewer]', 'UID of an Alibaba Cloud account/Alibaba Mail address/RAM user'); // Remove a user from a user group. 

// Examples:
// Remove a user from the <db>_admin user group of a database. 
call spm_revoke ('dbname_admin', 'p4_564306222995xxx');// Remove the RAM user whose UID is 564306222995xxx from the admin user group of the specified database. 
call spm_revoke ('dbname_admin', '197006222995xxx');// Remove the user whose Alibaba Cloud account UID is 197006222995xxx from the admin user group of the specified database. 
call spm_revoke('dbname_admin', 'xxx@aliyun.com');// Remove the user whose Alibaba Mail address is xxx@aliyun.com from the admin user group of the specified database. 

// Remove a user from the <db>_developer user group of a database. 
call spm_revoke('mydb_developer', 'RAM$mainaccount:subuser'); // Remove the RAM user named subuser of the Alibaba Cloud account named mainaccount from the mydb_developer user group. 
call spm_revoke('mydb_developer', 'p4_564306222995xxx');// Remove the RAM user whose UID is 564306222995xxx from the mydb_developer user group. 

// Remove a user from the <db>_viewer user group of a database. 
call spm_revoke('"MYDB_viewer"', 'p4_564306222995xxx'); // Remove the RAM user whose UID is 564306222995xxx from the MYDB_viewer user group.

Delete a user from a Hologres instance

You can execute the following statement to delete a user from a Hologres instance:

Important

After a user is deleted from a Hologres instance, the user has no permissions on the instance. Proceed with caution.

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

(Optional) Switch from the standard PostgreSQL authorization model to the SPM for existing objects

If a database uses the standard PostgreSQL authorization model and the database contains objects such as tables, views, and foreign tables, you can enable the SPM for better permission management. To call the spm_migrate function to transfer the ownership of existing objects to the SPM, execute the following statement:

call spm_migrate();  // Transfer the ownership of existing objects in the database to the developers that are specified in the SPM.

Note

When you enable the SPM for a database, make sure that no SQL statement is being executed in the database. Otherwise, you may fail to enable the SPM, and the business may be affected.

The spm_migrate function may transfer the ownership of a large number of objects at a time. However, the number of objects for which you can transfer ownership at a time by calling the spm_migrate function is no more than the value of the max_locks_per_transaction parameter. As a result, you may need to call the spm_migrate function multiple times until the ownership of all objects is transferred.

Disable the SPM

Disable the SPM.
A superuser can execute the following statement to disable the SPM for a database. For information about the spm_disable function, see spm_disable.
```
call spm_disable();
```
If a superuser disables the SPM for a database, the user groups of the database are not deleted. For information about the permissions of users in user groups, see Functions to manage the SPM.
Delete user groups of a database.
After the SPM is disabled, you can call the spm_cleanup function to delete the user groups of a database in one of the following cases:
Note
In most cases, we recommend that you retain user groups to facilitate user management.
- Case 1: Delete the user groups of an existing database
  To delete user groups but retain the database, execute the following statement as a superuser. For information about the spm_cleanup function, see spm_cleanup.
```
call spm_cleanup('{dbname}');
```
  Note
  When you call the spm_cleanup function, make sure that no SQL statement is being executed in the database. Otherwise, the function may fail, and the business may be affected.
  The spm_cleanup function may transfer the ownership of a large number of business objects at a time. However, the number of objects for which you can transfer ownership at a time by calling the spm_cleanup function is no more than the value of the max_locks_per_transaction parameter. As a result, you may need to call the spm_cleanup function multiple times until the ownership of all objects is transferred and all user groups of the database are deleted.
- 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 such as postgres as a superuser:
```
call spm_cleanup('mydb');
```

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

Only a superuser can disable the SPM for a database.
After the SPM is disabled, the PUBLIC group is granted the USAGE and CREATE permissions on the public schema of the database.
After the SPM is disabled, the PUBLIC group is granted the CONNECT and TEMPORARY permissions on the database.
After the SPM is disabled, the PUBLIC group is granted the EXECUTE permission on the functions and procedures of the database.
After the SPM is disabled, the PUBLIC group is granted the USAGE permission on the languages and data types (including domains) of the database.
After the SPM 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 (excluding the public schema).
After the SPM is disabled, the <db>_admin, <db>_developer, <db>_writer, and <db>_viewer user groups have the following permissions:
- <db>_admin: retains the obtained permissions on existing objects and has no permissions on new objects.
- <db>_developer: retains the obtained permissions on existing objects and has no permissions on new objects.
- <db>_writer: retains the obtained permissions on existing objects and has no permissions on new objects.
- <db>_viewer: retains the obtained permissions on existing objects and has no permissions on new objects.

Enable the SPM again

You enabled the SPM for your database but switched it to the standard PostgreSQL authorization model for existing objects. If you need to enable the SPM again, execute the following statements:

call spm_enable (); // Enable the SPM for the current database. 
call spm_migrate(); // Transfer the ownership of existing objects in the database to the developers that are specified in the SPM.

Note

When you enable the SPM for a database, make sure that no SQL statement is being executed in the database. Otherwise, you may fail to enable the SPM, and the business may be affected.