Default role

A predefined role which provides access to certain, commonly needed, privileged capabilities and information

A default 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.

Default roles were introduced in PostgreSQL 9.6 (commit 7a542700), albeit initially only with a single role available (pg_signal_backend).

Availability

  12 11 10 9.6
pg_read_all_settings        
pg_read_all_stats        
pg_stat_scan_tables        
pg_signal_backend        
pg_read_server_files        
pg_write_server_files        
pg_execute_server_program        
pg_monitor        

pg_monitor

The meta-role pg_monitor combines the following default roles:

  • pg_read_all_settings
  • pg_read_all_stats
  • pg_stat_scan_tables

See src/backend/catalog/system_views.sql.

Source code

The following constants representing the OIDs of each default role are created at compile time (src/backend/catalog/pg_authid_d.h):

  • DEFAULT_ROLE_MONITOR
  • DEFAULT_ROLE_READ_ALL_SETTINGS
  • DEFAULT_ROLE_READ_ALL_STATS
  • DEFAULT_ROLE_READ_ALL_STATS
  • DEFAULT_ROLE_READ_SERVER_FILES
  • DEFAULT_ROLE_WRITE_SERVER_FILES
  • DEFAULT_ROLE_EXECUTE_SERVER_PROGRAM
  • DEFAULT_ROLE_SIGNAL_BACKENDID

These are included via src/backend/catalog/pg_authid.h.

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

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(), DEFAULT_ROLE_READ_ALL_STATS) == false)
	{
		elog(ERROR, "must be superuser or a member of the pg_read_all_stats role");
	}
...

Categories

Management / adminstration, Security