Hologres is compatible with PostgreSQL and allows you to use the standard PostgreSQL authorization model. This topic shows you how to grant and revoke permissions by using the standard PostgreSQL authorization model.

Grant permissions by using the standard PostgreSQL authorization model

After you connect a Hologres instance to a development tool, you can execute SQL statements to grant permissions on the instance to a user by using the standard PostgreSQL authorization model.

  1. Create a user.
    You must create a Hologres user for an account before the account can be used to access Hologres and analyze data.
    Create a user by executing an SQL statement in the following syntax:
    -- Create a user that has the permissions to log on to the Hologres instance. If you want to grant the permissions to a RAM user, you must follow the formats that are used to specify RAM users in Hologres. 
    CREATE USER "Alibaba Cloud account ID/Alibaba Cloud account name/RAM username"; 
    -- Create a user and assign the superuser role to the user. 
    CREATE USER "Alibaba Cloud account ID/Alibaba Cloud account name/RAM username" SUPERUSER;
    The following sample statements show how to create a user. For more information about the formats that are used to specify Alibaba Cloud accounts and RAM users in Hologres, see Account overview.
    -- Use the ID of an Alibaba Cloud account to create a user. 
    CREATE USER "11822780xxx";
    -- Assign the superuser role to a RAM user. 
    CREATE USER "p4_1822780xxx" SUPERUSER; 
    For more information about how to create a role, see CREATE ROLE.
  2. Grant permissions to the user.
    After you create a Hologres user, you must grant permissions to the user so that the user can perform authorized operations in Hologres. The following table describes the commonly used statements that are executed to grant permissions in Hologres.
    Note You can grant permissions only on existing objects to a user by using the standard PostgreSQL authorization model. The permissions do not take effect on objects that are created after the authorization. For example, User A grants User B the SELECT permission on all tables in the public schema. Then, User A creates another table. User B does not have the SELECT permission on this table. To allow User B to query this table, User A must grant the SELECT permission to User B again.
    Operation Statement Required
    Create a user that has the permissions to log on to a Hologres instance.
    CREATE USER "Alibaba Cloud account ID/Alibaba Cloud account name/RAM username";
    Yes
    Create a user and assign the superuser role to the user.
    CREATE USER "Alibaba Cloud account ID/Alibaba Cloud account name/RAM username" SUPERUSER;
    No
    Grant a user the permissions to create a schema.
    GRANT CREATE ON SCHEMA schema_name TO "Alibaba Cloud account ID/Alibaba Cloud account name/RAM username";
    No
    Grant a user the permissions to query a schema.
    GRANT USAGE ON SCHEMA schema_name TO "Alibaba Cloud account ID/Alibaba Cloud account name/RAM username";
    Yes
    Note A user can query tables in a schema only after the user is granted the permissions to query the schema.
    Grant all users the SELECT, INSERT, and UPDATE permissions on all tables in the public schema.
    GRANT SELECT,INSERT,UPDATE ON ALL TABLES IN SCHEMA public to PUBLIC;
    No
    Grant a user the SELECT permission on a table.
    GRANT SELECT ON TABLE <tablename> TO "Alibaba Cloud account ID/Alibaba Cloud account name/RAM username";
    No
    Grant a user the SELECT permission on a table and authorize the user to grant the permission to other users.
    GRANT SELECT ON TABLE <tablename> TO "Alibaba Cloud account ID/Alibaba Cloud account name/RAM username" WITH GRANT OPTION;
    No
    Grant a user the SELECT permission on all tables in the public schema.
    GRANT SELECT ON ALL TABLES IN SCHEMA public TO "Alibaba Cloud account ID/Alibaba Cloud account name/RAM username";
    No
    Grant all users the SELECT permission as the default permission on all tables, including the tables that will be created in the future, in the public schema.
    ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO PUBLIC;
    No
    Change the role of a user from regular user to superuser.
    ALTER USER "Alibaba Cloud account ID/Alibaba Cloud account name/RAM username" SUPERUSER;
    No
    Change the role of a user from superuser to regular user.
    ALTER USER "Alibaba Cloud account ID/Alibaba Cloud account name/RAM username" NOSUPERUSER;
    No
    Set a user as an owner of a table created by another user.
    ALTER TABLE <tablename> OWNER TO "Alibaba Cloud account ID/Alibaba Cloud account name/RAM username";
    No
    Create a role that does not have the permissions to log on to a Hologres instance.
    CREATE ROLE "Alibaba Cloud account ID/Alibaba Cloud account name/RAM username";
    No
    Assign a role to a user.
    GRANT <rolename> TO "Alibaba Cloud account ID/Alibaba Cloud account name/RAM username"
    No
    A CREATE ROLE statement is used to create a role that does not have the permissions to log on to a Hologres instance, for example, a virtual role or a user group that represents a specific user type. For more information about how to grant permissions, see GRANT.
  3. Grant the user the permissions to delete a table.
    Only the superusers and the owners of a table can delete the table. You can authorize the user to delete a table by using one of the following methods:
    • Set the user as an owner of the table.
      ALTER TABLE TABLENAME OWNER TO "Alibaba Cloud account ID/Alibaba Cloud account name/RAM username";
    • Assign the superuser role to the user.
      ALTER USER "Alibaba Cloud account ID/Alibaba Cloud account name/RAM username" SUPERUSER;
    • Add multiple users, including the user that you created, to a user group and assign the ownership of the table to the user group.
      CREATE USER "Alibaba Cloud account ID/Alibaba Cloud account name/RAM username";
      CREATE ROLE <rolename>;
      GRANT <rolename> TO "Alibaba Cloud account ID/Alibaba Cloud account name/RAM username";
      ALTER TABLE <tablename> OWNER TO <rolename>;

