Standard PostgreSQL authorization model - Hologres - Alibaba Cloud Documentation Center

Grant permissions by using the standard PostgreSQL authorization model

After you connect a Hologres instance to a development tool, you can execute SQL statements to grant permissions on the instance to a user by using the standard PostgreSQL authorization model.

Create a user in the Hologres instance.

You must create a Hologres user for an account before the account can be used to access Hologres and develop data.

You can create a user by executing an SQL statement in the following syntax:

-- Create a user that has permissions to log on to the Hologres instance. If you want to grant permissions to a RAM user, you must follow the formats that are used to specify RAM users in Hologres. 
CREATE USER "Alibaba Cloud account ID/Alibaba Cloud account name/RAM username"; 
-- Create a user and assign the superuser role to the user. 
CREATE USER "Alibaba Cloud account ID/Alibaba Cloud account name/RAM username" SUPERUSER;

The following sample statements show how to create a user. For more information about the formats that are used to specify Alibaba Cloud accounts and RAM users in Hologres, see Overview.

-- Use the ID of an Alibaba Cloud account to create a user. 
CREATE USER "11822780xxx";
-- Create a RAM user and assign the superuser role to the RAM user. 
CREATE USER "p4_1822780xxx" SUPERUSER;

For more information about how to create a role, see CREATE ROLE.

Grant permissions to a user.

After you create a Hologres user, you must grant permissions to the user. In this case, the user can perform authorized operations in Hologres. You can grant the permissions on databases, tables, views, and columns to the user by using the standard PostgreSQL authorization model. The following table describes the statements that are commonly used to grant permissions in Hologres.

Note You can grant permissions only on existing objects by using the standard PostgreSQL authorization model. Permissions do not take effect on objects that are created after the permissions are granted. For example, User A grants User B the SELECT permission on all tables in the public schema. Then, User A creates another table. User B does not have the SELECT permission on this table. To allow User B to query this table, User A must grant the SELECT permission on this table to User B.


Operation	Statement	Required
Create a user that has permissions to log on to a Hologres instance	`CREATE USER "Alibaba Cloud account ID/Alibaba Cloud account name/RAM username";`	Yes
Create a user and assign the superuser role to the user	`CREATE USER "Alibaba Cloud account ID/Alibaba Cloud account name/RAM username" SUPERUSER;`	No
Grant a user the permissions to create a table in a schema	`GRANT CREATE ON SCHEMA schema_name TO "Alibaba Cloud account ID/Alibaba Cloud account name/RAM username";`	No
Grant a user the permissions to access a schema	`GRANT USAGE ON SCHEMA schema_name TO "Alibaba Cloud account ID/Alibaba Cloud account name/RAM username";`	Yes Note A user can query tables in a schema only after the user is granted the permissions to access the schema.
Grant all users the SELECT, INSERT, and UPDATE permissions on all tables in the public schema	`GRANT SELECT,INSERT,UPDATE ON ALL TABLES IN SCHEMA public to PUBLIC;`	No
Grant a user the SELECT permission on a table	`GRANT SELECT ON TABLE <tablename> TO "Alibaba Cloud account ID/Alibaba Cloud account name/RAM username";`	No
Grant a user the SELECT permission on a table and authorize the user to grant the permission to other users	`GRANT SELECT ON TABLE <tablename> TO "Alibaba Cloud account ID/Alibaba Cloud account name/RAM username" WITH GRANT OPTION;`	No
Grant a user the SELECT permission on all tables in the public schema	`GRANT SELECT ON ALL TABLES IN SCHEMA public TO "Alibaba Cloud account ID/Alibaba Cloud account name/RAM username";`	No
Grant all users the read permissions on the tables that will be created in the `public schema` in the future	`ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO PUBLIC;`	No
Change the role of a user from a regular user to a superuser	`ALTER USER "Alibaba Cloud account ID/Alibaba Cloud account name/RAM username" SUPERUSER;`	No
Change the role of a user from a superuser to a regular user	`ALTER USER "Alibaba Cloud account ID/Alibaba Cloud account name/RAM username" NOSUPERUSER;`	No
Set a user as an owner of a table created by another user	`ALTER TABLE <tablename> OWNER TO "Alibaba Cloud account ID/Alibaba Cloud account name/RAM username";`	No
Create a role that does not have permissions to log on to a Hologres instance	`CREATE ROLE "Alibaba Cloud account ID/Alibaba Cloud account name/RAM username";`	No
Assign a role to a user	`GRANT <rolename> TO "Alibaba Cloud account ID/Alibaba Cloud account name/RAM username";`	No
Grant a user the SELECT permission on some columns of a table	`GRANT SELECT (<column1>,<column2>,<column3>,...) ON TABLE <tablename> TO "Alibaba Cloud account ID/Alibaba Cloud account name/RAM username";` entry	No

