Re: Non-superuser subscription owners

> On Nov 18, 2021, at 2:50 AM, Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:
>
>> I gave that a slight amount of thought during the design of this patch, but didn't think we could refuse to revoke superuser on such a basis, and didn't see what we should do with the subscription other than have it continue to be owned by the recently-non-superuser. If you have a better idea, we can discuss it, but to some degree I think that is also orthogonal to the purpose of this patch. The only sense in which this patch depends on that issue is that this patch proposes that non-superuser subscription owners are already an issue, and therefore that this patch isn't creating a new issue, but rather making more sane something that already can happen.
>>
>
> Don't we want to close this gap irrespective of the other part of the
> feature? I mean if we take out the part of your 0003 patch that checks
> whether the current user has permission to perform a particular
> operation on the target table then the gap related to the owner losing
> superuser privileges should be addressed.

I don't think there is a gap. The patch does the right thing, causing the subscription whose owner has had superuser revoked to itself no longer function with superuser privileges. Whether that causes the subscription to fail depends on whether the previously-superuser now non-superuser owner now lacks sufficient privileges on the target relation(s). I think removing that part of the patch would be a regression.

Let's compare two scenarios. In the first, we have a regular user "alice" who owns a subscription which replicates into table "accounting.receipts" for which she has been granted privileges by the table's owner. What would you expect to happen after the table's owner revokes privileges from alice? I would expect that the subscription can no longer function, and periodic attempts to replicate into that table result in permission denied errors in the logs.

In the second, we have a superuser "alice" who owns a subscription that replicates into table "accounting.receipts", and she only has sufficient privileges to modify "accounting.receipts" by virtue of being superuser. I would expect that when she has superuser revoked, the subscription can likewise no longer function.

Now, maybe I'm wrong in both cases, and both should continue to function. But I would find it really strange if the first situation behaved differently from the second.

I think intuitions about how subscriptions behave differ depending on the reason you expect the subscription to be owned by a particular user. If the reason the user owns the subscription is that the user just happens to be the user who created it, but isn't in your mind associated with the subscription, then having the subscription continue to function regardless of what happens to the user, even the user being dropped, is probably consistent with your expectations. In a sense, you think of the user who creates the subscription as having gifted it to the universe rather than continuing to own it. Or perhaps you think of the creator of the subscription as a solicitor/lawyer/agent working on behalf of client, and once that legal transaction is completed, you don't expect the lawyer being disbarred should impact the subscription which exists for the benefit of the client.

If instead you think about the subscription owner as continuing to be closely associated with the subscription (as I do), then you expect changes in the owner's permissions to impact the subscription.

I think the "gifted to the universe"/"lawyer" mental model is not consistent with how the system is already designed to work. You can't drop the subscription's owner without first running REASSIGN OWNED, or ALTER SUBSCRIPTION..OWNER TO, or simply dropping the subscription:

DROP ROLE regress_subscription_user;
ERROR: role "regress_subscription_user" cannot be dropped because some objects depend on it
DETAIL: owner of subscription regress_testsub

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