How to use permission models to grant permissions to a RAM user - Hologres

This topic explains how to use the simple permission model and the standard PostgreSQL authorization model to grant development permissions on a Hologres instance to a RAM user.

Background information

By default, the Alibaba Cloud account used to purchase an instance becomes the superuser for that instance. A superuser has all permissions on the instance, such as creating and deleting databases, creating roles, and granting permissions to roles.

A RAM user must be authorized by an Alibaba Cloud account to access an instance. A RAM user can also be assigned the Superuser role.
Even if a RAM user has the permission to purchase instances, they can perform data development in a Hologres instance only after an Alibaba Cloud account grants them development permissions on that instance.

RAM permissions and instance development permissions are controlled differently. For more information, see Grant permissions to a RAM user.

Grant permissions using the simple permission model (Recommended)

Optional: Grant permissions to the user. This step is required if you selected Normal user.

You can also use the simple permission model to grant permissions to a RAM user by executing SQL statements. For more information, see Use the simple permission model.

Grant permissions using the standard PostgreSQL model

To grant permissions to a RAM user using the standard PostgreSQL authorization model, follow these steps:

Create a user.

Add the RAM user to the Hologres instance. For example:

CREATE USER "p4_UID"; // The ID is the UID of the Alibaba Cloud RAM user.
CREATE USER "p4_UID" SUPERUSER; // Grants the superuser role to the RAM user.

Grant permissions to the user.
You must grant the RAM user the required permissions to access specific objects. For example:
```
GRANT SELECT ON TABLE TABLENAME TO "p4_UID"; // Grants the RAM user the SELECT permission on a table.
GRANT SELECT,INSERT,UPDATE ON ALL TABLES IN SCHEMA PUBLIC TO "p4_UID"; // Grants the RAM user the SELECT, INSERT, and UPDATE permissions on all tables in the PUBLIC schema.
```
Note
Only a superuser or the table owner can delete a table.
For more information about authorization in the standard PostgreSQL authorization model, see Standard PostgreSQL authorization model.

Using Hologres as a RAM user

After the permissions are granted, the RAM user can connect to and use the Hologres instance from a PSQL client. For more information, see PSQL client.

For example:

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

View RAM user permissions

View the permissions of a RAM user using SQL statements.

After you connect to the Hologres instance using a development tool, you can run the following SQL statements to view the permissions of the RAM user.

SELECT * FROM pg_roles WHERE rolname = 'p4_UID'; // View the roles of a specific member.
SELECT rolname FROM pg_roles;
SELECT user_display_name(rolname) FROM pg_roles;

Note

You can run the SELECT * FROM pg_catalog.pg_roles; command to view the permissions of all users.