public inbox for [email protected]  

help / color / mirror / Atom feed

From: Tristan Partin <[email protected]>
To: Fujii Masao <[email protected]>
Cc: PostgreSQL Hackers <[email protected]>
Subject: Re: Fix publisher-side sequence permission reporting
Date: Thu, 18 Jun 2026 23:11:48 +0000
Message-ID: <[email protected]> (raw)
In-Reply-To: <CAHGQGwGNTaXnBKUV510_P1KwhdbHT+kgZ4zU5njBHy7nCqdhzg@mail.gmail.com>
References: <CAHGQGwGNTaXnBKUV510_P1KwhdbHT+kgZ4zU5njBHy7nCqdhzg@mail.gmail.com>

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)

view thread (20+ messages)  latest in thread

reply

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: [email protected]
  Cc: [email protected], [email protected], [email protected]
  Subject: Re: Fix publisher-side sequence permission reporting
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox