Re: Fix publisher-side sequence permission reporting

On Mon, Jun 22, 2026 at 1:03 PM Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:
> > Do you mean that the documentation for pg_get_sequence_data() should
> > also mention other sessions' temporary sequences and unlogged sequences
> > on standbys, as the comment does? If I've misunderstood your point,
> > could you clarify?
> >
>
> It is better to update docs for all cases. Sorry, I was wrong in
> saying that code comments need an update.

Understood.

BTW, isn't the current documentation a bit misleading? It says:

This function returns a row of NULL values if the sequence does not exist.

But if the specified object does not exist, pg_get_sequence_data()
raises an error rather than returning a row of NULL values. On the
other hand, it does return a row of NULL values if the specified
object exists but is not a sequence. Is my understanding correct? If
so, how about something like:

---------------------
This function returns a row of NULL values if the specified object
exists but is not a sequence, if the current user lacks privileges on
the sequence, if the sequence is another session's temporary
sequence, or if it is an unlogged sequence on a standby server.
---------------------

> > Do you mean that sequencesync.c should also distinguish other
> > sessions' temporary sequences and unlogged sequences on standbys, and
> > report separate warnings for those cases?
> >
>
> Right. I mean to ask if we want to distinguish the lack of privilege
> as a separate case then why not others? My opinion on this point is
> that improving all these cases (including lack of privileges) together
> could be considered as an enhancement for the next version but if you
> think this is sort of a must to distinguish one or more cases then we
> can do it now as well. However, the reason for doing it now is not
> clear to me.

I think the lack-of-privilege case should be checked first, since it is
likely to be the most common one.

As for another session's temporary sequences, I don't think a sequence
sync worker can actually encounter one. To do so, it would have to
specify another session's temporary namespace when executing the query
below. However, the namespace comes from the publication, and temporary
sequences are never published, so that doesn't seem possible.

appendStringInfo(&cmd,
"SELECT s.seqidx, has_sequence_privilege(c.oid, 'SELECT'),\n"
" ps.*, seq.seqtypid,\n"
" seq.seqstart, seq.seqincrement, seq.seqmin,\n"
" seq.seqmax, seq.seqcycle\n"
"FROM ( VALUES %s ) AS s (schname, seqname, seqidx)\n"
"JOIN pg_namespace n ON n.nspname = s.schname\n"
"JOIN pg_class c ON c.relnamespace = n.oid AND c.relname = s.seqname\n"
"JOIN pg_sequence seq ON seq.seqrelid = c.oid\n"
"JOIN LATERAL pg_get_sequence_data(seq.seqrelid) AS ps ON true\n",
seqstr.data);

An unlogged sequence on a standby seems theoretically possible, but
only under unlikely sequence of events:

1. The sequence sync worker fetches the sequence information from the
publication.
2. The sequence is dropped on the primary.
3. An unlogged sequence with the same name is created.
4. The schema change is replicated to the standby.
5. The sequence sync worker executes the above query with the fetched info.

So I think this case is possible, but unlikely.

> > > errhint("Grant SELECT on the sequence to the replication role on the
> > > publisher.")
> >
> > How about making this a bit more precise?
> >
> > errhint("Grant SELECT on the sequence to the role used for the
> > replication connection on the publisher.")
> >
>
> makes sense.

The attached patch adds those HINT messages. It also updates the
related documentation.

Regards,

--
Fujii Masao