Re: Predefined role pg_maintenance for VACUUM, ANALYZE, CHECKPOINT.

Greetings,

* Andres Freund (andres(at)anarazel(dot)de) wrote:
> On 2021-11-05 08:54:37 -0400, Robert Haas wrote:
> > On Thu, Nov 4, 2021 at 6:46 PM Andres Freund <andres(at)anarazel(dot)de> wrote:
> > > What about extending GRANT to allow to grant rights on commands? Yes, it'd be
> > > a bit of work to make that work in the catalogs, but it doesn't seem too hard
> > > to tackle.
> >
> > I think that there aren't too many commands where the question is just
> > whether you can execute the command or not. CHECKPOINT is one that
> > does work that way, but if it's VACUUM or ANALYZE the question will be
> > whether you can run it on a particular table; if it's ALTER SYSTEM it
> > will be whether you can run it for that GUC; and so on. CHECKPOINT is
> > one of the few commands that has no target.
>
> I don't know if that's really such a big deal. It's useful to be able to grant
> the right to do a system wide ANALYZE etc to a role that can't otherwise do
> anything with the table. Even for ALTER SYSTEM etc it seems like it'd be
> helpful, because it allows to constrain an admin tool to "legitimate" admin
> paths, without allowing, say, UPDATE pg_proc.

Note that it's already possible to have a non-superuser who can run
VACUUM and ANALYZE on all non-shared tables in a database but who
otherwise isn't able to mess with the tables owned by other users- that
is something the database owner can do.

Perhaps it's useful to break that out into a separately grantable
permission but the argument should really by why you'd want to GRANT
someone the ability to VACUUM/ANALYZE an entire database while *not*
having them be the database owner. That's a much more narrow use-case
vs. having them not be a superuser or be able to do things like UPDATE
pg_proc.

Thanks,

Stephen