pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Joao Rito <joaovictor.rito@gmail.com>
To: shihao zhong <zhong950419@gmail.com>
Cc: pgsql-bugs@lists.postgresql.org, Tom Lane <tgl@sss.pgh.pa.us>
Cc: Peter Eisentraut <peter@eisentraut.org>
Cc: imchifan@163.com
Subject: Re: BUG #19703: information_schema.usage_privileges omits a sequence owner's implicit USAGE privilege
Date: Sun, 15 Nov 2026 00:53:59 -0300
Message-ID: <20261115035400.23692-1-joaovictor.rito@gmail.com> (raw)
In-Reply-To: <CAGRkXqTssJ5ttoY_BATULMChBHQAAyO3sPNBMXGnCOws3ANRQQ@mail.gmail.com>
References: <CAGRkXqTssJ5ttoY_BATULMChBHQAAyO3sPNBMXGnCOws3ANRQQ@mail.gmail.com>

Hi Shihao,

I tested v1 0001 and 0002 on current master (81b2179b492, built with cassert and tap tests). Both apply cleanly. On master, for a sequence with no explicit grants, has_sequence_privilege says the owner has USAGE but the view has no row for it. The same happens for the new owner after ALTER SEQUENCE ... OWNER TO. Sequences with an explicit GRANT already showed the owner row. A table owner shows up in table_privileges without any grant, so sequences were the odd one out. The reproducer from the report gives implicit_view_rows=0 on master.

With the patches, the owner row appears for a sequence with no grants (is_grantable YES), for one with an explicit GRANT, for one with GRANT ... WITH GRANT OPTION, and for the new owner after ALTER OWNER TO. A role with no privileges sees no rows, and a sequence where the owner revoked its own USAGE has no owner row. For three roles and five sequences the view and has_sequence_privilege agree (0 mismatches, 2 before the patch). The reproducer now gives implicit_view_rows=1 and explicit_view_rows=1. make check passes (243 tests). With 0001 reverted and 0002 kept, privileges fails because the first new query returns 0 rows instead of 1, so the test does catch the bug.

What I did not test: the isolation and tap suites, pgindent, the docs, other platforms (I ran one Linux build), upgrades from an existing cluster, and the back branches. I agree with Tom's vote against back-patching, so I checked only master.

Small suggestions: the commit message of 0001 could mention that catversion is bumped or not, since Tom noted that information_schema changes have usually come with one, and the patch does not include one. The test could also cover a sequence whose owner is not the current user, but I have no objection to the change as it is.

Regards,
Joao Rito





view thread (7+ messages)  latest in thread

Message-ID: <20261115035400.23692-1-joaovictor.rito@gmail.com>
Permalink:  ../20261115035400.23692-1-joaovictor.rito@gmail.com/
Also on:    postgresql.org/message-id/20261115035400.23692-1-joaovictor.rito@gmail.com

 · 

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 from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-bugs@postgresql.org
  Cc: joaovictor.rito@gmail.com, zhong950419@gmail.com, tgl@sss.pgh.pa.us, peter@eisentraut.org, imchifan@163.com
  Subject: Re: BUG #19703: information_schema.usage_privileges omits a sequence owner's implicit USAGE privilege
  In-Reply-To: <20261115035400.23692-1-joaovictor.rito@gmail.com>
  References: <CAGRkXqTssJ5ttoY_BATULMChBHQAAyO3sPNBMXGnCOws3ANRQQ@mail.gmail.com> <20261115035400.23692-1-joaovictor.rito@gmail.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