Re: predefined role(s) for VACUUM and ANALYZE

On Sat, Jul 23, 2022 at 2:07 AM Nathan Bossart <nathandbossart@gmail.com> wrote:
>
> Hi hackers,
>
> The previous attempt to add a predefined role for VACUUM and ANALYZE [0]
> resulted in the new pg_checkpoint role in v15.  I'd like to try again to
> add a new role (or multiple new roles) for VACUUM and ANALYZE.
>
> 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.

Thanks. I'm personally happy with more granular levels of control (as
we don't have to give full superuser access to just run a few commands
or maintenance operations) for various postgres commands. The only
concern is that we might eventually end up with many predefined roles
(perhaps one predefined role per command), spreading all around the
code base and it might be difficult for the users to digest all of the
roles in. It will be great if we can have some sort of rules or
methods to define a separate role for a command.

> The attached patch adds a pg_vacuum_analyze role that allows VACUUM and
> ANALYZE commands on all relations.  I started by trying to introduce
> separate pg_vacuum and pg_analyze roles, but that quickly became
> complicated because the VACUUM and ANALYZE code is intertwined.  To
> initiate the discussion, here's the simplest thing I could think of.

pg_vacuum_analyze, immediately, makes me think if we need to have a
predefined role for CLUSTER command and maybe for other commands as
well such as EXECUTE, CALL, ALTER SYSTEM SET, LOAD, COPY and so on.

> An alternate approach might be to allow using GRANT to manage these
> privileges, as suggested in the previous thread [1].
>
> Thoughts?
>
> [0] https://postgr.es/m/67a1d667e8ec228b5e07f232184c80348c5d93f4.camel%40j-davis.com
> [1] https://postgr.es/m/20211104224636.5qg6cfyjkw52rh4d@alap3.anarazel.de

I think GRANT approach [1] is worth considering or at least discussing
its pros and cons might give us a better idea as to why we need
separate predefined roles.

Regards,
Bharath Rupireddy.