Re: Non-superuser subscription owners

> On Jan 27, 2023, at 1:35 PM, Robert Haas <robertmhaas@gmail.com> wrote:
>
>> I started out asking what benefits it provides to own a subscription one
>> cannot modify. But I think it is a good capability to have, to restrict the
>> set of relations that replication could target.  Although perhaps it'd be
>> better to set the "replay user" as a separate property on the subscription?
>
> That's been proposed previously, but for reasons I don't quite
> remember it seems not to have happened. I don't think it achieved
> consensus.
>
>> Does owning a subscription one isn't allowed to modify useful outside of that?
>
> Uh, possibly that's a question for Mark or Jeff. I don't know. I can't
> see what they would be, but I just work here.

If the owner cannot modify the subscription, then the owner degenerates into a mere "run-as" user.  Note that this
isn'thow things work now, and even if we disallowed owners from modifying the connection string, there would still be
otherattributes the owner could modify, such as the set of publications subscribed. 


More generally, my thinking on this thread is that there needs to be two nosuperuser roles:  A higher privileged role
whichcan create a subscription, and a lower privileged role serving the "run-as" function.  Those shouldn't be the
same,because the "run-as" concept doesn't logically need to have subscription creation power, and likely *shouldn't*
havethat power.  Depending on which sorts of attributes a subscription object has, such as the connection string, the
answerdiffers for whether the owner/"run-as" user should get to change those attributes.  One advantage of Jeff's idea
ofusing a server object rather than a string is that it becomes more plausibly safe to allow the subscription owner to
makechanges to that attribute of the subscription. 



—
Mark Dilger
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company