Common development permission FAQ for Hologres - Hologres

Permission issues can cause errors during Hologres development. This topic describes solutions to common permission issues that occur in Hologres instances.

FAQ

You must have the required development permissions to perform operations in a Hologres instance. Click the following links to find solutions for common permission issues.

How do I choose the right Hologres permission model?
Error when operating in a Hologres instance: role "RAM$xxx" does not exist
Error when connecting to an instance: password authentication failed for user "xxx"
Error when querying a table: permission denied for table xxxx
Error when operating in a Hologres instance: permission denied for database "xxx" detail: user does not have CONNECT privilege
Error when using the call spm_enable() command: because roles conflict
Error during authorization: current database is NOT in simple privilege mode
Error when operating on a table: must be the owner of table xxxx
Error when querying a view created across schemas: permission denied for table xxx
Error when executing SQL: permission denied for Schema xxx
Error when executing SQL: ALTER TABLE xxx is not supported in Simple Privilege Mode

How do I choose the right Hologres permission model?

Hologres provides the standard PostgreSQL authorization model, the simple permission model, and the schema-level permission model (SLPM). This topic describes scenarios to help you select the most suitable permission model.

The standard PostgreSQL authorization model is the native permission model of PostgreSQL. If you are familiar with PostgreSQL and its permission management, you can use this model for authorization with no learning curve. Use this model if you require fine-grained permission management at the table level and need to grant and revoke permissions for each table and user.
The simple permission model (SPM) is a simple, database-level permission management model. All users who need to access a database must be added to a user group. Each user group has specific access permissions for objects in any schema within the database. You can use the SPM if you rarely use schemas for development, or if you only use schemas to classify table objects like folders and do not require schema-level permission isolation.
The schema-level permission model (SLPM) provides each schema with its own developer, writer, and viewer user groups. You can use the SLPM if you heavily rely on schema-level user and table permission isolation.

Error when operating in a Hologres instance: role "RAM$xxx" does not exist

Symptom
When you perform an operation, such as a query, in an instance after connecting to a development tool, the error `role "RAM$xxx" does not exist` is reported.
Cause
The current Resource Access Management (RAM) user has not been added to the instance.
Solution
You can grant the required permissions, such as Superuser, to the current RAM user for the instance. For more information about how to grant permissions, see Grant development permissions on an instance to a RAM user.

Error when connecting to an instance: password authentication failed for user "xxx"

Symptom
When you connect to an instance using a development tool, the error `password authentication failed for user "xxx"` is reported.
Cause
- If you log on to the instance using an AccessKey, this error indicates that the current user does not exist in the Hologres instance, or the AccessKey is incorrect or disabled.
- If you log on to the instance using a custom account (BASIC account), this error indicates that the current custom account does not exist in the Hologres instance, or the password is incorrect.
Solution
- If you log on to an instance using an AccessKey, check the following items in order:
  1. Check whether the current user exists in the instance. If the user does not exist, add the user. For more information, see User management.
  2. Check whether the AccessKey ID and AccessKey secret are correct and whether they are disabled.
- If you log on to an instance using a custom account (BASIC account), check the following items in order:
  1. Check whether the current custom account exists in the instance. If the account does not exist, add the account. For more information, see User management.
  2. Check whether the password for the custom account is correct.

Error when querying a table: permission denied for table xxxx

