Re: Non-superuser subscription owners

> On Nov 1, 2021, at 7:18 AM, Andrew Dunstan <andrew@dunslane.net> wrote:
>
> w.r.t. this:
>
> +   On the subscriber, the subscription owner's privileges are
> re-checked for
> +   each change record when applied, but beware that a change of
> ownership for a
> +   subscription may not be noticed immediately by the replication workers.
> +   Changes made on the publisher may be applied on the subscriber as
> +   the old owner.  In such cases, the old owner's privileges will be
> the ones
> +   that matter.  Worse still, it may be hard to predict when replication
> +   workers will notice the new ownership.  Subscriptions created
> disabled and
> +   only enabled after ownership has been changed will not be subject to
> this
> +   race condition.
>
>
> maybe we should disable the subscription before making such a change and
> then re-enable it?

Right.  I commented the code that way because there is a clear concern, but I was uncertain which way around the
problemwas best. 

ALTER SUBSCRIPTION..[ENABLE | DISABLE] do not synchronously start or stop subscription workers.  The ALTER command
updatesthe catalog's subenabled field, but workers only lazily respond to that.  Disabling and enabling the
subscriptionas part of the OWNER TO would not reliably accomplish anything. 

The attached patch demonstrates the race condition.  It sets up a publisher and subscriber, and toggles the
subscriptionon and off on the subscriber node, interleaved with inserts and deletes on the publisher node.  If the
ALTERSUBSCRIPTION commands were synchronous, the test results would be deterministic, with only the inserts performed
whilethe subscription is enabled being replicated, but because the ALTER commands are asynchronous, the results are
nondeterministic.

It is unclear that I can make ALTER SUBSCRIPTION..OWNER TO synchronous without redesigning the way workers respond to
pg_subscriptioncatalog updates generally.  That may be a good project to eventually tackle, but I don't see that it is
moreimportant to close the race condition in an OWNER TO than for a DISABLE. 

Thoughts?


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