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

Greetings,

* Bossart, Nathan (bossartn(at)amazon(dot)com) wrote:
> On 10/30/21, 11:14 AM, "Jeff Davis" <pgsql(at)j-davis(dot)com> wrote:
> > On Sat, 2021-10-30 at 13:24 +0530, Bharath Rupireddy wrote:
> >> IMHO, moving away from SQL command "CHECKPOINT" to function
> >> "pg_checkpoint()" isn't nice as the SQL command has been there for a
> >> long time and all the applications or services that were/are being
> >> built around the postgres ecosystem would have to adapt someday to
> >> the
> >> new function (if at all we deprecate the command and onboard the
> >> function). This isn't good at all given the CHECKPOINT is one of the
> >> mostly used commands in the apps or services layer. Moreover, if we
> >> go
> >> with the function pg_checkpoint(), we might see patches coming in for
> >> pg_vacuum(), pg_reindex(), pg_cluster() and so on.
> >
> > I tend to agree with all of this. The CHECKPOINT command is already
> > there and people already use it. If we are already chipping away at the
> > need for superuser elsewhere, we should offer a way to use CHECKPOINT
> > without being superuser.
>
> I think Bharath brings up some good points. The simple fact is that
> CHECKPOINT has been around for a while, and creating functions for
> maintenance tasks would add just as much or more clutter than adding a
> predefined role for each one. I do wonder what we would've done if
> CHECKPOINT didn't already exist. Based on the goal of this thread, I
> get the feeling that we might've seriously considered introducing it
> as a function so that you can just GRANT EXECUTE as needed.

I don't really buy off on the "because it's been around a long time" as
a reason to invent a predefined role for an individual command that
doesn't take any options and could certainly just be a function.
Applications developed to run as a superuser aren't likely to magically
start working because they were GRANT'd this one additional predefined
role either but likely would need other changes anyway.

All that said, I wonder if we can have our cake and eat it too. I
haven't looked into this at all yet and perhaps it's foolish on its
face, but, could we make CHECKPOINT; basically turn around and just run
select pg_checkpoint(); with the regular privilege checking happening?
Then we'd keep the existing syntax working, but if the user is allowed
to run the command would depend on if they've been GRANT'd EXECUTE
rights on the function or not.

Thanks,

Stephen