Secure PostgreSQL User Permissions with Fine-Grained Authorization - Hologres

The standard PostgreSQL authorization model lets you manage user permissions in Hologres using standard PostgreSQL GRANT and REVOKE statements. It gives you fine-grained control at the database, schema, table, column, and view levels.

Hologres also provides two simplified models — SPM (Simple Permission Model) and SLPM (Schema-level Permission Model) — for teams that prefer a higher-level abstraction. The standard PostgreSQL model is the right choice when you need precise, object-level control that matches your existing PostgreSQL workflows.

Important

Permissions granted with this model apply only to objects that exist at the time of the grant. Objects created later require a separate grant. To set permissions for future tables, use ALTER DEFAULT PRIVILEGES.

Prerequisites

Before you begin, ensure that you have:

A Hologres instance
Superuser access to the instance
A development tool connected to the instance (such as psql or HoloWeb)

Grant permissions

Step 1: create a user

An account must be registered as a Hologres user before it can access the instance.

-- Create a user with login access
CREATE USER "Alibaba Cloud account ID/email";

-- Create a user and grant Superuser privileges
CREATE USER "Alibaba Cloud account ID/email" SUPERUSER;

For Resource Access Management (RAM) users, use the RAM user account format:

-- Create a user from an Alibaba Cloud account ID
CREATE USER "11822780xxx";

-- Create a RAM user and grant Superuser privileges
CREATE USER "p4_1822780xxx" SUPERUSER;

For more information about account formats, see Account system. For the full CREATE ROLE syntax, see the PostgreSQL documentation.

Step 2: grant privileges

Use GRANT statements to give users access to specific objects. The tables below organize common grants by object type.

Important

Grant USAGE on a schema before granting any table-level privileges within that schema. Without USAGE, table grants have no effect.

Schema privileges

Description	Syntax
Grant the privilege to create objects in a schema	`GRANT CREATE ON SCHEMA <schema_name> TO "account";`
Grant the privilege to access objects in a schema	`GRANT USAGE ON SCHEMA <schema_name> TO "account";`

Table privileges

Description	Syntax
Grant SELECT on a specific table	`GRANT SELECT ON TABLE <tablename> TO "account";`
Grant SELECT and allow the user to pass the privilege to others	`GRANT SELECT ON TABLE <tablename> TO "account" WITH GRANT OPTION;`
Grant SELECT on all tables in the public schema	`GRANT SELECT ON ALL TABLES IN SCHEMA public TO "account";`
Grant SELECT, INSERT, and UPDATE on all tables in the public schema to all users	`GRANT SELECT, INSERT, UPDATE ON ALL TABLES IN SCHEMA public TO PUBLIC;`
Transfer table ownership to another user	`ALTER TABLE <tablename> OWNER TO "account";`

Column privileges

Description	Syntax
Grant SELECT on specific columns	`GRANT SELECT (<column1>, <column2>, ...) ON TABLE <tablename> TO "account";`

View privileges

Description	Syntax
Grant SELECT on a view	`GRANT SELECT ON <viewname> TO "account";`

To access a view with the SPM or SLPM model, the user must have viewer-level permissions or higher. With the standard PostgreSQL model, an explicit GRANT SELECT ON <viewname> is required.

User and role privileges

Description	Syntax
Promote a user to Superuser	`ALTER USER "account" SUPERUSER;`
Demote a Superuser to a regular user	`ALTER USER "account" NOSUPERUSER;`
Create a role (no login) for use as a user group	`CREATE ROLE <rolename>;`
Assign a role to a user	`GRANT <rolename> TO "account";`

CREATE ROLE creates an entity without login access — useful for grouping users or representing a shared permission set. For the full GRANT syntax, see the PostgreSQL documentation.

Step 3: grant drop privileges (optional)

Only a Superuser or the table Owner can drop a table. To grant this privilege to another user, choose one of the following approaches:

Transfer ownership to the user:

ALTER TABLE <tablename> OWNER TO "account";

Promote the user to Superuser:
```
ALTER USER "account" SUPERUSER;
```

Use a role group to share ownership across multiple users:

CREATE USER "account";
CREATE ROLE <rolename>;
GRANT <rolename> TO "account";
ALTER TABLE <tablename> OWNER TO <rolename>;

Quick start: grant read-only access to a new user

The following example creates a new user and grants read-only access to a single table. This is the minimum set of statements required for a user to run a SELECT query.

-- 1. Register the account as a Hologres user
CREATE USER "Alibaba Cloud account/email";

-- 2. Grant schema access (required before any table-level grant)
GRANT USAGE ON SCHEMA <schema_name> TO "Alibaba Cloud account/email";

