Overview - Hologres - Alibaba Cloud Documentation Center

After a user connects to a Hologres instance, the user must be granted the required permissions to perform operations on the instance. This topic describes specific development permissions that are required to allow a user to perform data analytics operations on a Hologres instance.

Hologres authentication process

The following figure shows a complete Hologres authentication process from the step of adding a user to a Hologres instance to the step of performing operations on the instance.

Users

When you connect a user to a Hologres instance, you must execute the create user "xxx" statement to assign the user a role to manage the Hologres instance. Otherwise, the error message role "xxx" does not exist is returned. After the statement is successfully executed, the user is granted permissions on the Hologres instance. When you create or delete a Hologres user, the user is added to or deleted from a Hologres instance. To perform specific operations such as creating a table in a Hologres instance, a user must be granted permissions on a database. After the user is granted permissions on the database, the user can perform operations on only objects in this database but not objects in other databases.

Note You can execute the select * from pg_user; statement to view the superuser of the current instance.

Permission models

Compatible with PostgreSQL 11, Hologres provides the standard PostgreSQL authorization model to grant a user the development permissions on a Hologres instance. In this model, the permissions are managed in a fine-grained manner, and PostgreSQL authorization statements are complex. To simplify operations, Hologres provides the simple permission model (SPM) that allows you to manage permissions with ease.

The following table describes the SPM, schema-level permission model (SLPM), and standard PostgreSQL authorization model, and their scenarios.


Permission model	Scenario	Description
Standard PostgreSQL authorization model	This model is suitable for scenarios in which permissions are strictly managed. For example, this model allows you to grant a user permissions to use a specific table.	In this model, permissions are granted in a fine-grained and flexible manner. This way, a user can be granted permissions to use a specific table. However, the PostgreSQL authorization statements are complex. For more information about how to grant permissions by using the standard PostgreSQL authorization model, see Standard PostgreSQL authorization model.
SPM	This model allows you to manage database-level permissions and is applicable to scenarios in which permissions are managed in a coarse-grained manner.	In this model, specific permissions of each user group on databases cannot be changed. This model is applicable to most scenarios, and the authorization operations are simple. For more information about how to grant permissions by using the SPM, see Use the SPM.
SLPM	This model allows you to manage schema-level permissions and is suitable for scenarios in which permissions are managed in a fine-grained manner and a simple authorization process is required.	In this model, specific permissions of each user group on schemas cannot be changed. This model is applicable to scenarios in which permissions are managed in a fine-grained manner. The authorization operations are simple. For more information about how to grant permissions by using the SLPM, see Use the SLPM.

Permission authorization

If a user needs to perform data analytics operations on a Hologres instance, the user must be granted the specific permissions. You can select a permission model and grant the user the required permissions based on operations that the user wants to perform. The following tables describe specific operations that can be performed and related permissions that must be granted to users by using the standard PostgreSQL authorization model, SPM, or SLPM.

Note By default, a superuser has all permissions that are described in the following tables.


Operation to be authorized by using the standard PostgreSQL authorization model	Required permission	Authorization statement
CREATE USER(ROLE) DROP USER(ROLE)	CREATEROLE	You can execute the following statement to grant User A the permission to create a role: `alter user A createrole;`
CREATE TABLE VIEW TABLE FOREIGN TABLE	The CONNECT permission on a database and the CREATE permission on a schema	You can execute the following statement to grant User A the permission to create a table in a schema: `GRANT CREATE ON SCHEMA xx TO A;` By default, all users have permissions to create a table in the public schema.
SELECT	The USAGE or SELECT permission on a schema	You can execute one of the following statements to perform authorization operations: `GRANT USAGE ON SCHEMA xx to A; GRANT SELECT ON TABLE xx TO A; GRANT SELECT ON ALL TABLES IN SCHEMA xx TO A;`
INSERT UPDATE DELETE TRUNCATE	The USAGE, INSERT, UPDATE, DELETE, or TRUNCATE permission on a schema	You can execute one of the following statements to perform authorization operations: `GRANT USAGE ON SCHEMA xx to A; GRANT [INSERT/UPDATE/DELETE/TRUNCATE] ON TABLE xx TO A; GRANT [INSERT/UPDATE/DELETE/TRUNCATE] ON ALL TABLES IN SCHEMA xx TO A;`
ALTER TABLE	The ownership of a table (You can execute an ALTER OWNER statement to change the ownership of a table.)	You cannot execute a GRANT statement to allow User A to delete a table. Instead, you must execute the `ALTER TABLE xx OWNER TO A;` statement to transfer the ownership of the table to User A. Then, User A can delete the table.
DROP TABLE
CREATE DATABASE	CREATEDB	You can execute the following statement to grant User A the permission to create a database: `ALTER USER A CREATEDB;`
DROP DATABASE	Ownership of a database	You cannot execute a GRANT statement to allow User A to delete a database. Instead, you must execute the `ALTER DATABASE xx OWNER TO A;` statement to transfer the ownership of the database to User A. Then, User A can delete the database.
CREATE EXTENSION	Ownership of a database	-
GRANT REVOKE	The required permissions and the GRANT OPTION permission	You can execute the following statement to grant User A a specific permission and allow User A to grant the permission to other users: `GRANT [Privilege] TO A WITH GRANT OPTION;`


