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

Greetings,

* Bossart, Nathan (bossartn@amazon.com) wrote:
> On 11/1/21, 9:51 AM, "Stephen Frost" <sfrost@snowman.net> wrote:
> > 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.
>
> I'd be worried about the behavior of CHECKPOINT changing because
> someone messed with the function.

Folks playing around in the catalog can break lots of things, I don't
really see this as an argument against the idea.

I do wonder if we should put a bit more effort into preventing people
from messing with functions and such in pg_catalog.  Being able to do
something like:

create or replace function xpath ( text, xml ) returns xml[]
as $$ begin return 'xml'; end; $$ language plpgsql;

(or with much worse functions..) strikes me as just a bit too easy to
mistakenly cause problems as a superuser.  Still, that's really an
independent issue from this discussion.  It's not like someone breaking
CHECKPOINT; would actually impact normal checkpoints anyway.

Thanks,

Stephen