All Products
Search
Document Center

Hologres:Grant the development permissions on a Hologres instance to a RAM user

Last Updated:Feb 27, 2024

This topic describes how to use the simple permission model (SPM) or the standard PostgreSQL authorization model to grant the development permissions on a Hologres instance to a RAM user by using your Alibaba Cloud account.

Background information

By default, the system sets the Alibaba Cloud account that is used to purchase an instance as a superuser of the instance. A superuser has all permissions on the instance. The superuser can create and delete databases, create roles, and grant permissions to roles.

When you access Hologres as a RAM user, take note of the following items:

  • The RAM user must be authorized by the Alibaba Cloud account before the RAM user can access a Hologres instance. The RAM user can be assigned the superuser role.

  • Even if the RAM user has permissions to purchase instances, the RAM user can perform data development in a Hologres instance only after relevant development permissions on the instance are granted by the Alibaba Cloud account.

The RAM permissions and the development permissions on instances apply to different scenarios. For more information, see Grant permissions to a RAM user.

Use the SPM to grant permissions to a RAM user (recommended)

  1. (Optional) Grant permissions to the RAM user.

    The SPM also allows you to grant permissions to a RAM user by executing SQL statements. For more information, see Use the SPM.

Use the standard PostgreSQL authorization model to grant permissions to a RAM user

To use the standard PostgreSQL authorization model to grant permissions to a RAM user, perform the following steps:

  1. Create a RAM user.

    Create a RAM user for a Hologres instance. Sample statements:

    CREATE USER "p4_Account ID"; // Specify the UID of the RAM user. 
    CREATE USER "p4_Account ID" SUPERUSER; // Assign the superuser role to the RAM user.

  2. Grant permissions to the RAM user.

    You can perform operations on the Hologres instance as the RAM user only after the required permissions are granted to the RAM user. Sample statements:

    GRANT SELECT ON TABLE TABLENAME TO "Account ID"; // Grant the RAM user the SELECT permission on a table. 
    GRANT SELECT,INSERT,UPDATE ON ALL TABLES IN SCHEMA PUBLIC TO "p4_Account ID"; // Grant the RAM user the SELECT, INSERT, and UPDATE permissions on all tables.

    Note

    Only the superusers and owner of a table can delete the table.

    For more information about the authorization by using the standard PostgreSQL authorization model, see Standard PostgreSQL authorization model.

Use Hologres as a RAM user

After a RAM user is granted required permissions on a Hologres instance, you can use the PostgreSQL client to connect to and perform operations on the Hologres instance as the RAM user. For more information, see Use the PostgreSQL client to connect to Hologres.

Sample statements:

PGUSER=<AccessID> PGPASSWORD=<AccessKey> psql -p <Port> -h <Endpoint> -d <Database>

View the permissions that are granted to a RAM user

This section describes how to view the permissions that are granted to a RAM user.

  • Use SQL statements

    Use a development tool to connect to the Hologres instance and execute the following SQL statements to query the permissions that are granted to the RAM user:

    SELECT * FROM pg_roles WHERE rolname = 'p4_ID'; // Query the role that is assigned to the user. 
    SELECT rolname FROM pg_roles;
    SELECT user_display_name(rolname) FROM pg_roles;