How to grant and revoke user privileges in Hologres-Hologres(Hologres)-阿里云帮助中心

Granting privileges

After connecting a Hologres instance to a development tool, you can run SQL statements to grant privileges to a user with the standard PostgreSQL authorization model.

Create a user.

To access Hologres and develop data, an account must first be created as a Hologres user.

The syntax for creating a user is as follows:

-- Create a user that has the privilege to log on to the Hologres instance. If you grant privileges to a RAM user, use the required format for RAM users.
CREATE USER "Alibaba Cloud account ID/email address"; 
-- Create a user and grant the superuser privilege.
CREATE USER "Alibaba Cloud account ID/email address" SUPERUSER;

You can create a user based on the following examples. For more information about the formats of Alibaba Cloud accounts and RAM users, see Account overview.

-- Create a user by using an Alibaba Cloud account ID.
CREATE USER "11822780xxx";
-- Grant the superuser privilege to a RAM user.
CREATE USER "p4_1822780xxx" SUPERUSER;

For more information about creating roles, see CREATE ROLE.

Grant privileges.

After creating a Hologres user, you must grant them privileges. The standard PostgreSQL authorization model allows you to control user privileges at the database, table, view, and column levels. The following table describes common authorization operations in Hologres.

Note

The standard PostgreSQL authorization model can grant privileges only on existing objects. These privileges do not apply to objects created after the grant. For example, if user A grants user B the SELECT privilege on all tables in the public schema and then creates a new table, user B does not have the SELECT privilege on the new table. User A must grant the privilege on the new table explicitly.

Description	Syntax example	Required
Create a user with the privilege to log on to a Hologres instance	`CREATE USER "Alibaba Cloud account/email address";`	Required
Create a user and grant the superuser privilege	`CREATE USER "Alibaba Cloud account/email address" SUPERUSER ;`	Optional
Grant the CREATE privilege on a schema	`GRANT CREATE ON SCHEMA schema_name TO "Alibaba Cloud account/email address";`	Optional
Grant the USAGE privilege on a schema	`GRANT USAGE ON SCHEMA schema_name TO "Alibaba Cloud account/email address";`	Required Note A user must have the USAGE privilege on a schema to query its tables.
Grant all users the SELECT, INSERT, and UPDATE privileges on all tables in the public schema	`GRANT SELECT,INSERT,UPDATE ON ALL TABLES IN SCHEMA public to PUBLIC;`	Optional
Grant a user the SELECT privilege on a table	`GRANT SELECT ON TABLE <tablename> TO "Alibaba Cloud account/email address";`	Optional
Grant a user the SELECT privilege on a table with the grant option	`GRANT SELECT ON TABLE <tablename> TO "Alibaba Cloud account/email address" WITH GRANT OPTION;`	Optional
Grant a user the SELECT privilege on all tables in the public schema	`GRANT SELECT ON ALL TABLES IN SCHEMA public TO "Alibaba Cloud account/email address";`	Optional
Grant all users the SELECT privilege on future tables created by the current role in the `public` schema.	`ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO PUBLIC;`	Optional
Change a regular user to a superuser	`ALTER USER "Alibaba Cloud account/email address" SUPERUSER;`	Optional
Change a superuser to a regular user	`ALTER USER "Alibaba Cloud account/email address" NOSUPERUSER;`	Optional
Transfer the ownership of a table to another user	`ALTER TABLE <tablename> OWNER TO "Alibaba Cloud account/email address";`	Optional
Create a role without the login privilege	`CREATE ROLE "Alibaba Cloud account/email address";`	Optional
Grant a role to a user	`GRANT <rolename> TO "Alibaba Cloud account/email address" ;`	Optional
Grant a user the SELECT privilege on specific columns of a table	`GRANT SELECT (<column1>,<column2>,<column3>,...) ON TABLE <tablename> TO "Alibaba Cloud account/email address" ;`	Optional
Grant a user the SELECT privilege on a view Note In the standard PostgreSQL authorization model, you must grant the SELECT privilege on a view to access it. In the simple permission model (SPM)/schema-level permission model (SLPM), you must be a member of the viewer user group or a user group with higher privileges to access a view.	`-- Grant a user the SELECT privilege on a view by using the standard PostgreSQL authorization model. GRANT SELECT ON <viewname> TO "Alibaba Cloud account/email address" ;`	Optional

