In a previous post we’ve seen that vacuum and analyze operations can be granted to users which are not superuser starting with PostgreSQL 16. Work is still in progress to further reduce maintenance tasks which require superuser privileges. Today another patch was committed which brings even more delegation for maintenance operations.

One more, a simple setup to demonstrate what has been done:

postgres=# select version();
                                               version                                                
------------------------------------------------------------------------------------------------------
 PostgreSQL 16devel on x86_64-pc-linux-gnu, compiled by gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit
(1 row)

postgres=# create user u with password 'u' login;
CREATE ROLE
postgres=# create user v with password 'v' login;
CREATE ROLE
postgres=# create schema su authorization u;
CREATE SCHEMA
postgres=# \c postgres u
You are now connected to database "postgres" as user "u".
postgres=> create table su.t ( a int, b text );
postgres=> create unique index i on su.t(a);
CREATE INDEX
postgres=> create materialized view su.mv as select * from su.t;
SELECT 0

Two users, one having a schema containing a table, an unique index, and a materialized view. We’ve already seen that we can grant vacuum and analyze of objects to another user:

postgres=# grant analyze on su.t to v;
ERROR:  syntax error at or near "analyze"

This does not work anymore. Instead of granting separate privileges for maintenance operations these are now grouped together under a single privilege:

postgres=# grant maintain on su.t to v;
GRANT
postgres=# grant maintain on su.mv to v;
GRANT
postgres=# grant usage on schema su to v;
GRANT

The maintain privilege consist of these privileges:

  • VACUUM
  • ANALYZE
  • REINDEX
  • REFRESH MATERIALIZED VIEW
  • CLUSTER
  • LOCK TABLE

Having that privilege all these operations are delegated:

postgres=> analyze su.t;
ANALYZE
postgres=> refresh materialized view su.mv;
REFRESH MATERIALIZED VIEW
postgres=> reindex index su.i;
REINDEX

A new role has been added as well, which grants all these operations by default:

postgres=# \c postgres u
You are now connected to database "postgres" as user "u".
postgres=> create table su.x ( a int );
CREATE TABLE
postgres=> \c postgres postgres
You are now connected to database "postgres" as user "postgres".
postgres=# grant pg_maintain to v;
GRANT ROLE
postgres=# \c postgres v
You are now connected to database "postgres" as user "v".
postgres=> analyze su.x;
ANALYZE
postgres=>

This is nice, as it further reduces the tasks that require superuser privileges.

Post Views: 2,184
Thumbnail [60x60]
by
Daniel Westermann