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
reador 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 ...
REFRESHCOLLATION VERSION.  These latter operations may be less critical to database maintenance than is VACUUM, but
arguablyANALYZE 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
shoulddraw the line around just VACUUM and ANALYZE?  I am not arguing for including these other commands, but don't
wantto 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