Hologres supports the standard PostgreSQL permission model (also called the expert permission model), which gives you fine-grained control over who can access, modify, or delete specific database objects. Use this model when your workload requires per-object permission control.
For simpler scenarios, Hologres also provides the simple permission model (SPM). SPM uses coarse-grained permissions and is easier to set up, but does not support per-object control.
How the PostgreSQL permission model works
The PostgreSQL permission model has two key behaviors that differ from simpler models.
Permissions apply only to existing objects. When you grant a privilege on all tables in a schema, that grant covers only tables that exist at that moment—not tables created later. For example:
User1 runs
GRANT SELECT ON ALL TABLES IN SCHEMA public TO User2.User1 creates a new table
table_newin thepublicschema.User2 runs
SELECT * FROM table_newand getsERROR: permission denied.
To cover future tables, use ALTER DEFAULT PRIVILEGES. See the ALTER DEFAULT PRIVILEGES reference.
Ownership controls who can drop a table. PostgreSQL assigns a newly created table to the user who created it. Only the table owner, the schema owner, or a superuser can drop a table. To let a group manage or drop a table, transfer the table's ownership to that group.
Plan your permission model
Before writing any SQL, answer these questions for your Hologres instance:
How many permission groups do you need?
What is the purpose of each group?
Which users belong to each group?
Which schemas does each group work in?
Who can drop tables, and under what conditions?
Recommended group structure
Organize permissions around projects. For each project, create three groups:
| Group | Privilege level | What members can do |
|---|---|---|
XX_DEV_GROUP | Full (table owner) | Create, read, write, and drop tables |
XX_WRITE_GROUP | Write | Insert data into specific tables |
XX_VIEW_GROUP | Read | Query data in specific tables |
Replace XX with your project name. For a project named PROJ1, the groups are PROJ1_DEV_GROUP, PROJ1_WRITE_GROUP, and PROJ1_VIEW_GROUP.
Notes:
This naming convention is a recommendation, not a requirement.
A DEV_GROUP can own multiple tables, but each table belongs to only one DEV_GROUP.
A user can belong to multiple DEV_GROUPs. For example, a user can be a member of both
PROJ1_DEV_GROUPandPROJ2_DEV_GROUP.Assign one schema per project where possible. One project can span multiple schemas, and one schema can serve multiple projects.
By default, all users have CREATE and USAGE privileges on the
publicschema.
Best practice 1: Grant permissions per table
Use this approach when you want explicit control over each table's permissions at creation time.
Step 1: Create permission groups
Have a superuser create the three groups for your project:
CREATE ROLE PROJ1_DEV_GROUP; -- Table owner with full privileges
CREATE ROLE PROJ1_WRITE_GROUP; -- Write privilege to insert data into specific tables
CREATE ROLE PROJ1_VIEW_GROUP; -- Read privilege to view data in specific tablesStep 2: Grant schema privileges to groups
Grant each group the schema-level access it needs. This example uses SCHEMA1:
-- Grant schema privileges to PROJ1 groups
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;By default, all users have CREATE and USAGE privileges on the public schema.Step 3: Create users and assign them to groups
Create users and add them to the appropriate groups:
CREATE USER "USER1";
GRANT PROJ1_DEV_GROUP TO "USER1";
CREATE USER "USER2";
GRANT PROJ1_VIEW_GROUP TO "USER2";Step 4: Grant table privileges after creating a table
When a member of PROJ1_DEV_GROUP (or a superuser) creates a new table, run the following statements to assign privileges and transfer ownership. This example uses TABLE1:
GRANT ALL ON TABLE SCHEMA1.TABLE1 TO PROJ1_WRITE_GROUP; -- Write privilege on TABLE1
GRANT SELECT ON TABLE SCHEMA1.TABLE1 TO PROJ1_VIEW_GROUP; -- Read privilege on TABLE1
ALTER TABLE SCHEMA1.TABLE1 OWNER TO PROJ1_DEV_GROUP; -- Transfer ownership to the groupTransferring ownership to the group lets all members of PROJ1_DEV_GROUP manage or drop TABLE1. Without this step, only the original creator can drop the table.
Conditions for changing table ownership:
The current user must be the table owner.
The current user must be a direct or indirect member of the target group.
The target group must have CREATE privilege on the schema.
A superuser can change the owner of any table.
Best practice 2: Use default privileges to automate grants
Use this approach when you know in advance which project a new table belongs to. ALTER DEFAULT PRIVILEGES acts like a trigger: when a table is created, Hologres checks pg_catalog.pg_default_acl for a matching rule and automatically applies it.
Matching uses only the current user (not the user's groups). ALTER DEFAULT PRIVILEGES rules are evaluated only at table creation time—changing the owner afterward does not trigger them.Step 1: Create permission groups
CREATE ROLE PROJ1_DEV_GROUP; -- Table owner with full privileges
CREATE ROLE PROJ1_WRITE_GROUP; -- Write privilege to insert data into specific tables
CREATE ROLE PROJ1_VIEW_GROUP; -- Read privilege to view data in specific tablesStep 2: Grant schema privileges to groups
-- Grant schema privileges to PROJ1 groups
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;By default, all users have CREATE and USAGE privileges on the public schema.Step 3: Create a user and set default privileges
Create USER1 (a valid Alibaba Cloud account), set default privileges so that tables USER1 creates are automatically shared with the PROJ1 groups, then add USER1 to the DEV group:
CREATE USER "USER1";
-- Automatically grant full privileges to PROJ1_DEV_GROUP on tables USER1 creates
ALTER DEFAULT PRIVILEGES FOR ROLE "USER1" GRANT ALL ON TABLES TO PROJ1_DEV_GROUP;
-- Automatically grant read and write permission to PROJ1_WRITE_GROUP on tables USER1 creates
ALTER DEFAULT PRIVILEGES FOR ROLE "USER1" GRANT ALL ON TABLES TO PROJ1_WRITE_GROUP;
-- Automatically grant read privilege to PROJ1_VIEW_GROUP on tables USER1 creates
ALTER DEFAULT PRIVILEGES FOR ROLE "USER1" GRANT SELECT ON TABLES TO PROJ1_VIEW_GROUP;
GRANT PROJ1_DEV_GROUP TO "USER1"; -- Add USER1 to PROJ1_DEV_GROUPConditions for using `ALTER DEFAULT PRIVILEGES FOR ROLE xxx`:
The current user is a member of the group
xxx, orThe current user is a superuser and
xxxis a user or group.
Verify the default privilege rules:
\ddpThis queries pg_catalog.pg_default_acl and lists all configured default privilege rules.
How matching works: The rule matches only on the current user, not the user's groups. For example, if USER1 belongs to GROUP1 and you set a default privilege rule for GROUP1, the rule does not trigger when USER1 creates a table. To trigger the rule, switch the session role first:
SET SESSION ROLE GROUP1;
-- Now create the table; the rule matches because the current user is GROUP1Step 4: Transfer table ownership to the group
After USER1 creates a table, transfer ownership to PROJ1_DEV_GROUP so that all group members can manage or drop it. This example uses TABLE1:
ALTER TABLE SCHEMA1.TABLE1 OWNER TO PROJ1_DEV_GROUP;Skip this step if only the table creator or a superuser will ever manage or drop the table.
Two common patterns for running this step:
A superuser periodically transfers ownership of newly created tables.
The creator transfers ownership before another group member needs to manage or drop the table.
Step 5: Move a user to a different project
To reassign USER1 from PROJ1 to PROJ2 without affecting existing tables, revoke the current default privileges first, then set new ones. Revoking first ensures old rules no longer apply to future tables before the new rules take effect.
-- Revoke existing default privileges for PROJ1
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;
-- Set new default privileges for PROJ2
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;Run \ddp again to confirm the rules have been updated.