In the standard PostgreSQL authorization model, you can run the following statements to grant the SELECT privilege on a table to a new user.

CREATE USER "Alibaba Cloud account/email address";
GRANT USAGE ON SCHEMA <schema_name>  TO "Alibaba Cloud account/email address";
GRANT SELECT ON TABLE <tablename> TO "Alibaba Cloud account/email address";

The CREATE ROLE statement is used to create a role without the privilege to log on to a Hologres instance, such as a user group or a virtual role that represents a specific type of user. For more information about granting privileges, see GRANT.

Drop a table.
Only a superuser or the table owner can drop a table. To grant users the privilege to drop a table, use one of the following methods:
- Transfer the ownership of the table to a new user.
```
ALTER TABLE TABLENAME OWNER TO "Alibaba Cloud account/email address";
```
- Grant the superuser privilege to a new user.
```
ALTER USER "Alibaba Cloud account/email address" SUPERUSER;
```
- Add multiple users to a user group and transfer the table ownership to the user group.
```
CREATE USER "Alibaba Cloud account ID/email address";
CREATE ROLE <rolename>;
GRANT <rolename> TO "Alibaba Cloud account/email address";
ALTER TABLE <tablename> OWNER TO <rolename>;
```

Default privileges for future tables

By default, the standard PostgreSQL authorization model does not grant privileges on future tables. To do so, you must use the ALTER DEFAULT PRIVILEGES statement.

Note

This statement does not affect existing logical objects.
This statement can set default privileges only for tables, schemas, functions, sequences, or types.

Grant privileges.
- After you configure default privileges, tables that a user creates in a specific schema can be queried by a specified user or all users. The following sample statements show how to grant default privileges.
  - After you run this statement, all users can query future tables that are created by the user p4_id1 in the public schema.
```
ALTER DEFAULT PRIVILEGES FOR ROLE "p4_id1" IN SCHEMA public GRANT SELECT ON TABLES TO PUBLIC;
```
  - After you run this statement, the user p4_id2 can query future tables that are created by the user p4_id1 in the public schema.
```
ALTER DEFAULT PRIVILEGES FOR ROLE "p4_id1" IN SCHEMA public GRANT SELECT ON TABLES TO "p4_id2";
```
  - After you run this statement, all users can query future tables that are created by the user p4_id1 in the test schema.
```
ALTER DEFAULT PRIVILEGES FOR ROLE "p4_id1" IN SCHEMA test GRANT SELECT ON TABLES TO PUBLIC;
```
- To revoke the default privileges, run the following SQL statements.
  - Revoke from all users the default SELECT privilege on future tables that are created by the user p4_id1 in the public schema.
```
ALTER DEFAULT PRIVILEGES FOR ROLE "p4_id1" IN SCHEMA public REVOKE SELECT ON TABLES FROM PUBLIC;
```
  - Revoke from the user p4_id2 the default SELECT privilege on future tables that are created by the user p4_id1 in the public schema.
```
ALTER DEFAULT PRIVILEGES FOR ROLE "p4_id1" IN SCHEMA public REVOKE SELECT ON TABLES FROM "p4_id2";
```
  - Revoke from all users the default SELECT privilege on future tables that are created by the user p4_id1 in the test schema.