For example, you can execute the following SQL statements to grant the SELECT permission on a table to a new user by using the standard PostgreSQL authorization model:

CREATE USER "Alibaba Cloud account ID/Alibaba Cloud account name/RAM username";
GRANT USAGE ON SCHEMA <schema_name> TO "Alibaba Cloud account ID/Alibaba Cloud account name/RAM username";
GRANT SELECT ON TABLE <tablename> TO "Alibaba Cloud account ID/Alibaba Cloud account name/RAM username";

The CREATE ROLE statement is used to create a role that does not have permissions to log on to a Hologres instance. For example, you can execute the statement to create a virtual role or a user group that represents a specific user type. For more information about how to grant permissions, see GRANT.

Grant one or more users the permissions to delete a table.
Only superusers and owners of a table can delete the table. You can grant one or more users the permissions to delete a table by using one of the following methods:
- Set a new user as an owner of a table.
```
ALTER TABLE TABLENAME OWNER TO "Alibaba Cloud account ID/Alibaba Cloud account name/RAM username";
```
- Assign the superuser role to a new user.
```
ALTER USER "Alibaba Cloud account ID/Alibaba Cloud account name/RAM username" SUPERUSER;
```
- Add multiple users to a user group and assign the ownership of the table to the user group.
```
CREATE USER "Alibaba Cloud account ID/Alibaba Cloud account name/RAM username";
CREATE ROLE <rolename>;
GRANT <rolename> TO "Alibaba Cloud account ID/Alibaba Cloud account name/RAM username";
ALTER TABLE <tablename> OWNER TO <rolename>;
```

Grant permissions on the tables that will be created in the future

The standard PostgreSQL authorization model does not allow you to grant a user the permissions on the tables that will be created in the future. You can execute the ALTER DEFAULT PRIVILEGES statement to grant a user the permissions on the tables that will be created in the future. To grant permissions on the tables that will be created in the future, perform the following steps:

Note

This statement does not affect the permissions on existing objects.
This statement can be used to grant the default permissions only on tables, schemas, functions, sequences, and types.

Grant the default permissions.
- Grant all users or a specific user the SELECT permission as the default permission on the tables that you will create in a schema in the future. For example, you can execute the following statements.
  - Grant the p4_123xxx user the SELECT permission on the tables that you will create in the public schema in the future.
```
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO "p4_123xxx";
```
  - Grant the p4_123xxx user the SELECT permission on the tables that you will create in the test schema in the future.
```
ALTER DEFAULT PRIVILEGES IN SCHEMA test GRANT SELECT ON TABLES TO "p4_123xxx";
```
  - Grant all users the SELECT permission on the tables that you will create in the public schema in the future.
```
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO PUBLIC;
```
- Grant all users or a specific user the SELECT permission as the default permission on the tables that another user will create in the future. For example, you can execute the following statements.
  - Grant all users of the public schema the SELECT permission on the tables that the p4_id1 user will create in the future.
```
ALTER DEFAULT PRIVILEGES FOR ROLE "p4_id1" IN SCHEMA public GRANT SELECT ON TABLES TO PUBLIC;
```
  - Grant the p4_id2 user of the public schema the SELECT permission on the tables that the p4_id1 user will create in the future.
