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

On 10/24/21, 10:20 AM, "Jeff Davis" <pgsql(at)j-davis(dot)com> wrote:
> On Sun, 2021-10-24 at 20:19 +0530, Bharath Rupireddy wrote:
>> Are there any other database activities that fall under the
>> "maintenance" category? How about CLUSTER, REINDEX? I didn't check
>> the
>> code for their permissions.
>
> I looked around and didn't see much else to fit into this category.
> CLUSTER and REINDEX are a little too specific for a generic maintenance
> operation -- it's unlikely that you'd want to perform those expensive
> operations just to tidy up. But if you think something else should fit,
> let me know.

My initial reaction was that members of pg_maintenance should be able
to do all of these things (VACUUM, ANALYZE, CLUSTER, REINDEX, and
CHECKPOINT). It's true that some of these are more expensive or
disruptive than others, but how else could we divvy it up? Maybe one
option is to have two separate roles, one for commands that require
lower lock levels (i.e., ANALYZE and VACUUM without TRUNCATE and
FULL), and another for all of the maintenance commands.

Nathan