How to grant permissions based on the standard PostgreSQL authorization model - Hologres

This topic provides the best practices for granting permissions based on the standard PostgreSQL authorization model in Hologres. This helps you simplify authorization and manage permissions in a fine-grained manner.

Background information

Hologres is compatible with PostgreSQL and supports the standard PostgreSQL authorization model. Hologres also provides the simple permission model (SPM). For more information, see Overview.

However, SPM manages permissions in a coarse-grained manner. The standard PostgreSQL authorization model manages permissions in a fine-grained manner. If you need to manage permissions in a fine-grained manner, see the "Best practice 1" and "Best practice 2" sections of this topic.

Overview

The standard PostgreSQL authorization model has a detailed permission management system. For more information, see PostgreSQL authorization.

The standard PostgreSQL authorization model has the following limits:

PostgreSQL permissions apply only to the existing objects and do not apply to new objects. Examples:
1. User1 executes the GRANT SELECT ON ALL TABLES IN SCHEMA public TO User2; statement to authorize User2 to select all tables in the public schema.
2. User1 creates a table named table_new in the public schema.
3. The error Permission denied is displayed when User2 executes the SELECT * FROM table_new statement.
  The SELECT permission that User1 grants to User2 applies only to the existing tables in the public schema and does not apply to new tables in the public schema. Therefore, the preceding error occurred.
You can execute the ALTER DEFAULT PRIVILEGES statement to set the default permissions on new objects. For more information, see ALTER DEFAULT PRIVILEGES. The default permissions apply only to new objects. Sample statement:
```
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO PUBLIC; -- The current authorized user has read permissions on newly created tables in the public schema.
```
You can also execute the ALTER DEFAULT PRIVILEGES FOR ROLE xxx statement to grant the default permissions on new objects to other roles. The default permissions can be granted only when the current user and xxx meet the following requirements:
- The current user is a member of the permission group xxx.
- The current user is a superuser. xxx can be a user or a permission group.
You can run the \ddp command to check whether the ALTER DEFAULT PRIVILEGES statement takes effect. The default permissions are stored in the pg_catalog.pg_default_acl catalog.
ALTER DEFAULT PRIVILEGES serves as a trigger. When you create a table, Hologres compares the table and the pg_catalog.pg_default_acl catalog based on the current user and schema. If matches are found, the corresponding match rules are added.
Note
- Only the current user can be used for comparison. The permission group to which the current user belongs cannot be used for comparison.
- The ALTER DEFAULT PRIVILEGES statement can be executed only when you create a table. If you execute the ALTER TABLE TABLENAME OWNER TO statement after you create a table, the ALTER DEFAULT PRIVILEGES statement is not executed.
Assume that User1 belongs to Group1 and you want to grant all permissions for a table created in the future and compare the table and Group1. The following results are obtained:
- If the current user is User1, no matches are found during the comparison.
- If you execute the SET SESSION ROLE Group1 statement to change the current user to Group1 before you create a table, matches are found during the comparison. Then, the permissions are automatically granted to the table.
Only the table owner can delete a table.
You can decide whether the table can be deleted based on the current role. Only the following roles have the DELETE permission:
- The owner of the table.
- The owner of the schema to which the table belongs.
- Superuser
By default, the user who creates a table is the owner of the table. The user has all permissions on the table, including the DELETE permission.
The following sample statements are used to assign a table to a new owner:
```
ALTER TABLE <tablename> OWNER TO user2; // Change the owner of the table from User1 to User2. 
ALTER TABLE <tablename> OWNER TO group1;// Change the owner of the table to Group1.
```
The following limits apply when a table is assigned to a new owner:
- User1 is the owner of the table.
- User1 must directly or indirectly belong to Group1.
  For example, User1 is a member of Group1, or User1 is a member of a group in Group1.
- Group1 must have the permissions to create tables in the schema.
- A superuser can assign a table to a new owner.

Planning

You must plan the following items before you manage permissions by using the standard PostgreSQL authorization model:

The total number of permission groups.
The permissions of the permission groups.
The users in each permission group.
The roles that can delete tables and the time when tables can be deleted.
The schemas to which the permission groups belong.