```
ALTER DEFAULT PRIVILEGES FOR ROLE "p4_id1" IN SCHEMA public GRANT SELECT ON TABLES TO "p4_id2";
```
  - Grant all users of the test schema the SELECT permission on the tables that the p4_id1 user will create in the future.
```
ALTER DEFAULT PRIVILEGES FOR ROLE "p4_id1" IN SCHEMA test GRANT SELECT ON TABLES TO PUBLIC;
```
- Revoke the SELECT permission that you granted based on specific scenarios. For example, you can execute the following statements:
```
-- Revoke the SELECT permission on the tables that you will create in the public schema in the future from the p4_123xxx user.
ALTER DEFAULT PRIVILEGES IN SCHEMA public REVOKE SELECT ON TABLES FROM "p4_123xxx";

-- Revoke the SELECT permission on the tables that you will create in the test schema in the future from the p4_123xxx user.
ALTER DEFAULT PRIVILEGES IN SCHEMA test REVOKE SELECT ON TABLES FROM "p4_123xxx";

-- Revoke the SELECT permission on the tables that you will create in the public schema in the future from all users.
ALTER DEFAULT PRIVILEGES IN SCHEMA public REVOKE SELECT ON TABLES FROM PUBLIC;
```
Check whether the default permissions are granted.
- Run the \ddp command on the PostgreSQL client to query the execution result of the ALTER DEFAULT PRIVILEGES statement.
- Execute the following SQL statement in Hologres to directly check the result:
```
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 you create a table as a user, Hologres checks the user and schema based on the pg_catalog.pg_default_acl system table. If an ALTER DEFAULT PRIVILEGES statement is detected, Hologres uses the relevant matching rule for authentication. Take note of the following matching rules:
- If you perform operations as a user, Hologres checks whether the user has the default permissions.
- If you execute the SET SESSION ROLE GROUP1; statement before you create a table as a user, the GROUP1 role is assigned to the user. Then, Hologres checks whether the GROUP1 role has the default permissions.
The matching rules take effect only when you create tables. If you execute the ALTER TABLE SET OWNER TO statement to modify the table owner after a table is created, the matching rules are not triggered.

Revoke permissions by using the standard PostgreSQL authorization model

To revoke specific permissions from a user, execute the following REVOKE statement. For more information, see REVOKE.

REVOKE SELECT ON TABLE tablename FROM "Alibaba Cloud account ID/Alibaba Cloud account name/RAM username"; -- If you want to revoke permissions from a RAM user, you must follow the formats that are used to specify RAM users in Hologres.

Query the permissions of a user

Execute the following SQL statements to query the permissions of a user:

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

Delete a user from a Hologres instance

If your Hologres instance is connected to a development tool, you can use SQL statements to delete users.

Delete a regular user
If your regular user has not created objects such as tables, views, or extensions, you can execute the following statement to delete the user from the Hologres instance. You can also delete the user in the HoloWeb console.
```
DROP USER "Alibaba Cloud account ID/Alibaba Cloud account name/RAM username";
```
Delete an administrator
If your user has created objects such as tables, views, or extensions in the instance and is the administrator of these objects (especially in the standard PostgreSQL authorization model), you must transfer the objects to another user before you can delete the user. You can execute the following statements to delete the user:
```
-- Transfer the objects that are owned by User A to User B.
REASSIGN OWNED BY "ID of User A" to "ID of User B";     
-- Delete User A.
DROP USER "ID of User A";
```

You can execute the following SQL statement to delete a RAM user from a Hologres instance:

DROP USER "Alibaba Cloud account ID/Alibaba Cloud account name/RAM username";

Important After you delete a RAM user from a Hologres instance, the RAM user cannot connect to the instance or access objects in the instance. Proceed with caution.

The standard PostgreSQL authorization model divides permissions in a strict manner. Best practices for using the model are provided for your reference. You can use the model based on best practices and your business requirements. For more information, see Authorize roles based on PostgreSQL privileges.