skip to content

In PostgreSQL 18, what do the predefined roles pg_read_all_data and pg_maintain grant, and what is risky about giving them to analysts?

level: middleimportance: should knowfreq 37%

answer

  1. checked at access time, not per object
  2. covers tables that don't exist yet
  3. every database in the cluster
  4. MAINTAIN privilege since 17
  5. LOCK TABLE in any mode

basics

~20 s

pg_read_all_data acts as SELECT on every table, view and sequence plus USAGE on every schema; pg_maintain (17+) allows VACUUM, ANALYZE, CLUSTER, REINDEX, REFRESH MATERIALIZED VIEW and LOCK TABLE on every relation. Both are cluster-wide and cannot be narrowed per table.

solid answer

~50 s

Predefined roles are checked when a permission is decided, not written into object ACLs. `pg_read_all_data` behaves as if the member had `SELECT` on every table, view and sequence and `USAGE` on every schema, so it covers tables created tomorrow, every schema and every database the member can connect to; it does not bypass row-level security. `pg_maintain`, added in 17 with the per-table `MAINTAIN` privilege, allows `VACUUM`, `ANALYZE`, `CLUSTER`, `REFRESH MATERIALIZED VIEW`, `REINDEX` and `LOCK TABLE` on all relations. For analysts of an HR product the risk is breadth: salary and medical-leave tables are readable with no way to carve one out, since revoking a table grant they never held changes nothing. `pg_maintain` adds the power to take an `ACCESS EXCLUSIVE` lock or rewrite a table. Narrower tools are schema grants with default privileges and `GRANT MAINTAIN ON TABLE`.

code

sql · 9 lines
sql
-- broad: every table, view and sequence in every database of the cluster
GRANT pg_read_all_data TO hr_analysts;

-- narrow alternatives
GRANT USAGE ON SCHEMA hr_reporting TO hr_analysts;
GRANT SELECT ON ALL TABLES IN SCHEMA hr_reporting TO hr_analysts;
GRANT MAINTAIN ON TABLE hr.leave_requests TO hr_ops;

SELECT pg_has_role('ana_kim', 'pg_read_all_data', 'USAGE');

go deeper

for a junior

Know that predefined pg_ roles bundle capabilities, and that pg_read_all_data means read access to every table without per-table grants.

for a middle

Explain why access-time checks cover future tables and every database, what pg_maintain permits, and that membership needs INHERIT to work implicitly.

for a senior

Judge when the convenience is worth the breadth, and replace it with schema grants, default privileges, views and per-table MAINTAIN where data is sensitive.

for a principal

Set the access-review policy for predefined roles across clusters, since they never appear in table ACLs and can quietly outgrow their original purpose.

