TencentDB for PostgreSQL Database Privilege Overview-Operation Guide-Help & Documentation-Tencent Cloud

Account Privilege System
The PostgreSQL permission management model is a typical implementation of RBAC (Role-Based Access Control). It manages users, roles, and permissions through this model.
In PostgreSQL, the concepts of users and roles are almost the same. The only difference is that a user has the login privilege, while a role has the nologin privilege.
PostgreSQL permissions can be divided into two categories: "system permissions" and "database object permissions." PostgreSQL manages permissions through roles, which can have both system and database object permissions. Roles can also serve as a collection of permissions granted to other roles or users. Database management can be achieved by granting appropriate system and object permissions to roles/users.
System Permissions
System permissions refer to the privileges that allow specific database operations to be performed. In PostgreSQL, system permissions are managed using two methods: "Role Attributes" and "Default Roles."
Role Attributes
Role attributes can be specified during CREATE ROLE or modified using ALTER ROLE. Role attributes are stored in the pg_authid system table.
The syntax for CREATE ROLE is as follows:
CREATE ROLE name [ [ WITH ] option [ ... ] ]
where option can be:
      SUPERUSER | NOSUPERUSER
    | CREATEDB | NOCREATEDB
    | CREATEROLE | NOCREATEROLE
    | INHERIT | NOINHERIT
    | LOGIN | NOLOGIN
    | REPLICATION | NOREPLICATION
    | BYPASSRLS | NOBYPASSRLS
    | CONNECTION LIMIT connlimit
    | [ ENCRYPTED ] PASSWORD 'password' | PASSWORD NULL
    | VALID UNTIL 'timestamp'
    | IN ROLE role_name [, ...]
    | IN GROUP role_name [, ...]
    | ROLE role_name [, ...]
    | ADMIN role_name [, ...]
    | USER role_name [, ...]
    | SYSID uid
The superuser role attribute can bypass all permission checks and perform any operation in the database, holding the highest authority. The superuser privilege is similar to the root privilege in the Linux operating system.
Note
 In accordance with security requirements, TencentDB for PostgreSQL has disabled the use of superuser privileges. However, since some operations require superuser access, TencentDB for PostgreSQL provides the tencentdb_superuser role. Please refer to User and Permission Operations for more information.
Default Role
PostgreSQL provides a set of default roles that grant access to certain privileged features and information. Administrators can grant these roles to other users or roles, allowing them to access the specified features and information. The following table lists the default roles supported in PostgreSQL 11.
Role
Allowed operation
pg_execute_server_program
Allow the execution of corresponding operating system statements through functions or features that can perform partial operation statements.
pg_monitor
The permission to read and execute functions or views related to monitoring. This role includes pg_read_all_settings, pg_read_all_stats, and pg_stat_scan_tables.
pg_read_all_settings
Read all configuration information, including some information that only superusers can access.
pg_read_all_stats
Read all pg_stat_* views and utilize various statistics-related extensions, including some information visible only to superusers.
pg_read_server_files
Allows reading of certain authorized files on the server where the database is located, through copy or file access functions.
pg_signal_backend
You can initiate or terminate a session by sending commands to the backend.
pg_stat_scan_tables
Execute monitoring functions that may require long-term table locking (ACCESS SHARE lock).
pg_write_server_files
Allows writing to some authorized files on the server where the database is located, through copy or file access functions.
public
Public role is a hidden role that represents everyone. If a permission is granted to the public role, then all roles have that permission. Some objects have default permissions granted to the public role.
Database Object Permissions
Database object permissions are controlled using ACL (Access Control List). The table below lists all database object permissions and their abbreviations in PostgreSQL.
Permission
Abbreviation
Supported Objects
SELECT
r (“read”)
LARGE OBJECT、SEQUENCE、TABLE (and table-like objects)、table column
INSERT
a (“append”)
TABLE、table column
UPDATE
w (“write”)
LARGE OBJECT、SEQUENCE、TABLE、table column
DELETE
d
TABLE
TRUNCATE
D
TABLE
REFERENCES
x
TABLE、table column
TRIGGER
t
TABLE
CREATE
C
DATABASE、SCHEMA、TABLESPACE
CONNECT
c
DATABASE
TEMPORARY
T
DATABASE
EXECUTE
X
FUNCTION、PROCEDURE
USAGE
U
DOMAIN、FOREIGN DATA WRAPPER、FOREIGN SERVER、LANGUAGE、SCHEMA、SEQUENCE、TYPE
The following table lists the  permissions owned by a type of objects and the psql command to query the permissions:
object type
All Permissions
Default Permissions for the Public Role
psql command to view permissions
DATABASE
CTc
Tc
\l
DOMAIN
U
U
\dD+
FUNCTION or PROCEDURE
X
X
\df+
FOREIGN DATA WRAPPER
U
none
\dew+
FOREIGN SERVER
U
none
\des+
LANGUAGE
U
U
\dL+
LARGE OBJECT
rw
none
-
SCHEMA
UC
none
\dn+
SEQUENCE
rwU
none
\dp
TABLE (and table-like objects)
arwdDxt
none
\dp
Table column
arwx
none
\dp
TABLESPACE
C
none
\db+
TYPE
U
U
\dT+
In PostgreSQL, an aclitem represents a specific permission on a database object. For databases and schemas, aclitem is stored in pg_database.datacl and pg_namespace.nspacl. For other database objects like tables and views, a list of aclitem is saved in pg_class.relacl. For column-level permissions, aclitem is stored in pg_attribute.attacl.
For instance, normal_user=a*r/test1 indicates that the user 'normal_user' has INSERT and SELECT permissions for the current database object. The INSERT permission comes with a 'with grant option', which means it can be granted to other users. The final '/test1' indicates that this aclitem permission was granted by 'test1'.
postgres=# \dp
                            Access privileges
 Schema | Name | Type  |  Access privileges    | Column privileges | Policies
