Waiting for PostgreSQL 16 – Add grantable MAINTAIN privilege and pg_maintain role.

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://postgr.es/m/45224.1670476523@sss.pgh.pa.us

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.