Answers to frequently asked questions about the development permissions on Hologres instances - Hologres

When you develop data in Hologres, you may receive error messages related to permissions. This topic provides answers to frequently asked questions about the development permissions on Hologres instances.

Overview

You can develop data on a Hologres instance only after you have development permissions on the instance. To view the solutions to frequently asked questions about permissions on the instance, click the following links:

How do I select an appropriate Hologres permission model?
Why do I receive the following error message when I develop data on a Hologres instance: role “RAM$xxx” doesn't not exist?
Why do I receive the following error message when I connect to a Hologres instance: password authentication failed for user “xxx”?
Why do I receive the following error message when I query a table: permission denied for table xxxx?
Why do I receive the following error message when I develop data on a Hologres instance: permission denied for database“xxx” detail: user does not have CONNECT privilege?
Why do I receive the following error message when I run the call spm_enable() command: because roles conflict?
Why do I receive the following error message when I grant permissions: current database is NOT in simple privilege mode?
Why do I receive the following error message when I manage a table: must be the owner of table xxxx?
Why do I receive the following error message when I create a view across schemas: permission denied for table xxx?
Why do I receive the following error message when I execute an SQL statement: permission denied for Schema xxx?
Why do I receive the following error message when I execute an SQL statement: ALTER TABLE xxx is not supported in Simple Privilege Mode?

How do I select an appropriate Hologres permission model?

Hologres provides the standard PostgreSQL authorization model, simple permission model (SPM), or schema-level permission model (SLPM). Select a permission model based on the following rules:

The standard PostgreSQL authorization model is a native PostgreSQL permission model. If you are familiar with PostgreSQL and relevant permission management, you can use this model to easily grant permissions to users. If you need to manage permissions on tables and have time and energy, you can use this model to grant each user the permissions to manage tables and revoke the permissions.
The SPM is a database-level simple permission model. In this model, all users who need to access a database are added to a specific user group. Each user group is granted specific access permissions on all objects in any schema of the database. You may rarely perform data development based on schemas or only use schemas to classify table objects as you use directories without the need to isolate permissions based on the schemas. In this case, we recommend that you use the SPM.
In the SLPM, each schema includes a developer group, a writer group, and a viewer group. If you need to perform schema-level user isolation and permission isolation for tables, we recommend that you use the SLPM.

Why do I receive the following error message when I develop data on a Hologres instance: role “RAM$xxx” doesn't not exist?

Problem description
When I connect to a Hologres instance and perform queries on the instance, the following error message is returned: role “RAM$xxx” doesn't not exist.
Cause
The current RAM user is not added to the instance.
Solution
You can grant the current RAM user relevant permissions on the instance based on your business requirements. For example, you can assign the superuser role to the RAM user. For more information about permission authorization, see Grant the development permissions on a Hologres instance to RAM users.

Why do I receive the following error message when I connect to a Hologres instance: password authentication failed for user “xxx”?

Problem description
The following error message is reported when I connect to a Hologres instance: password authentication failed for user “xxx”.
Cause
- If you log on to the Hologres instance by using the AccessKey pair of an account, this error message is reported because the account does not exist on the Hologres instance or the AccessKey pair is invalid or disabled.
- If you log on to the Hologres instance by using a BASIC account, this error message is reported because the BASIC account does not exist on the Hologres instance or the logon password is invalid.
Solution
- If you log on to the Hologres instance by using the AccessKey pair, perform the following steps:
  1. Check whether the account that you use to log on to the Hologres instance exists. If the account does not exist, add the user by following the instructions in Manage users.
  2. Check whether the AccessKey ID and AccessKey secret are valid or enabled.
- If you log on to the Hologres instance by using a BASIC account, perform the following steps:
  1. Check whether the BASIC account exists on the instance. If the account does not exist, add the account by following the instructions in Manage users.
  2. Check whether the password of the BASIC account is valid.

Why do I receive the following error message when I query a table: permission denied for table xxxx?

Problem description
When I perform operations such as data queries on an instance, the following error message is returned: Execution failed: ERROR: permission denied for table xxxx.
Cause
The RAM user does not have permissions to view specific tables.
Solution
Select a solution based on the permission model that you use.
- Standard PostgreSQL authorization model: Execute the following statement to grant permissions to the RAM user. In the statement, p4_UID specifies the account information of the RAM user. For more information about the standard PostgreSQL authorization model, see Standard PostgreSQL authorization model.
```
grant select on table tablename to "p4_UID";
```
- SPM: Add the RAM user to the viewer user group or another user group that has higher permissions than the viewer user group. For more information, see Use the SPM.

Why do I receive the following error message when I develop data on a Hologres instance: permission denied for database“xxx” detail: user does not have CONNECT privilege?

