All Products
Search

This Product

    Hologres:Expert permission model

    Document Center

    Hologres:Expert permission model

    Last Updated:Jul 10, 2026

    Hologres is compatible with PostgreSQL and uses the same authorization system, known as the expert permission model. You can grant and revoke user privileges by running standard PostgreSQL statements.

    Granting privileges

    After connecting a Hologres instance to a development tool, you can run SQL statements to grant privileges to users.

    1. Create a user.

      An account must first be created as a Hologres user before it can access and develop in Hologres.

      The syntax for creating a user is as follows:

      -- Create a user that has the privilege to log on to the Hologres instance. If you grant privileges to a RAM user, use the required format for RAM users.
CREATE USER "Alibaba Cloud account ID/email address"; 
-- Create a user and grant the superuser privilege.
CREATE USER "Alibaba Cloud account ID/email address" SUPERUSER;

      You can create a user based on the following examples. For more information about the formats of Alibaba Cloud accounts and RAM users, see Account overview.

      -- Create a user by using an Alibaba Cloud account ID.
CREATE USER "11822780xxx";
-- Grant the superuser privilege to a RAM user.
CREATE USER "p4_1822780xxx" SUPERUSER;

      For more information about creating roles, see CREATE ROLE.

    2. Grant privileges.

      After creating a Hologres user, you must grant them privileges. The expert permission model allows you to control privileges at the database, table, view, and column levels.

      Note

      The standard PostgreSQL authorization model can grant privileges only on existing objects. These privileges do not apply to objects created after the grant. For example, if user A grants user B the SELECT privilege on all tables in the public schema and then creates a new table, user B does not have the SELECT privilege on the new table. User A must grant the privilege on the new table explicitly.

      Description

      Syntax example

      Required

      Create a user with the privilege to log on to a Hologres instance

      		 
      CREATE USER "Alibaba Cloud account/email address";

      Required

      Create a user and grant the superuser privilege

      		 
      CREATE USER "Alibaba Cloud account/email address" SUPERUSER ;

      Optional

      Grant the CREATE privilege on a schema

      		 
      GRANT CREATE ON SCHEMA schema_name  TO "Alibaba Cloud account/email address";

      Optional

      Grant the USAGE privilege on a schema

      		 
      GRANT USAGE ON SCHEMA schema_name  TO "Alibaba Cloud account/email address";

      Required

      Note

      A user must have the USAGE privilege on a schema to query its tables.

      Grant all users the SELECT, INSERT, and UPDATE privileges on all tables in the public schema

      		 
      GRANT SELECT,INSERT,UPDATE ON ALL TABLES IN SCHEMA public to PUBLIC;

      Optional

      Grant a user the SELECT privilege on a table

      		 
      GRANT SELECT ON TABLE <tablename> TO "Alibaba Cloud account/email address";

      Optional

      Grant a user the SELECT privilege on a table with the grant option

      		 
      GRANT SELECT ON TABLE <tablename> TO "Alibaba Cloud account/email address" WITH GRANT OPTION;

      Optional

      Grant a user the SELECT privilege on all tables in the public schema

      		 
      GRANT SELECT ON ALL TABLES IN SCHEMA public TO "Alibaba Cloud account/email address";

      Optional

      Grant all users the SELECT privilege on future tables created by the current role in the public schema.

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

      Optional

      Change a regular user to a superuser

      		 
      ALTER USER "Alibaba Cloud account/email address" SUPERUSER;

      Optional

      Change a superuser to a regular user

      		 
      ALTER USER "Alibaba Cloud account/email address" NOSUPERUSER;

      Optional

      Transfer the ownership of a table to another user

      		 
      ALTER TABLE <tablename> OWNER TO "Alibaba Cloud account/email address";

      Optional

      Create a role without the login privilege

      		 
      CREATE ROLE "Alibaba Cloud account/email address";

      Optional

      Grant a role to a user

      		 
      GRANT <rolename> TO "Alibaba Cloud account/email address" ;

      Optional

      Grant a user the SELECT privilege on specific columns of a table

      		 
      GRANT SELECT (<column1>,<column2>,<column3>,...) ON TABLE <tablename> TO "Alibaba Cloud account/email address" ;

      Optional

      Grant a user the SELECT privilege on a view

      Note

      • In the standard PostgreSQL authorization model, you must grant the SELECT privilege on a view to access it.

      • In the simple permission model (SPM)/schema-level permission model (SLPM), you must be a member of the viewer user group or a user group with higher privileges to access a view.

      		 
      -- Grant a user the SELECT privilege on a view by using the standard PostgreSQL authorization model. 
GRANT SELECT ON <viewname> TO "Alibaba Cloud account/email address" ;

      Optional

      To grant the SELECT privilege on a table to a new user, run the following statements:

      CREATE USER "Alibaba Cloud account/email address";
GRANT USAGE ON SCHEMA <schema_name>  TO "Alibaba Cloud account/email address";
GRANT SELECT ON TABLE <tablename> TO "Alibaba Cloud account/email address";

      The CREATE ROLE statement creates a role without login privilege, such as a user group or a virtual role representing a specific type of user. For more information about granting privileges, see GRANT.

    3. Drop a table.

      Only a superuser or the table owner can drop a table. To grant users the privilege to drop a table, use one of the following methods:

      • Transfer the ownership of the table to a new user. 

        ALTER TABLE TABLENAME OWNER TO "Alibaba Cloud account/email address";

      • Grant the superuser privilege to a new user. 

        ALTER USER "Alibaba Cloud account/email address" SUPERUSER;

      • Add multiple users to a user group and transfer the table ownership to the user group. 

        CREATE USER "Alibaba Cloud account ID/email address";
