Not everyone knows, but at since PostgreSQL 9.6, we have some built-in roles.

Of course, there is always superuser (usually called postgres), but I'm not talking about it. I'm talking about magical roles that have names starting with pg_.

Over the years we got more and more of them:

• pg_signal_backend (since PostgreSQL 9.6)
• pg_monitor (since PostgreSQL 10)
• pg_read_all_settings (since PostgreSQL 10)
• pg_read_all_stats (since PostgreSQL 10)
• pg_stat_scan_tables (since PostgreSQL 10)
• pg_execute_server_program (since PostgreSQL 11)
• pg_read_server_files (since PostgreSQL 11)
• pg_write_server_files (since PostgreSQL 11)
• pg_database_owner (since PostgreSQL 14)
• pg_read_all_data (since PostgreSQL 14)
• pg_write_all_data (since PostgreSQL 14)
• pg_checkpoint (since PostgreSQL 15)
• pg_maintain (since PostgreSQL 16)
• pg_use_reserved_connections (since PostgreSQL 16)

All of them are groups – that is, you can't log in to database using any of these, but you can grant any of these roles to some user, and then they will get some more privileges.

Before I will go into details, small warning.

Roles, and who has what role granted, are not per database. It's global thing. So, if you'd, for example, grant role pg_read_all_data to some user in database prod-copy, they will also have the same privileges in database prod.

So, what do the roles allow one to do?

pg_checkpoint (Pg 15+)

User that belongs to this role will gain the ability to run CHECKPOINT command. Normally this can be done only by superusers, but if your usecase requires checkpoints at specific times, you can now (since Pg 15) do it without superuser.

pg_database_owner (Pg 14+)

Interesting role that doesn't grant any privileges. But, privileges can be granted to the role, and then, whoever owns the database will get the privilege.

For example, let's consider case, where I have database named test owned by user test:

$ \l test
                                                 List OF DATABASES
 Name │ Owner │ Encoding │ Locale Provider │   COLLATE   │    Ctype    │ ICU Locale │ ICU Rules │ Access privileges
──────┼───────┼──────────┼─────────────────┼─────────────┼─────────────┼────────────┼───────────┼───────────────────
 test │ test  │ UTF8     │ libc            │ en_US.UTF-8 │ en_US.UTF-8 │            │           │
(1 ROW)

In this database, I have table, named whatever that belongs to user depesz:

$ \dt whatever
         List OF relations
 Schema │   Name   │ TYPE  │ Owner
────────┼──────────┼───────┼────────
 public │ whatever │ TABLE │ depesz
(1 ROW)

Now, if I'll try to select data, from this table, while being logged as test user, I will get:

$ SELECT * FROM whatever;
ERROR:  permission denied FOR TABLE whatever

But, if depesz (or any other user that has this privilege) would grant select to pg_database_owner:

$ GRANT SELECT ON whatever TO pg_database_owner ;
GRANT
 
$ \dp whatever
                                   Access privileges
 Schema │   Name   │ TYPE  │     Access privileges      │ COLUMN privileges │ Policies
────────┼──────────┼───────┼────────────────────────────┼───────────────────┼──────────
 public │ whatever │ TABLE │ depesz=arwdDxtm/depesz    ↵│                   │
        │          │       │ pg_database_owner=r/depesz │                   │
(1 ROW)

Now, user test can read it:

$ SELECT CURRENT_USER, current_database(), * FROM whatever;
 CURRENT_USER │ current_database │ STATUS
──────────────┼──────────────────┼────────
 test         │ test             │ works
(1 ROW)

I don't think this will be often needed, but there definitely are cases where it can be helpful.

pg_execute_server_program (Pg 11+)

This privilege is used, as far as I can tell, in two separate cases:

• when you use FROM/TO PROGRAM option to COPY command, which normally requires superuser.
• when you use file_fdw foreign data wrapper, and you want to CREATE FOREIGN TABLE using it's PROGRAM option.

On my desktop I have a two files with data in csv format:

• /tmp/input.csv – plain text csv
• /tmp/input.csv.gz – same data, but gzip compresses