Problem description
When I connect to a Hologres instance to perform operations such as data queries, the following error message is returned: FATAL:permission denied for database''xxx'' detail: user does not have CONNECT privilege.
Cause
The current RAM user is only added to the instance but is not granted the development permissions on the instance.
Solution
Grant the RAM user relevant permissions on the instance based on your business requirements. For example, you can assign the superuser role to the RAM user. For more information about permission authorization, see Grant the development permissions on a Hologres instance to a RAM user.

Why do I receive the following error message when I run the `call spm_enable()` command: because roles conflict?

Problem description
When I run the call spm_enable() command to manage a database that uses the standard PostgreSQL authorization mode, an error message similar to the following content is returned: because roles conflict.
Cause
The system retains information about the SPM that was enabled for the current database.
Solution
Run the call spm_enable ('t'); command to enable the SPM.

Why do I receive the following error message when I grant permissions: current database is NOT in simple privilege mode?

Problem description
The following error message is returned when I grant permissions: current database is NOT in simple privilege mode.
Cause
The SPM is not enabled for the current database.
Solution
1. Run the show hg_experimental_enable_spm; command to check whether the SPM is disabled.
2. Run the following commands to enable the SPM for the current database:
```
-- Enable the SPM for the database.  
call spm_enable ('t');
-- Change the owner of the objects in the database to a user in the developer user group and use the SPM to manage the objects.
call spm_migrate (); 
```
  If you run the call spm_enable ('t'); command, the existing system roles and permissions are retained after you disable the SPM by running the call spm_disable command. If you run the spm_enable() command, the SPM will fail to be enabled because of system role conflicts. If you run the spm_enable ('t') command, the system role conflicts are ignored, and the system roles can be used.

Why do I receive the following error message when I manage a table: must be the owner of table xxxx?

Problem description
When I perform data operations on an instance, the following error message is returned: must be the owner of table xxxx.
Cause
The current RAM user does not have the ownership of the table and is not allowed to create a child table or delete the table.
Solution
Select a solution based on the permission model that you use.
- Standard PostgreSQL authorization model: Run the following command to grant owner permissions of the table to the current RAM user. In the command, p4_UID specifies the account information of the RAM user. For more information about the standard PostgreSQL authorization model, see Standard PostgreSQL authorization model.
```
alter table tablename owner to "p4_UID";
```
- SPM: Add the current RAM user to the developer user group or another user group that has higher permissions than the developer user group. For more information, see Use the SPM.

Why do I receive the following error message when I create a view across schemas: permission denied for table xxx?

Cause
The SLPM is enabled for the instance. This model does not allow you to create a view across schemas. For more information about the SLPM, see Overview.
Solution
Check whether the view is created across schemas when the SLPM is used.

Why do I receive the following error message when I execute an SQL statement: permission denied for Schema xxx?

Cause
The current RAM user is not granted the permissions on the schema.
Solution
Check the permission model used by the current RAM user and select a solution based on the permission model.
- If you use the SPM or the SLPM and the error message is returned when you query data, add the current RAM user to a viewer user group or a higher-level user group. If you use the SPM or the SLPM and the error message is returned when you create a table, add the current RAM user to the developer user group or a higher-level user group. For more information, see Use the SPM and Use the SLPM.
- If you use the standard PostgreSQL authorization model, explicitly grant the query permissions on the schema to the current RAM user based on your business requirements. For more information, see Standard PostgreSQL authorization model. You can run the following commands to grant permissions on the schema to the current RAM user:
```
-- Grant the RAM user the USAGE permission on the schema.
GRANT USAGE ON SCHEMA <schema_name> TO "Alibaba Cloud account ID/Alibaba Cloud email address";
-- Grant the RAM user the CREATE permission on the schema.
GRANT CREATE ON SCHEMA <schema_name> TO "Alibaba Cloud account ID/Alibaba Cloud email address";
                            
```

Why do I receive the following error message when I execute an SQL statement: ALTER TABLE xxx is not supported in Simple Privilege Mode?

Problem description
When I execute an SQL statement, the following error message is returned: ALTER TABLE xxx is not supported in Simple Privilege Mode.
Cause
The SPM is enabled for the current database. This model does not allow you to execute the ALTER TABLE statement.
Solution
- Method 1: Grant permissions on the table by using the SPM based on your business requirements.
- Method 2: Switch the permission model of the database from the SPM to the standard PostgreSQL authorization model. For more information, see Switch between permission models.
  Important
  We recommend that you do not switch to the standard PostgreSQL authorization model because permissions are granted in a fine-grained manner and the PostgreSQL authorization statements are complex in this model.