Predefined role

A built-in role which provides access to certain, commonly needed, privileged capabilities and information

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

	15	14	13	12	11	10	9.6
pg_checkpointer
pg_database_owner
pg_execute_server_program
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_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()).

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_CHECKPOINTER
ROLE_PG_DATABASE_OWNER
ROLE_PG_MONITOR
ROLE_PG_READ_ALL_SETTINGS
ROLE_PG_READ_ALL_STATS
ROLE_PG_READ_ALL_STATS
ROLE_PG_READ_SERVER_FILES
ROLE_PG_WRITE_SERVER_FILES
ROLE_PG_EXECUTE_SERVER_PROGRAM
ROLE_PG_SIGNAL_BACKENDID
ROLE_PG_READ_ALL_DATA
ROLE_PG_WRITE_ALL_DATA

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 15
- pg_checkpointer role added (commit 4168a474)
- pg_read_all_stats can now access pg_backend_memory_contexts and pg_shmem_allocations (commit 77ea4f94)
PostgreSQL 14
- pg_database_owner role added (commit a14a0118)
- pg_read_all_data role added (commit 6c3ffd69)
- pg_write_all_data role added (commit 6c3ffd69)
- renamed from "default role" to "predefined role" (commit c9c41c7a)
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

Version charts

Useful overviews

Useful links

Contents