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?
answer
- checked at access time, not per object
- covers tables that don't exist yet
- every database in the cluster
- MAINTAIN privilege since 17
- LOCK TABLE in any mode
basics
~20 spg_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 sPredefined 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-- 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
Know that predefined pg_ roles bundle capabilities, and that pg_read_all_data means read access to every table without per-table grants.
Explain why access-time checks cover future tables and every database, what pg_maintain permits, and that membership needs INHERIT to work implicitly.
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.
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.