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

On 10/25/21, 6:48 PM, "Jeff Davis" <pgsql(at)j-davis(dot)com> wrote:
> On Tue, 2021-10-26 at 00:07 +0000, Bossart, Nathan wrote:
>> It feels a bit excessive to introduce a new predefined role just for
>> this. Perhaps this could be accomplished with a new function that
>> could be granted.
>
> It would be nice if the syntax could be used, since it's pretty
> widespread. I guess it does feel excessive to have its own predefined
> role, but at the same time it's hard to group a command like CHECKPOINT
> into a category. Maybe if we named it something like pg_performance or
> something we could make a larger group?

I do think a larger group is desirable, but as is evidenced by this
thread, it may be some time until we can figure out exactly how that
would look. I feel like there's general support for being able to
allow non-superusers to CHECKPOINT and do other
maintenance/performance tasks, though.

I think my main concern with pg_checkpointer is that it could set a
precedent for new predefined roles, and we'd end up with dozens or
more. But as long as new predefined roles aren't terribly expensive,
maybe that's not all that bad. The advantage of having a
pg_checkpointer role is that it enables users to grant just CHECKPOINT
and nothing else. If we wanted a larger "pg_performance" group in the
future, we could introduce that role and make it a member of
pg_checkpointer and others (similar to how pg_monitor works).

Nathan