Re: Non-superuser subscription owners - Mailing list pgsql-hackers

> On Nov 18, 2021, at 2:50 AM, Amit Kapila <amit.kapila16@gmail.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
superuseron such a basis, and didn't see what we should do with the subscription other than have it continue to be
ownedby the recently-non-superuser.  If you have a better idea, we can discuss it, but to some degree I think that is
alsoorthogonal to the purpose of this patch.  The only sense in which this patch depends on that issue is that this
patchproposes that non-superuser subscription owners are already an issue, and therefore that this patch isn't creating
anew 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
revokedto itself no longer function with superuser privileges.  Whether that causes the subscription to fail depends on
whetherthe previously-superuser now non-superuser owner now lacks sufficient privileges on the target relation(s).  I
thinkremoving 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
intotable "accounting.receipts" for which she has been granted privileges by the table's owner.  What would you expect
tohappen 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",
andshe only has sufficient privileges to modify "accounting.receipts" by virtue of being superuser.  I would expect
thatwhen 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
firstsituation behaved differently from the second. 

I think intuitions about how subscriptions behave differ depending on the reason you expect the subscription to be
ownedby a particular user.  If the reason the user owns the subscription is that the user just happens to be the user
whocreated it, but isn't in your mind associated with the subscription, then having the subscription continue to
functionregardless 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
thancontinuing to own it.  Or perhaps you think of the creator of the subscription as a solicitor/lawyer/agent working
onbehalf of client, and once that legal transaction is completed, you don't expect the lawyer being disbarred should
impactthe 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
simplydropping 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