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

On Tue, 2 Nov 2021 at 18:14, Vik Fearing <vik(at)postgresfriends(dot)org> wrote:

> On 11/2/21 4:06 PM, Robert Haas wrote:
> > There's bound to be somebody who wants to grant some of
> > these permissions and not others, or who wants to grant the ability to
> > run those commands on some tables but not others.
> Is there anything stopping us from adding syntax like this?
>
> GRANT VACUUM, ANALYZE ON TABLE foo TO bar;
>

There is a limited number of bits available in the way privileges are
stored. I investigated this in 2018 in connection with an idea I had to
allow granting the ability to refresh a materialized view; after
consideration and discussion I came to the idea of having a "MAINTAIN"
permission which would allow refreshing materialized views and would also
cover clustering, reindexing, vacuuming, and analyzing on objects to which
those actions are applicable.

This message from me summarizes the history of usage of the available
privilege bits:

https://www.postgresql.org/message-id/CAMsGm5c4DycKBYZCypfV02s-SC8GwF%2BKeTt%3D%3DvbWrFn%2Bdz%3DKeg%40mail.gmail.com

If you dig into the replies you will find the revised proposal.

That doesn't fix the CHECKPOINT issue, but surely vacuum and analyze can
> be done that way. I would much prefer that over new predefined roles.
>
> This would be nice, but there is nothing to hang our hat on:
>
> GRANT CHECKPOINT TO username;
>