Hologres:Grant development permissions to a RAM user

Grant permissions using the simple permission model (Recommended)

To visually grant permissions in the Hologres management console, follow these steps:

1. Create a user.

   1. Log in to the Alibaba Cloud official website with your Alibaba Cloud account.

   2. Log in to the Hologres management console and click the name of the target instance to open its details page.

   3. In the left-side navigation pane of the instance details page, click Account Management. The Users page opens by default.

   4. On the Users page, click Add User in the upper-right corner.

   5. In the Add User dialog box, use the search box to find and select the target user. In the Select Member Role section, select Superuser or Normal user, and then click OK.

      Parameter	Description
      Select Organization Member	Select the RAM user to whom you want to grant permissions and add them to the instance.
      Select Member Role	Superuser: Has all operational permissions on the instance. Normal user: Has no operational permissions on the instance by default. You must grant specific permissions to the RAM user before they can connect to and use the Hologres instance.

   6. Click OK.

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

   1. On the Users page, click DB Authorization in the left-side navigation pane.

      If the DB Management page has no databases, you need to perform the following operations:

      1. Click Create Database.

      2. Configure the Instance Name, enter a Database Name, and set Permission Model to SPM.

   2. Go to the DB Authorization page. In the Actions column, click Authorize User > Grant Permission.

   3. In the Grant Permission dialog box, select the target user from the User drop-down list. In the User Group section, select a role: Admin, Developer, Writer, or Viewer. Then, click OK.

      Parameter	Description
      User	The RAM user to whom you want to grant permissions.
      User Group	Admin: Can manage all objects and user groups in the database. Developer: Can use DDL statements to create, delete, or modify database objects, and can read from and write to database objects. Writer: Can read from and write to database objects. Viewer: Has read-only permissions on all database objects.

   4. Click OK.

   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:

1. 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.

2. 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.

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

View RAM user permissions

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;