Re: Non-superuser subscription owners

> On Jan 27, 2023, at 1:35 PM, Robert Haas <robertmhaas(at)gmail(dot)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't how things work now, and even if we disallowed owners from modifying the connection string, there would still be other attributes 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 which can 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* have that power. Depending on which sorts of attributes a subscription object has, such as the connection string, the answer differs for whether the owner/"run-as" user should get to change those attributes. One advantage of Jeff's idea of using a server object rather than a string is that it becomes more plausibly safe to allow the subscription owner to make changes to that attribute of the subscription.

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