CREATE ROLE <rolename>;
GRANT <rolename> TO "Alibaba Cloud account/email address";
ALTER TABLE <tablename> OWNER TO <rolename>;

    Default privileges for future tables

    By default, the standard PostgreSQL authorization model does not grant privileges on future tables. To do so, you must use the ALTER DEFAULT PRIVILEGES statement.

    Note

    • This statement does not affect existing logical objects.

    • This statement can set default privileges only for tables, schemas, functions, sequences, or types.

    1. Grant privileges.

      • After you configure default privileges, tables created by a user in a specific schema can be queried by a specified user or all users.

        • After you run this statement, all users can query future tables that are created by the user p4_id1 in the public schema.

          ALTER DEFAULT PRIVILEGES FOR ROLE "p4_id1" IN SCHEMA public GRANT SELECT ON TABLES TO PUBLIC;

        • After you run this statement, the user p4_id2 can query future tables that are created by the user p4_id1 in the public schema.

          ALTER DEFAULT PRIVILEGES FOR ROLE "p4_id1" IN SCHEMA public GRANT SELECT ON TABLES TO "p4_id2";

        • After you run this statement, all users can query future tables that are created by the user p4_id1 in the test schema.

          ALTER DEFAULT PRIVILEGES FOR ROLE "p4_id1" IN SCHEMA test GRANT SELECT ON TABLES TO PUBLIC;

      • To revoke the default privileges, run the following SQL statements.

        • Revoke from all users the default SELECT privilege on future tables that are created by the user p4_id1 in the public schema.

          ALTER DEFAULT PRIVILEGES FOR ROLE "p4_id1" IN SCHEMA public REVOKE SELECT ON TABLES FROM PUBLIC;

        • Revoke from the user p4_id2 the default SELECT privilege on future tables that are created by the user p4_id1 in the public schema.

          ALTER DEFAULT PRIVILEGES FOR ROLE "p4_id1" IN SCHEMA public REVOKE SELECT ON TABLES FROM "p4_id2";

        • Revoke from all users the default SELECT privilege on future tables that are created by the user p4_id1 in the test schema.

          ALTER DEFAULT PRIVILEGES FOR ROLE "p4_id1" IN SCHEMA test REVOKE SELECT ON TABLES FROM PUBLIC;

    2. Verify that the default privileges are set.

      • In a psql client, run the \ddp command to verify that the ALTER DEFAULT PRIVILEGES statement was successful.

      • Run the following SQL statement in Hologres to query the result directly.

        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 a new table is created, Hologres matches the current user and schema against the pg_catalog.pg_default_acl system catalog. If a matching ALTER DEFAULT PRIVILEGES rule is found, the rule is applied to the new table. The current user is determined as follows:

      • If the current role is a user, the user is used for matching when the table is created.

      • If a user runs the SET SESSION ROLE GROUP1; statement before creating a table, the current role changes to GROUP1. In this case, GROUP1 is used for matching.

      The matching rule is triggered only when a table is created. If you run the ALTER TABLE SET OWNER TO statement to change the table owner after the table is created, the matching rule is not triggered.

    Revoking privileges

    To revoke privileges from a user, run the REVOKE statement. For more information about revoking privileges, see REVOKE.

    Before you revoke schema privileges, check whether the account owns any scheduled jobs in DataWorks. If it does, reassign those jobs to an active, authorized account through the DataWorks Operation Center first. Otherwise, the jobs will fail at their next run with permission denied for schema.

    REVOKE SELECT ON TABLE tablename FROM "Alibaba Cloud account ID/email address" ; -- If you revoke privileges from a RAM user, use the required format for RAM users.

    System catalog privileges

    Starting from Hologres V3.0, any user who connects to a database can view information about all schemas and tables by querying the pg_class, pg_attribute, and pg_namespace system catalogs. This is especially true when connecting with BI or development tools, which automatically fetch schema, table, and column lists from these system catalogs. To restrict metadata visibility to authorized users only, Hologres V3.0.23 and later versions support row-level access control (RLS) on these three system catalogs. You can enable RLS by setting the following GUC parameter:

    • This GUC parameter controls only the pg_class, pg_attribute, and pg_namespace system catalogs.

    • A superuser must run this statement once for each database.

    • After this is set, only a superuser, an object's owner, or a user with any privilege on an object (table or schema) can see the corresponding metadata. Other users cannot view the metadata.

    -- Set by a superuser.
ALTER DATABASE <database_name> SET hg_experimental_enable_catalog_rls = on;
    Note

    For more information about system catalogs, see System Catalogs.

    Viewing privileges

    Run the following SQL statements to view user roles and privileges:

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

    Drop a user

    You can run SQL statements to drop a user from an instance connected to a development tool. The procedure varies based on the user type.

    • Drop a regular user

      To drop a regular user who has not created any objects such as tables, views, or extensions, you can run the following statement or drop the user in the HoloWeb console.

      drop user "Alibaba Cloud account ID/email address";

    • Drop an administrator such as a superuser

      To drop an administrator (such as a superuser) who owns objects, you must first transfer ownership of those objects. Attempting to drop such a user directly results in an error. Run the following statements:

      -- Transfer objects owned by Account A to Account B.
reassign owned by "Alibaba Cloud account ID of A" to "Alibaba Cloud account ID of B";     
-- Drop Account A.
drop user "Alibaba Cloud account ID of A";

    You can drop a RAM user from an instance by using one of the following methods:

    DROP USER "Alibaba Cloud account ID/email address";
    Important

    After a RAM user is dropped, they can no longer connect to the instance or access its objects. Proceed with caution.

    PostgreSQL has a strict privilege model. For best practices on choosing an authorization approach based on your business requirements, see Authorization based on the standard PostgreSQL permission model.