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

On Sun, 2021-10-24 at 20:19 +0530, Bharath Rupireddy wrote:
> At this point, the idea of having a new role for maintenance work
> looks good. With this patch and Mark Dilger's patch introducing a
> bunch of new predefined roles, one concern is that we might reach to
> a
> state where we will have patches being proposed for new predefined
> roles for every database activity and the superuser eventually will
> have nothing to do in the database, it just becomes dummy?

The idea is that, in different environments, the notion of an
"administrator" should have different capabilities and different risks.
By making the privileges more fine-grained, we enable those different
use cases.

I don't see it as necessarily a problem if superuser doesn't have much
left to do.

> I'm not sure if Mark Dilger's patch on new predefined roles has a
> suitable/same role that we can use here.

I didn't see one. I think one of the most common reasons to do manual
checkpoints and vacuums is for performance testing, so another
potential name might be "pg_performance". But "pg_maintenance" seemed a
slightly better fit.

> 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.

Thank you,
Jeff Davis