Predefined role
A predefined role
is a built-in role which provides access to certain, commonly needed, privileged capabilities and information. Membership of such roles can be granted to individual users without needing to make that user a superuser.
Predefined roles
were introduced in PostgreSQL 9.6, albeit initially only with a single role available (pg_signal_backend
).
Availability
16 | 15 | 14 | 13 | 12 | 11 | 10 | 9.6 | |
---|---|---|---|---|---|---|---|---|
pg_checkpoint | ||||||||
pg_database_owner | ||||||||
pg_execute_server_program | ||||||||
pg_maintain | ||||||||
pg_monitor | ||||||||
pg_read_all_data | ||||||||
pg_read_all_settings | ||||||||
pg_read_all_stats | ||||||||
pg_read_server_files | ||||||||
pg_signal_backend | ||||||||
pg_stat_scan_tables | ||||||||
pg_use_reserved_connections | ||||||||
pg_write_all_data | ||||||||
pg_write_server_files |
pg_monitor
The pg_monitor
role combines the following default roles:
pg_read_all_settings
pg_read_all_stats
pg_stat_scan_tables
as well as providing execution permission for the following file system functions:
(but not however pg_ls_dir()
).
See also: src/backend/catalog/system_views.sql.
Source code
The following constants representing the OIDs of each default role are created at compile time (src/include/catalog/pg_authid.dat):
ROLE_PG_CHECKPOINT
ROLE_PG_DATABASE_OWNER
ROLE_PG_EXECUTE_SERVER_PROGRAM
ROLE_PG_MAINTAIN
ROLE_PG_MONITOR
ROLE_PG_READ_ALL_DATA
ROLE_PG_READ_ALL_SETTINGS
ROLE_PG_READ_ALL_STATS
ROLE_PG_READ_SERVER_FILES
ROLE_PG_SIGNAL_BACKENDID
ROLE_PG_STAT_SCAN_TABLES
ROLE_PG_USE_RESERVED_CONNECTIONS
ROLE_PG_WRITE_ALL_DATA
ROLE_PG_WRITE_SERVER_FILES
Note: in PostgreSQL 13 and earlier, these constants were prefixed with "DEFAULT_ROLE_
".
Renaming proposal
During the PostgreSQL 13 development cycle, it was proposed that "default_role
" should be renamed to "predefined role
", and the change was actually committed (commit 0e936a21) but subsequently reverted pending further discussion (commit c185a577). The change was later made for PostgreSQL 14 (commit c9c41c7a).
Change history
- PostgreSQL 16
- PostgreSQL 15
pg_checkpoint
role added (commits 4168a474 and b9eb0ff0)pg_read_all_stats
can now accesspg_backend_memory_contexts
andpg_shmem_allocations
(commit 77ea4f94)
- PostgreSQL 14
- PostgreSQL 11 - following default roles added (commit 0fdc8495):
pg_read_server_files
pg_write_server_files
pg_execute_server_program
- PostgreSQL 10 - following default roles added (commit 25fff407):
pg_read_all_settings
pg_read_all_stats
pg_stat_scan_tables
pg_monitor
- PostgreSQL 9.6
pg_signal_backend
added (commit 7a542700)
Examples
postgres=> \du someuser List of roles Role name | Attributes | Member of -----------+------------+----------- someuser | | {} postgres=> SHOW data_directory; ERROR: must be superuser or a member of pg_read_all_settings to examine "data_directory" postgres=> \c - postgres You are now connected to database "postgres" as user "postgres". postgres=# GRANT pg_read_all_settings TO someuser; GRANT ROLE postgres=# \c - someuser You are now connected to database "postgres" as user "someuser". postgres=> \du someuser List of roles Role name | Attributes | Member of -----------+------------+------------------------ someuser | | {pg_read_all_settings} postgres=> SHOW data_directory; data_directory --------------------- /var/lib/pgsql/data (1 row)
Usage example in C:
#include "utils/acl.h" #include "catalog/pg_authid.h" ... if (is_member_of_role(GetUserId(), ROLE_PG_READ_ALL_STATS) == false) { elog(ERROR, "must be superuser or a member of the pg_read_all_stats role"); } ...
References
- PostgreSQL documentation: Predefined roles
- PostgreSQL 13 documentation: Default roles