Symptom
When you perform an operation, such as a query, in the instance, the error `Execution failed: ERROR: permission denied for table xxxx` is reported.
Cause
The current account does not have the permission to view the table.
Solution
The solution varies based on the authorization model that is used.
- Standard PostgreSQL authorization model: Execute the following command to grant permissions to the user. In the command, `p4_UID` specifies the 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";
```
- Simple permission model: Add the current user to the viewer user group or a user group that has higher permissions. For more information, see Use the simple permission model.

Error when operating in a Hologres instance: permission denied for database "xxx" detail: user does not have CONNECT privilege

Symptom
When you perform an operation, such as a query, in an instance after connecting to a development tool, the error `FATAL: permission denied for database "xxx" detail: user does not have CONNECT privilege` is reported.
Cause
The current account is created in the instance but does not have development permissions on the instance.
Solution
You can grant the required permissions, such as Superuser, to the current user for the instance. For more information about how to grant permissions, see Grant development permissions on an instance to a RAM user.

Error when using the `call spm_enable()` command: because roles conflict

Symptom
In expert mode, running call spm_enable() returns a similar error because of a role conflict.
Cause
This error occurs because the simple permission model was previously enabled for the current database and residual information exists.
Solution
You can execute the call spm_enable ('t'); command to enable the model.

Error during authorization: current database is NOT in simple privilege mode

Symptom
When you perform an authorization operation, the error `current database is NOT in simple privilege mode` is reported.
Cause
The simple permission model is not enabled for the current database.
Solution
1. You can execute the show hg_experimental_enable_spm; command to check whether the simple permission model is disabled.
2. You can use the following commands to enable the simple permission model for the current database.
```
-- Enable the simple permission model for the current DB.  
call spm_enable ('t');
-- Change the owner of existing objects in the DB to the developer role to manage them using the SPM.
call spm_migrate (); 
```
  The `t` in the call spm_enable ('t'); command specifies that the original system roles and their permissions are retained instead of being deleted after you run the call spm_disable command. If you later run the spm_enable() command without `t`, a role conflict error occurs and the model cannot be enabled. The spm_enable ('t') command ignores this conflict and reuses the existing system roles.

Error when operating on a table: must be the owner of table xxxx

Symptom
When you perform an operation in the instance, the error `must be the owner of table xxxx` is reported.
Cause
The current RAM user cannot create child partitions or delete the table because this user is not the table owner.
Solution
The solution varies based on the authorization model that is used.
- Standard PostgreSQL authorization model: Execute the following command to grant ownership of the table to the current account. In the command, `p4_UID` specifies the 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";
```
- Simple permission model: Add the current user to the developer user group or a user group that has higher permissions. For more information, see Use the simple permission model.

Error when querying a view created across schemas: permission denied for table xxx

Cause
The schema-level permission model (SLPM) is enabled for the instance. The SLPM does not support the creation of views across schemas.
Solution
You can check whether you are creating a view across schemas in the SLPM.

Error when executing SQL: permission denied for Schema xxx

Cause
The query fails because the current user does not have permissions on the schema.
Solution
You can confirm the permission model for the current user and select the appropriate solution.
- If you use the SPM or SLPM, you can add the user to the viewer user group or a user group that has higher permissions for queries. To resolve errors that occur during table creation, you can add the user to the developer user group or a user group that has higher permissions. For more information, see SPM authorization and SLPM authorization.
- If you use the standard PostgreSQL authorization model, you must explicitly grant the user query permissions on the schema. You can grant the required permissions to the user. For more information, see Standard PostgreSQL authorization model. You can use the following commands to grant query permissions on a schema:
```
-- Grant the USAGE permission on a schema to view all tables in the schema.
GRANT USAGE ON SCHEMA <schema_name> TO "Alibaba Cloud account/email";
-- Grant the CREATE permission on a schema to create tables in the schema.
GRANT CREATE ON SCHEMA <schema_name>  TO "Alibaba Cloud account/email";
                            
```

Error when executing SQL: ALTER TABLE xxx is not supported in Simple Privilege Mode

Error
When you execute an SQL statement, the error `ALTER TABLE xxx is not supported in Simple Privilege Mode` is reported.
Cause
This error occurs because the simple permission model is enabled for the current database. This model does not support the direct execution of ALTER TABLE operations.
Solution
- Method 1: Grant the required permissions in the simple permission model.
- Method 2: Switch the permission model of the database from the simple permission model to the standard PostgreSQL authorization model. For more information, see Switch permission models.
  Important
  The standard PostgreSQL authorization model has fine permission granularity and is difficult to manage. We do not recommend that you switch to this model unless it is necessary.

FAQ