Add a permission check to pg_stat_get_backend_subxact()

Started by shihao zhong3 months ago27 messageshackers
Jump to latest
#1shihao zhong
zhong950419@gmail.com

Hi hackers,

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.

I reported this to pgsql-security first. Michael Paquier replied that
it is not a vulnerability, since the count and the flag are of no use to
an unprivileged user, and suggested that a consistency fix on HEAD be
discussed here.

The patch:

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

- 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.

pid, dbid, userid and idset stay open to everyone, and I think that is
right: they leak nothing new, since pg_stat_get_activity() already hands
datid, pid, usesysid and application_name to every caller before it
reaches its permission check. subxact_count and subxact_overflowed are
different. They are not columns of pg_stat_activity at all, so this
function is the only way to read them, and today it is an unchecked one.

Thanks,
Shihao

Attachments:

t253703_1
0001-Make-pg_stat_get_backend_subxact-respect-statistics-.patchapplication/octet-stream; name=0001-Make-pg_stat_get_backend_subxact-respect-statistics-.patchDownload+62-3
#2Jim Jones
jim.jones@uni-muenster.de
In reply to: shihao zhong (#1)
Re: 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

Attachments:

t253703_2
v2-0001-Make-pg_stat_get_backend_subxact-respect-statisti.patchtext/x-patch; charset=UTF-8; name=v2-0001-Make-pg_stat_get_backend_subxact-respect-statisti.patchDownload+62-3
v2-0002-Add-test-case-for-explicit-pg_read_all_stats-gran.patchtext/x-patch; charset=UTF-8; name=v2-0002-Add-test-case-for-explicit-pg_read_all_stats-gran.patchDownload+21-1
#3Michael Paquier
michael@paquier.xyz
In reply to: Jim Jones (#2)
Re: Add a permission check to pg_stat_get_backend_subxact()

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

#4Jim Jones
jim.jones@uni-muenster.de
In reply to: Michael Paquier (#3)
Re: Add a permission check to pg_stat_get_backend_subxact()

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

Attachments:

t253703_4
v3-0001-Fix-output-column-name-for-pg_stat_get_backend_su.patchtext/x-patch; charset=UTF-8; name=v3-0001-Fix-output-column-name-for-pg_stat_get_backend_su.patchDownload+1-2
v3-0002-Make-per-backend-statistics-functions-respect-sta.patchtext/x-patch; charset=UTF-8; name=v3-0002-Make-per-backend-statistics-functions-respect-sta.patchDownload+147-12
#5Michael Paquier
michael@paquier.xyz
In reply to: Jim Jones (#4)
Re: Add a permission check to pg_stat_get_backend_subxact()

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

#6shihao zhong
zhong950419@gmail.com
In reply to: Michael Paquier (#5)
Re: Add a permission check to pg_stat_get_backend_subxact()

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

Attachments:

t253703_6
v4-0002-Make-per-backend-statistics-functions-respect-sta.patchapplication/octet-stream; name=v4-0002-Make-per-backend-statistics-functions-respect-sta.patchDownload+157-12
#7Michael Paquier
michael@paquier.xyz
In reply to: shihao zhong (#6)
Re: Add a permission check to pg_stat_get_backend_subxact()

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

#8Bertrand Drouvot
bertranddrouvot.pg@gmail.com
In reply to: Michael Paquier (#7)
Re: Add a permission check to pg_stat_get_backend_subxact()

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

#9shihao zhong
zhong950419@gmail.com
In reply to: Bertrand Drouvot (#8)
Re: Add a permission check to pg_stat_get_backend_subxact()

Hi Bertrand,

Worth to fix at the same time?

Thanks, I can reproduce it. A role with no privileges reads the IO and WAL
numbers of a superuser session under the PID of its own session.

v5-0002 fixes it by making the PID part of the object ID of the backend
stats entries. The entry of an older backend is then never found with
the PID of a newer one, and nothing needs to be checked after the fetch.
Entries are still dropped at exit, so the number of live entries is still
bounded by the number of proc numbers.

It is a separate patch as the problem has existed since v18.

v5-0001 is v4 rebased, without the tuple descriptor fix that Michael has
applied.

Thanks,
Shihao

On Mon, Sep 21, 2026 at 5:58 AM Bertrand Drouvot <
bertranddrouvot.pg@gmail.com> wrote:

Show quoted text

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

Attachments:

t253703_9
v5-0001-Make-per-backend-statistics-functions-respect-sta.patchapplication/octet-stream; name=v5-0001-Make-per-backend-statistics-functions-respect-sta.patchDownload+156-11
v5-0002-Include-the-PID-in-the-key-of-backend-statistics-.patchapplication/octet-stream; name=v5-0002-Include-the-PID-in-the-key-of-backend-statistics-.patchDownload+23-10
#10Michael Paquier
michael@paquier.xyz
In reply to: shihao zhong (#9)
Re: Add a permission check to pg_stat_get_backend_subxact()

On Mon, Sep 21, 2026 at 07:54:04PM -0400, shihao zhong wrote:

v5-0002 fixes it by making the PID part of the object ID of the backend
stats entries. The entry of an older backend is then never found with
the PID of a newer one, and nothing needs to be checked after the fetch.
Entries are still dropped at exit, so the number of live entries is still
bounded by the number of proc numbers.

+ * Object ID of the stats entry of a backend.  The PID is part of the key, so
+ * that an entry cached for an older backend that used the same proc number,
+ * for example in a stats snapshot, is never mistaken for the entry of the
+ * backend currently using this proc number.
+ */
+#define PGSTAT_BACKEND_OBJID(pid, procnum) \
+	((((uint64) (uint32) (pid)) << 32) | (uint32) (procnum))

This breaks the fundamental law of the backend stats and makes the
whole more brittle. Having *only* the procnum in the key naturally
caps the maximum amount of shared memory used by this stats kind
because they would be recycled when connecting a new backend. You are
removing this cap, so your patch means a lot of potential bloat on a
live server in the shared hash table used by pgstats, the more bloat
the more connection turnover.

One approach that may be saner is to store a trace of the PID in
PgStat_Backend when a new backend connects, then compare it back with
the existing PGPROC entry, then decide what to show based on the state
of both. That should be much cheaper, and much lower in shared memory
footprint than what you are suggesting.
--
Michael

#11shihao zhong
zhong950419@gmail.com
In reply to: Michael Paquier (#10)
Re: Add a permission check to pg_stat_get_backend_subxact()

Hi Michael,

One approach that may be saner is to store a trace of the PID in
PgStat_Backend when a new backend connects, then compare it back with
the existing PGPROC entry, then decide what to show based on the state
of both.

Thanks for pointing that out. Applied.

0001 is unchanged.
Thanks,
Shihao

Attachments:

v6-0001-Make-per-backend-statistics-functions-respect-sta.patchapplication/octet-stream; name=v6-0001-Make-per-backend-statistics-functions-respect-sta.patchDownload+156-11
v6-0002-Store-the-PID-of-a-backend-in-its-statistics-entr.patchapplication/octet-stream; name=v6-0002-Store-the-PID-of-a-backend-in-its-statistics-entr.patchDownload+15-2
#12shihao zhong
zhong950419@gmail.com
In reply to: shihao zhong (#11)
Re: Add a permission check to pg_stat_get_backend_subxact()

Rebase with master.

Attachments:

t253703_12
v7-0001-Make-per-backend-statistics-functions-respect-sta.patchapplication/octet-stream; name=v7-0001-Make-per-backend-statistics-functions-respect-sta.patchDownload+156-11
v7-0002-Store-the-PID-of-a-backend-in-its-statistics-entr.patchapplication/octet-stream; name=v7-0002-Store-the-PID-of-a-backend-in-its-statistics-entr.patchDownload+15-2
#13Michael Paquier
michael@paquier.xyz
In reply to: Bertrand Drouvot (#8)
Re: Add a permission check to pg_stat_get_backend_subxact()

On Mon, Sep 21, 2026 at 09:58:27AM +0000, Bertrand Drouvot wrote:

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.

I was wondering about the validity of this argument regarding a
possible split of 0001 and 0002. And this points to the fact that
0001 is simply unsafe: we should not do a HAS_PGSTAT_PERMISSIONS()
based on the user ID retrieved from a beentry as it may refer to a
role different than the one associated to the stats data depending on
the level of stats consistency.

I don't see a way through here except by storing more information into
the stats entries themselves to use in the checks. Another option
than the PID would be to store the user ID in the stats entry and
reuse it for the HAS_PGSTAT_PERMISSIONS() check? If we do that, a
snapshot of the data would still be able to work even if the proc slot
is reused due to a slot being recycled, and that could be arguably
more useful than the PID (a session willing a snapshot of the data
would still be able to refer to it based on a past point)?

I'd slightly prefer storing a user ID, I think, because it means that
the ACL check is done only based on the stats data, and there would be
no cross-dependency between the data in the beentry and the stats
data. Perhaps you have a different view or more ideas?
--
Michael

#14Bertrand Drouvot
bertranddrouvot.pg@gmail.com
In reply to: Michael Paquier (#13)
Re: Add a permission check to pg_stat_get_backend_subxact()

Hi,

On Tue, Sep 22, 2026 at 06:24:36PM +0900, Michael Paquier wrote:

On Mon, Sep 21, 2026 at 09:58:27AM +0000, Bertrand Drouvot wrote:

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.

I'd slightly prefer storing a user ID, I think, because it means that
the ACL check is done only based on the stats data, and there would be
no cross-dependency between the data in the beentry and the stats
data. Perhaps you have a different view or more ideas?

Yeah, storing the user ID in PgStat_Backend and using it for the ACL check
makes sense to me.

I'm not sure the user ID alone is enough though: if B reuses A's ProcNumber,
pg_stat_get_backend_wal(B_pid) could still return A's cached statistics when
the caller is allowed to see A's data.

I'd keep the PID check from 0002 as well. A generation would be more robust
against PID reuse, as done for example for AIO handles, but introducing a
backend generation seems like too much for this case.

So storing both seems like the simplest approach: the user ID for the ACL
check and the PID for matching the statistics to the requested backend.

Thoughts?

Regards,

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

#15Michael Paquier
michael@paquier.xyz
In reply to: Bertrand Drouvot (#14)
Re: Add a permission check to pg_stat_get_backend_subxact()

On Tue, Sep 22, 2026 at 10:03:10AM +0000, Bertrand Drouvot wrote:

I'm not sure the user ID alone is enough though: if B reuses A's ProcNumber,
pg_stat_get_backend_wal(B_pid) could still return A's cached statistics when
the caller is allowed to see A's data.

I'd keep the PID check from 0002 as well. A generation would be more robust
against PID reuse, as done for example for AIO handles, but introducing a
backend generation seems like too much for this case.

So storing both seems like the simplest approach: the user ID for the ACL
check and the PID for matching the statistics to the requested backend.

The PID would also act as a kind of weaker generation number, slightly
weaker but simpler. So that works here. Perhaps you would like to
give it a shot?
--
Michael

#16Bertrand Drouvot
bertranddrouvot.pg@gmail.com
In reply to: Michael Paquier (#15)
Re: Add a permission check to pg_stat_get_backend_subxact()

Hi,

On Tue, Sep 22, 2026 at 08:23:54PM +0900, Michael Paquier wrote:

On Tue, Sep 22, 2026 at 10:03:10AM +0000, Bertrand Drouvot wrote:

I'm not sure the user ID alone is enough though: if B reuses A's ProcNumber,
pg_stat_get_backend_wal(B_pid) could still return A's cached statistics when
the caller is allowed to see A's data.

I'd keep the PID check from 0002 as well. A generation would be more robust
against PID reuse, as done for example for AIO handles, but introducing a
backend generation seems like too much for this case.

So storing both seems like the simplest approach: the user ID for the ACL
check and the PID for matching the statistics to the requested backend.

The PID would also act as a kind of weaker generation number, slightly
weaker but simpler. So that works here. Perhaps you would like to
give it a shot?

Do you mean adding the user ID on top of Shihao's 0002? If so, I can have a look,
unless Shihao is already planning to update the patch along those lines?

Regards,

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

#17shihao zhong
zhong950419@gmail.com
In reply to: Bertrand Drouvot (#16)
Re: Add a permission check to pg_stat_get_backend_subxact()

Do you mean adding the user ID on top of Shihao's 0002? If so, I can have

a look,

unless Shihao is already planning to update the patch along those lines?

Done in v8, attached.

0001 is the PID check, same as v7-0002. It goes first now because 0002
needs a field in PgStat_Backend.

0002 stores the user ID in PgStat_Backend next to the PID when the entry
is created, and pg_stat_get_backend_io(), wal() and lock() check the
caller against that instead of the beentry. The reset callback restores
both fields. pg_stat_get_backend_subxact() still checks the beentry, the
counters it reports come from there.

Thanks,
Shihao

Attachments:

t253703_17
v8-0002-Make-per-backend-statistics-functions-respect-sta.patchapplication/octet-stream; name=v8-0002-Make-per-backend-statistics-functions-respect-sta.patchDownload+156-9
v8-0001-Store-the-PID-of-a-backend-in-its-statistics-entr.patchapplication/octet-stream; name=v8-0001-Store-the-PID-of-a-backend-in-its-statistics-entr.patchDownload+15-2
#18Bertrand Drouvot
bertranddrouvot.pg@gmail.com
In reply to: shihao zhong (#17)
Re: Add a permission check to pg_stat_get_backend_subxact()

Hi,

On Tue, Sep 22, 2026 at 11:22:40PM -0400, shihao zhong wrote:

Do you mean adding the user ID on top of Shihao's 0002? If so, I can have

a look,

unless Shihao is already planning to update the patch along those lines?

Done in v8, attached.

Thanks!

I've a few comments:

0001 is the PID check, same as v7-0002. It goes first now because 0002
needs a field in PgStat_Backend.

0002 stores the user ID in PgStat_Backend next to the PID when the entry
is created, and pg_stat_get_backend_io(), wal() and lock() check the
caller against that instead of the beentry. The reset callback restores
both fields. pg_stat_get_backend_subxact() still checks the beentry, the
counters it reports come from there.

=== 1

pgstat_read_current_status() first copies the activity entry and then calls
ProcNumberGetTransactionIds() separately. If the backend exits and its
ProcNumber is reused in between, the userid can belong to the old backend
while the subxact counters belong to the new one.

This race exists before the patch, but it matters for the new permission check.
I wonder if we should pass the copied PID to ProcNumberGetTransactionIds() and
validate it under ProcArrayLock, following the same idea as
pgstat_fetch_stat_backend_by_pid()?

=== 2

typedef struct PgStat_Backend
{
+ int pid; /* PID of the backend owning these stats */
TimestampTz stat_reset_timestamp;

0002 explicitly says that it is not intended for backpatching, but what about
0001? If it is backpatched to v18, adding pid here changes the offsets of all
the existing fields.

I think it would make sense to add pid at the end in the backbranches (if we
back patch it), as suggested in [1]https://wiki.postgresql.org/wiki/Committing_checklist. That would preserve the existing field
offsets, though it would still change sizeof(PgStat_Backend). FWIW, I could not
find any use of sizeof(PgStat_Backend) in a GitHub code search and there is
no padding to add the new field into.

[1]: https://wiki.postgresql.org/wiki/Committing_checklist

Regards,

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

#19shihao zhong
zhong950419@gmail.com
In reply to: Bertrand Drouvot (#18)
Re: Add a permission check to pg_stat_get_backend_subxact()

Hi Bertrand,

I wonder if we should pass the copied PID to

ProcNumberGetTransactionIds() and

validate it under ProcArrayLock, following the same idea as
pgstat_fetch_stat_backend_by_pid()?

Yes, done in v9-0002, a new patch. pgstat_read_current_status() passes the
PID it copied, and ProcNumberGetTransactionIds() reports nothing when the
PGPROC entry holds another PID. It changes an exported function, so I
would keep it for HEAD.

I think it would make sense to add pid at the end in the backbranches (if

we

back patch it), as suggested in [1].

0001 fixes a misreport in 18, so I think it should go there too. v9 puts
the PID at the end of PgStat_Backend, and 0003 puts the user ID after it,
so the existing offsets do not change.

0003 is v8-0002 with only that move.'

Thanks,
Shihao

Attachments:

t253703_19
v9-0002-Check-the-PID-when-reading-the-transaction-status.patchapplication/octet-stream; name=v9-0002-Check-the-PID-when-reading-the-transaction-status.patchDownload+8-4
v9-0003-Make-per-backend-statistics-functions-respect-sta.patchapplication/octet-stream; name=v9-0003-Make-per-backend-statistics-functions-respect-sta.patchDownload+156-9
v9-0001-Store-the-PID-of-a-backend-in-its-statistics-entr.patchapplication/octet-stream; name=v9-0001-Store-the-PID-of-a-backend-in-its-statistics-entr.patchDownload+15-2
#20Michael Paquier
michael@paquier.xyz
In reply to: Bertrand Drouvot (#18)
Re: Add a permission check to pg_stat_get_backend_subxact()

On Wed, Sep 23, 2026 at 07:23:01AM +0000, Bertrand Drouvot wrote:

typedef struct PgStat_Backend
{
+ int pid; /* PID of the backend owning these stats */
TimestampTz stat_reset_timestamp;

0002 explicitly says that it is not intended for backpatching, but what about
0001? If it is backpatched to v18, adding pid here changes the offsets of all
the existing fields.

One thing that itches me quite a bit about adding a cross-check of the
PID in the set of checks is that it also changes the behavior of
stats_fetch_consistency when set to "cache" or "snapshot", by forcibly
discarding the stats numbers previously fetched when requesting stats
for a PID that matches with a previous procnumber slot, so it is just
switching from one behavior to another. In terms of implementation
simplicity, I see more merit with the existing logic where we don't
add a PID tracking because it's well, simpler! And the problem with
these numbers only becomes a problem if we deal with a connection
turnover that it impacts the numbers obtained. The main use case of
backend stats is for benchmarking and get numbers with longer-running
connections, so as a whole I think that we are making a big issue of
something that is not really one in practice.

Note that there is a parallel with replication slot stats, which are
indexed not by name but with an integer number. A backend could grab
in a snapshot data from slot 1, while concurrent activity has the idea
to drop and recreate a slot. The snapshot would still refer to the
data of the previous slot. If we aim at improving this kind of use
cases with stats snapshots, and I am not sure that it's really worth
bothering, this should work across all the stats kinds, not be plugged
multiple times across the board.

The role ID case is different: we want consistency to check for the
permissions.
--
Michael

#21shihao zhong
zhong950419@gmail.com
In reply to: Michael Paquier (#20)
#22Bertrand Drouvot
bertranddrouvot.pg@gmail.com
In reply to: Michael Paquier (#20)
#23shihao zhong
zhong950419@gmail.com
In reply to: Bertrand Drouvot (#22)
#24Michael Paquier
michael@paquier.xyz
In reply to: shihao zhong (#23)
#25shihao zhong
zhong950419@gmail.com
In reply to: Michael Paquier (#24)
#26solai v
solai.cdac@gmail.com
In reply to: shihao zhong (#21)
#27solai v
solai.cdac@gmail.com
In reply to: shihao zhong (#25)