Re: Special role for subscriptions

At Wed, 13 Mar 2019 23:03:26 -0400, Stephen Frost <sfrost(at)snowman(dot)net> wrote in <20190314030326(dot)GQ6197(at)tamriel(dot)snowman(dot)net>
> 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.

The subscription privileges is completely reasonable, but I've
heard of users who want to restrict tables on which a role can
make subscription. Subscription causes INSERT/UPDATE/DELETE to a
table, is it too-much to check the privileges addition to the
subscription privileges?

regards.

--
Kyotaro Horiguchi
NTT Open Source Software Center