Authorization based on permission models	SPM		SLPM
Operation	Required permission	Authorization statement	Required permission	Authorization statement
CREATE USER(ROLE) DROP USER(ROLE)	DB admin	You can execute one of the following statements to grant User A the permissions that belong to the <db>_admin user group: `call spm_grant('A', '<dbname>_admin');` `call spm_create_user('A', '<dbname>_admin');` Important SPM does not support custom user authorization for usernames that end with `admin`, `developer`, `writer`, `viewer`, or `all_users`.	DB admin	You can execute one of the following statements to grant User A the permissions that belong to the {db}.admin user group: `call slpm_create_user('A', '<dbname>.<schema>.admin');` `call slpm_grant('<dbname>.<schema>.admin','A');` Important SLPM does not support custom user authorization for usernames that end with `admin`, `developer`, `writer`, `viewer`, or `all_users`.
CREATE TABLE VIEW TABLE FOREIGN TABLE	The permissions that belong to the superuser, <db>_admin, or <db>_developer user group	You can grant a user the permissions that belong to the <db>_admin or <db>_developer user group by using the SPM authorization statements that are described in this table.	The permissions that belong to the superuser, {db}.admin, or {db}.{schema}.developer user group	You can grant a user the permissions that belong to the {db}.admin or {db}.{schema}.developer user group by using the SLPM authorization statements that are described in this table.
SELECT	The permissions that belong to the superuser, <db>_admin, <db>_developer, <db>_writer, or <db>_viewer user group	You can grant a user the permissions that belong to the <db>_admin, <db>_developer, <db>_writer, or <db>_viewer user group by using the SPM authorization statements that are described in this table.	The permissions that belong to the superuser, {db}.admin, {db}.{schema}.developer, {db}.{schema}.writer, or {db}.{schema}.viewer user group	You can grant a user the permissions that belong to the {db}.admin, {db}.{schema}.developer, {db}.{schema}.writer, or {db}.{schema}.viewer user group by using the SLPM authorization statements that are described in this table.
INSERT UPDATE DELETE TRUNCATE	The permissions that belong to the superuser, <db>_admin, <db>_developer, or <db>_writer user group	You can grant a user the permissions that belong to the <db>_admin, <db>_developer, or <db>_writer user group by using the SPM authorization statements that are described in this table.	The permissions that belong to the superuser, {db}.admin, {db}.{schema}.developer, or {db}.{schema}.writer user group	You can grant a user the permissions that belong to the {db}.admin, {db}.{schema}.developer, {db}.{schema}.writer, or {db}.{schema}.viewer user group by using the SLPM authorization statements that are described in this table.
ALTER TABLE	The permissions that belong to the superuser, <db>_admin, or <db>_developer user group	You can grant a user the permissions that belong to the <db>_admin or <db>_developer user group by using the SPM authorization statements that are described in this table.	The permissions that belong to the superuser, {db}.admin, or {db}.{schema}.developer user group	You can grant a user the permissions that belong to the {db}.admin or {db}.{schema}.developer user group by using the SLPM authorization statements that are described in this table.
DROP TABLE
CREATE DATABASE DROP DATABASE CREATE EXTENSION	DB admin	You can grant a user the permissions that belong to the <db>_admin user group by using the SPM authorization statements that are described in this table.	DB admin	You can grant a user the permissions that belong to the {db}.admin user group by using the SLPM authorization statements that are described in this table.
GRANT REVOKE	DB admin	You can execute following statements to grant User A the GRANT and REVOKE permissions: `call spm_grant('A', 'role'); call spm_revoke('A', 'role');`	DB admin	You can execute following statements to grant User A the GRANT and REVOKE permissions: `call slpm_grant('A', 'role'); call slpm_revoke('A', 'role');`