Hologres uses two independent access control layers: RAM permissions control which users can view or manage instances in the console, while development permissions control what data operations a user can perform inside an instance. This topic explains how to grant development permissions on a Hologres instance to a Resource Access Management (RAM) user using either the simple permission model or the standard PostgreSQL authorization model.
Background
By default, the Alibaba Cloud account that purchases a Hologres instance becomes its Superuser. A Superuser has full control over the instance, including creating and deleting databases, creating roles, and granting permissions to other roles.
RAM users have no instance access by default. Two things are true regardless of a RAM user's RAM-level permissions:
An Alibaba Cloud account must explicitly grant a RAM user development permissions before the user can perform data operations in the instance. A RAM user can also be granted Superuser permissions.
A RAM user with permission to purchase instances still needs development permissions before they can work with data.
RAM permissions and instance development permissions are independent. For details on granting RAM-level access, see Grant access to Hologres for RAM users.
Choose a permission model
| Model | Best for |
|---|---|
| Simple permission model (recommended) | Most use cases. Grants permissions at the database or schema level with minimal SQL. |
| Standard PostgreSQL authorization model | Fine-grained control over individual objects such as tables and schemas. Requires familiarity with PostgreSQL GRANT syntax. |
Grant permissions using the simple permission model
Optional: Grant permissions to the user.
The simple permission model also supports using SQL statements to grant permissions to RAM users. For more information, see Use the simple permission model.
The simple permission model lets you grant database-level or schema-level permissions with a few steps. It also supports SQL-based grants for more granular control.
For SQL-based grants using the simple permission model, see Use the simple permission model.
Grant permissions using the standard PostgreSQL authorization model
Prerequisites
Before you begin, make sure you have:
Superuser access to the Hologres instance
The UID of the RAM user you want to authorize
Step 1: Create the RAM user in the instance
Run one of the following statements to add the RAM user to the instance:
-- Add the RAM user (replace AccountID with the RAM user's UID)
CREATE USER "p4_AccountID";
-- Add the RAM user and grant Superuser permissions
CREATE USER "p4_AccountID" SUPERUSER;Step 2: Grant object-level permissions
Grant the RAM user the permissions needed to access specific objects.
The following table describes the available permission grants:
| Permission | Object | Purpose |
|---|---|---|
SELECT | A specific table | Allows the user to read data from the table |
SELECT, INSERT, UPDATE | All tables in a schema | Allows the user to read and write all tables in the schema |
-- Grant SELECT on a specific table
GRANT SELECT ON TABLE TABLENAME TO "AccountID";
-- Grant SELECT, INSERT, and UPDATE on all tables in the public schema
GRANT SELECT,INSERT,UPDATE ON ALL TABLES IN SCHEMA PUBLIC TO "p4_AccountID";Only a Superuser or the table owner can delete a table.
For the full reference on authorization operations, see Standard PostgreSQL authorization model.
Connect to the instance as a RAM user
After the RAM user is granted development permissions, they can connect to the instance using a PostgreSQL client. The following example uses the psql client:
PGUSER=<AccessID> PGPASSWORD=<AccessKey> psql -p <Port> -h <Endpoint> -d <Database>Replace the placeholders with the actual values:
| Placeholder | Description |
|---|---|
<AccessID> | The AccessID of the RAM user |
<AccessKey> | The AccessKey Secret of the RAM user |
<Port> | The port of the Hologres instance |
<Endpoint> | The endpoint of the Hologres instance |
<Database> | The database to connect to |
For more information about connecting with the psql client, see PSQL client.
View RAM user permissions
After connecting to the instance with a developer tool, run the following statements to inspect a user's roles and permissions:
-- View the roles assigned to a specific user
SELECT * FROM pg_roles WHERE rolname = 'p4_ID';
-- List all role names
SELECT rolname FROM pg_roles;
-- List all role display names
SELECT user_display_name(rolname) FROM pg_roles;
-- View permissions for all users
SELECT * FROM pg_catalog.pg_roles;Use SQL statements to view the permissions of a RAM user.
After you connect a developer tool to a Hologres instance, you can use the following SQL statements to view the permissions of a RAM user.
SELECT * FROM pg_roles WHERE rolname = 'p4_ID'; //View the roles that a member has.
SELECT rolname FROM pg_roles;
SELECT user_display_name(rolname) FROM pg_roles;You can use the SELECT * FROM pg_catalog.pg_roles; command to view the permissions of all users.