When you perform data analytics operations on a Hologres instance, an error may occur if you are not granted the required development permissions. This topic provides answers to frequently asked questions about the development permissions on Hologres instances.

Overview

You can perform data analytics operations on a Hologres instance only after you are granted specific 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?

Hologres allows you to use the standard PostgreSQL authorization model, simple permission model (SPM), or schema-level permission model (SLPM). The following content describes how to select an appropriate Hologress permission model in different scenarios:
  • The standard PostgreSQL authorization model is a native PostgreSQL permission model. If you are familiar with PostgreSQL and relevant permission management, you can grant permissions to users by using this model with ease. If you need to manage permissions on tables and have sufficient time and energy, you can grant each user the permissions to manage tables and revoke the permissions by using this model.
  • The SPM is a database-level simple permission model. In this model, all users who need to use a database are added to a specific user group. Each user group is granted specific access permissions on all objects in a schema. 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 the schema-level user isolation and permission isolation for tables, we recommend that you use the SLPM.

Why is the error message "role "RAM$xxx" doesn't not exsit" returned when I perform data analytics operations on a Hologres instance?

  • Issue description

    After I connected to an instance by using a development tool, I could not perform data analytics operations such as data query in the instance. The following error message is returned: role "RAM$xxx" doesn't not exsit.

  • Cause

    The current Resource Access Management (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 authorization, see Grant the development permissions on a Hologres instance to RAM users.

Why is the error message "Cloud authentication failed for access id "xxxx"" returned when I connect to an instance by using a development tool?

  • Issue description

    When I connected to an instance by using a development tool, an error occurred. The following error message is returned: Cloud authentication failed for access id "xxxx".

  • Cause

    The specified password is invalid.

  • Solution

    You must check the password to ensure that the password is an AccessKey pair and does not contain spaces. For more information about the AccessKey pair, see Create an Alibaba Cloud account.

Why is the error message "permission denied for table xxxx" returned when I query a table?

  • Issue description

    When I performed data analytics operations such as data query in an instance, an error occurred. The following error message is returned: Execution failed: ERROR: permission denied for table xxxx.

  • Cause

    The current RAM user does not have permissions to view specific tables.

  • Solution

    You can select a solution based on the permission model that you use.

    • Standard PostgreSQL authorization model: In this model, run the following command to grant permissions 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. 
      grant select on table tablename to "p4_UID";
    • SPM: In this model, add the current RAM user to a viewer group or another user group. For more information, see Use the SPM.

Why is the error message "permission denied for database''xxx'' detail: user does not have CONNECT privilege" returned when I perform data analytics operations on a Hologres instance?

  • Issue description

    After I connected to an instance by using a development tool, I could not perform data analytics operations such as data query in the instance. 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

    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 authorization, see Grant the development permissions on a Hologres instance to RAM users.

Why is the error message "because roles conflict" returned when I run the call spm_enable() command?

  • Issue description

    When I run the call spm_enable() command to manage a database that uses the standard PostgreSQL authorization model, 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

    You need to run the call spm_enable ('t'); command to enable the SPM.

Why is the error message "current database is NOT in simple privilege mode" returned when I grant permissions on specific objects of a database?

  • Issue description

    The following error message is returned when I grant permissions on specific objects of a database: 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 current database.  
call spm_enable ('t');
-- Change the owner of the objects in the database to a user in a developer group and use the SPM to manage the objects.
call spm_migrate ();
      If you run the call spm_enable ('t'); command with 't', 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 without 't', 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 is the error message "must be the owner of table xxxx" returned when I manage a table?

  • Issue description

    When I performed data analytics operations on an instance, an error occurred. The following error message is returned: must be the owner of table xxxx.

  • Cause

    The current RAM user is not the owner of the table and is not allowed to create a child table or delete the table.

  • Solution

    You can select a solution based on the permission model that you use.

    • Standard PostgreSQL authorization model: In this 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: In this model, add the current RAM user to a developer group or another user group. For more information, see Use the SPM.

Why is the error message "permission denied for table xxx" returned when I create a view across schemas?

  • Cause

    The SLPM feature is enabled for the instance. You cannot create a view across schemas by using this model. For more information about the SLPM, see Overview.

  • Solution

    You need to check whether the view is created across schemas by using the SLPM.

Why is the error message "permission denied for Schema xxx" returned when I execute an SQL statement?

  • Cause

    The current RAM user is not granted the permissions on the schema, which has caused the query to fail.

  • Solution

    You need to verify the permission model that the current RAM user uses 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 execute an SQL statement to query data, you need to add the current RAM user to a viewer group or another user group. If you use the SPM or the SLPM and the error message is returned when you execute an SQL statement to create a table, you need to add the current RAM user to a developer group or another user group. For more information, see Use the SPM and Use the SLPM.
    • If you use the standard PostgreSQL authorization model, you need to 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. Run the following commands to grant permissions on the schema to the current RAM user:
      -- Grant the RAM user the permissions to query all tables in the schema.
GRANT USAGE ON SCHEMA <schema_name> TO "Alibaba Cloud account ID/Alibaba Cloud account name/RAM username";
-- Grant the RAM user the permissions to create a table in the schema.
GRANT CREATE ON SCHEMA <schema_name> TO "Alibaba Cloud account ID/Alibaba Cloud account name/RAM username";

Why is the error message "ALTER TABLE xxx is not supported in Simple Privilege Mode" returned when I execute an SQL statement to modify a table?

  • Issue description

    When I executed an SQL statement to modify a table, an error occurred. 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. You cannot execute the ALTER TABLE statement to modify a table by using the SPM.

  • Solution
    • Method 1: You can grant permissions on the table by using the SPM based on your business requirements.Overview
    • 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.