> On Jul 22, 2022, at 1:37 PM, Nathan Bossart <nathandboss...@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