-- 3. Grant read access to the target table
GRANT SELECT ON TABLE <tablename> TO "Alibaba Cloud account/email";

Replace <schema_name> and <tablename> with your actual schema and table names.

Grant permissions on future tables

By default, this model grants privileges only on existing objects. Use ALTER DEFAULT PRIVILEGES to apply privileges automatically to tables created in the future.

ALTER DEFAULT PRIVILEGES does not affect existing objects. It can only set default privileges for TABLE, SCHEMA, FUNCTION, SEQUENCE, or TYPE.

Grant default privileges

The following examples grant SELECT on all future tables created by user p4_id1:

-- All users can query future tables created by p4_id1 in the public schema
ALTER DEFAULT PRIVILEGES FOR ROLE "p4_id1" IN SCHEMA public GRANT SELECT ON TABLES TO PUBLIC;

-- Only p4_id2 can query future tables created by p4_id1 in the public schema
ALTER DEFAULT PRIVILEGES FOR ROLE "p4_id1" IN SCHEMA public GRANT SELECT ON TABLES TO "p4_id2";

-- All users can query future tables created by p4_id1 in the test schema
ALTER DEFAULT PRIVILEGES FOR ROLE "p4_id1" IN SCHEMA test GRANT SELECT ON TABLES TO PUBLIC;

To apply to tables created by the current user in any schema, omit the FOR ROLE clause:

ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO PUBLIC;

Revoke default privileges

-- Revoke for all users (public schema)
ALTER DEFAULT PRIVILEGES FOR ROLE "p4_id1" IN SCHEMA public REVOKE SELECT ON TABLES FROM PUBLIC;

-- Revoke for a specific user (public schema)
ALTER DEFAULT PRIVILEGES FOR ROLE "p4_id1" IN SCHEMA public REVOKE SELECT ON TABLES FROM "p4_id2";

-- Revoke for all users (test schema)
ALTER DEFAULT PRIVILEGES FOR ROLE "p4_id1" IN SCHEMA test REVOKE SELECT ON TABLES FROM PUBLIC;

Verify default privileges

Use the psql \ddp command to check whether ALTER DEFAULT PRIVILEGES was applied successfully. Alternatively, run the following SQL in Hologres:

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;

How Hologres matches default privileges

When a new table is created, Hologres looks up the current user and schema in the pg_catalog.pg_default_acl system table and applies any matching rules:

If the current user is a standard user, that user's identity is used for matching.
If the user runs SET SESSION ROLE <rolename>; before creating a table, the role is used for matching instead.

The match is evaluated only at table creation time. Running ALTER TABLE SET OWNER TO afterward does not trigger a re-evaluation.

Revoke permissions (Expert Mode)

Use REVOKE to remove previously granted privileges. For RAM users, use the RAM user account format.

REVOKE SELECT ON TABLE <tablename> FROM "Alibaba Cloud account ID/email";

For the full REVOKE syntax, see the PostgreSQL documentation.

Control system table visibility

Starting with Hologres V3.0, users can view metadata for all schemas and tables in an instance through the pg_class, pg_attribute, and pg_namespace system tables — regardless of whether they have access to those objects. BI tools and development tools commonly query these tables automatically to populate schema and table lists.

Hologres V3.0.23 and later support row-level permissions on these three system tables so that only authorized users can see the corresponding metadata.

To enable this, run the following as a Superuser — once per database:

ALTER DATABASE <database_name> SET hg_experimental_enable_catalog_rls = on;

After enabling, metadata in pg_class, pg_attribute, and pg_namespace is visible only to:

Superusers
Users with Owner permission on the object (table or schema)
Users with any privilege on the object (table or schema)

For more information about system tables, see System tables.

View roles and permissions

Run the following queries to inspect current roles and their privileges:

-- List all roles
SELECT ROLNAME FROM pg_roles;

-- List all roles with display names
SELECT user_display_name(ROLNAME) FROM pg_roles;

Delete users

Delete a regular user

If the user has not created any objects (tables, views, or extensions), drop the user directly:

DROP USER "Alibaba Cloud account ID/email";

You can also delete the user in HoloWeb.

Delete a Superuser or administrator

If the user owns objects in the instance, transfer ownership first, then drop the account:

-- Transfer all objects from account A to account B
REASSIGN OWNED BY "A Alibaba Cloud account ID" TO "B Alibaba Cloud account ID";

-- Drop account A
DROP USER "A Alibaba Cloud account ID";

Important

After a user is deleted, the account can no longer connect to the instance or access any objects. This action cannot be undone.

What's next

For common authorization patterns — such as setting up read-only users or team-based access — see Grant permissions based on the standard PostgreSQL permission model.