Re: Special role for subscriptions

Greetings,

* Robert Haas (robertmhaas(at)gmail(dot)com) wrote:
> On Mon, Mar 11, 2019 at 10:39 PM Michael Paquier <michael(at)paquier(dot)xyz> wrote:
> > On Mon, Mar 11, 2019 at 06:32:10PM -0700, Jeff Davis wrote:
> > > * Is the original idea of a special role still viable?
> >
> > In my opinion, that part may be valuable. The latest patches proposed
> > change the way tables are filtered and listed on the subscription
> > side, lowering the permission to spawn a new thread and to connect to a
> > publication server by just being a database owner instead of being a
> > superuser, and that's quite a gap.
>
> I agree. I think the original idea was better than what Stephen
> suggested, and for basically the reasons you mention.
>
> However, I'm not sure that you are right when you say "just being a
> database owner." I think that what's being proposed is that anybody
> who is a *table* owner could make PostgreSQL run off and try to sync
> that table from a remote server in perpetuity. That seems like WAY
> too much access to give an unprivileged user. I don't think we want
> unprivileged users to be able to launch more or less permanent
> background processes, nor do we want them to be able to initiate
> outbound network traffic from the server. Whether we want database
> owners to be able to do those things is more debatable, but even that
> would represent a significant expansion of their current rights, IIUC.
>
> Just letting the superuser decide who gets to create subscriptions
> seems good enough from here.

It seems I wasn't very clear earlier in the thread- I *definitely* think
we need to have a special role which a superuser can grant to certain
roles (possibly with the permission to grant further) to allow them to
create subscriptions, and that's definitely distinct from "database
owner" and shouldn't be combined with that.

I view that as the first step towards building a more granular privilege
system for subscription creation, and that was the second half of what I
was trying to say before- I do think there's value in having something
more granular than just "this role can create ANY subscription". As an
administrator, I might be fine with subscriptions to system X, but not
to system Y, for example. As long as we don't block off the ability to
build something finer grained in the future, then having the system role
to allow a given role to do create subscription seems fine to me.

Thanks!

Stephen