From: Jeff Davis <pgsql@j-davis.com>
To: Noah Misch <noah@leadboat.com>
Cc: pgsql-hackers@postgresql.org, Amit Kapila <amit.kapila16@gmail.com>
Subject: Re: CREATE SUBSCRIPTION ... SERVER vs. pg_dump, etc.
Date: Tue, 28 Jul 2026 09:10:39 -0700
Message-ID: <e96efe16fb47fcca4ae0cd0157fb6bc662470712.camel@j-davis.com> (raw)
In-Reply-To: <35e50b0f80c850e5b9eb1dedcc0844852a2d2de0.camel@j-davis.com>
References: <20260710195902.4f.noahmisch@microsoft.com>
<35e50b0f80c850e5b9eb1dedcc0844852a2d2de0.camel@j-davis.com>
On Sun, 2026-07-19 at 15:32 -0700, Jeff Davis wrote:
> Generating and validating the connection requires the subscription
> owner to be set correctly, the foreign server ACLs to be set, and the
> user mapping to exist. The checks at DDL time are were a convenient
> way
> to catch errors, but end up being too strict because those things can
> change before the connection is actually needed. In particular,
> pg_dump
> does the DDL in parts (first creating the subscription, then changing
> the owner), and we need the first part to succeed.
There are two other threads discussing closely-related problems:
https://www.postgresql.org/message-id/CAHGQGwFGa6+wWVgUmZPFwN=fBY59mYPkMK3=TxT=Pv5C1mNNRQ@mail.gmail...https://www.postgresql.org/message-id/OS9PR01MB12149C3ED34272966B25DB173F5C12@OS9PR01MB12149.jpnprd0...
I'd like to step back and discuss where the complexity comes from:
* During restore, we simply want it to recreate the right catalog
state, and it uses multiple commands to do so (CREATE SUBSCRIPTION,
ALTER SUBSCRIPTION OWNER TO, etc.). It should never connect to the
publisher, and validation is mostly counterproductive for the
intermediate states.
* When we no longer need a slot (due to ALTER/DROP), we would like to
drop it from the publisher, but for various reasons a connection to the
publisher may be impossible. In that case, the user may still want the
ALTER/DROP to succeed.
* Validation at DDL-time is useful for interactive purposes, but
limited. Whatever is validated may change before connection time (e.g.
privileges on the server may be revoked), so connection-time validation
is the authoritative one.
To reconcile these goals, we need to weaken DDL-time validation a bit,
be more precise about when we try to generate a conninfo, and then be
sure that restore doesn't do anything that would cause a conninfo to be
generated or a connection to happen.
We can preserve the most useful kinds of validation by putting it
behind an if (!superuser()) guard, so it doesn't interfere with
restores.
Thoughts?
Regards,
Jeff Davis
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: pgsql@j-davis.com, noah@leadboat.com, amit.kapila16@gmail.com
Subject: Re: CREATE SUBSCRIPTION ... SERVER vs. pg_dump, etc.
In-Reply-To: <e96efe16fb47fcca4ae0cd0157fb6bc662470712.camel@j-davis.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox