Hologres:Hologres permission models

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.

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');

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');

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 the 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 the following statements to grant User A the GRANT and REVOKE permissions:

call slpm_grant('A', 'role');
call slpm_revoke('A', 'role');