On 14th of December 2022, Jeff Davis committed patch:
Add grantable MAINTAIN privilege and pg_maintain role. Allows VACUUM, ANALYZE, REINDEX, REFRESH MATERIALIZED VIEW, CLUSTER, and LOCK TABLE. Effectively reverts 4441fc704d. Instead of creating separate privileges for VACUUM, ANALYZE, and other maintenance commands, group them together under a single MAINTAIN privilege. Author: Nathan Bossart Discussion: https://postgr.es/m/20221212210136.GA449764@nathanxps13 Discussion: https://firstname.lastname@example.org
This is actually pretty interesting.
So far if you wanted/needed to run some maintenance tasks, you cronned them using postgres, or some other superuser, account.
This could lead to problems. We had once (around version 8.2) security bug related to this, and it's always one more account that can be hacked for nefarious reasons.
Not any more.
Let's see what we can do. In my test database I have table buildings:
$ \dt buildings List OF relations Schema │ Name │ TYPE │ Owner ────────┼───────────┼───────┼──────── public │ buildings │ TABLE │ depesz (1 ROW) $ \dp buildings Access privileges Schema │ Name │ TYPE │ Access privileges │ COLUMN privileges │ Policies ────────┼───────────┼───────┼───────────────────┼───────────────────┼────────── public │ buildings │ TABLE │ │ │ (1 ROW)
No privileges granted, all fine.
Now, let's make test maintenance user:
$ CREATE USER maint; CREATE ROLE
Now, if I'd connect using role maint to the db, I can't access these tables:
$ SELECT * FROM buildings ; ERROR: permission denied FOR TABLE buildings $ vacuum buildings ; WARNING: permission denied TO vacuum "buildings", skipping it VACUUM
But if I'd grant pg_maintain role to this new role:
$ GRANT pg_maintain TO maint; GRANT
I can vacuum the table:
=$ psql -U maint -d depesz -X -c 'vacuum buildings' VACUUM
While I still can't read data from the table:
=$ psql -U maint -d depesz -X -c 'select * from buildings' ERROR: permission denied FOR TABLE buildings
Just like commit message says, this privilege will allow to run, by low-level maint account any of:
Sweet, thanks a lot to everyone involved.