This topic provides the best practices for Hologres when you authorize roles based on PostgreSQL privileges. This way, you can simplify authorization and manage privileges in a fine-grained manner.
Background information
Hologres is compatible with PostgreSQL and supports authorization based on PostgreSQL privileges. Hologres also provides an authorization method called Simple Permission Model (SPM). For more information, see Overview.
However, SPM manages privileges in a coarse-grained manner. If you need to manage privileges in a fine-grained manner, see the "Best practice 1" and "Best practice 2" sections.
Overview of PostgreSQL privileges
For information about PostgreSQL privileges, see 5.7. Privileges.
- PostgreSQL privileges apply only to the existing objects and do not apply to new objects. Example:
- User1 executes the
GRANT SELECT ON ALL TABLES IN SCHEMA public TO User2;
statement to authorize User2 to select all the tables in the public schema. - User1 creates a table named table_new in the public schema.
- A
Permission denied
error occurs when User2 executes theSELECT * FROM table_new
statement.The SELECT privilege that User1 grants to User2 apply only to the existing tables in the public schema and do not apply to new tables in the public schema. Therefore, the preceding error occurred.
- User1 executes the
- You can execute the
ALTER DEFAULT PRIVILEGES
statement to grant default privileges on the objects created in the future to all the roles. For more information, see ALTER DEFAULT PRIVILEGES. The default privileges apply only to the objects created in the future. The following statement is used as an example:ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO PUBLIC; // By default, all the roles can read the new tables in the public schema.
You can also execute theALTER DEFAULTPRIVILEGES FOR ROLE xxx
statement to grant default privileges on new objects to xxx. The default privileges can be granted only when the current role and xxx meet one of the following requirements:- The current user is a member of the permission group to which xxx belongs.
- The current role is a superuser. xxx can be either a role or a permission group.
You can use the
\ddp
command to check whether theALTER DEFAULT PRIVILEGES
statement takes effect. Default privileges are stored in thepg_catalog.pg_default_acl
catalog.ALTER DEFAULT PRIVILEGES
serves as a trigger. When you create a table, Hologres compares the table and thepg_catalog.pg_default_acl
catalog based on the current role and schema information. 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 role belongs cannot be used for comparison.
- The
ALTER DEFAULT PRIVILEGES
can be executed only when you create a table. If you execute theALTER TABLE <TABLE> OWNER TO XX
statement after you create a table, theALTER DEFAULT PRIVILEGES
statement is not executed.
Assume that User1 belongs to Group1 and you want to grant privileges to a table created in the future and compare the table and Group1. The following results are obtained:- If the current role is User1, no matches are found during the comparison.
- If you execute the
SET SESSION ROLE Group1
statement to change the current role to Group1 before you create a table, matches are found during the comparison. Then, the privileges 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 privilege:
- 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
the privileges on the table, including the DELETE privilege.
The following sample statements are used to assign a table to a new owner:
The following limits apply when a table is assigned to a new owner:alter table <table> owner to ; // Assign the table to User2. alter table <table> owner to GROUP1; // Assign the table to GROUP1.
- 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 USAGE privilege on the schema.
- A superuser can assign a table to a new owner.
Assign privileges
- The total number of permission groups.
- The privileges of the permission groups.
- The roles in each permission group.
- The roles that can delete tables and the time that tables can be deleted.
- The schemas to which the permission groups respectively belong.
- Create permission groups and assign their privileges.
Permission groups are divided into the following types:
- XX_DEV_GROUP: the owner of a table. The owner has all the privileges on the table.
- XX_WRITE_GROUP: the privileges to write data to a table.
- XX_VIEW_GROUP: the privileges 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 permission group can own multiple tables. However, each table can belong to only one permission group. For example, TABLE1 can belong only to PROJ1_DEV_GROUP.
Each role 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.
All the members in the permission group where the owner belongs can manage or delete the table.
Perform the following operations to add a role to the permission group where the owner belongs:
Best practice 2
The ALTER DEFAULT PRIVILEGES
statement is used in this example.
Perform the following operations to assign a table to a new owner or change the project to which a role belongs: