Re: Special role for subscriptions - Mailing list pgsql-hackers

At Wed, 13 Mar 2019 23:03:26 -0400, Stephen Frost <sfrost@snowman.net> wrote in
<20190314030326.GQ6197@tamriel.snowman.net>
> Greetings,
> 
> * Robert Haas (robertmhaas@gmail.com) wrote:
> > On Mon, Mar 11, 2019 at 10:39 PM Michael Paquier <michael@paquier.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