We recommend that you perform the following operations before you manage permissions:

Create permission groups and grant permissions.
Permission groups are divided into the following types:
- XX_DEV_GROUP: the owner of a table. The owner has all permissions on the table.
- XX_WRITE_GROUP: the permissions to write data to a table.
- XX_VIEW_GROUP: the permissions to view data in a table.
XX indicates a project. For example, the permission groups of the PROJ1 project include PROJ1_DEV_GROUP, PROJ1_WRITE_GROUP, and PROJ1_VIEW_GROUP.
Note
The preceding naming formats are only for reference.
Assign schemas for the permission groups.
We recommend that you assign a schema for the permission groups of a project.
Each DEV_GROUP permission group can own multiple tables. However, each table can belong to only one DEV_GROUP permission group. For example, TABLE1 can belong only to PROJ1_DEV_GROUP.
Each user can belong to multiple permission groups. For example, User1 can belong to PROJ1_DEV_GROUP and PROJ2_DEV_GROUP.

Best practice 1

A table is used as an example.

The owner of the table belongs to the XXX_DEV_GROUP permission group. Therefore, all users in the DEV_GROUP group can manage or delete the table.

For example, after a user is added to the PROJ1_DEV_GROUP permission group, the user has the permissions to manage or delete tables in the PROJ1 project. Perform the following steps:

Create permission groups.

You can create permission groups as a superuser based on your needs. Assume that the project is named PROJ1. The following statements are used as an example:

CREATE ROLE PROJ1_DEV_GROUP; // The owner of the table. The owner has all permissions on the table. 
CREATE ROLE PROJ1_WRITE_GROUP; // The permissions to write data to the table. 
CREATE ROLE PROJ1_VIEW_GROUP; // The permissions to view data in the table.

Grant permissions on schemas to the permission groups.
You must grant permissions on schemas to the permission groups that you created. Assume that PROJ1 belongs to schema1. The following statements are used as an example:
```
Grant all permissions on schema1 to PROJ1. 
GRANT CREATE,usage ON schema SCHEMA1 TO PROJ1_DEV_GROUP;
GRANT usage ON schema SCHEMA1 TO PROJ1_WRITE_GROUP;
GRANT usage ON schema SCHEMA1 TO PROJ1_VIEW_GROUP;
```
Note
- Each project can belong to multiple schemas. Each schema can have multiple projects.
- By default, all users in the public schema have the CREATE permission and the USAGE permission.
Create users and manage the permission groups.
After you grant permissions to the permission groups as a superuser, you must create and add a user to the permission groups. The following statements are used as an example:
```
CREATE USER "USER1";
GRANT PROJ1_DEV_GROUP TO "USER1"; 

CREATE USER "USER2";
GRANT PROJ1_VIEW_GROUP TO "USER2";
```

Create a table and grant the permissions on the table to the users.

When a table is created, the owner or a superuser must grant the permissions on the table to the users. Take note that the owner must be a member of PROJ1_DEVE_GROUP. In this example, the table is TABLE1. Examples:

GRANT ALL ON TABLE SCHEMA1.TABLE1 TO PROJ1_WRITE_GROUP;// Grant PROJ1_WRITE_GROUP the permissions to write data to TABLE1. 
GRANT SELECT ON TABLE SCHEMA1.TABLE1 TO PROJ1_VIEW_GROUP; // Grant PROJ1_VIEW_GROUP the SELECT permission on TABLE1. 
ALTER TABLE SCHEMA1.TABLE1 owner TO PROJ1_DEV_GROUP; // Assign TABLE1 to PROJ1_DEV_GROUP.

Best practice 2

The ALTER DEFAULT PRIVILEGES statement is used in this example.

You need to determine the project to which the created table belongs in advance. Perform the following steps:

Create permission groups.

You can create permission groups as a superuser based on your needs. Assume that the project is named PROJ1. The following statements are used as an example:

CREATE ROLE PROJ1_DEV_GROUP; The owner of the table. The owner has all permissions on the table. 
CREATE ROLE PROJ1_WRITE_GROUP; // The permissions to write data to the table. 
CREATE ROLE PROJ1_VIEW_GROUP; // The permissions to view data in the table.

