Postgres Pro
Downloads
Home   >   mailing lists

Re: Predefined role pg_maintenance for VACUUM, ANALYZE, CHECKPOINT. - Mailing list pgsql-hackers

From Isaac Morland
Subject Re: Predefined role pg_maintenance for VACUUM, ANALYZE, CHECKPOINT.
Date
Msg-id CAMsGm5eQTZii9d_yc3OS+cHUquNDAwvSWV-ukMgWiZkmWYW5rw@mail.gmail.com
Whole thread Raw
In response to Re: Predefined role pg_maintenance for VACUUM, ANALYZE, CHECKPOINT.  (Vik Fearing <vik@postgresfriends.org>)
List pgsql-hackers
Tree view
On Tue, 2 Nov 2021 at 18:14, Vik Fearing <vik@postgresfriends.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:


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;

pgsql-hackers by date:

Previous
From: Tom Lane
Date:
Subject: Re: [PATCH] postgres_fdw: suppress explicit casts in text:text comparisons (was: column option to override foreign types)
Next
From: Thomas Munro
Date:
Subject: Re: [PATCH] Native spinlock support on RISC-V