Re: Fix publisher-side sequence permission reporting

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=foo dbname=postgres ...'
> 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 continue
> -# retrying.
> +# get misreported as a missing sequence. The subscriber should log a warning
> +# and continue retrying.
> ##########

I think a better comment might be:

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

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

The API for pg_get_sequence_data() is very _interesting_. Overloading
the NULLs row to mean many things is a bit strange. I'm not sure what
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
you submitted achieves the stated goal.

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