## Predefined roles in brief PostgreSQL ships roles whose names begin with `pg_` that stand for capabilities an ordinary object grant cannot express. You grant them like any group role, for example `GRANT pg_read_all_data TO hr_analysts`, and the server checks membership in them while deciding a permission. Two consequences follow: - like every role they are **cluster-wide**, so the capability applies in every database the member can connect to; - the check looks at privileges the member **has through inheritance**, so the membership must carry `INHERIT TRUE` (or the session must `SET ROLE` to the predefined role). ## pg_read_all_data and pg_write_all_data `pg_read_all_data` behaves as if the member had `SELECT` on every table, view and sequence and `USAGE` on every schema; `pg_write_all_data` is the same with `INSERT`, `UPDATE` and `DELETE`. Because the check happens at access time rather than through each object's ACL: - tables created tomorrow are covered, with no default privileges needed; - every schema is covered, including ones the analysts were never meant to see; - `REVOKE SELECT ON hr.payroll_runs FROM ana_kim` changes nothing, because she never held a table grant to lose; - row-level security still applies; the manual suggests `BYPASSRLS` where that is really wanted, which is a separate decision. ## pg_maintain and the MAINTAIN privilege | Grant | Scope | Allows | |---|---|---| | `GRANT MAINTAIN ON TABLE t` | one relation | `VACUUM`, `ANALYZE`, `CLUSTER`, `REFRESH MATERIALIZED VIEW`, `REINDEX`, `LOCK TABLE` | | `pg_maintain` | every relation | the same, as if holding `MAINTAIN` everywhere | | `pg_read_all_data` | every table, view, sequence, schema | `SELECT` and schema `USAGE` | | `pg_write_all_data` | every table, view, sequence, schema | `INSERT`, `UPDATE`, `DELETE` and schema `USAGE` | Before 17 there was neither the privilege nor the role, so letting a job vacuum or reindex a table meant making it the owner, using the database owner, or using a superuser. In 17 and 18 a maintenance job can get exactly the right it needs. Two neighbours are easy to confuse with these. `pg_read_all_settings` and `pg_read_all_stats` expose configuration values and statistics views, not table contents. `pg_write_all_data` is the write counterpart and deserves even more caution: a member can change rows in every table of every database, so it has no place in an analysts' group at all. ## The risks of giving them to an analysts' group 1. **Sensitive data in one grant.** In an HR SaaS, `pg_read_all_data` exposes salaries, bank details and medical-leave notes in every schema, including tables nobody has created yet, and offers no per-table exception. 2. **Cross-database reach.** A role is a cluster object, so the grant covers every database on the cluster, not only the analytics one the request was about. 3. **Locks and rewrites from pg_maintain.** `MAINTAIN` permits `LOCK TABLE` in any mode, including `ACCESS EXCLUSIVE`, which blocks even plain reads until the transaction ends. It also permits `VACUUM FULL`, `CLUSTER` and `REFRESH MATERIALIZED VIEW`, which take heavy locks while they work. 4. **Surprises with row-level security.** Analysts with `pg_read_all_data` may still see filtered or empty results where policies exist, which leads to requests for `BYPASSRLS` that widen access further. ## Narrower alternatives - Grant `USAGE` on the reporting schemas and `SELECT` on chosen tables or views, plus `ALTER DEFAULT PRIVILEGES FOR ROLE hr_owner` for future tables; expose sensitive tables only through views that omit the sensitive columns. - Give a maintenance job `GRANT MAINTAIN ON TABLE hr.leave_requests TO hr_ops` instead of `pg_maintain`. - For dashboards that read `pg_stat_*` views, `pg_monitor` (which includes `pg_read_all_stats` and `pg_read_all_settings`) is the purpose-built role. - To let an operator cancel runaway queries, `pg_signal_backend` does it without superuser, though it cannot signal superuser backends. ## Checking who holds them psql's `\drgS` lists memberships including system roles, and `pg_has_role('ana_kim', 'pg_read_all_data', 'USAGE')` answers whether the role's privileges are usable without `SET ROLE`. Review these memberships as part of access reviews, because nothing in the table ACLs shown by `\dp` reveals them.

  • An analyst was granted pg_read_all_data WITH INHERIT FALSE in PostgreSQL 18. Can they read hr.employees right after connecting?
    No. The server checks whether the session has the predefined role's privileges through inheritance, and `INHERIT FALSE` cuts that. With the default `SET TRUE` they could `SET ROLE pg_read_all_data` and then read, so the option changes how access is used, not whether it exists.
  • Why might pg_maintain be safer than ownership for a nightly maintenance job in PostgreSQL 18, and what does it still allow?
    An owner can `ALTER` or `DROP` the table and grant privileges on it; `pg_maintain` cannot. It still allows `LOCK TABLE` in any mode, `VACUUM FULL`, `CLUSTER` and `REINDEX` on every relation, so per-table `MAINTAIN` grants are tighter when the job touches only a few tables.

  • pg_read_all_data only covers tables that existed when it was granted.
  • Revoking SELECT on one table hides it from a pg_read_all_data member.
  • pg_read_all_data bypasses row-level security policies.
  • pg_maintain only allows VACUUM and ANALYZE, so it cannot block anyone.
  • Predefined roles apply only in the database where they were granted.

Assumes PostgreSQL 18; pg_maintain and the MAINTAIN privilege arrived in 17, so on 16 and older table maintenance needed ownership, database ownership or superuser.