- Release Notes and Announcements
- Release Notes
- Open-Source Plugin Privilege Escalation Has Vulnerability Risk for PostgreSQL
- Announcements
- Security Risk Statement for Creating Partial Plugins in PostgreSQL Cloud Database
- TencentDB for PostgreSQL Will Start Billing for Backup Space
- Some Extensions on Lower Kernel Version Can't Be Created and Upgraded in TencentDB for PostgreSQL
- Announcement on the Risk of Privilege Escalation Vulnerability in Cloud Database PostgreSQL Open Source Plugin
- Product Introduction
- Purchase Guide
- Getting Started
- Kernel Version Introduction
- Operation Guide
- Instance Management
- Upgrading Instance
- Read-Only Instance
- Account Management
- Database Optimization
- Parameter Management
- Log management
- Backup and Restoration
- Extension Management
- Network Management
- Access Management
- Data Encryption
- Security Groups
- Monitoring and Alarms
- Tag
- PostgreSQL for Serverless
- Best Practice
- postgres_fdw Extension for Cross-database Access
- Automatically Creating Partition in PostgreSQL
- Searching in High Numbers of Tags Based on pg_roaringbitmap
- Querying People Nearby with One SQL Statement
- Configuring TencentDB for PostgreSQL as GitLab's External Data Source
- Supporting Tiered Storage Based on cos_fdw Extension
- Implement Read/Write Separation via pgpool
- API Documentation
- History
- Introduction
- API Category
- Making API Requests
- Instance APIs
- DescribeDBInstanceHAConfig
- SwitchDBInstancePrimary
- ModifyDBInstanceChargeType
- ModifyDBInstanceHAConfig
- UpgradeDBInstanceKernelVersion
- CreateInstances
- DescribeDBInstanceAttribute
- DescribeDBInstances
- InitDBInstances
- UpgradeDBInstance
- SetAutoRenewFlag
- RestartDBInstance
- RenewInstance
- ModifyDBInstancesProject
- ModifyDBInstanceName
- DestroyDBInstance
- IsolateDBInstances
- DisIsolateDBInstances
- ModifySwitchTimePeriod
- ModifyDBInstanceSpec
- ModifyDBInstanceDeployment
- DescribeEncryptionKeys
- CreateDBInstances
- Read-only Replica APIs
- Backup and Restoration APIs
- DescribeBackupDownloadRestriction
- ModifyBackupDownloadRestriction
- DescribeDBXlogs
- DescribeDBBackups
- ModifyBackupPlan
- DescribeCloneDBInstanceSpec
- DescribeBackupPlans
- DescribeAvailableRecoveryTime
- CloneDBInstance
- ModifyBaseBackupExpireTime
- DescribeLogBackups
- DescribeBaseBackups
- DescribeBackupSummaries
- DescribeBackupOverview
- DescribeBackupDownloadURL
- DeleteLogBackup
- DeleteBaseBackup
- CreateBaseBackup
- Parameter Management APIs
- Network APIs
- Security Group APIs
- Performance Optimization APIs
- Account APIs
- Specification APIs
- PostgreSQL for Serverless APIs
- Data Types
- Error Codes
- FAQs
- Service Agreement
- Glossary
- Contact Us
- Release Notes and Announcements
- Release Notes
- Open-Source Plugin Privilege Escalation Has Vulnerability Risk for PostgreSQL
- Announcements
- Security Risk Statement for Creating Partial Plugins in PostgreSQL Cloud Database
- TencentDB for PostgreSQL Will Start Billing for Backup Space
- Some Extensions on Lower Kernel Version Can't Be Created and Upgraded in TencentDB for PostgreSQL
- Announcement on the Risk of Privilege Escalation Vulnerability in Cloud Database PostgreSQL Open Source Plugin
- Product Introduction
- Purchase Guide
- Getting Started
- Kernel Version Introduction
- Operation Guide
- Instance Management
- Upgrading Instance
- Read-Only Instance
- Account Management
- Database Optimization
- Parameter Management
- Log management
- Backup and Restoration
- Extension Management
- Network Management
- Access Management
- Data Encryption
- Security Groups
- Monitoring and Alarms
- Tag
- PostgreSQL for Serverless
- Best Practice
- postgres_fdw Extension for Cross-database Access
- Automatically Creating Partition in PostgreSQL
- Searching in High Numbers of Tags Based on pg_roaringbitmap
- Querying People Nearby with One SQL Statement
- Configuring TencentDB for PostgreSQL as GitLab's External Data Source
- Supporting Tiered Storage Based on cos_fdw Extension
- Implement Read/Write Separation via pgpool
- API Documentation
- History
- Introduction
- API Category
- Making API Requests
- Instance APIs
- DescribeDBInstanceHAConfig
- SwitchDBInstancePrimary
- ModifyDBInstanceChargeType
- ModifyDBInstanceHAConfig
- UpgradeDBInstanceKernelVersion
- CreateInstances
- DescribeDBInstanceAttribute
- DescribeDBInstances
- InitDBInstances
- UpgradeDBInstance
- SetAutoRenewFlag
- RestartDBInstance
- RenewInstance
- ModifyDBInstancesProject
- ModifyDBInstanceName
- DestroyDBInstance
- IsolateDBInstances
- DisIsolateDBInstances
- ModifySwitchTimePeriod
- ModifyDBInstanceSpec
- ModifyDBInstanceDeployment
- DescribeEncryptionKeys
- CreateDBInstances
- Read-only Replica APIs
- Backup and Restoration APIs
- DescribeBackupDownloadRestriction
- ModifyBackupDownloadRestriction
- DescribeDBXlogs
- DescribeDBBackups
- ModifyBackupPlan
- DescribeCloneDBInstanceSpec
- DescribeBackupPlans
- DescribeAvailableRecoveryTime
- CloneDBInstance
- ModifyBaseBackupExpireTime
- DescribeLogBackups
- DescribeBaseBackups
- DescribeBackupSummaries
- DescribeBackupOverview
- DescribeBackupDownloadURL
- DeleteLogBackup
- DeleteBaseBackup
- CreateBaseBackup
- Parameter Management APIs
- Network APIs
- Security Group APIs
- Performance Optimization APIs
- Account APIs
- Specification APIs
- PostgreSQL for Serverless APIs
- Data Types
- Error Codes
- FAQs
- Service Agreement
- Glossary
- Contact Us
Account Privilege System
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
System permissions are used to perform database operations. PostgreSQL manages system permissions using role attributes and default roles.
Role attributes
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.
Note:
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.
Default roles
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.
Role | Allowed Access |
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. |
Database Object Permissions
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.
Permissions | Abbreviation | Supported Object |
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 | Permissions | Permissions of Default Role (public) | psql Command to Query 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, 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=# \\dpAccess privilegesSchema | Name | Type | Access privileges | Column privileges | Policies--------+------+-------+-------------------+-------------------+----------public | t1 | table | test1=arwdDxt/test1 | |(1 rows)postgres=# grant select on t1 to normal_user;GRANTpostgres=# grant insert on t1 to normal_user with grant option;GRANTpostgres=# grant update on t1 to public;GRANTpostgres=# grant select (a) on t1 to test2;GRANTpostgres=# \\dpAccess privilegesSchema | 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.
Yes
No
Was this page helpful?