Date: Mon, 21 Sep 2026 09:58:27 +0000
From: Bertrand Drouvot <bertranddrouvot.pg@gmail.com>
To: Michael Paquier <michael@paquier.xyz>
Cc: shihao zhong <zhong950419@gmail.com>,
	Jim Jones <jim.jones@uni-muenster.de>,
	pgsql-hackers <pgsql-hackers@lists.postgresql.org>
Subject: Re: Add a permission check to pg_stat_get_backend_subxact()
Message-ID: <arD/w47Ug1GaObfq@bdtpg>
References: 
 <CAGRkXqTBZ+zbVuDC8xGEB6Btj61hsui5H5nGqzFBDyOXc=4bjQ@mail.gmail.com>
 <ef48668c-cf8e-4f4b-bf39-4b3b9fb9b85a@uni-muenster.de>
 <aqOzYr0It5sP7eFF@paquier.xyz>
 <c7a0be5d-5328-41dd-a7bf-6b964e9f7a06@uni-muenster.de>
 <aqSXMjhwFFzz_zyo@paquier.xyz>
 <CAGRkXqRGVNvvkY7a7X=3Bn=WgfLuFZ5iVsBf+t6HSfm2RwVC+g@mail.gmail.com>
 <aqec3MF_GDsMdadl@paquier.xyz>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <aqec3MF_GDsMdadl@paquier.xyz>
Archived-At: <https://www.postgresql.org/message-id/arD/w47Ug1GaObfq%40bdtpg>
Precedence: bulk

Hi,

On Mon, Sep 14, 2026 at 04:06:04PM +0900, Michael Paquier wrote:
> On Sat, Sep 12, 2026 at 08:43:28AM -0400, shihao zhong wrote:
> > Thanks for committing that, I will not include 0001 in the following emails.
> 
> Fixed the subxact_overflow -> subxact_overflowed, as that's
> independent.
> 
> > 1. The first test block ran as superuser, so the owner branch of
> >   HAS_PGSTAT_PERMISSIONS() was never exercised: with "userid" forced to
> >   InvalidOid the test still passed.  The block now grants the test role
> >   membership in the session's role instead.  With that, forcing userid
> >   to InvalidOid fails the test, and removing the checks fails the
> >   "unrelated role" block.
> > 
> > 2. The doc paragraph above the per-backend table said the functions
> >   "return NULL", but activity/wait_event return "<insufficient
> >   privilege>" and the SRFs return no rows.  Reworded.
> > 
> > 3. Commit message: noted that processes owned by no role (autovacuum
> >   workers, WAL writer, ...) are now visible only to superusers and
> >   pg_read_all_stats, as in pg_stat_activity, and that no backpatch is
> >   done.
> 
> That seems globally sensible, at quick glance.  I am also adding
> Bertrand Drouvot in CC to comment about this change, as he has worked
> on three of these functions.
> 
> @Bertrand, what do you think?

pg_stat_io, pg_stat_wal and pg_stat_lock expose aggregate statistics without
restrictions but as pg_stat_get_backend_io(), pg_stat_get_backend_wal() and
pg_stat_get_backend_lock() expose the stats for a particular backend, I think the
proposed patch makes sense.

One thing I noticed while looking at this is that with stats_fetch_consistency = snapshot,
pgstat_fetch_stat_backend_by_pid() could validate the PID and user from one backend
while returning cumulative statistics cached for an older backend that used the
same ProcNumber.

The race is not introduced by this patch, but the new permission check makes it
more relevant here. Worth to fix at the same time?

Regards,

-- 
Bertrand Drouvot
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com