Grant permissions on schemas to the permission groups.
You must grant permissions on schemas to the permission groups that you created. Assume that PROJ1 belongs to schema1. The following statements are used as an example:
```
Grant all permissions on schema1 to PROJ1. 
GRANT CREATE,USAGE ON SCHEMA SCHEMA1 TO PROJ1_DEV_GROUP;
GRANT USAGE ON SCHEMA SCHEMA1 TO PROJ1_WRITE_GROUP;
GRANT USAGE ON SCHEMA SCHEMA1 TO PROJ1_VIEW_GROUP;
```
Note
- Each project can belong to multiple schemas. Each schema can have multiple projects.
- By default, all roles in the public schema have the CREATE permission and the USAGE permission.

Create users and grant the default permissions to the users.

After permissions on schemas are granted, a superuser needs to create users and add the users to the permission groups. The superuser also needs to grant the default permissions to the users.

By default, the table created by USER1 belongs to PROJ1_DEV_GROUP, and USER1 is a valid Alibaba Cloud account. The following statements are used as an example:

CREATE USER "USER1";

ALTER DEFAULT PRIVILEGES FOR ROLE "USER1" GRANT ALL ON TABLES TO PROJ1_DEV_GROUP; // Grant PROJ1_DEV_GROUP the default permissions on the table created by USER1. 
ALTER DEFAULT PRIVILEGES FOR ROLE "USER1" GRANT ALL ON TABLES TO PROJ1_WRITE_GROUP; // Grant PROJ1_WRITE_GROUP the default permissions on the table created by USER1. 
ALTER DEFAULT PRIVILEGES FOR ROLE "USER1" GRANT SELECT ON TABLES TO PROJ1_VIEW_GROUP; // Grant PROJ1_VIEW_GROUP read permissions on the table created by USER1. 

GRANT PROJ1_DEV_GROUP TO "USER1";  // Add USER1 to PROJ1_DEV_GROUP.

Assign the table to a new owner.
If you want to authorize other members of PROJ1_DEV_GROUP to perform operations on the table, you can assign the table to PROJ1_DEV_GROUP.
The statement that is used to modify the owner of a table must be executed by the owner of the table or a superuser. The owner of the table in the example must be a member of PROJ1_DEV_GROUP. Assume that the table is named TABLE1. The following statement is used as an example:
```
ALTER TABLE SCHEMA1.TABLE1 OWNER TO PROJ1_DEV_GROUP; // Assign TABLE1 to PROJ1_DEV_GROUP.
```
A table can be assigned to a new owner when the following requirements are met:
- The table is newly created and a superuser modifies the owner on a regular basis.
- The table is assigned to a new owner before operations are performed on the table.
Note
If the table is modified or deleted by the owner or a superuser, you do not need to execute the preceding statements.

Change the default project to which a user belongs.

To change the default project of a user, the superuser or the user must execute the ALTER DEFAULT PRIVILEGES statement to revoke the default permissions. Then, the user or superuser must execute the ALTER DEFAULT PRIVILEGES statement again to grant the default permissions.

If the project is changed, the table is not affected. Sample statement:

Revoke the default permissions granted to the current project. 
ALTER DEFAULT PRIVILEGES FOR ROLE "USER1" REVOKE ALL ON TABLES FROM PROJ1_DEV_GROUP;
ALTER DEFAULT PRIVILEGES FOR ROLE "USER1" REVOKE ALL ON TABLES FROM PROJ1_WRITE_GROUP;
ALTER DEFAULT PRIVILEGES FOR ROLE "USER1" REVOKE SELECT ON TABLES FROM PROJ1_VIEW_GROUP;

Grant the default permissions to another project. 
ALTER DEFAULT PRIVILEGES FOR ROLE "USER1" GRANT ALL ON TABLES TO PROJ2_DEV_GROUP;
ALTER DEFAULT PRIVILEGES FOR ROLE "USER1" GRANT ALL ON TABLES TO PROJ2_WRITE_GROUP;
ALTER DEFAULT PRIVILEGES FOR ROLE "USER1" GRANT SELECT ON TABLES TO PROJ2_VIEW_GROUP;