Add a permission check to pg_stat_get_backend_subxact()

On 08/09/2026 02:33, shihao zhong wrote:

pg_stat_get_backend_subxact() does not check the caller's permissions.
Every other function in the "Per-Backend Statistics Functions" table
that reports what a session is doing calls HAS_PGSTAT_PERMISSIONS()
first and returns NULL to a caller who may not see it.  This one is the
only exception.

pg_stat_get_backend_wal, pg_stat_get_backend_io, and
pg_stat_get_backend_lock also lack this check. Out of scope here, but
perhaps worth a followup patch?

- adds the HAS_PGSTAT_PERMISSIONS() check, with a regression test that
  fails without it;

I believe the tests should also cover a non-superuser with explicit
pg_read_all_stats permission (see 0002 attached)

- documents the rule above that table.  The table says nothing about
  permissions today; the rule is only written down for the dynamic
  statistics views;

- corrects one column name in the docs: subxact_overflow should be
  subxact_overflowed.

The C tuple descriptor still says "subxact_overflow" -- most likely the
source of the confusion.

TupleDescInitEntry(tupdesc, (AttrNumber) 2, "subxact_overflow",
BOOLOID, -1, 0);

Best, Jim

On Thu, Sep 10, 2026 at 05:47:36PM +0200, Jim Jones wrote:

On 08/09/2026 02:33, shihao zhong wrote:
pg_stat_get_backend_wal, pg_stat_get_backend_io, and
pg_stat_get_backend_lock also lack this check. Out of scope here, but
perhaps worth a followup patch?

Hmm, yeah. You have a consistency point here. None of these stats
are critical in any way, but cleaning up all that on HEAD shoulds like
a good thing to do, while we are on it. Let's group all that in a
single patch. All these functions treat similar backend-level stats.

I believe the tests should also cover a non-superuser with explicit
pg_read_all_stats permission (see 0002 attached)

Yep. Let's make that also cheaper: one role for all of the functions.

The C tuple descriptor still says "subxact_overflow" -- most likely the
source of the confusion.

TupleDescInitEntry(tupdesc, (AttrNumber) 2, "subxact_overflow",
BOOLOID, -1, 0);

Ahah, nice catch. The value that primes (reported back to the user)
is not the one set in the TupleDesc but the one defined in pg_proc.dat.

Could you group all your findings in a single patch? The doc entry
needs to be at least backpatched, mentioning "overflow" instead of
"overflowed" is not correct.
--
Michael

On 11/09/2026 09:53, Michael Paquier wrote:

Could you group all your findings in a single patch? The doc entry
needs to be at least backpatched, mentioning "overflow" instead of
"overflowed" is not correct.

Attached v3 with these changes.

0001 - docs fix for backpatching
0002 - adds the same permission checks into pg_stat_get_backend_subxact,
pg_stat_get_backend_io, pg_stat_get_backend_lock, and
pg_stat_get_backend_wal.

Shihao, feel free to modify or reject these changes (it's your patch)

Thanks!

Best, Jim

On Fri, Sep 11, 2026 at 12:58:50PM +0200, Jim Jones wrote:

0001 - docs fix for backpatching

Applied this one for now.

0002 - adds the same permission checks into pg_stat_get_backend_subxact,
pg_stat_get_backend_io, pg_stat_get_backend_lock, and
pg_stat_get_backend_wal.

And will look at that later..
--
Michael

Hi Jim, Michael,

Shihao, feel free to modify or reject these changes (it's your patch)

Thanks Jim, grouping the four functions is the right thing. v4 attached,
with three small changes to 0002:

Applied this one for now.

Thanks for committing that, I will not include 0001 in the following emails.

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.

Thanks,
Shihao

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?
--
Michael