```
ALTER DEFAULT PRIVILEGES FOR ROLE "p4_id1" IN SCHEMA test REVOKE SELECT ON TABLES FROM PUBLIC;
```
Verify that the default privileges are set.
- In a psql client, run the \ddp command to verify that the ALTER DEFAULT PRIVILEGES statement was successful.
- Run the following SQL statement in Hologres to query the result directly.
```
SELECT pg_catalog.pg_get_userbyid(d.defaclrole) AS "Owner",
  n.nspname AS "Schema",
  CASE d.defaclobjtype WHEN 'r' THEN 'table' WHEN 'S' THEN 'sequence' WHEN 'f' THEN 'function' WHEN 'T' THEN 'type' WHEN 'n' THEN 'schema' END AS "Type",
  pg_catalog.array_to_string(d.defaclacl, E'\n') AS "Access privileges"
FROM pg_catalog.pg_default_acl d
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = d.defaclnamespace
ORDER BY 1, 2, 3;
```
When a new table is created, Hologres matches the current user and schema against the pg_catalog.pg_default_acl system catalog. If a matching ALTER DEFAULT PRIVILEGES rule is found, the rule is applied to the new table. The current user is determined as follows:
- If the current role is a user, the user is used for matching when the table is created.
- If a user runs the SET SESSION ROLE GROUP1; statement before creating a table, the current role changes to GROUP1. In this case, GROUP1 is used for matching.
The matching rule is triggered only when a table is created. If you run the ALTER TABLE SET OWNER TO statement to change the table owner after the table is created, the matching rule is not triggered.

Revoking privileges

To revoke privileges from a user, run the REVOKE statement. For more information about revoking privileges, see REVOKE.

REVOKE SELECT ON TABLE tablename FROM "Alibaba Cloud account ID/email address" ; -- If you revoke privileges from a RAM user, use the required format for RAM users.

System catalog privileges

Starting from Hologres V3.0, when a user connects to a database, they can view information about all schemas and tables in the instance by querying the pg_class, pg_attribute, and pg_namespace system catalogs. This is especially true when connecting with BI or development tools, which automatically fetch schema, table, and column lists from these system catalogs. This allows any user to retrieve metadata, even without access privileges to the actual data. To address this, Hologres V3.0.23 and later versions support row-level access control (RLS) on these three system catalogs. This ensures that only authorized users can see the corresponding metadata. You can enable RLS by setting the following GUC parameter:

This GUC parameter controls only the pg_class, pg_attribute, and pg_namespace system catalogs.
A superuser must run this statement once for each database.
After this is set, only a superuser, an object's owner, or a user with any privilege on an object (table or schema) can see the corresponding metadata. Other users cannot view the metadata.

-- Set by a superuser.
ALTER DATABASE <database_name> SET hg_experimental_enable_catalog_rls = on;

Note

For more information about system catalogs, see System Catalogs.

Viewing privileges

You can run the following SQL statements to view user roles and privileges.

SELECT ROLNAME FROM pg_roles;
SELECT user_display_name(ROLNAME) FROM pg_roles;

Drop a user

If your instance is connected to a development tool, you can run SQL statements to drop a user. The procedure varies based on the user type.

Drop a regular user
To drop a regular user who has not created any objects such as tables, views, or extensions, you can run the following statement or drop the user in the HoloWeb console.
```
drop user "Alibaba Cloud account ID/email address";
```
Drop an administrator such as a superuser
To drop an administrator, such as a superuser, who has created objects and is their owner, you must first transfer the ownership of these objects. This is especially important in the standard PostgreSQL authorization model. Attempting to drop the user directly results in an error. Run the following statements:
```
-- Transfer objects owned by Account A to Account B.
reassign owned by "Alibaba Cloud account ID of A" to "Alibaba Cloud account ID of B";     
-- Drop Account A.
drop user "Alibaba Cloud account ID of A";
```

You can drop a RAM user from an instance by using one of the following methods:

DROP USER "Alibaba Cloud account ID/email address";

Important

After a RAM user is dropped, they can no longer connect to the instance or access its objects. Proceed with caution.

PostgreSQL has a strict privilege model. For best practices to help you choose an approach based on your business requirements, see Authorization based on the standard PostgreSQL permission model.