Grant the permissions on the tables that will be created in the future

The standard PostgreSQL authorization model does not allow you to grant a user the permissions on the tables that will be created in the future. You can execute the ALTER DEFAULT PRIVILEGES statement to grant a user the permissions on the tables that will be created in the future by performing the following steps:
Note
  • This statement does not affect the permissions on existing objects.
  • This statement can be used to grant the default permissions only on tables, schemas, functions, sequences, and types.
  1. Grant the default permissions.
    • Grant all users or a specified user the SELECT permission as the default permission on the tables that you will create in a schema in the future. The following sample statements are for your reference:
      • Grant the user p4_123xxx the SELECT permission on the tables that you will create in the public schema in the future.
        ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO "p4_123xxx";
      • Grant the user p4_123xxx the SELECT permission on the tables that you will create in the test schema in the future.
        ALTER DEFAULT PRIVILEGES IN SCHEMA test GRANT SELECT ON TABLES TO "p4_123xxx";
      • Grant all users the SELECT permission on the tables that you will create in the public schema in the future.
        ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO PUBLIC;
    • Grant all users or a specified user the SELECT permission as the default permission on the tables that another user will create in the future. The following sample statements are for your reference:
      • Grant all users of the public schema the SELECT permission on the tables that the user p4_id1 will create in the future.
        ALTER DEFAULT PRIVILEGES FOR ROLE "p4_id1" IN SCHEMA public GRANT SELECT ON TABLES TO PUBLIC;
      • Grant the user p4_id2 of the public schema the SELECT permission on the tables that the user p4_id1 will create in the future.
        ALTER DEFAULT PRIVILEGES FOR ROLE "p4_id1" IN SCHEMA public GRANT SELECT ON TABLES TO "p4_id2";
      • Grant all users of the test schema the SELECT permission on the tables that the user p4_id1 will create in the future.
        ALTER DEFAULT PRIVILEGES FOR ROLE "p4_id1" IN SCHEMA test GRANT SELECT ON TABLES TO PUBLIC;
  2. Check whether the default permissions are granted.
    Run the \ddp command on the PostgreSQL client to view the result of the ALTER DEFAULT PRIVILEGES statement.
    When you create a table as a user, Hologres checks the user and schema that you use based on the pg_catalog.pg_default_acl system table. If an ALTER DEFAULT PRIVILEGES statement is detected, Hologres uses the relevant matching rule for authentication. The system provides the following matching rules:
    • If you perform operations as a user, Hologres checks whether you use a user that has the default permissions.
    • If you execute the SET SESSION ROLE GROUP1; statement before you create a table as a user, the GROUP1 role is assigned to the user. Then, Hologres checks whether the GROUP1 role has the default permissions.
    The matching rules take effect only when you create tables. If you execute the ALTER TABLE SET OWNER TO statement to modify the table owner after a table is created, the matching rules are not triggered.

Revoke permissions by using the standard PostgreSQL authorization model

To revoke specific permissions from a user, execute the following REVOKE statement. For more information, see REVOKE.
REVOKE SELECT ON TABLE tablename FROM "Alibaba Cloud account ID/Alibaba Cloud account name/RAM username"; -- If you want to revoke permissions from a RAM user, you must follow the formats that are used to specify RAM users in Hologres. 

View the permissions of a user

Execute the following SQL statements to view the permissions of a user:
SELECT ROLNAME FROM pg_roles;
SELECT user_display_name(ROLNAME) FROM pg_roles;

Delete a user

Execute the following SQL statement to delete a user from a Hologres instance:
DROP USER "Alibaba Cloud account ID/Alibaba Cloud account name/RAM username";
Notice After you delete a user from a Hologres instance, the user cannot connect to the instance or access objects in the instance. Proceed with caution.

The standard PostgreSQL authorization model strictly divides permissions. Best practices for using the model are provided for your reference. You can use the model based on the best practices and your business requirements. For more information, see Authorize roles based on PostgreSQL privileges.