If I'd try to load them, using any of the mentioned methods, using user that isn't superuser, I will get errors:

$ CREATE TABLE for_copy (x text, y int4);
CREATE TABLE
 
$ copy for_copy FROM program 'zcat /tmp/input.csv.gz' WITH csv;
ERROR:  must be superuser OR have privileges OF the pg_execute_server_program ROLE TO COPY TO OR FROM an external program
HINT:  Anyone can COPY TO stdout OR FROM stdin. psql's \copy command also works for anyone.
 
$ CREATE FOREIGN TABLE for_fdw ( x text, y int4) SERVER pglog OPTIONS ( program 'zcat /tmp/INPUT.csv.gz', format 'csv' );
ERROR:  only superuser or a role with privileges of the pg_execute_server_program role may specify the program option of a file_fdw foreign table

But if I'd grant pg_execute_server_program to my test user, it will work:

$ copy for_copy FROM program 'zcat /tmp/input.csv.gz' WITH csv;
COPY 2
 
$ SELECT * FROM for_copy ;
 x │ y
───┼───
 a │ 2
 b │ 1
(2 ROWS)
 
$ CREATE FOREIGN TABLE for_fdw ( x text, y int4) SERVER pglog OPTIONS ( program 'zcat /tmp/input.csv.gz', format 'csv' );
CREATE FOREIGN TABLE
 
$ SELECT * FROM for_fdw ;
 x │ y
───┼───
 a │ 2
 b │ 1
(2 ROWS)

Of course caution has to be exercised, as user with pg_execute_server_program privilege can, using these commands, run ANY command on the server, from the system account that postgres runs as.

pg_maintain (Pg 16+)

Rather simple role – user with this role has can run, on every table:

• VACUUM
• ANALYZE
• CLUSTER
• REFRESH MATERIALIZED VIEW
• REINDEX
• LOCK TABLE

This makes it possible to run these commands without needing superuser. Which, I know, some people will be very happy about 🙂

pg_monitor (Pg 10+)

This role belong to three other roles: pg_read_all_settings, pg_read_all_stats, and pg_stat_scan_tables, as it is pretty common to need all these privileges to some monitoring user.

For details what can be done, check description of the three roles above.

pg_read_all_data (Pg 14+)

Bypasses all, except for row-level security policies, checks if user can read/access data. For any table.

Every select will succeed, including to system tables:

$ SELECT oid, rolname FROM pg_authid LIMIT 1;
 oid  │      rolname
──────┼───────────────────
 6171 │ pg_database_owner
(1 ROW)

It also bypasses checks on schemas, so works as if the user that has pg_read_all_data had USAGE privilege on each schema.

pg_read_all_settings (Pg 10+)

Allows user to check configuration of server, including things that are normally visible only to superuser.

Without this role, I get an error, or no data:

$ SHOW config_file;
ERROR:  must be superuser OR have privileges OF pg_read_all_settings TO examine "config_file"
 
$ SELECT name, setting FROM pg_settings WHERE name = 'config_file';
 name │ setting
──────┼─────────
(0 ROWS)

But when pg_read_all_settings will be granted to test user, it can:

$ SHOW config_file;
           config_file
──────────────────────────────────
 /home/pgdba/DATA/postgresql.conf
(1 ROW)
 
$ SELECT name, setting FROM pg_settings WHERE name = 'config_file';
    name     │             setting
─────────────┼──────────────────────────────────
 config_file │ /home/pgdba/DATA/postgresql.conf
(1 ROW)

pg_read_all_stats (Pg 10+)

This role gives ability to fully access all pg_stat_* views, without any “censoring".

For example, without this, queries that belong to other users are censored in pg_stat_activity:

$ SELECT pid, usename, query FROM pg_stat_activity WHERE usename IN ('depesz', 'test');
  pid   │ usename │                                         query
────────┼─────────┼───────────────────────────────────────────────────────────────────────────────────────
 546919 │ depesz  │ <insufficient privilege>
 540697 │ test    │ SELECT pid, usename, query FROM pg_stat_activity WHERE usename IN ('depesz', 'test');
(2 ROWS)

But, with this privilege I can see it all:

