Re: predefined role(s) for VACUUM and ANALYZE

> On Jul 22, 2022, at 1:37 PM, Nathan Bossart <nathandbossart@gmail.com> wrote:
> 
> The primary motivation for this is to continue chipping away at things that
> require special privileges or even superuser.  VACUUM and ANALYZE typically
> require table ownership, database ownership, or superuser.  And only
> superusers can VACUUM/ANALYZE shared catalogs.  A predefined role for these
> operations would allow delegating such tasks (e.g., a nightly VACUUM
> scheduled with pg_cron) to a role with fewer privileges.
> 
> The attached patch adds a pg_vacuum_analyze role that allows VACUUM and
> ANALYZE commands on all relations.

Granting membership in a role that can VACUUM and ANALYZE any relation seems to grant a subset of a more general category, the ability to perform modifying administrative operations on a relation without necessarily being able to read or modify the logical contents of that relation.  That more general category would seem to also include CLUSTER, REINDEX, REFRESH MATERIALIZED VIEW and more broadly ALTER SUBSCRIPTION ... REFRESH PUBLICATION and ALTER DATABASE ... REFRESH COLLATION VERSION.  These latter operations may be less critical to database maintenance than is VACUUM, but arguably ANALYZE isn't as critical as is VACUUM, either.

Assuming for the sake of argument that we should create a role something like you propose, can you explain why we should draw the line around just VACUUM and ANALYZE?  I am not arguing for including these other commands, but don't want to regret having drawn the line in the wrong place when later we decide to add more roles like the one you are proposing.

—
Mark Dilger
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company