> On Nov 18, 2021, at 2:50 AM, Amit Kapila <amit.kapil...@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 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
