Feedback-ID: idd01497b:Fastmail
Mime-Version: 1.0
Content-Transfer-Encoding: quoted-printable
Content-Type: text/plain; charset=UTF-8
Date: Thu, 18 Jun 2026 23:11:48 +0000
Message-Id: <DJCK033ELDM0.24V36DWNB6XH@partin.io>
Cc: "PostgreSQL Hackers" <pgsql-hackers@lists.postgresql.org>
Subject: Re: Fix publisher-side sequence permission reporting
To: "Fujii Masao" <masao.fujii@gmail.com>
From: "Tristan Partin" <tristan@partin.io>
References: 
 <CAHGQGwGNTaXnBKUV510_P1KwhdbHT+kgZ4zU5njBHy7nCqdhzg@mail.gmail.com>
In-Reply-To: 
 <CAHGQGwGNTaXnBKUV510_P1KwhdbHT+kgZ4zU5njBHy7nCqdhzg@mail.gmail.com>
Archived-At: 
 <https://www.postgresql.org/message-id/DJCK033ELDM0.24V36DWNB6XH%40partin.io>
Precedence: bulk

Hi Masao-san,

On Thu Jun 18, 2026 at 10:36 AM CDT, Fujii Masao wrote:
> Hi,
>
> While testing logical replication sequence synchronization, I found
> that a publisher-side permission problem can be reported as a
> misleading "missing sequence on publisher" warning.
>
> The issue can be reproduced as follows:
>
> 1. On the publisher:
>
>    CREATE SEQUENCE myseq;
>    CREATE PUBLICATION mypub FOR ALL SEQUENCES;
>    CREATE ROLE foo LOGIN REPLICATION NOSUPERUSER;
>
> 2. On the subscriber:
>
>    CREATE SEQUENCE myseq;
>    CREATE SUBSCRIPTION mysub
>      CONNECTION 'user=3Dfoo dbname=3Dpostgres ...'
>      PUBLICATION mypub;
>
> The subscriber currently emits:
>
>    WARNING:  missing sequence on publisher ("public.myseq")
>
> even though the sequence still exists on the publisher. The real
> problem is that the replication connection lacks SELECT privilege to
> read the sequence data.
>
> The cause is that; sequence synchronization obtains sequence data using
> pg_get_sequence_data(). When the user lacks SELECT privilege on
> the sequence, pg_get_sequence_data() returns a row containing all NULL
> values. Sequence synchronization currently treats that the same as
> a missing sequence, so publisher-side permission failures and
> genuinely missing sequences are not distinguished, leading to
> the misleading warning.
>
> Patch 0001 fixes this by distinguishing the two cases during sequence
> synchronization. It checks whether the replication connection has the
> required privilege for each published sequence and reports
> publisher-side permission failures separately.

The patch looks good to me! I had one suggestion:

>  ##########
>  # Ensure that insufficient privileges on the publisher for a sequence do=
 not
> -# disrupt the subscriber. The subscriber should log a warning and contin=
ue
> -# retrying.
> +# get misreported as a missing sequence. The subscriber should log a war=
ning
> +# and continue retrying.
>  ##########

I think a better comment might be:

	Ensure that insufficient privileges on the publisher for a sequence=20
	are reported correctly...

My reasoning for suggesting that is because your comment would to=20
indicate that any warning is accurate as long as it isn't related to=20
a missing sequence.

The API for pg_get_sequence_data() is very _interesting_. Overloading=20
the NULLs row to mean many things is a bit strange. I'm not sure what=20
a better solution would be. Just thought I would mention that.

> While working on this, I also noticed that the documented privilege
> requirement for pg_get_sequence_data() does not match the
> implementation. The documentation says that USAGE or SELECT privilege
> is sufficient, but the implementation requires SELECT.
>
> Patch 0002 updates the documentation to match the current behavior.
>
> I chose to update the documentation rather than broaden the
> implementation for two reasons.
>
> First, commit c8b06bb969b, which introduced the predecessor of
> pg_get_sequence_data(), described it as a substitute for SELECT
> from a sequence, and its implementation has always required
> SELECT privilege.
>
> Second, the logical replication documentation already states that
> replicating sequence data requires SELECT privilege.

Your reasoning for updating the documentation makes sense, and the patch=20
you submitted achieves the stated goal.

--=20
Tristan Partin
PostgreSQL Contributors Team
AWS (https://aws.amazon.com)