PostgreSQL predefined roles: least privilege without superuser

PostgreSQL ships predefined roles so you can grant narrow powers instead of making someone superuser. Use pg_read_all_data for read-only reporting and pg_monitor for monitoring. Treat pg_read_server_files, pg_write_server_files and pg_execute_server_program as superuser equivalent, because the manual says they could be used to gain superuser level access.

Data access roles

• pg_read_all_data reads all tables, views and sequences, as if holding SELECT on them and USAGE on all schemas.
• pg_write_all_data writes all of them, as if holding INSERT, UPDATE and DELETE.

Neither bypasses row level security, which is a useful property when you also use policies. See row level security basics.

Monitoring roles

• pg_monitor reads and executes monitoring views and functions. It is a member of pg_read_all_settings, pg_read_all_stats and pg_stat_scan_tables.
• pg_read_all_settings reads all configuration variables, including those normally visible only to superusers.
• pg_read_all_stats reads the pg_stat_* views.

A monitoring agent needs pg_monitor, not a superuser login. That one change removes a surprising amount of risk.

Maintenance and signaling

• pg_maintain runs VACUUM, ANALYZE, CLUSTER, REINDEX and similar on all relations.
• pg_signal_backend cancels queries or terminates sessions of other backends, but not those owned by a superuser.
• pg_checkpoint runs CHECKPOINT.

The three to treat as superuser

pg_read_server_files, pg_write_server_files and pg_execute_server_program let a role read or write files, or run programs, as the database's operating system user, using COPY and related functions. The manual warns they bypass database level permission checks for direct file access and could be used to gain superuser level access. Do not hand them to application accounts.

How to apply this

1. List your non-human logins: monitoring, backups, reporting, migrations.
2. For each, write down what it really does.
3. Grant the matching predefined role, or specific table privileges, instead of superuser.
4. Keep application roles away from the server file and program roles entirely.
5. Re-review after upgrades. The manual advises watching release notes because role permissions may change.

Grants on future tables need their own handling, covered in ALTER DEFAULT PRIVILEGES.

Quick check

Sources

• PostgreSQL documentation: Predefined Roles

Last reviewed: 19 October 2026.