Hologres is compatible with PostgreSQL and allows you to use the standard PostgreSQL authorization model. This topic describes 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 in the Hologres instance.
    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 permissions to log on to the Hologres instance. If you want to grant permissions to a RAM user, you must follow the formats 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 Accounts.
    -- Use the ID of an Alibaba Cloud account to create a user. 
    CREATE USER "11822780xxx";
    -- Create a RAM user and assign the superuser role to the 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 statements that are commonly used to grant permissions in Hologres.
    Note You can grant permissions only on existing objects by using the standard PostgreSQL authorization model. Permissions do not take effect on objects that are created after the permissions are granted. 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 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 table in 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 access 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 access 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 in the public schema, including the tables that will be created in the future
    ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO PUBLIC;
    No
    Change the role of a user from normal 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 normal 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 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
    For example, you can execute the following SQL statements to grant the SELECT permission on a table to a new user by using the standard PostgreSQL authorization model:
    CREATE USER "Alibaba Cloud account ID/Alibaba Cloud account name/RAM username";
    GRANT USAGE ON SCHEMA <schema_name> TO "Alibaba Cloud account ID/Alibaba Cloud account name/RAM username";
    GRANT SELECT ON TABLE <tablename> TO "Alibaba Cloud account ID/Alibaba Cloud account name/RAM username";
    The CREATE ROLE statement is used to create a role that does not have permissions to log on to a Hologres instance. For example, you can execute the statement to create 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 superuser and the owners of a table can delete the table. You can use one of the following methods to authorize the user to delete a table:
    • 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 permissions on 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.
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 specific user the SELECT permission as the default permission on the tables that you will create in a schema in the future. For example, you can execute the following statements:
      • Grant the p4_123xxx user 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 p4_123xxx user 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 specific user the SELECT permission as the default permission on the tables that another user will create in the future. For example, you can execute the following statements:
      • Grant all users of the public schema the SELECT permission on the tables that the p4_id1 user will create in the future.
        ALTER DEFAULT PRIVILEGES FOR ROLE "p4_id1" IN SCHEMA public GRANT SELECT ON TABLES TO PUBLIC;
      • Grant the p4_id2 user of the public schema the SELECT permission on the tables that the p4_id1 user 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 p4_id1 user 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 execution result of the ALTER DEFAULT PRIVILEGES statement.
    When you create a table as a user, Hologres checks the user and schema 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. Take note of the following matching rules:
    • If you perform operations as a user, Hologres checks whether the user 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 from a Hologres instance

If your Hologres instance is connected to a development tool, you can use SQL statements to delete users.
  • Delete a normal user
    If your normal user has not created objects such as tables, views, or extensions, you can execute the following statement to delete the user from the Hologres instance. You can also delete the user in the HoloWeb console.
    DROP USER "Alibaba Cloud account ID/Alibaba Cloud account name/RAM username";
  • Delete an administrator
    If your user has created objects such as tables, views, or extensions in the instance and is the administrator of these objects (especially in the standard authorization model), you must transfer the objects to another user before you can delete the user. You can execute the following statements to delete the user:
    -- Transfer the objects owned by User A to User B.
    REASSIGN OWNED BY "ID of User A" to "ID of User B";     
    -- Delete User A.
    DROP USER "ID of User A";
You can execute the following SQL statement to delete a RAM user from a Hologres instance:
DROP USER "Alibaba Cloud account ID/Alibaba Cloud account name/RAM username";
Notice After you delete a RAM user from a Hologres instance, the RAM user cannot connect to the instance or access objects in the instance. Proceed with caution.

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