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

Greetings,

* Alvaro Herrera (alvherre(at)alvh(dot)no-ip(dot)org) wrote:
> On 2021-Nov-04, Jeff Davis wrote:
> > But I don't see it generalizing to a lot of commands, either. I looked
> > at the list, and it's taking some creativity to think of more than a
> > couple other commands where it makes sense. Maybe LISTEN/NOTIFY? But
> > even then, there are three related commands: LISTEN, UNLISTEN, and
> > NOTIFY. Are those one privilege representing them all, two
> > (LISTEN/UNLISTEN, and NOTIFY), or three separate privileges?
>
> What about things like CREATE SUBSCRIPTION/PUBLICATION? Sounds like it
> would be useful to allow non-superusers do those, too.

Agreed. Having these be limited to superusers is unfortunate, though at
the time probably made sense as otherwise it would have made it that
much more difficult to get logical replication in. Now is a great time
to try and improve on that situation though. This is a bit tricky
though since creating a subscription means that you'll be able to cause
some code to be executed with higher privileges today, as I recall, and
we'd need to make sure to address that. If we can make sure that a
subscription isn't able to be used to execute code as effectively a
superuser then I would think the other permission needed to create one,
for tables which you own, would be just a "network access" kind of
capability. In other words, I'm not 100% sure we need to have 'create
subscription' require different privileges from 'create a foreign
server'. Then again, having additional predefined rules isn't a huge
cost and perhaps it would be better to avoid the confusion that
introducing a separate 'capabilities' kind of system would involve where
those capabilities cross multiple commands.

> That said, if the list is short, then additional predefined roles seem
> preferrable to having a ton of infrastructure code that might be much
> more clutter than what seems a short list of additional predefined roles.

None of this strikes me as a 'ton of infrastructure code' and so I'm not
quite sure I'm following the argument being made here.

Thanks,

Stephen