PostgreSQL adopts a role-based access control (RBAC) model to manage users, roles, and permissions.
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 supports system permissions and database object permissions, and manages them using the concept of roles. Both categories of the permissions can be granted to a role, and this role can grant its own permissions to other roles or users.
You can grant system or object permissions to roles/users to manage databases.
System permissions are used to perform database operations. PostgreSQL manages system permissions using role attributes and default roles.
You can specify attributes when creating a role with CREATE ROLE, or modify them after creation with ALTER ROLE. Role attributes are stored in the pg_authid system table.
CREATE ROLE syntax:
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
A role with the superuser attribute can bypass all privilege checks and perform all database operations, because a superuser has the highest privilege in the database, which is similar to the root privilege in Linux.
TencentDB for PostgreSQL has disabled the superuser privilege due to security requirements. However, some operations must be performed by a superuser, so TencentDB for PostgreSQL provides the tencentdb_superuser role. For details, see Roles and Permissions.
PostgreSQL provides a set of default roles which provide access to certain, commonly needed, privileged capabilities and information. Administrators can grant these roles to users and/or other roles in their environment, providing those users with access to the specified capabilities and information. The following table lists the default roles supported in PostgreSQL 11.
|pg_execute_server_program||Allow executing programs on the database server as the user the database runs as with COPY and other functions which allow executing a server-side program.|
|pg_monitor||Read/Execute various monitoring views and functions. This role is a member of pg_read_all_settings, pg_read_all_stats, and pg_stat_scan_tables.|
|pg_read_all_settings||Read all configuration variables, even those normally visible only to superusers.|
|pg_read_all_stats||Read all pg_stat_* views and use various statistics related extensions, even those normally visible only to superusers.|
|pg_read_server_files||Allow reading files from any location the database can access on the server with COPY and other file-access functions.|
|pg_signal_backend||Signal another backend to cancel a query or terminate its session.|
|pg_stat_scan_tables||Execute monitoring functions that may take ACCESS SHARE locks on tables, potentially for a long time.|
|pg_write_server_files||Allow writing to files in any location the database can access on the server with COPY and other file-access functions.|
|public||An implicitly defined group that always includes all roles. Any particular role will have the sum of permissions granted directly to public. PostgreSQL grants default permissions on some types of objects to public.|
PostgreSQL uses an access control list (ACL) to manage database object permissions. The following table lists all database object permissions in PostgreSQL and their abbreviations.
|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|
|REFERENCES||x||TABLE, table column|
|CREATE||C||DATABASE, SCHEMA, TABLESPACE|
|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||Permissions||Permissions of Default Role (public)||psql Command to Query Permissions|
|FUNCTION or PROCEDURE||X||X||\df+|
|FOREIGN DATA WRAPPER||U||none||\dew+|
|TABLE (and table-like objects)||arwdDxt||none||\dp|
In PostgreSQL, the permissions granted for a particular object are displayed as a list of aclitem entries. The aclitem list of database and schema permissions is stored in pg_database.datacl and pg_namespace.nspacl, that of permissions for tables, views, and other objects stored in pg_class.relacl, and that of column permissions stored in pg_attribute.attacl.
For example, "normal_user=a*r/test1" specifies that the user normal_user has the privilege INSERT with grant option (which gives the user the right to grant the privilege to others) and the privilege SELECT, both 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.