$ SELECT pid, usename, query FROM pg_stat_activity WHERE usename IN ('depesz', 'test');
  pid   │ usename │                                         query
────────┼─────────┼───────────────────────────────────────────────────────────────────────────────────────
 546919 │ depesz  │ GRANT pg_read_all_stats TO test;
 540697 │ test    │ SELECT pid, usename, query FROM pg_stat_activity WHERE usename IN ('depesz', 'test');
(2 ROWS)

Similarly works pg_stat_statements view, from pg_stat_statements extension.

Additionally, you get access to two more views that aren't pg_stat_*: pg_backend_memory_contexts and pg_shmem_allocations.

pg_read_server_files (Pg 11+)

This is somewhat similar to pg_execute_server_program, but instead of allowing use of PROGRAM options to COPY, and CREATE FOREIGN TABLE, it allows reading files, using syntax like:

$ copy for_copy FROM '/tmp/input.csv' WITH csv;
COPY 2
 
$ CREATE FOREIGN TABLE for_fdw ( x text, y int4) SERVER pglog OPTIONS ( filename '/tmp/input.csv', format 'csv' );
CREATE FOREIGN TABLE

Docs say:

The functions shown in Table 9.99 provide native access to files on
the machine hosting the server. Only files within the database
cluster directory and the log_directory can be accessed, unless the
user is a superuser or is granted the role pg_read_server_files. Use
a relative path for files in the cluster directory, and a path
matching the log_directory configuration setting for log files.

But my tests failed. Calling pg_ls_dir() or pg_read_file() was failing with “ERROR: permission denied for function pg…“, regardless of whether I wanted to access file/dir in PGDATA, in log dir, or outside of them.

Not entirely sure why, so I filled a bug, and hopefully either someone will enlighten me, or it will be fixed.

pg_signal_backend (Pg 9.6+)

This is again very simple, basically gives user ability to run pg_cancel_backend() and pg_terminate_backend() functions, for backend that don't belong to the user.

So, normally, any user can pg_cancel_backend() or pg_terminate_backend() to any backend that is logged as the same user.

But, with this role, we can also stop backends that belong to other users.

Without pg_signal_backend:

$ SELECT pg_cancel_backend(777058);
ERROR:  must be a member OF the ROLE whose query IS being canceled OR member OF pg_signal_backend

but with this role granted:

$ SELECT pg_cancel_backend(788285);
 pg_cancel_backend
───────────────────
 t
(1 ROW)

There is one limit, though. If you're not superuser, even with pg_signal_backend, you can't cancel/terminate backends that belong to superusers:

$ SELECT pg_cancel_backend(777058);
ERROR:  must be a superuser TO cancel superuser query

pg_stat_scan_tables (Pg 10+)

At the moment, it looks that with this role, you will be able to use functions from four standard PostgreSQL extensions:

• pg_freespacemap
• pgrowlocks
• pgstattuple
• pg_visibility

Of course, you need to create the extensions first 🙂

pg_use_reserved_connections (Pg 16+)

This is new thing, in PostgreSQL 16. Aside from superuser_reserved_connections which set aside some number of connections only for superusers, now, we also get reserved_connections which is pool of connections reserved for users in pg_use_reserved_connections role.

pg_write_all_data (Pg 14+)

This role is like pg_read_all_data, but gives user access to INSERT, UPDATE, and DELETE commands.

There is one catch, though. If your query is using data from table, you will also need privileges to read, for example using pg_read_all_data.

Without it, you can:

$ DELETE FROM tst;
DELETE 5

but adding WHERE condition will fail:

$ DELETE FROM tst WHERE i < 2;
ERROR:  permission denied FOR TABLE tst

pg_write_server_files (Pg 11+)

Currently this privilege gives user access to:

• COPY command outputting data to file on server, like, for example:

  $ COPY tst TO '/tmp/some.file';

• use pg_basebackup in such a way that backup will be made not on client, but on server, using –target=server:/… option.
• use functions from adminpack extension, but in here I found the same problem as in pg_read_server_files.

And that's it. Quite a lot of stuff to check, and perhaps start using.