Re: Non-superuser subscription owners

> On Nov 1, 2021, at 7:18 AM, Andrew Dunstan <andrew(at)dunslane(dot)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 problem was best.

ALTER SUBSCRIPTION..[ENABLE | DISABLE] do not synchronously start or stop subscription workers. The ALTER command updates the catalog's subenabled field, but workers only lazily respond to that. Disabling and enabling the subscription as 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 subscription on and off on the subscriber node, interleaved with inserts and deletes on the publisher node. If the ALTER SUBSCRIPTION commands were synchronous, the test results would be deterministic, with only the inserts performed while the 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_subscription catalog updates generally. That may be a good project to eventually tackle, but I don't see that it is more important to close the race condition in an OWNER TO than for a DISABLE.

Thoughts?