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
From 1e31facd9e01b4033ba9ee269059ca503891cd7a Mon Sep 17 00:00:00 2001
From: Zhong ShiHao <[email protected]>
Date: Mon, 7 Sep 2026 20:22:13 -0400
Subject: [PATCH v2 1/2] Make pg_stat_get_backend_subxact() respect statistics
permissions
Every other per-backend statistics function that reports the details of a
session first calls HAS_PGSTAT_PERMISSIONS(), so it returns NULL to a
caller that is neither a superuser, nor a member of pg_read_all_stats,
nor a member of the role that owns the session.
pg_stat_get_backend_subxact() had no such check and reported the
subtransaction count and overflow flag to any caller. Add the check the
sibling functions use.
The rule is stated for the dynamic statistics views but not for these
functions, so document it above the per-backend function table, and
correct the name of the subxact_overflowed output column while at it.
---
doc/src/sgml/monitoring.sgml | 12 +++++++++++-
src/backend/utils/adt/pgstatfuncs.c | 7 ++++++-
src/test/regress/expected/stats.out | 27 +++++++++++++++++++++++++++
src/test/regress/sql/stats.sql | 18 ++++++++++++++++++
4 files changed, 62 insertions(+), 2 deletions(-)
diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml
index b403fb990a7..1958c7c7636 100644
--- a/doc/src/sgml/monitoring.sgml
+++ b/doc/src/sgml/monitoring.sgml
@@ -6193,6 +6193,16 @@ FROM pg_stat_get_backend_idset() AS backendid;
</programlisting>
</para>
+ <para>
+ These functions are security restricted in the same way as
+ <structname>pg_stat_activity</structname>. The existence of a session and
+ its general properties, such as its session user and database, are visible
+ to all users, but the functions that report the details of a session's
+ activity return NULL unless the caller is a superuser, has privileges of the
+ <link linkend="predefined-role-pg-monitor"><literal>pg_read_all_stats</literal></link>
+ role, or is a member of the role that owns the session.
+ </para>
+
<table id="monitoring-stats-backend-funcs-table">
<title>Per-Backend Statistics Functions</title>
<tgroup cols="1">
@@ -6325,7 +6335,7 @@ FROM pg_stat_get_backend_idset() AS backendid;
backend with the specified ID.
The fields returned are <parameter>subxact_count</parameter>, which
is the number of subtransactions in the backend's subtransaction cache,
- and <parameter>subxact_overflow</parameter>, which indicates whether
+ and <parameter>subxact_overflowed</parameter>, which indicates whether
the backend's subtransaction cache is overflowed or not.
</para></entry>
</row>
diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c
index 0d47d745c18..f91f9b39614 100644
--- a/src/backend/utils/adt/pgstatfuncs.c
+++ b/src/backend/utils/adt/pgstatfuncs.c
@@ -838,7 +838,12 @@ pg_stat_get_backend_subxact(PG_FUNCTION_ARGS)
TupleDescFinalize(tupdesc);
BlessTupleDesc(tupdesc);
- if ((local_beentry = pgstat_get_local_beentry_by_proc_number(procNumber)) != NULL)
+ /*
+ * Like the other per-backend statistics functions, report the details of
+ * a session only to a caller that is allowed to see them.
+ */
+ if ((local_beentry = pgstat_get_local_beentry_by_proc_number(procNumber)) != NULL &&
+ HAS_PGSTAT_PERMISSIONS(local_beentry->backendStatus.st_userid))
{
/* Fill values and NULLs */
values[0] = Int32GetDatum(local_beentry->backend_subxact_count);
diff --git a/src/test/regress/expected/stats.out b/src/test/regress/expected/stats.out
index 8b15471248b..54af4cb3032 100644
--- a/src/test/regress/expected/stats.out
+++ b/src/test/regress/expected/stats.out
@@ -1141,6 +1141,33 @@ WHERE pg_stat_get_backend_pid(beid) = pg_backend_pid();
t
(1 row)
+-- pg_stat_get_backend_subxact() reports the details of a session, so like the
+-- other per-backend functions it is only meant to answer callers that are
+-- allowed to see them.
+SELECT beid FROM pg_stat_get_backend_idset() beid
+WHERE pg_stat_get_backend_pid(beid) = pg_backend_pid() \gset
+-- the role that owns this backend sees the values
+SELECT subxact_count IS NOT NULL AS count_visible,
+ subxact_overflowed IS NOT NULL AS overflow_visible
+FROM pg_stat_get_backend_subxact(:beid);
+ count_visible | overflow_visible
+---------------+------------------
+ t | t
+(1 row)
+
+CREATE ROLE regress_stat_subxact_role;
+SET ROLE regress_stat_subxact_role;
+-- an unrelated role gets NULLs instead
+SELECT subxact_count IS NULL AS count_hidden,
+ subxact_overflowed IS NULL AS overflow_hidden
+FROM pg_stat_get_backend_subxact(:beid);
+ count_hidden | overflow_hidden
+--------------+-----------------
+ t | t
+(1 row)
+
+RESET ROLE;
+DROP ROLE regress_stat_subxact_role;
-----
-- Test that resetting stats works for reset timestamp
-----
diff --git a/src/test/regress/sql/stats.sql b/src/test/regress/sql/stats.sql
index 674637e172b..0ccf3c9b839 100644
--- a/src/test/regress/sql/stats.sql
+++ b/src/test/regress/sql/stats.sql
@@ -535,6 +535,24 @@ SELECT (current_schemas(true))[1] = ('pg_temp_' || beid::text) AS match
FROM pg_stat_get_backend_idset() beid
WHERE pg_stat_get_backend_pid(beid) = pg_backend_pid();
+-- pg_stat_get_backend_subxact() reports the details of a session, so like the
+-- other per-backend functions it is only meant to answer callers that are
+-- allowed to see them.
+SELECT beid FROM pg_stat_get_backend_idset() beid
+WHERE pg_stat_get_backend_pid(beid) = pg_backend_pid() \gset
+-- the role that owns this backend sees the values
+SELECT subxact_count IS NOT NULL AS count_visible,
+ subxact_overflowed IS NOT NULL AS overflow_visible
+FROM pg_stat_get_backend_subxact(:beid);
+CREATE ROLE regress_stat_subxact_role;
+SET ROLE regress_stat_subxact_role;
+-- an unrelated role gets NULLs instead
+SELECT subxact_count IS NULL AS count_hidden,
+ subxact_overflowed IS NULL AS overflow_hidden
+FROM pg_stat_get_backend_subxact(:beid);
+RESET ROLE;
+DROP ROLE regress_stat_subxact_role;
+
-----
-- Test that resetting stats works for reset timestamp
-----
--
2.55.0
From ae0f4dcb625509d425994bf0fb84be06c0377831 Mon Sep 17 00:00:00 2001
From: Jim Jones <[email protected]>
Date: Thu, 10 Sep 2026 17:19:23 +0200
Subject: [PATCH v2 2/2] Add test case for explicit pg_read_all_stats grant
---
src/test/regress/expected/stats.out | 12 ++++++++++++
src/test/regress/sql/stats.sql | 9 +++++++++
2 files changed, 21 insertions(+)
diff --git a/src/test/regress/expected/stats.out b/src/test/regress/expected/stats.out
index 54af4cb3032..a2bad787de8 100644
--- a/src/test/regress/expected/stats.out
+++ b/src/test/regress/expected/stats.out
@@ -1166,6 +1166,18 @@ FROM pg_stat_get_backend_subxact(:beid);
t | t
(1 row)
+RESET ROLE;
+GRANT pg_read_all_stats TO regress_stat_subxact_role;
+SET ROLE regress_stat_subxact_role;
+-- pg_read_all_stats sees the values again
+SELECT subxact_count IS NULL AS count_hidden,
+ subxact_overflowed IS NULL AS overflow_hidden
+FROM pg_stat_get_backend_subxact(:beid);
+ count_hidden | overflow_hidden
+--------------+-----------------
+ f | f
+(1 row)
+
RESET ROLE;
DROP ROLE regress_stat_subxact_role;
-----
diff --git a/src/test/regress/sql/stats.sql b/src/test/regress/sql/stats.sql
index 0ccf3c9b839..809206a3212 100644
--- a/src/test/regress/sql/stats.sql
+++ b/src/test/regress/sql/stats.sql
@@ -550,6 +550,15 @@ SET ROLE regress_stat_subxact_role;
SELECT subxact_count IS NULL AS count_hidden,
subxact_overflowed IS NULL AS overflow_hidden
FROM pg_stat_get_backend_subxact(:beid);
+RESET ROLE;
+
+GRANT pg_read_all_stats TO regress_stat_subxact_role;
+SET ROLE regress_stat_subxact_role;
+-- pg_read_all_stats sees the values again
+SELECT subxact_count IS NULL AS count_hidden,
+ subxact_overflowed IS NULL AS overflow_hidden
+FROM pg_stat_get_backend_subxact(:beid);
+
RESET ROLE;
DROP ROLE regress_stat_subxact_role;
--
2.55.0
