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

Greetings,

* Andres Freund (andres@anarazel.de) wrote:
> On 2021-11-05 08:42:58 -0400, Robert Haas wrote:
> > On Thu, Nov 4, 2021 at 7:38 PM Jeff Davis <pgsql@j-davis.com> wrote:
> > > It seems like this specific approach has been mostly shot down already.
> > >  But out of curiosity, are you intending to run CHECKPOINT during
> > > bootstrap or something?
> >
> > Imagine a system with corruption in pg_proc. Right now, that won't
> > prevent you from successfully executing a checkpoint. With this
> > approach, it might.
>
> Exactly. It wouldn't matter if checkpoints weren't something needed to
> potentially bring the system back into a sane state, but ...

This really isn't that hard to address- do a superuser check, if it
passes then just call the checkpoint function like CHECKPOINT; does
today.  Otherwise, check the perms on the function or just call the
function in a manner which would check privileges, or maybe have another
predefined role, though I continue to feel like the function based
approach is better.

If we're actually worried about catalog corruption (and, frankly, I've
got some serious doubts that jumping in and running CHECKPOINT; by hand
is a great idea if there's such active corruption) then we must use such
an approach no matter how we allow non-superusers to run the command
because any approach to that necessarily involves some amount of catalog
access.

Any concern leveraged against pg_proc applies equally to pg_auth_members
after all, so having it be something role-based vs. function privilege
is really just moving deck chairs around on the titanic at that point.

Thanks,

Stephen