--------+------+-------+-------------------+-------------------+----------
 public | t1   | table | test1=arwdDxt/test1 |                   |
(1 rows)
postgres=# grant select on t1 to normal_user;
GRANT
postgres=# grant insert on t1 to normal_user with grant option;
GRANT
postgres=# grant update on t1 to public;
GRANT
postgres=# grant select (a) on t1 to test2;
GRANT
postgres=# \dp
                              Access privileges
 Schema | Name | Type  |  Access privileges    | Column privileges | Policies
--------+------+-------+-----------------------+-------------------+----------
 public | t1   | table | test1=arwdDxt/test1  +| a:               +|
        |      |       | normal_user=a*r/test1+|   test2=r/test1   |
        |      |       | =w/test1              |                   |
(1 rows)
-- Where, "=w/test1" specifies that test1 grants public the UPDATE privilege.
﻿

Role	Allowed operation
pg_execute_server_program	Allow the execution of corresponding operating system statements through functions or features that can perform partial operation statements.
pg_monitor	The permission to read and execute functions or views related to monitoring. This role includes pg_read_all_settings, pg_read_all_stats, and pg_stat_scan_tables.
pg_read_all_settings	Read all configuration information, including some information that only superusers can access.
pg_read_all_stats	Read all pg_stat_* views and utilize various statistics-related extensions, including some information visible only to superusers.
pg_read_server_files	Allows reading of certain authorized files on the server where the database is located, through copy or file access functions.
pg_signal_backend	You can initiate or terminate a session by sending commands to the backend.
pg_stat_scan_tables	Execute monitoring functions that may require long-term table locking (ACCESS SHARE lock).
pg_write_server_files	Allows writing to some authorized files on the server where the database is located, through copy or file access functions.
public	Public role is a hidden role that represents everyone. If a permission is granted to the public role, then all roles have that permission. Some objects have default permissions granted to the public role.

Permission	Abbreviation	Supported Objects
SELECT	r (“read”)	LARGE OBJECT、SEQUENCE、TABLE (and table-like objects)、table column
INSERT	a (“append”)	TABLE、table column
UPDATE	w (“write”)	LARGE OBJECT、SEQUENCE、TABLE、table column
DELETE	d	TABLE
TRUNCATE	D	TABLE
REFERENCES	x	TABLE、table column
TRIGGER	t	TABLE
CREATE	C	DATABASE、SCHEMA、TABLESPACE
CONNECT	c	DATABASE
TEMPORARY	T	DATABASE
EXECUTE	X	FUNCTION、PROCEDURE
USAGE	U	DOMAIN、FOREIGN DATA WRAPPER、FOREIGN SERVER、LANGUAGE、SCHEMA、SEQUENCE、TYPE

object type	All Permissions	Default Permissions for the Public Role	psql command to view permissions
DATABASE	CTc	Tc	\l
DOMAIN	U	U	\dD+
FUNCTION or PROCEDURE	X	X	\df+
FOREIGN DATA WRAPPER	U	none	\dew+
FOREIGN SERVER	U	none	\des+
LANGUAGE	U	U	\dL+
LARGE OBJECT	rw	none	-
SCHEMA	UC	none	\dn+
SEQUENCE	rwU	none	\dp
TABLE (and table-like objects)	arwdDxt	none	\dp
Table column	arwx	none	\dp
TABLESPACE	C	none	\db+
TYPE	U	U	\dT+

Database Privilege Overview

On this page:

Account Privilege System

System Permissions

Role Attributes

Default Role

Database Object Permissions