Contents

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()).

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_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

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");
	}
...

Categories

Management / adminstration, Security