Hologres: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 develop data.

   You can 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 specify the account ID in the required format for RAM users. 
   CREATE USER "Alibaba Cloud account ID/Alibaba Cloud email address"; 
   -- Create a user and assign the superuser role to the user. 
   CREATE USER "Alibaba Cloud account ID/Alibaba Cloud email address" 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 Overview.

   -- 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. In this case, the user can perform authorized operations in Hologres. You can grant the permissions on databases, tables, views, and columns to the user by using the standard PostgreSQL authorization model. 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 in the schema. 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 on this table to User B.

   Operation	Statement syntax	Required
   Create a user that has permissions to log on to a Hologres instance	CREATE USER "Alibaba Cloud account ID/Alibaba Cloud email address";	Yes
   Create a user and assign the superuser role to the user	CREATE USER "Alibaba Cloud account ID/Alibaba Cloud email address" 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 email address";	No
   Grant a user the permissions to access a schema	GRANT USAGE ON SCHEMA schema_name TO "Alibaba Cloud account ID/Alibaba Cloud email address";	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 email address";	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 email address" 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 email address";	No
   Grant all users the SELECT permission on the tables to be created by the current authorizer in the public schema	ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO PUBLIC;	No
   Change the role of a user from a regular user to a superuser	ALTER USER "Alibaba Cloud account ID/Alibaba Cloud email address" SUPERUSER;	No
   Change the role of a user from a superuser to a regular user	ALTER USER "Alibaba Cloud account ID/Alibaba Cloud email address" NOSUPERUSER;	No
   Configure a user as the owner of a table that is created by another user	ALTER TABLE <tablename> OWNER TO "Alibaba Cloud account ID/Alibaba Cloud email address";	No
   Create a role that does not have permissions to log on to a Hologres instance	CREATE ROLE "Alibaba Cloud account ID/Alibaba Cloud email address";	No
   Assign a role to a user	GRANT <rolename> TO "Alibaba Cloud account ID/Alibaba Cloud email address" ;	No
   Grant a user the SELECT permission on specific columns of a table	GRANT SELECT (<column1>,<column2>,<column3>,...) ON TABLE <tablename> TO "Alibaba Cloud account ID/Alibaba Cloud email address" ;	No
   Grant a user the SELECT permission on a view Note If you use the standard PostgreSQL authorization model and want to access a view, you must be granted the SELECT permission on the view. If you use the simple permission model (SPM) or schema-level permission model (SLPM) and want to access a view, you must be added to the viewer user group or a higher-level user group.	-- Grant a user the SELECT permission on a view by using the standard PostgreSQL authorization model. GRANT SELECT ON <viewname> TO "Alibaba Cloud account ID/Alibaba Cloud email address" ;	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 email address";
   GRANT USAGE ON SCHEMA <schema_name>  TO "Alibaba Cloud account ID/Alibaba Cloud email address";
   GRANT SELECT ON TABLE <tablename> TO "Alibaba Cloud account ID/Alibaba Cloud email address";

   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 type of user. For more information about how to grant permissions, see GRANT.

3. Grant one or more users the permissions to delete a table.

   Only superusers and the owner of a table can delete the table. You can grant one or more users the permissions to delete a table by using one of the following methods:

   • Configure a new user as the owner of a table.

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

   • Assign the superuser role to a new user.

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

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

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

Grant permissions on the tables to be created

1. Grant permissions.

   • Grant all users or a specific user the SELECT permission as the default permission on the tables to be created by the authorizer in a schema. Examples:

     • Grant the p4_123xxx RAM user the SELECT permission on the tables to be created by the authorizer in the public schema.

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

     • Grant the p4_123xxx RAM user the SELECT permission on the tables to be created by the authorizer in the test schema.

       ALTER DEFAULT PRIVILEGES IN SCHEMA test GRANT SELECT ON TABLES TO "p4_123xxx";

     • Grant all users the SELECT permission on the tables to be created by the authorizer in the public schema.

       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 to be created by another user. Examples:

     • Grant all users of the public schema the SELECT permission on the tables to be created by the p4_id1 user.

       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 to be created by the p4_id1 user.

       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 to be created by the p4_id1 user.

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

   • Revoke the default permissions that you granted. Examples:

     -- Revoke the SELECT permission on the tables to be created by the authorizer in the public schema from the p4_123xxx user.
     ALTER DEFAULT PRIVILEGES IN SCHEMA public REVOKE SELECT ON TABLES FROM "p4_123xxx";
     
     -- Revoke the SELECT permission on the tables to be created by the authorizer in the test schema from the p4_123xxx user.
     ALTER DEFAULT PRIVILEGES IN SCHEMA test REVOKE SELECT ON TABLES FROM "p4_123xxx";
     
     -- Revoke the SELECT permission on the tables to be created by the authorizer in the public schema from all users.
     ALTER DEFAULT PRIVILEGES IN SCHEMA public REVOKE SELECT ON TABLES FROM PUBLIC;

2. Check whether the default permissions are granted.

   • Run the \ddp command on the PostgreSQL client to query the execution result of the ALTER DEFAULT PRIVILEGES statement.

   • Execute the following SQL statement in Hologres to check the result:

     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 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 permission.

   • 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 permission.

   The matching rules take effect only when you create tables. If you execute the ALTER TABLE SET OWNER TO statement to change the table owner after a table is created, the matching rules are not triggered.

Revoke permissions by using the standard PostgreSQL authorization model

REVOKE SELECT ON TABLE tablename FROM "Alibaba Cloud account ID/Alibaba Cloud email address" ; -- For a RAM user, you must specify the account ID in the required format for RAM users.

Query the permissions of a user

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

Drop a user

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

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.