agora inbox for pgsql-hackers@postgresql.org
help / color / mirror / Atom feedAdd a permission check to pg_stat_get_backend_subxact()
21+ messages / 4 participants
[nested] [flat]
* Add a permission check to pg_stat_get_backend_subxact()
@ 2026-09-08 00:33 shihao zhong <zhong950419@gmail.com>
0 siblings, 1 reply; 21+ messages in thread
From: shihao zhong @ 2026-09-08 00:33 UTC (permalink / raw)
To: pgsql-hackers <pgsql-hackers@lists.postgresql.org>; +Cc: Michael Paquier <michael@paquier.xyz>
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:
[application/octet-stream] 0001-Make-pg_stat_get_backend_subxact-respect-statistics-.patch (6.0K, ../../CAGRkXqTBZ+zbVuDC8xGEB6Btj61hsui5H5nGqzFBDyOXc=4bjQ@mail.gmail.com/3-0001-Make-pg_stat_get_backend_subxact-respect-statistics-.patch)
download | inline diff:
From 93d81224a3c19d7b6874e99fa59ff7346f95e58c Mon Sep 17 00:00:00 2001
From: Zhong ShiHao <zhong950419@gmail.com>
Date: Mon, 7 Sep 2026 20:22:13 -0400
Subject: [PATCH] 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.37.1 (Apple Git-137.1)
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: Add a permission check to pg_stat_get_backend_subxact()
@ 2026-09-10 15:47 Jim Jones <jim.jones@uni-muenster.de>
parent: shihao zhong <zhong950419@gmail.com>
0 siblings, 1 reply; 21+ messages in thread
From: Jim Jones @ 2026-09-10 15:47 UTC (permalink / raw)
To: shihao zhong <zhong950419@gmail.com>; pgsql-hackers <pgsql-hackers@lists.postgresql.org>; +Cc: Michael Paquier <michael@paquier.xyz>
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:
[text/x-patch] v2-0001-Make-pg_stat_get_backend_subxact-respect-statisti.patch (6.0K, ../../ef48668c-cf8e-4f4b-bf39-4b3b9fb9b85a@uni-muenster.de/2-v2-0001-Make-pg_stat_get_backend_subxact-respect-statisti.patch)
download | inline diff:
From 1e31facd9e01b4033ba9ee269059ca503891cd7a Mon Sep 17 00:00:00 2001
From: Zhong ShiHao <zhong950419@gmail.com>
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
[text/x-patch] v2-0002-Add-test-case-for-explicit-pg_read_all_stats-gran.patch (1.8K, ../../ef48668c-cf8e-4f4b-bf39-4b3b9fb9b85a@uni-muenster.de/3-v2-0002-Add-test-case-for-explicit-pg_read_all_stats-gran.patch)
download | inline diff:
From ae0f4dcb625509d425994bf0fb84be06c0377831 Mon Sep 17 00:00:00 2001
From: Jim Jones <jim.jones@uni-muenster.de>
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
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: Add a permission check to pg_stat_get_backend_subxact()
@ 2026-09-11 07:53 Michael Paquier <michael@paquier.xyz>
parent: Jim Jones <jim.jones@uni-muenster.de>
0 siblings, 1 reply; 21+ messages in thread
From: Michael Paquier @ 2026-09-11 07:53 UTC (permalink / raw)
To: Jim Jones <jim.jones@uni-muenster.de>; +Cc: shihao zhong <zhong950419@gmail.com>; pgsql-hackers <pgsql-hackers@lists.postgresql.org>
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
Attachments:
[application/pgp-signature] signature.asc (832B, ../../aqOzYr0It5sP7eFF@paquier.xyz/2-signature.asc)
download
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: Add a permission check to pg_stat_get_backend_subxact()
@ 2026-09-11 10:58 Jim Jones <jim.jones@uni-muenster.de>
parent: Michael Paquier <michael@paquier.xyz>
0 siblings, 1 reply; 21+ messages in thread
From: Jim Jones @ 2026-09-11 10:58 UTC (permalink / raw)
To: Michael Paquier <michael@paquier.xyz>; +Cc: shihao zhong <zhong950419@gmail.com>; pgsql-hackers <pgsql-hackers@lists.postgresql.org>
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:
[text/x-patch] v3-0001-Fix-output-column-name-for-pg_stat_get_backend_su.patch (1.6K, ../../c7a0be5d-5328-41dd-a7bf-6b964e9f7a06@uni-muenster.de/2-v3-0001-Fix-output-column-name-for-pg_stat_get_backend_su.patch)
download | inline diff:
From 6e49568335e2b4006c454382945bc0c5fab94655 Mon Sep 17 00:00:00 2001
From: Jim Jones <jim.jones@uni-muenster.de>
Date: Fri, 11 Sep 2026 12:09:04 +0200
Subject: [PATCH v3 1/2] Fix output column name for
pg_stat_get_backend_subxact()
The documentation described the second output column of
pg_stat_get_backend_subxact() as subxact_overflow, but the function
declares it as subxact_overflowed in pg_proc.dat, and that is the name
callers actually see. Correct the documentation to match.
Author: Shihao Zhong <zhong950419@gmail.com>
Reviewed-by: Jim Jones <jim.jones@uni-muenster.de>
Reviewed-by: Michael Paquier <michael@paquier.xyz>
Discussion: https://www.postgresql.org/message-id/flat/CAGRkXqTBZ%2BzbVuDC8xGEB6Btj61hsui5H5nGqzFBDyOXc%3D4bjQ%40mail.gmail.com
Backpatch-through: 16
---
doc/src/sgml/monitoring.sgml | 2 +-
1 file changed, 1 insertion(+), 1 deletion(-)
diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml
index 6a4cb9bb144..49bf6b51c49 100644
--- a/doc/src/sgml/monitoring.sgml
+++ b/doc/src/sgml/monitoring.sgml
@@ -6343,7 +6343,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>
--
2.55.0
[text/x-patch] v3-0002-Make-per-backend-statistics-functions-respect-sta.patch (14.7K, ../../c7a0be5d-5328-41dd-a7bf-6b964e9f7a06@uni-muenster.de/3-v3-0002-Make-per-backend-statistics-functions-respect-sta.patch)
download | inline diff:
From af1e5e7fb321c3a132bdc2b04804ba8e0bb29bb1 Mon Sep 17 00:00:00 2001
From: Jim Jones <jim.jones@uni-muenster.de>
Date: Fri, 11 Sep 2026 12:26:11 +0200
Subject: [PATCH v3 2/2] Make per-backend statistics functions respect
statistics permissions
The per-backend statistics functions that report the details of a session
call HAS_PGSTAT_PERMISSIONS() first, so that they report nothing to a
caller that is neither a superuser, nor has privileges of
pg_read_all_stats, nor is a member of the role that owns the session.
pg_stat_get_backend_subxact(), pg_stat_get_backend_io(),
pg_stat_get_backend_wal() and pg_stat_get_backend_lock() had no such
check and reported their statistics to any caller. Add the check the
sibling functions use.
The last three look up a backend by PID and have no backend status entry
at hand, so pgstat_fetch_stat_backend_by_pid() gains an optional "userid"
output argument, next to the existing "bktype" one, returning the OID of
the role that owns the backend.
The permission rule was documented for the dynamic statistics views but
not for these functions, so state it above the per-backend function table
and on the three functions listed among the additional statistics
functions. While on it, correct the name of the subxact_overflowed
column in the tuple descriptor built by pg_stat_get_backend_subxact().
Author: Shihao Zhong <zhong950419@gmail.com>
Author: Jim Jones <jim.jones@uni-muenster.de>
Reviewed-by: Michael Paquier <michael@paquier.xyz>
Discussion: https://www.postgresql.org/message-id/flat/CAGRkXqTBZ%2BzbVuDC8xGEB6Btj61hsui5H5nGqzFBDyOXc%3D4bjQ%40mail.gmail.com
---
doc/src/sgml/monitoring.sgml | 25 ++++++++++
src/backend/utils/activity/pgstat_backend.c | 12 ++++-
src/backend/utils/adt/pgstatfuncs.c | 24 ++++++---
src/include/pgstat.h | 3 +-
src/test/regress/expected/stats.out | 54 +++++++++++++++++++++
src/test/regress/sql/stats.sql | 40 +++++++++++++++
6 files changed, 147 insertions(+), 11 deletions(-)
diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml
index 49bf6b51c49..32168bc6f70 100644
--- a/doc/src/sgml/monitoring.sgml
+++ b/doc/src/sgml/monitoring.sgml
@@ -5799,6 +5799,11 @@ description | Waiting for a newly initialized WAL file to reach durable storage
the background writer, the startup process and the autovacuum launcher
as they are already visible in the <structname>pg_stat_io</structname>
view and there is only one of each.
+ </para>
+ <para>
+ This function returns no rows unless the caller is a superuser, has
+ privileges of the <literal>pg_read_all_stats</literal> role, or is a
+ member of the role that owns the backend.
</para></entry>
</row>
@@ -5834,6 +5839,11 @@ description | Waiting for a newly initialized WAL file to reach durable storage
<para>
The function does not return lock statistics for the checkpointer,
the background writer, the startup process and the autovacuum launcher.
+ </para>
+ <para>
+ This function returns no rows unless the caller is a superuser, has
+ privileges of the <literal>pg_read_all_stats</literal> role, or is a
+ member of the role that owns the backend.
</para></entry>
</row>
@@ -5853,6 +5863,11 @@ description | Waiting for a newly initialized WAL file to reach durable storage
<para>
The function does not return WAL statistics for the checkpointer,
the background writer, the startup process and the autovacuum launcher.
+ </para>
+ <para>
+ This function returns NULL unless the caller is a superuser, has
+ privileges of the <literal>pg_read_all_stats</literal> role, or is a
+ member of the role that owns the backend.
</para></entry>
</row>
@@ -6211,6 +6226,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">
diff --git a/src/backend/utils/activity/pgstat_backend.c b/src/backend/utils/activity/pgstat_backend.c
index b736b2ccc6f..59bc7e699b5 100644
--- a/src/backend/utils/activity/pgstat_backend.c
+++ b/src/backend/utils/activity/pgstat_backend.c
@@ -140,10 +140,12 @@ pgstat_fetch_stat_backend(ProcNumber procNumber)
*
* This routine includes sanity checks to ensure that the backend exists and
* is running. "bktype" can be optionally defined to return the BackendType
- * of the backend whose statistics are returned.
+ * of the backend whose statistics are returned. "userid" can be optionally
+ * defined to return the OID of the role that owns the backend, for callers
+ * that need to check whether they are allowed to report its statistics.
*/
PgStat_Backend *
-pgstat_fetch_stat_backend_by_pid(int pid, BackendType *bktype)
+pgstat_fetch_stat_backend_by_pid(int pid, BackendType *bktype, Oid *userid)
{
PGPROC *proc;
PgBackendStatus *beentry;
@@ -153,6 +155,8 @@ pgstat_fetch_stat_backend_by_pid(int pid, BackendType *bktype)
proc = BackendPidGetProc(pid);
if (bktype)
*bktype = B_INVALID;
+ if (userid)
+ *userid = InvalidOid;
/* this could be an auxiliary process */
if (!proc)
@@ -177,6 +181,8 @@ pgstat_fetch_stat_backend_by_pid(int pid, BackendType *bktype)
if (bktype)
*bktype = beentry->st_backendType;
+ if (userid)
+ *userid = beentry->st_userid;
/*
* Retrieve the entry. Note that "beentry" may be freed depending on the
@@ -187,6 +193,8 @@ pgstat_fetch_stat_backend_by_pid(int pid, BackendType *bktype)
{
if (bktype)
*bktype = B_INVALID;
+ if (userid)
+ *userid = InvalidOid;
return NULL;
}
diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c
index 081cd006666..f9fc3b65b2a 100644
--- a/src/backend/utils/adt/pgstatfuncs.c
+++ b/src/backend/utils/adt/pgstatfuncs.c
@@ -832,13 +832,15 @@ pg_stat_get_backend_subxact(PG_FUNCTION_ARGS)
tupdesc = CreateTemplateTupleDesc(PG_STAT_GET_SUBXACT_COLS);
TupleDescInitEntry(tupdesc, (AttrNumber) 1, "subxact_count",
INT4OID, -1, 0);
- TupleDescInitEntry(tupdesc, (AttrNumber) 2, "subxact_overflow",
+ TupleDescInitEntry(tupdesc, (AttrNumber) 2, "subxact_overflowed",
BOOLOID, -1, 0);
TupleDescFinalize(tupdesc);
BlessTupleDesc(tupdesc);
- if ((local_beentry = pgstat_get_local_beentry_by_proc_number(procNumber)) != NULL)
+ /* Report the details of a session only to a caller 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);
@@ -1673,6 +1675,7 @@ pg_stat_get_backend_io(PG_FUNCTION_ARGS)
ReturnSetInfo *rsinfo;
BackendType bktype;
int pid;
+ Oid userid;
PgStat_Backend *backend_stats;
PgStat_BktypeIO *bktype_stats;
@@ -1680,9 +1683,10 @@ pg_stat_get_backend_io(PG_FUNCTION_ARGS)
rsinfo = (ReturnSetInfo *) fcinfo->resultinfo;
pid = PG_GETARG_INT32(0);
- backend_stats = pgstat_fetch_stat_backend_by_pid(pid, &bktype);
+ backend_stats = pgstat_fetch_stat_backend_by_pid(pid, &bktype, &userid);
- if (!backend_stats)
+ /* Report the details of a session only to a caller allowed to see them */
+ if (!backend_stats || !HAS_PGSTAT_PERMISSIONS(userid))
return (Datum) 0;
bktype_stats = &backend_stats->io_stats;
@@ -1769,13 +1773,15 @@ Datum
pg_stat_get_backend_wal(PG_FUNCTION_ARGS)
{
int pid;
+ Oid userid;
PgStat_Backend *backend_stats;
PgStat_WalCounters bktype_stats;
pid = PG_GETARG_INT32(0);
- backend_stats = pgstat_fetch_stat_backend_by_pid(pid, NULL);
+ backend_stats = pgstat_fetch_stat_backend_by_pid(pid, NULL, &userid);
- if (!backend_stats)
+ /* Report the details of a session only to a caller allowed to see them */
+ if (!backend_stats || !HAS_PGSTAT_PERMISSIONS(userid))
PG_RETURN_NULL();
bktype_stats = backend_stats->wal_counters;
@@ -1857,6 +1863,7 @@ Datum
pg_stat_get_backend_lock(PG_FUNCTION_ARGS)
{
int pid;
+ Oid userid;
ReturnSetInfo *rsinfo;
PgStat_Backend *backend_stats;
@@ -1864,9 +1871,10 @@ pg_stat_get_backend_lock(PG_FUNCTION_ARGS)
rsinfo = (ReturnSetInfo *) fcinfo->resultinfo;
pid = PG_GETARG_INT32(0);
- backend_stats = pgstat_fetch_stat_backend_by_pid(pid, NULL);
+ backend_stats = pgstat_fetch_stat_backend_by_pid(pid, NULL, &userid);
- if (!backend_stats)
+ /* Report the details of a session only to a caller allowed to see them */
+ if (!backend_stats || !HAS_PGSTAT_PERMISSIONS(userid))
return (Datum) 0;
pg_stat_lock_build_tuples(rsinfo, backend_stats->lock_stats.stats,
diff --git a/src/include/pgstat.h b/src/include/pgstat.h
index 187d82c96fe..4c3dcc03df5 100644
--- a/src/include/pgstat.h
+++ b/src/include/pgstat.h
@@ -671,7 +671,8 @@ extern void pgstat_count_backend_lock_fastpath_exceeded(uint8 locktag_type);
extern PgStat_Backend *pgstat_fetch_stat_backend(ProcNumber procNumber);
extern PgStat_Backend *pgstat_fetch_stat_backend_by_pid(int pid,
- BackendType *bktype);
+ BackendType *bktype,
+ Oid *userid);
extern bool pgstat_tracks_backend_bktype(BackendType bktype);
extern void pgstat_create_backend(ProcNumber procnum);
diff --git a/src/test/regress/expected/stats.out b/src/test/regress/expected/stats.out
index 8b15471248b..a1ad1e86f64 100644
--- a/src/test/regress/expected/stats.out
+++ b/src/test/regress/expected/stats.out
@@ -1141,6 +1141,60 @@ WHERE pg_stat_get_backend_pid(beid) = pg_backend_pid();
t
(1 row)
+-- The per-backend statistics functions report the details of a session only
+-- to a caller that is allowed to see them: a superuser, a role with
+-- privileges of pg_read_all_stats, or the role that owns the session.
+SELECT beid FROM pg_stat_get_backend_idset() beid
+WHERE pg_stat_get_backend_pid(beid) = pg_backend_pid() \gset
+CREATE ROLE regress_stat_backend_role;
+-- the role that owns this backend sees the statistics
+SELECT (SELECT subxact_count IS NOT NULL
+ FROM pg_stat_get_backend_subxact(:beid)) AS subxact,
+ (SELECT count(*) > 0
+ FROM pg_stat_get_backend_io(pg_backend_pid())) AS io,
+ (SELECT count(*) > 0
+ FROM pg_stat_get_backend_lock(pg_backend_pid())) AS locks,
+ (SELECT wal_records IS NOT NULL
+ FROM pg_stat_get_backend_wal(pg_backend_pid())) AS wal;
+ subxact | io | locks | wal
+---------+----+-------+-----
+ t | t | t | t
+(1 row)
+
+SET ROLE regress_stat_backend_role;
+-- an unrelated role sees nothing
+SELECT (SELECT subxact_count IS NOT NULL
+ FROM pg_stat_get_backend_subxact(:beid)) AS subxact,
+ (SELECT count(*) > 0
+ FROM pg_stat_get_backend_io(pg_backend_pid())) AS io,
+ (SELECT count(*) > 0
+ FROM pg_stat_get_backend_lock(pg_backend_pid())) AS locks,
+ (SELECT wal_records IS NOT NULL
+ FROM pg_stat_get_backend_wal(pg_backend_pid())) AS wal;
+ subxact | io | locks | wal
+---------+----+-------+-----
+ f | f | f | f
+(1 row)
+
+RESET ROLE;
+-- but a role with privileges of pg_read_all_stats sees them again
+GRANT pg_read_all_stats TO regress_stat_backend_role;
+SET ROLE regress_stat_backend_role;
+SELECT (SELECT subxact_count IS NOT NULL
+ FROM pg_stat_get_backend_subxact(:beid)) AS subxact,
+ (SELECT count(*) > 0
+ FROM pg_stat_get_backend_io(pg_backend_pid())) AS io,
+ (SELECT count(*) > 0
+ FROM pg_stat_get_backend_lock(pg_backend_pid())) AS locks,
+ (SELECT wal_records IS NOT NULL
+ FROM pg_stat_get_backend_wal(pg_backend_pid())) AS wal;
+ subxact | io | locks | wal
+---------+----+-------+-----
+ t | t | t | t
+(1 row)
+
+RESET ROLE;
+DROP ROLE regress_stat_backend_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..bd9b56aaa6f 100644
--- a/src/test/regress/sql/stats.sql
+++ b/src/test/regress/sql/stats.sql
@@ -535,6 +535,46 @@ 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();
+-- The per-backend statistics functions report the details of a session only
+-- to a caller that is allowed to see them: a superuser, a role with
+-- privileges of pg_read_all_stats, or the role that owns the session.
+SELECT beid FROM pg_stat_get_backend_idset() beid
+WHERE pg_stat_get_backend_pid(beid) = pg_backend_pid() \gset
+CREATE ROLE regress_stat_backend_role;
+-- the role that owns this backend sees the statistics
+SELECT (SELECT subxact_count IS NOT NULL
+ FROM pg_stat_get_backend_subxact(:beid)) AS subxact,
+ (SELECT count(*) > 0
+ FROM pg_stat_get_backend_io(pg_backend_pid())) AS io,
+ (SELECT count(*) > 0
+ FROM pg_stat_get_backend_lock(pg_backend_pid())) AS locks,
+ (SELECT wal_records IS NOT NULL
+ FROM pg_stat_get_backend_wal(pg_backend_pid())) AS wal;
+SET ROLE regress_stat_backend_role;
+-- an unrelated role sees nothing
+SELECT (SELECT subxact_count IS NOT NULL
+ FROM pg_stat_get_backend_subxact(:beid)) AS subxact,
+ (SELECT count(*) > 0
+ FROM pg_stat_get_backend_io(pg_backend_pid())) AS io,
+ (SELECT count(*) > 0
+ FROM pg_stat_get_backend_lock(pg_backend_pid())) AS locks,
+ (SELECT wal_records IS NOT NULL
+ FROM pg_stat_get_backend_wal(pg_backend_pid())) AS wal;
+RESET ROLE;
+-- but a role with privileges of pg_read_all_stats sees them again
+GRANT pg_read_all_stats TO regress_stat_backend_role;
+SET ROLE regress_stat_backend_role;
+SELECT (SELECT subxact_count IS NOT NULL
+ FROM pg_stat_get_backend_subxact(:beid)) AS subxact,
+ (SELECT count(*) > 0
+ FROM pg_stat_get_backend_io(pg_backend_pid())) AS io,
+ (SELECT count(*) > 0
+ FROM pg_stat_get_backend_lock(pg_backend_pid())) AS locks,
+ (SELECT wal_records IS NOT NULL
+ FROM pg_stat_get_backend_wal(pg_backend_pid())) AS wal;
+RESET ROLE;
+DROP ROLE regress_stat_backend_role;
+
-----
-- Test that resetting stats works for reset timestamp
-----
--
2.55.0
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: Add a permission check to pg_stat_get_backend_subxact()
@ 2026-09-12 00:05 Michael Paquier <michael@paquier.xyz>
parent: Jim Jones <jim.jones@uni-muenster.de>
0 siblings, 1 reply; 21+ messages in thread
From: Michael Paquier @ 2026-09-12 00:05 UTC (permalink / raw)
To: Jim Jones <jim.jones@uni-muenster.de>; +Cc: shihao zhong <zhong950419@gmail.com>; pgsql-hackers <pgsql-hackers@lists.postgresql.org>
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
Attachments:
[application/pgp-signature] signature.asc (832B, ../../aqSXMjhwFFzz_zyo@paquier.xyz/2-signature.asc)
download
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: Add a permission check to pg_stat_get_backend_subxact()
@ 2026-09-12 12:43 shihao zhong <zhong950419@gmail.com>
parent: Michael Paquier <michael@paquier.xyz>
0 siblings, 1 reply; 21+ messages in thread
From: shihao zhong @ 2026-09-12 12:43 UTC (permalink / raw)
To: Michael Paquier <michael@paquier.xyz>; +Cc: Jim Jones <jim.jones@uni-muenster.de>; pgsql-hackers <pgsql-hackers@lists.postgresql.org>
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:
[application/octet-stream] v4-0002-Make-per-backend-statistics-functions-respect-sta.patch (15.4K, ../../CAGRkXqRGVNvvkY7a7X=3Bn=WgfLuFZ5iVsBf+t6HSfm2RwVC+g@mail.gmail.com/3-v4-0002-Make-per-backend-statistics-functions-respect-sta.patch)
download | inline diff:
From 37b5d8be55e718c11c19d588b8bb684512d2b034 Mon Sep 17 00:00:00 2001
From: Shihao Zhong <zhong950419@gmail.com>
Date: Fri, 11 Sep 2026 12:26:11 +0200
Subject: [PATCH v4 2/2] Make per-backend statistics functions respect
statistics permissions
The per-backend statistics functions that report the details of a session
call HAS_PGSTAT_PERMISSIONS() first, so that they hide these details from
a caller that is neither a superuser, nor has privileges of
pg_read_all_stats, nor is a member of the role that owns the session.
pg_stat_get_backend_subxact(), pg_stat_get_backend_io(),
pg_stat_get_backend_wal() and pg_stat_get_backend_lock() lacked this
check. Add it, for consistency with the sibling functions. Like
pg_stat_activity, this also hides the statistics of processes owned by
no role, such as autovacuum workers or the WAL writer, from callers
without these privileges.
The last three look up a backend by PID and have no backend status entry
at hand, so pgstat_fetch_stat_backend_by_pid() gains an optional "userid"
output argument, next to the existing "bktype" one, returning the OID of
the role that owns the backend.
The permission rule was documented for the dynamic statistics views but
not for these functions, so state it above the per-backend function table
and on the three functions listed among the additional statistics
functions. While on it, correct the name of the subxact_overflowed
column in the tuple descriptor built by pg_stat_get_backend_subxact().
This changes the output of existing functions for callers lacking the
required privileges, so no backpatch is done.
Author: Shihao Zhong <zhong950419@gmail.com>
Author: Jim Jones <jim.jones@uni-muenster.de>
Reviewed-by: Michael Paquier <michael@paquier.xyz>
Discussion: https://postgr.es/m/CAGRkXqTBZ+zbVuDC8xGEB6Btj61hsui5H5nGqzFBDyOXc=4bjQ@mail.gmail.com
---
doc/src/sgml/monitoring.sgml | 25 +++++++++
src/backend/utils/activity/pgstat_backend.c | 12 ++++-
src/backend/utils/adt/pgstatfuncs.c | 24 ++++++---
src/include/pgstat.h | 3 +-
src/test/regress/expected/stats.out | 59 +++++++++++++++++++++
src/test/regress/sql/stats.sql | 45 ++++++++++++++++
6 files changed, 157 insertions(+), 11 deletions(-)
diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml
index 2e3f57b5657..86b63319340 100644
--- a/doc/src/sgml/monitoring.sgml
+++ b/doc/src/sgml/monitoring.sgml
@@ -5799,6 +5799,11 @@ description | Waiting for a newly initialized WAL file to reach durable storage
the background writer, the startup process and the autovacuum launcher
as they are already visible in the <structname>pg_stat_io</structname>
view and there is only one of each.
+ </para>
+ <para>
+ This function returns no rows unless the caller is a superuser, has
+ privileges of the <literal>pg_read_all_stats</literal> role, or is a
+ member of the role that owns the backend.
</para></entry>
</row>
@@ -5834,6 +5839,11 @@ description | Waiting for a newly initialized WAL file to reach durable storage
<para>
The function does not return lock statistics for the checkpointer,
the background writer, the startup process and the autovacuum launcher.
+ </para>
+ <para>
+ This function returns no rows unless the caller is a superuser, has
+ privileges of the <literal>pg_read_all_stats</literal> role, or is a
+ member of the role that owns the backend.
</para></entry>
</row>
@@ -5853,6 +5863,11 @@ description | Waiting for a newly initialized WAL file to reach durable storage
<para>
The function does not return WAL statistics for the checkpointer,
the background writer, the startup process and the autovacuum launcher.
+ </para>
+ <para>
+ This function returns NULL unless the caller is a superuser, has
+ privileges of the <literal>pg_read_all_stats</literal> role, or is a
+ member of the role that owns the backend.
</para></entry>
</row>
@@ -6211,6 +6226,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 details of a session's activity are only shown if
+ 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">
diff --git a/src/backend/utils/activity/pgstat_backend.c b/src/backend/utils/activity/pgstat_backend.c
index b736b2ccc6f..59bc7e699b5 100644
--- a/src/backend/utils/activity/pgstat_backend.c
+++ b/src/backend/utils/activity/pgstat_backend.c
@@ -140,10 +140,12 @@ pgstat_fetch_stat_backend(ProcNumber procNumber)
*
* This routine includes sanity checks to ensure that the backend exists and
* is running. "bktype" can be optionally defined to return the BackendType
- * of the backend whose statistics are returned.
+ * of the backend whose statistics are returned. "userid" can be optionally
+ * defined to return the OID of the role that owns the backend, for callers
+ * that need to check whether they are allowed to report its statistics.
*/
PgStat_Backend *
-pgstat_fetch_stat_backend_by_pid(int pid, BackendType *bktype)
+pgstat_fetch_stat_backend_by_pid(int pid, BackendType *bktype, Oid *userid)
{
PGPROC *proc;
PgBackendStatus *beentry;
@@ -153,6 +155,8 @@ pgstat_fetch_stat_backend_by_pid(int pid, BackendType *bktype)
proc = BackendPidGetProc(pid);
if (bktype)
*bktype = B_INVALID;
+ if (userid)
+ *userid = InvalidOid;
/* this could be an auxiliary process */
if (!proc)
@@ -177,6 +181,8 @@ pgstat_fetch_stat_backend_by_pid(int pid, BackendType *bktype)
if (bktype)
*bktype = beentry->st_backendType;
+ if (userid)
+ *userid = beentry->st_userid;
/*
* Retrieve the entry. Note that "beentry" may be freed depending on the
@@ -187,6 +193,8 @@ pgstat_fetch_stat_backend_by_pid(int pid, BackendType *bktype)
{
if (bktype)
*bktype = B_INVALID;
+ if (userid)
+ *userid = InvalidOid;
return NULL;
}
diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c
index 081cd006666..f9fc3b65b2a 100644
--- a/src/backend/utils/adt/pgstatfuncs.c
+++ b/src/backend/utils/adt/pgstatfuncs.c
@@ -832,13 +832,15 @@ pg_stat_get_backend_subxact(PG_FUNCTION_ARGS)
tupdesc = CreateTemplateTupleDesc(PG_STAT_GET_SUBXACT_COLS);
TupleDescInitEntry(tupdesc, (AttrNumber) 1, "subxact_count",
INT4OID, -1, 0);
- TupleDescInitEntry(tupdesc, (AttrNumber) 2, "subxact_overflow",
+ TupleDescInitEntry(tupdesc, (AttrNumber) 2, "subxact_overflowed",
BOOLOID, -1, 0);
TupleDescFinalize(tupdesc);
BlessTupleDesc(tupdesc);
- if ((local_beentry = pgstat_get_local_beentry_by_proc_number(procNumber)) != NULL)
+ /* Report the details of a session only to a caller 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);
@@ -1673,6 +1675,7 @@ pg_stat_get_backend_io(PG_FUNCTION_ARGS)
ReturnSetInfo *rsinfo;
BackendType bktype;
int pid;
+ Oid userid;
PgStat_Backend *backend_stats;
PgStat_BktypeIO *bktype_stats;
@@ -1680,9 +1683,10 @@ pg_stat_get_backend_io(PG_FUNCTION_ARGS)
rsinfo = (ReturnSetInfo *) fcinfo->resultinfo;
pid = PG_GETARG_INT32(0);
- backend_stats = pgstat_fetch_stat_backend_by_pid(pid, &bktype);
+ backend_stats = pgstat_fetch_stat_backend_by_pid(pid, &bktype, &userid);
- if (!backend_stats)
+ /* Report the details of a session only to a caller allowed to see them */
+ if (!backend_stats || !HAS_PGSTAT_PERMISSIONS(userid))
return (Datum) 0;
bktype_stats = &backend_stats->io_stats;
@@ -1769,13 +1773,15 @@ Datum
pg_stat_get_backend_wal(PG_FUNCTION_ARGS)
{
int pid;
+ Oid userid;
PgStat_Backend *backend_stats;
PgStat_WalCounters bktype_stats;
pid = PG_GETARG_INT32(0);
- backend_stats = pgstat_fetch_stat_backend_by_pid(pid, NULL);
+ backend_stats = pgstat_fetch_stat_backend_by_pid(pid, NULL, &userid);
- if (!backend_stats)
+ /* Report the details of a session only to a caller allowed to see them */
+ if (!backend_stats || !HAS_PGSTAT_PERMISSIONS(userid))
PG_RETURN_NULL();
bktype_stats = backend_stats->wal_counters;
@@ -1857,6 +1863,7 @@ Datum
pg_stat_get_backend_lock(PG_FUNCTION_ARGS)
{
int pid;
+ Oid userid;
ReturnSetInfo *rsinfo;
PgStat_Backend *backend_stats;
@@ -1864,9 +1871,10 @@ pg_stat_get_backend_lock(PG_FUNCTION_ARGS)
rsinfo = (ReturnSetInfo *) fcinfo->resultinfo;
pid = PG_GETARG_INT32(0);
- backend_stats = pgstat_fetch_stat_backend_by_pid(pid, NULL);
+ backend_stats = pgstat_fetch_stat_backend_by_pid(pid, NULL, &userid);
- if (!backend_stats)
+ /* Report the details of a session only to a caller allowed to see them */
+ if (!backend_stats || !HAS_PGSTAT_PERMISSIONS(userid))
return (Datum) 0;
pg_stat_lock_build_tuples(rsinfo, backend_stats->lock_stats.stats,
diff --git a/src/include/pgstat.h b/src/include/pgstat.h
index 187d82c96fe..4c3dcc03df5 100644
--- a/src/include/pgstat.h
+++ b/src/include/pgstat.h
@@ -671,7 +671,8 @@ extern void pgstat_count_backend_lock_fastpath_exceeded(uint8 locktag_type);
extern PgStat_Backend *pgstat_fetch_stat_backend(ProcNumber procNumber);
extern PgStat_Backend *pgstat_fetch_stat_backend_by_pid(int pid,
- BackendType *bktype);
+ BackendType *bktype,
+ Oid *userid);
extern bool pgstat_tracks_backend_bktype(BackendType bktype);
extern void pgstat_create_backend(ProcNumber procnum);
diff --git a/src/test/regress/expected/stats.out b/src/test/regress/expected/stats.out
index 8b15471248b..fc1870bfd4b 100644
--- a/src/test/regress/expected/stats.out
+++ b/src/test/regress/expected/stats.out
@@ -1141,6 +1141,65 @@ WHERE pg_stat_get_backend_pid(beid) = pg_backend_pid();
t
(1 row)
+-- The per-backend statistics functions report the details of a session only
+-- to a caller that is allowed to see them: a superuser, a role with
+-- privileges of pg_read_all_stats, or the role that owns the session.
+SELECT beid FROM pg_stat_get_backend_idset() beid
+WHERE pg_stat_get_backend_pid(beid) = pg_backend_pid() \gset
+SELECT current_user AS regress_stat_backend_owner \gset
+CREATE ROLE regress_stat_backend_role;
+-- a role with privileges of the role that owns this backend sees them
+GRANT :"regress_stat_backend_owner" TO regress_stat_backend_role;
+SET ROLE regress_stat_backend_role;
+SELECT (SELECT subxact_count IS NOT NULL
+ FROM pg_stat_get_backend_subxact(:beid)) AS subxact,
+ (SELECT count(*) > 0
+ FROM pg_stat_get_backend_io(pg_backend_pid())) AS io,
+ (SELECT count(*) > 0
+ FROM pg_stat_get_backend_lock(pg_backend_pid())) AS locks,
+ (SELECT wal_records IS NOT NULL
+ FROM pg_stat_get_backend_wal(pg_backend_pid())) AS wal;
+ subxact | io | locks | wal
+---------+----+-------+-----
+ t | t | t | t
+(1 row)
+
+RESET ROLE;
+REVOKE :"regress_stat_backend_owner" FROM regress_stat_backend_role;
+SET ROLE regress_stat_backend_role;
+-- an unrelated role sees nothing
+SELECT (SELECT subxact_count IS NOT NULL
+ FROM pg_stat_get_backend_subxact(:beid)) AS subxact,
+ (SELECT count(*) > 0
+ FROM pg_stat_get_backend_io(pg_backend_pid())) AS io,
+ (SELECT count(*) > 0
+ FROM pg_stat_get_backend_lock(pg_backend_pid())) AS locks,
+ (SELECT wal_records IS NOT NULL
+ FROM pg_stat_get_backend_wal(pg_backend_pid())) AS wal;
+ subxact | io | locks | wal
+---------+----+-------+-----
+ f | f | f | f
+(1 row)
+
+RESET ROLE;
+-- but a role with privileges of pg_read_all_stats sees them again
+GRANT pg_read_all_stats TO regress_stat_backend_role;
+SET ROLE regress_stat_backend_role;
+SELECT (SELECT subxact_count IS NOT NULL
+ FROM pg_stat_get_backend_subxact(:beid)) AS subxact,
+ (SELECT count(*) > 0
+ FROM pg_stat_get_backend_io(pg_backend_pid())) AS io,
+ (SELECT count(*) > 0
+ FROM pg_stat_get_backend_lock(pg_backend_pid())) AS locks,
+ (SELECT wal_records IS NOT NULL
+ FROM pg_stat_get_backend_wal(pg_backend_pid())) AS wal;
+ subxact | io | locks | wal
+---------+----+-------+-----
+ t | t | t | t
+(1 row)
+
+RESET ROLE;
+DROP ROLE regress_stat_backend_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..17c8e2f5231 100644
--- a/src/test/regress/sql/stats.sql
+++ b/src/test/regress/sql/stats.sql
@@ -535,6 +535,51 @@ 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();
+-- The per-backend statistics functions report the details of a session only
+-- to a caller that is allowed to see them: a superuser, a role with
+-- privileges of pg_read_all_stats, or the role that owns the session.
+SELECT beid FROM pg_stat_get_backend_idset() beid
+WHERE pg_stat_get_backend_pid(beid) = pg_backend_pid() \gset
+SELECT current_user AS regress_stat_backend_owner \gset
+CREATE ROLE regress_stat_backend_role;
+-- a role with privileges of the role that owns this backend sees them
+GRANT :"regress_stat_backend_owner" TO regress_stat_backend_role;
+SET ROLE regress_stat_backend_role;
+SELECT (SELECT subxact_count IS NOT NULL
+ FROM pg_stat_get_backend_subxact(:beid)) AS subxact,
+ (SELECT count(*) > 0
+ FROM pg_stat_get_backend_io(pg_backend_pid())) AS io,
+ (SELECT count(*) > 0
+ FROM pg_stat_get_backend_lock(pg_backend_pid())) AS locks,
+ (SELECT wal_records IS NOT NULL
+ FROM pg_stat_get_backend_wal(pg_backend_pid())) AS wal;
+RESET ROLE;
+REVOKE :"regress_stat_backend_owner" FROM regress_stat_backend_role;
+SET ROLE regress_stat_backend_role;
+-- an unrelated role sees nothing
+SELECT (SELECT subxact_count IS NOT NULL
+ FROM pg_stat_get_backend_subxact(:beid)) AS subxact,
+ (SELECT count(*) > 0
+ FROM pg_stat_get_backend_io(pg_backend_pid())) AS io,
+ (SELECT count(*) > 0
+ FROM pg_stat_get_backend_lock(pg_backend_pid())) AS locks,
+ (SELECT wal_records IS NOT NULL
+ FROM pg_stat_get_backend_wal(pg_backend_pid())) AS wal;
+RESET ROLE;
+-- but a role with privileges of pg_read_all_stats sees them again
+GRANT pg_read_all_stats TO regress_stat_backend_role;
+SET ROLE regress_stat_backend_role;
+SELECT (SELECT subxact_count IS NOT NULL
+ FROM pg_stat_get_backend_subxact(:beid)) AS subxact,
+ (SELECT count(*) > 0
+ FROM pg_stat_get_backend_io(pg_backend_pid())) AS io,
+ (SELECT count(*) > 0
+ FROM pg_stat_get_backend_lock(pg_backend_pid())) AS locks,
+ (SELECT wal_records IS NOT NULL
+ FROM pg_stat_get_backend_wal(pg_backend_pid())) AS wal;
+RESET ROLE;
+DROP ROLE regress_stat_backend_role;
+
-----
-- Test that resetting stats works for reset timestamp
-----
--
2.37.1 (Apple Git-137.1)
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: Add a permission check to pg_stat_get_backend_subxact()
@ 2026-09-14 07:06 Michael Paquier <michael@paquier.xyz>
parent: shihao zhong <zhong950419@gmail.com>
0 siblings, 1 reply; 21+ messages in thread
From: Michael Paquier @ 2026-09-14 07:06 UTC (permalink / raw)
To: shihao zhong <zhong950419@gmail.com>; +Cc: Jim Jones <jim.jones@uni-muenster.de>; pgsql-hackers <pgsql-hackers@lists.postgresql.org>; Bertrand Drouvot <bertranddrouvot.pg@gmail.com>
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
Attachments:
[application/pgp-signature] signature.asc (832B, ../../aqec3MF_GDsMdadl@paquier.xyz/2-signature.asc)
download
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: Add a permission check to pg_stat_get_backend_subxact()
@ 2026-09-21 09:58 Bertrand Drouvot <bertranddrouvot.pg@gmail.com>
parent: Michael Paquier <michael@paquier.xyz>
0 siblings, 2 replies; 21+ messages in thread
From: Bertrand Drouvot @ 2026-09-21 09:58 UTC (permalink / raw)
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>
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
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: Add a permission check to pg_stat_get_backend_subxact()
@ 2026-09-21 23:54 shihao zhong <zhong950419@gmail.com>
parent: Bertrand Drouvot <bertranddrouvot.pg@gmail.com>
1 sibling, 1 reply; 21+ messages in thread
From: shihao zhong @ 2026-09-21 23:54 UTC (permalink / raw)
To: Bertrand Drouvot <bertranddrouvot.pg@gmail.com>; +Cc: Michael Paquier <michael@paquier.xyz>; Jim Jones <jim.jones@uni-muenster.de>; pgsql-hackers <pgsql-hackers@lists.postgresql.org>
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:
> 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:
[application/octet-stream] v5-0001-Make-per-backend-statistics-functions-respect-sta.patch (15.1K, ../../CAGRkXqRLvDk1HuAsJiSOmiJjFchg5Ax-vKF=M7=CxtS-vYvdRQ@mail.gmail.com/3-v5-0001-Make-per-backend-statistics-functions-respect-sta.patch)
download | inline diff:
From 463177f46b9e40c82f51dc36ec5a264ba58e20e9 Mon Sep 17 00:00:00 2001
From: Shihao Zhong <zhong950419@gmail.com>
Date: Fri, 11 Sep 2026 12:26:11 +0200
Subject: [PATCH v5 1/2] Make per-backend statistics functions respect
statistics permissions
The per-backend statistics functions that report the details of a session
call HAS_PGSTAT_PERMISSIONS() first, so that they hide these details from
a caller that is neither a superuser, nor has privileges of
pg_read_all_stats, nor is a member of the role that owns the session.
pg_stat_get_backend_subxact(), pg_stat_get_backend_io(),
pg_stat_get_backend_wal() and pg_stat_get_backend_lock() lacked this
check. Add it, for consistency with the sibling functions. Like
pg_stat_activity, this also hides the statistics of processes owned by
no role, such as autovacuum workers or the WAL writer, from callers
without these privileges.
The last three look up a backend by PID and have no backend status entry
at hand, so pgstat_fetch_stat_backend_by_pid() gains an optional "userid"
output argument, next to the existing "bktype" one, returning the OID of
the role that owns the backend.
The permission rule was documented for the dynamic statistics views but
not for these functions, so state it above the per-backend function table
and on the three functions listed among the additional statistics
functions. While on it, correct the name of the subxact_overflowed
column in the tuple descriptor built by pg_stat_get_backend_subxact().
This changes the output of existing functions for callers lacking the
required privileges, so no backpatch is done.
Author: Shihao Zhong <zhong950419@gmail.com>
Author: Jim Jones <jim.jones@uni-muenster.de>
Reviewed-by: Michael Paquier <michael@paquier.xyz>
Discussion: https://postgr.es/m/CAGRkXqTBZ+zbVuDC8xGEB6Btj61hsui5H5nGqzFBDyOXc=4bjQ@mail.gmail.com
---
doc/src/sgml/monitoring.sgml | 25 +++++++++
src/backend/utils/activity/pgstat_backend.c | 12 ++++-
src/backend/utils/adt/pgstatfuncs.c | 22 +++++---
src/include/pgstat.h | 3 +-
src/test/regress/expected/stats.out | 59 +++++++++++++++++++++
src/test/regress/sql/stats.sql | 45 ++++++++++++++++
6 files changed, 156 insertions(+), 10 deletions(-)
diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml
index 62dadf3e86c..78f224bf9e8 100644
--- a/doc/src/sgml/monitoring.sgml
+++ b/doc/src/sgml/monitoring.sgml
@@ -5799,6 +5799,11 @@ description | Waiting for a newly initialized WAL file to reach durable storage
the background writer, the startup process and the autovacuum launcher
as they are already visible in the <structname>pg_stat_io</structname>
view and there is only one of each.
+ </para>
+ <para>
+ This function returns no rows unless the caller is a superuser, has
+ privileges of the <literal>pg_read_all_stats</literal> role, or is a
+ member of the role that owns the backend.
</para></entry>
</row>
@@ -5834,6 +5839,11 @@ description | Waiting for a newly initialized WAL file to reach durable storage
<para>
The function does not return lock statistics for the checkpointer,
the background writer, the startup process and the autovacuum launcher.
+ </para>
+ <para>
+ This function returns no rows unless the caller is a superuser, has
+ privileges of the <literal>pg_read_all_stats</literal> role, or is a
+ member of the role that owns the backend.
</para></entry>
</row>
@@ -5853,6 +5863,11 @@ description | Waiting for a newly initialized WAL file to reach durable storage
<para>
The function does not return WAL statistics for the checkpointer,
the background writer, the startup process and the autovacuum launcher.
+ </para>
+ <para>
+ This function returns NULL unless the caller is a superuser, has
+ privileges of the <literal>pg_read_all_stats</literal> role, or is a
+ member of the role that owns the backend.
</para></entry>
</row>
@@ -6211,6 +6226,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 details of a session's activity are only shown if
+ 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">
diff --git a/src/backend/utils/activity/pgstat_backend.c b/src/backend/utils/activity/pgstat_backend.c
index b736b2ccc6f..59bc7e699b5 100644
--- a/src/backend/utils/activity/pgstat_backend.c
+++ b/src/backend/utils/activity/pgstat_backend.c
@@ -140,10 +140,12 @@ pgstat_fetch_stat_backend(ProcNumber procNumber)
*
* This routine includes sanity checks to ensure that the backend exists and
* is running. "bktype" can be optionally defined to return the BackendType
- * of the backend whose statistics are returned.
+ * of the backend whose statistics are returned. "userid" can be optionally
+ * defined to return the OID of the role that owns the backend, for callers
+ * that need to check whether they are allowed to report its statistics.
*/
PgStat_Backend *
-pgstat_fetch_stat_backend_by_pid(int pid, BackendType *bktype)
+pgstat_fetch_stat_backend_by_pid(int pid, BackendType *bktype, Oid *userid)
{
PGPROC *proc;
PgBackendStatus *beentry;
@@ -153,6 +155,8 @@ pgstat_fetch_stat_backend_by_pid(int pid, BackendType *bktype)
proc = BackendPidGetProc(pid);
if (bktype)
*bktype = B_INVALID;
+ if (userid)
+ *userid = InvalidOid;
/* this could be an auxiliary process */
if (!proc)
@@ -177,6 +181,8 @@ pgstat_fetch_stat_backend_by_pid(int pid, BackendType *bktype)
if (bktype)
*bktype = beentry->st_backendType;
+ if (userid)
+ *userid = beentry->st_userid;
/*
* Retrieve the entry. Note that "beentry" may be freed depending on the
@@ -187,6 +193,8 @@ pgstat_fetch_stat_backend_by_pid(int pid, BackendType *bktype)
{
if (bktype)
*bktype = B_INVALID;
+ if (userid)
+ *userid = InvalidOid;
return NULL;
}
diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c
index 64b6f60516c..f9fc3b65b2a 100644
--- a/src/backend/utils/adt/pgstatfuncs.c
+++ b/src/backend/utils/adt/pgstatfuncs.c
@@ -838,7 +838,9 @@ pg_stat_get_backend_subxact(PG_FUNCTION_ARGS)
TupleDescFinalize(tupdesc);
BlessTupleDesc(tupdesc);
- if ((local_beentry = pgstat_get_local_beentry_by_proc_number(procNumber)) != NULL)
+ /* Report the details of a session only to a caller 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);
@@ -1673,6 +1675,7 @@ pg_stat_get_backend_io(PG_FUNCTION_ARGS)
ReturnSetInfo *rsinfo;
BackendType bktype;
int pid;
+ Oid userid;
PgStat_Backend *backend_stats;
PgStat_BktypeIO *bktype_stats;
@@ -1680,9 +1683,10 @@ pg_stat_get_backend_io(PG_FUNCTION_ARGS)
rsinfo = (ReturnSetInfo *) fcinfo->resultinfo;
pid = PG_GETARG_INT32(0);
- backend_stats = pgstat_fetch_stat_backend_by_pid(pid, &bktype);
+ backend_stats = pgstat_fetch_stat_backend_by_pid(pid, &bktype, &userid);
- if (!backend_stats)
+ /* Report the details of a session only to a caller allowed to see them */
+ if (!backend_stats || !HAS_PGSTAT_PERMISSIONS(userid))
return (Datum) 0;
bktype_stats = &backend_stats->io_stats;
@@ -1769,13 +1773,15 @@ Datum
pg_stat_get_backend_wal(PG_FUNCTION_ARGS)
{
int pid;
+ Oid userid;
PgStat_Backend *backend_stats;
PgStat_WalCounters bktype_stats;
pid = PG_GETARG_INT32(0);
- backend_stats = pgstat_fetch_stat_backend_by_pid(pid, NULL);
+ backend_stats = pgstat_fetch_stat_backend_by_pid(pid, NULL, &userid);
- if (!backend_stats)
+ /* Report the details of a session only to a caller allowed to see them */
+ if (!backend_stats || !HAS_PGSTAT_PERMISSIONS(userid))
PG_RETURN_NULL();
bktype_stats = backend_stats->wal_counters;
@@ -1857,6 +1863,7 @@ Datum
pg_stat_get_backend_lock(PG_FUNCTION_ARGS)
{
int pid;
+ Oid userid;
ReturnSetInfo *rsinfo;
PgStat_Backend *backend_stats;
@@ -1864,9 +1871,10 @@ pg_stat_get_backend_lock(PG_FUNCTION_ARGS)
rsinfo = (ReturnSetInfo *) fcinfo->resultinfo;
pid = PG_GETARG_INT32(0);
- backend_stats = pgstat_fetch_stat_backend_by_pid(pid, NULL);
+ backend_stats = pgstat_fetch_stat_backend_by_pid(pid, NULL, &userid);
- if (!backend_stats)
+ /* Report the details of a session only to a caller allowed to see them */
+ if (!backend_stats || !HAS_PGSTAT_PERMISSIONS(userid))
return (Datum) 0;
pg_stat_lock_build_tuples(rsinfo, backend_stats->lock_stats.stats,
diff --git a/src/include/pgstat.h b/src/include/pgstat.h
index 187d82c96fe..4c3dcc03df5 100644
--- a/src/include/pgstat.h
+++ b/src/include/pgstat.h
@@ -671,7 +671,8 @@ extern void pgstat_count_backend_lock_fastpath_exceeded(uint8 locktag_type);
extern PgStat_Backend *pgstat_fetch_stat_backend(ProcNumber procNumber);
extern PgStat_Backend *pgstat_fetch_stat_backend_by_pid(int pid,
- BackendType *bktype);
+ BackendType *bktype,
+ Oid *userid);
extern bool pgstat_tracks_backend_bktype(BackendType bktype);
extern void pgstat_create_backend(ProcNumber procnum);
diff --git a/src/test/regress/expected/stats.out b/src/test/regress/expected/stats.out
index 8b15471248b..fc1870bfd4b 100644
--- a/src/test/regress/expected/stats.out
+++ b/src/test/regress/expected/stats.out
@@ -1141,6 +1141,65 @@ WHERE pg_stat_get_backend_pid(beid) = pg_backend_pid();
t
(1 row)
+-- The per-backend statistics functions report the details of a session only
+-- to a caller that is allowed to see them: a superuser, a role with
+-- privileges of pg_read_all_stats, or the role that owns the session.
+SELECT beid FROM pg_stat_get_backend_idset() beid
+WHERE pg_stat_get_backend_pid(beid) = pg_backend_pid() \gset
+SELECT current_user AS regress_stat_backend_owner \gset
+CREATE ROLE regress_stat_backend_role;
+-- a role with privileges of the role that owns this backend sees them
+GRANT :"regress_stat_backend_owner" TO regress_stat_backend_role;
+SET ROLE regress_stat_backend_role;
+SELECT (SELECT subxact_count IS NOT NULL
+ FROM pg_stat_get_backend_subxact(:beid)) AS subxact,
+ (SELECT count(*) > 0
+ FROM pg_stat_get_backend_io(pg_backend_pid())) AS io,
+ (SELECT count(*) > 0
+ FROM pg_stat_get_backend_lock(pg_backend_pid())) AS locks,
+ (SELECT wal_records IS NOT NULL
+ FROM pg_stat_get_backend_wal(pg_backend_pid())) AS wal;
+ subxact | io | locks | wal
+---------+----+-------+-----
+ t | t | t | t
+(1 row)
+
+RESET ROLE;
+REVOKE :"regress_stat_backend_owner" FROM regress_stat_backend_role;
+SET ROLE regress_stat_backend_role;
+-- an unrelated role sees nothing
+SELECT (SELECT subxact_count IS NOT NULL
+ FROM pg_stat_get_backend_subxact(:beid)) AS subxact,
+ (SELECT count(*) > 0
+ FROM pg_stat_get_backend_io(pg_backend_pid())) AS io,
+ (SELECT count(*) > 0
+ FROM pg_stat_get_backend_lock(pg_backend_pid())) AS locks,
+ (SELECT wal_records IS NOT NULL
+ FROM pg_stat_get_backend_wal(pg_backend_pid())) AS wal;
+ subxact | io | locks | wal
+---------+----+-------+-----
+ f | f | f | f
+(1 row)
+
+RESET ROLE;
+-- but a role with privileges of pg_read_all_stats sees them again
+GRANT pg_read_all_stats TO regress_stat_backend_role;
+SET ROLE regress_stat_backend_role;
+SELECT (SELECT subxact_count IS NOT NULL
+ FROM pg_stat_get_backend_subxact(:beid)) AS subxact,
+ (SELECT count(*) > 0
+ FROM pg_stat_get_backend_io(pg_backend_pid())) AS io,
+ (SELECT count(*) > 0
+ FROM pg_stat_get_backend_lock(pg_backend_pid())) AS locks,
+ (SELECT wal_records IS NOT NULL
+ FROM pg_stat_get_backend_wal(pg_backend_pid())) AS wal;
+ subxact | io | locks | wal
+---------+----+-------+-----
+ t | t | t | t
+(1 row)
+
+RESET ROLE;
+DROP ROLE regress_stat_backend_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..17c8e2f5231 100644
--- a/src/test/regress/sql/stats.sql
+++ b/src/test/regress/sql/stats.sql
@@ -535,6 +535,51 @@ 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();
+-- The per-backend statistics functions report the details of a session only
+-- to a caller that is allowed to see them: a superuser, a role with
+-- privileges of pg_read_all_stats, or the role that owns the session.
+SELECT beid FROM pg_stat_get_backend_idset() beid
+WHERE pg_stat_get_backend_pid(beid) = pg_backend_pid() \gset
+SELECT current_user AS regress_stat_backend_owner \gset
+CREATE ROLE regress_stat_backend_role;
+-- a role with privileges of the role that owns this backend sees them
+GRANT :"regress_stat_backend_owner" TO regress_stat_backend_role;
+SET ROLE regress_stat_backend_role;
+SELECT (SELECT subxact_count IS NOT NULL
+ FROM pg_stat_get_backend_subxact(:beid)) AS subxact,
+ (SELECT count(*) > 0
+ FROM pg_stat_get_backend_io(pg_backend_pid())) AS io,
+ (SELECT count(*) > 0
+ FROM pg_stat_get_backend_lock(pg_backend_pid())) AS locks,
+ (SELECT wal_records IS NOT NULL
+ FROM pg_stat_get_backend_wal(pg_backend_pid())) AS wal;
+RESET ROLE;
+REVOKE :"regress_stat_backend_owner" FROM regress_stat_backend_role;
+SET ROLE regress_stat_backend_role;
+-- an unrelated role sees nothing
+SELECT (SELECT subxact_count IS NOT NULL
+ FROM pg_stat_get_backend_subxact(:beid)) AS subxact,
+ (SELECT count(*) > 0
+ FROM pg_stat_get_backend_io(pg_backend_pid())) AS io,
+ (SELECT count(*) > 0
+ FROM pg_stat_get_backend_lock(pg_backend_pid())) AS locks,
+ (SELECT wal_records IS NOT NULL
+ FROM pg_stat_get_backend_wal(pg_backend_pid())) AS wal;
+RESET ROLE;
+-- but a role with privileges of pg_read_all_stats sees them again
+GRANT pg_read_all_stats TO regress_stat_backend_role;
+SET ROLE regress_stat_backend_role;
+SELECT (SELECT subxact_count IS NOT NULL
+ FROM pg_stat_get_backend_subxact(:beid)) AS subxact,
+ (SELECT count(*) > 0
+ FROM pg_stat_get_backend_io(pg_backend_pid())) AS io,
+ (SELECT count(*) > 0
+ FROM pg_stat_get_backend_lock(pg_backend_pid())) AS locks,
+ (SELECT wal_records IS NOT NULL
+ FROM pg_stat_get_backend_wal(pg_backend_pid())) AS wal;
+RESET ROLE;
+DROP ROLE regress_stat_backend_role;
+
-----
-- Test that resetting stats works for reset timestamp
-----
--
2.37.1 (Apple Git-137.1)
[application/octet-stream] v5-0002-Include-the-PID-in-the-key-of-backend-statistics-.patch (5.2K, ../../CAGRkXqRLvDk1HuAsJiSOmiJjFchg5Ax-vKF=M7=CxtS-vYvdRQ@mail.gmail.com/4-v5-0002-Include-the-PID-in-the-key-of-backend-statistics-.patch)
download | inline diff:
From c9bac6e4041afd91422a7df8ae92e120877930ef Mon Sep 17 00:00:00 2001
From: Shihao <zhong950419@gmail.com>
Date: Mon, 21 Sep 2026 19:19:02 -0400
Subject: [PATCH v5 2/2] Include the PID in the key of backend statistics
entries
With stats_fetch_consistency set to "snapshot", the statistics of a
backend could be reported under the PID of a newer backend that reused
its proc number. Make the PID part of the object ID of these entries,
so that an entry is only found with the PID of the backend it belongs to.
Reported-by: Bertrand Drouvot <bertranddrouvot.pg@gmail.com>
Author: Shihao Zhong <zhong950419@gmail.com>
Discussion: https://postgr.es/m/arD/w47Ug1GaObfq@bdtpg
---
src/backend/utils/activity/pgstat.c | 3 ++-
src/backend/utils/activity/pgstat_backend.c | 15 +++++++++------
src/backend/utils/adt/pgstatfuncs.c | 3 ++-
src/include/pgstat.h | 11 ++++++++++-
4 files changed, 23 insertions(+), 9 deletions(-)
diff --git a/src/backend/utils/activity/pgstat.c b/src/backend/utils/activity/pgstat.c
index 5177f880f70..0a7000844ec 100644
--- a/src/backend/utils/activity/pgstat.c
+++ b/src/backend/utils/activity/pgstat.c
@@ -666,7 +666,8 @@ pgstat_shutdown_hook(int code, Datum arg)
dlist_init(&pgStatPending);
/* drop the backend stats entry */
- if (!pgstat_drop_entry(PGSTAT_KIND_BACKEND, InvalidOid, MyProcNumber, false))
+ if (!pgstat_drop_entry(PGSTAT_KIND_BACKEND, InvalidOid,
+ PGSTAT_BACKEND_OBJID(MyProcPid, MyProcNumber), false))
pgstat_request_entry_refs_gc();
pgstat_detach_shmem();
diff --git a/src/backend/utils/activity/pgstat_backend.c b/src/backend/utils/activity/pgstat_backend.c
index 59bc7e699b5..b278cf03ef1 100644
--- a/src/backend/utils/activity/pgstat_backend.c
+++ b/src/backend/utils/activity/pgstat_backend.c
@@ -121,15 +121,16 @@ pgstat_count_backend_lock_fastpath_exceeded(uint8 locktag_type)
}
/*
- * Returns statistics of a backend by proc number.
+ * Returns statistics of a backend by PID and proc number.
*/
PgStat_Backend *
-pgstat_fetch_stat_backend(ProcNumber procNumber)
+pgstat_fetch_stat_backend(int pid, ProcNumber procNumber)
{
PgStat_Backend *backend_entry;
backend_entry = (PgStat_Backend *) pgstat_fetch_entry(PGSTAT_KIND_BACKEND,
- InvalidOid, procNumber,
+ InvalidOid,
+ PGSTAT_BACKEND_OBJID(pid, procNumber),
NULL);
return backend_entry;
@@ -188,7 +189,7 @@ pgstat_fetch_stat_backend_by_pid(int pid, BackendType *bktype, Oid *userid)
* Retrieve the entry. Note that "beentry" may be freed depending on the
* value of stats_fetch_consistency, so do not access it from this point.
*/
- backend_stats = pgstat_fetch_stat_backend(procNumber);
+ backend_stats = pgstat_fetch_stat_backend(pid, procNumber);
if (!backend_stats)
{
if (bktype)
@@ -366,7 +367,8 @@ pgstat_flush_backend(bool nowait, uint32 flags)
return false;
entry_ref = pgstat_get_entry_ref_locked(PGSTAT_KIND_BACKEND, InvalidOid,
- MyProcNumber, nowait);
+ PGSTAT_BACKEND_OBJID(MyProcPid, MyProcNumber),
+ nowait);
if (!entry_ref)
return true;
@@ -406,7 +408,8 @@ pgstat_create_backend(ProcNumber procnum)
PgStatShared_Backend *shstatent;
entry_ref = pgstat_get_entry_ref_locked(PGSTAT_KIND_BACKEND, InvalidOid,
- procnum, false);
+ PGSTAT_BACKEND_OBJID(MyProcPid, procnum),
+ false);
shstatent = (PgStatShared_Backend *) entry_ref->shared_stats;
/*
diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c
index f9fc3b65b2a..a84e263bf15 100644
--- a/src/backend/utils/adt/pgstatfuncs.c
+++ b/src/backend/utils/adt/pgstatfuncs.c
@@ -2204,7 +2204,8 @@ pg_stat_reset_backend_stats(PG_FUNCTION_ARGS)
if (!pgstat_tracks_backend_bktype(beentry->st_backendType))
PG_RETURN_VOID();
- pgstat_reset(PGSTAT_KIND_BACKEND, InvalidOid, procNumber);
+ pgstat_reset(PGSTAT_KIND_BACKEND, InvalidOid,
+ PGSTAT_BACKEND_OBJID(backend_pid, procNumber));
PG_RETURN_VOID();
}
diff --git a/src/include/pgstat.h b/src/include/pgstat.h
index 4c3dcc03df5..ba1c9b40768 100644
--- a/src/include/pgstat.h
+++ b/src/include/pgstat.h
@@ -665,11 +665,20 @@ extern void pgstat_count_backend_io_op(IOObject io_object,
IOOp io_op, uint32 cnt,
uint64 bytes);
+/*
+ * 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))
+
/* used by pgstat_lock.c for lock stats tracked in backends */
extern void pgstat_count_backend_lock_waits(uint8 locktag_type, PgStat_Counter usecs);
extern void pgstat_count_backend_lock_fastpath_exceeded(uint8 locktag_type);
-extern PgStat_Backend *pgstat_fetch_stat_backend(ProcNumber procNumber);
+extern PgStat_Backend *pgstat_fetch_stat_backend(int pid, ProcNumber procNumber);
extern PgStat_Backend *pgstat_fetch_stat_backend_by_pid(int pid,
BackendType *bktype,
Oid *userid);
--
2.37.1 (Apple Git-137.1)
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: Add a permission check to pg_stat_get_backend_subxact()
@ 2026-09-22 00:12 Michael Paquier <michael@paquier.xyz>
parent: shihao zhong <zhong950419@gmail.com>
0 siblings, 1 reply; 21+ messages in thread
From: Michael Paquier @ 2026-09-22 00:12 UTC (permalink / raw)
To: shihao zhong <zhong950419@gmail.com>; +Cc: Bertrand Drouvot <bertranddrouvot.pg@gmail.com>; Jim Jones <jim.jones@uni-muenster.de>; pgsql-hackers <pgsql-hackers@lists.postgresql.org>
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
Attachments:
[application/pgp-signature] signature.asc (832B, ../../arHH7ihI4EqUzTQ-@paquier.xyz/2-signature.asc)
download
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: Add a permission check to pg_stat_get_backend_subxact()
@ 2026-09-22 00:18 shihao zhong <zhong950419@gmail.com>
parent: Michael Paquier <michael@paquier.xyz>
0 siblings, 1 reply; 21+ messages in thread
From: shihao zhong @ 2026-09-22 00:18 UTC (permalink / raw)
To: Michael Paquier <michael@paquier.xyz>; +Cc: Bertrand Drouvot <bertranddrouvot.pg@gmail.com>; Jim Jones <jim.jones@uni-muenster.de>; pgsql-hackers <pgsql-hackers@lists.postgresql.org>
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:
[application/octet-stream] v6-0001-Make-per-backend-statistics-functions-respect-sta.patch (15.1K, ../../CAGRkXqRRuYSPpVi2_Ycss3QNYDLFgptX+w3yt_qPaMG+dZAOXg@mail.gmail.com/3-v6-0001-Make-per-backend-statistics-functions-respect-sta.patch)
download | inline diff:
From 463177f46b9e40c82f51dc36ec5a264ba58e20e9 Mon Sep 17 00:00:00 2001
From: Shihao Zhong <zhong950419@gmail.com>
Date: Fri, 11 Sep 2026 12:26:11 +0200
Subject: [PATCH v6 1/2] Make per-backend statistics functions respect
statistics permissions
The per-backend statistics functions that report the details of a session
call HAS_PGSTAT_PERMISSIONS() first, so that they hide these details from
a caller that is neither a superuser, nor has privileges of
pg_read_all_stats, nor is a member of the role that owns the session.
pg_stat_get_backend_subxact(), pg_stat_get_backend_io(),
pg_stat_get_backend_wal() and pg_stat_get_backend_lock() lacked this
check. Add it, for consistency with the sibling functions. Like
pg_stat_activity, this also hides the statistics of processes owned by
no role, such as autovacuum workers or the WAL writer, from callers
without these privileges.
The last three look up a backend by PID and have no backend status entry
at hand, so pgstat_fetch_stat_backend_by_pid() gains an optional "userid"
output argument, next to the existing "bktype" one, returning the OID of
the role that owns the backend.
The permission rule was documented for the dynamic statistics views but
not for these functions, so state it above the per-backend function table
and on the three functions listed among the additional statistics
functions. While on it, correct the name of the subxact_overflowed
column in the tuple descriptor built by pg_stat_get_backend_subxact().
This changes the output of existing functions for callers lacking the
required privileges, so no backpatch is done.
Author: Shihao Zhong <zhong950419@gmail.com>
Author: Jim Jones <jim.jones@uni-muenster.de>
Reviewed-by: Michael Paquier <michael@paquier.xyz>
Discussion: https://postgr.es/m/CAGRkXqTBZ+zbVuDC8xGEB6Btj61hsui5H5nGqzFBDyOXc=4bjQ@mail.gmail.com
---
doc/src/sgml/monitoring.sgml | 25 +++++++++
src/backend/utils/activity/pgstat_backend.c | 12 ++++-
src/backend/utils/adt/pgstatfuncs.c | 22 +++++---
src/include/pgstat.h | 3 +-
src/test/regress/expected/stats.out | 59 +++++++++++++++++++++
src/test/regress/sql/stats.sql | 45 ++++++++++++++++
6 files changed, 156 insertions(+), 10 deletions(-)
diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml
index 62dadf3e86c..78f224bf9e8 100644
--- a/doc/src/sgml/monitoring.sgml
+++ b/doc/src/sgml/monitoring.sgml
@@ -5799,6 +5799,11 @@ description | Waiting for a newly initialized WAL file to reach durable storage
the background writer, the startup process and the autovacuum launcher
as they are already visible in the <structname>pg_stat_io</structname>
view and there is only one of each.
+ </para>
+ <para>
+ This function returns no rows unless the caller is a superuser, has
+ privileges of the <literal>pg_read_all_stats</literal> role, or is a
+ member of the role that owns the backend.
</para></entry>
</row>
@@ -5834,6 +5839,11 @@ description | Waiting for a newly initialized WAL file to reach durable storage
<para>
The function does not return lock statistics for the checkpointer,
the background writer, the startup process and the autovacuum launcher.
+ </para>
+ <para>
+ This function returns no rows unless the caller is a superuser, has
+ privileges of the <literal>pg_read_all_stats</literal> role, or is a
+ member of the role that owns the backend.
</para></entry>
</row>
@@ -5853,6 +5863,11 @@ description | Waiting for a newly initialized WAL file to reach durable storage
<para>
The function does not return WAL statistics for the checkpointer,
the background writer, the startup process and the autovacuum launcher.
+ </para>
+ <para>
+ This function returns NULL unless the caller is a superuser, has
+ privileges of the <literal>pg_read_all_stats</literal> role, or is a
+ member of the role that owns the backend.
</para></entry>
</row>
@@ -6211,6 +6226,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 details of a session's activity are only shown if
+ 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">
diff --git a/src/backend/utils/activity/pgstat_backend.c b/src/backend/utils/activity/pgstat_backend.c
index b736b2ccc6f..59bc7e699b5 100644
--- a/src/backend/utils/activity/pgstat_backend.c
+++ b/src/backend/utils/activity/pgstat_backend.c
@@ -140,10 +140,12 @@ pgstat_fetch_stat_backend(ProcNumber procNumber)
*
* This routine includes sanity checks to ensure that the backend exists and
* is running. "bktype" can be optionally defined to return the BackendType
- * of the backend whose statistics are returned.
+ * of the backend whose statistics are returned. "userid" can be optionally
+ * defined to return the OID of the role that owns the backend, for callers
+ * that need to check whether they are allowed to report its statistics.
*/
PgStat_Backend *
-pgstat_fetch_stat_backend_by_pid(int pid, BackendType *bktype)
+pgstat_fetch_stat_backend_by_pid(int pid, BackendType *bktype, Oid *userid)
{
PGPROC *proc;
PgBackendStatus *beentry;
@@ -153,6 +155,8 @@ pgstat_fetch_stat_backend_by_pid(int pid, BackendType *bktype)
proc = BackendPidGetProc(pid);
if (bktype)
*bktype = B_INVALID;
+ if (userid)
+ *userid = InvalidOid;
/* this could be an auxiliary process */
if (!proc)
@@ -177,6 +181,8 @@ pgstat_fetch_stat_backend_by_pid(int pid, BackendType *bktype)
if (bktype)
*bktype = beentry->st_backendType;
+ if (userid)
+ *userid = beentry->st_userid;
/*
* Retrieve the entry. Note that "beentry" may be freed depending on the
@@ -187,6 +193,8 @@ pgstat_fetch_stat_backend_by_pid(int pid, BackendType *bktype)
{
if (bktype)
*bktype = B_INVALID;
+ if (userid)
+ *userid = InvalidOid;
return NULL;
}
diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c
index 64b6f60516c..f9fc3b65b2a 100644
--- a/src/backend/utils/adt/pgstatfuncs.c
+++ b/src/backend/utils/adt/pgstatfuncs.c
@@ -838,7 +838,9 @@ pg_stat_get_backend_subxact(PG_FUNCTION_ARGS)
TupleDescFinalize(tupdesc);
BlessTupleDesc(tupdesc);
- if ((local_beentry = pgstat_get_local_beentry_by_proc_number(procNumber)) != NULL)
+ /* Report the details of a session only to a caller 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);
@@ -1673,6 +1675,7 @@ pg_stat_get_backend_io(PG_FUNCTION_ARGS)
ReturnSetInfo *rsinfo;
BackendType bktype;
int pid;
+ Oid userid;
PgStat_Backend *backend_stats;
PgStat_BktypeIO *bktype_stats;
@@ -1680,9 +1683,10 @@ pg_stat_get_backend_io(PG_FUNCTION_ARGS)
rsinfo = (ReturnSetInfo *) fcinfo->resultinfo;
pid = PG_GETARG_INT32(0);
- backend_stats = pgstat_fetch_stat_backend_by_pid(pid, &bktype);
+ backend_stats = pgstat_fetch_stat_backend_by_pid(pid, &bktype, &userid);
- if (!backend_stats)
+ /* Report the details of a session only to a caller allowed to see them */
+ if (!backend_stats || !HAS_PGSTAT_PERMISSIONS(userid))
return (Datum) 0;
bktype_stats = &backend_stats->io_stats;
@@ -1769,13 +1773,15 @@ Datum
pg_stat_get_backend_wal(PG_FUNCTION_ARGS)
{
int pid;
+ Oid userid;
PgStat_Backend *backend_stats;
PgStat_WalCounters bktype_stats;
pid = PG_GETARG_INT32(0);
- backend_stats = pgstat_fetch_stat_backend_by_pid(pid, NULL);
+ backend_stats = pgstat_fetch_stat_backend_by_pid(pid, NULL, &userid);
- if (!backend_stats)
+ /* Report the details of a session only to a caller allowed to see them */
+ if (!backend_stats || !HAS_PGSTAT_PERMISSIONS(userid))
PG_RETURN_NULL();
bktype_stats = backend_stats->wal_counters;
@@ -1857,6 +1863,7 @@ Datum
pg_stat_get_backend_lock(PG_FUNCTION_ARGS)
{
int pid;
+ Oid userid;
ReturnSetInfo *rsinfo;
PgStat_Backend *backend_stats;
@@ -1864,9 +1871,10 @@ pg_stat_get_backend_lock(PG_FUNCTION_ARGS)
rsinfo = (ReturnSetInfo *) fcinfo->resultinfo;
pid = PG_GETARG_INT32(0);
- backend_stats = pgstat_fetch_stat_backend_by_pid(pid, NULL);
+ backend_stats = pgstat_fetch_stat_backend_by_pid(pid, NULL, &userid);
- if (!backend_stats)
+ /* Report the details of a session only to a caller allowed to see them */
+ if (!backend_stats || !HAS_PGSTAT_PERMISSIONS(userid))
return (Datum) 0;
pg_stat_lock_build_tuples(rsinfo, backend_stats->lock_stats.stats,
diff --git a/src/include/pgstat.h b/src/include/pgstat.h
index 187d82c96fe..4c3dcc03df5 100644
--- a/src/include/pgstat.h
+++ b/src/include/pgstat.h
@@ -671,7 +671,8 @@ extern void pgstat_count_backend_lock_fastpath_exceeded(uint8 locktag_type);
extern PgStat_Backend *pgstat_fetch_stat_backend(ProcNumber procNumber);
extern PgStat_Backend *pgstat_fetch_stat_backend_by_pid(int pid,
- BackendType *bktype);
+ BackendType *bktype,
+ Oid *userid);
extern bool pgstat_tracks_backend_bktype(BackendType bktype);
extern void pgstat_create_backend(ProcNumber procnum);
diff --git a/src/test/regress/expected/stats.out b/src/test/regress/expected/stats.out
index 8b15471248b..fc1870bfd4b 100644
--- a/src/test/regress/expected/stats.out
+++ b/src/test/regress/expected/stats.out
@@ -1141,6 +1141,65 @@ WHERE pg_stat_get_backend_pid(beid) = pg_backend_pid();
t
(1 row)
+-- The per-backend statistics functions report the details of a session only
+-- to a caller that is allowed to see them: a superuser, a role with
+-- privileges of pg_read_all_stats, or the role that owns the session.
+SELECT beid FROM pg_stat_get_backend_idset() beid
+WHERE pg_stat_get_backend_pid(beid) = pg_backend_pid() \gset
+SELECT current_user AS regress_stat_backend_owner \gset
+CREATE ROLE regress_stat_backend_role;
+-- a role with privileges of the role that owns this backend sees them
+GRANT :"regress_stat_backend_owner" TO regress_stat_backend_role;
+SET ROLE regress_stat_backend_role;
+SELECT (SELECT subxact_count IS NOT NULL
+ FROM pg_stat_get_backend_subxact(:beid)) AS subxact,
+ (SELECT count(*) > 0
+ FROM pg_stat_get_backend_io(pg_backend_pid())) AS io,
+ (SELECT count(*) > 0
+ FROM pg_stat_get_backend_lock(pg_backend_pid())) AS locks,
+ (SELECT wal_records IS NOT NULL
+ FROM pg_stat_get_backend_wal(pg_backend_pid())) AS wal;
+ subxact | io | locks | wal
+---------+----+-------+-----
+ t | t | t | t
+(1 row)
+
+RESET ROLE;
+REVOKE :"regress_stat_backend_owner" FROM regress_stat_backend_role;
+SET ROLE regress_stat_backend_role;
+-- an unrelated role sees nothing
+SELECT (SELECT subxact_count IS NOT NULL
+ FROM pg_stat_get_backend_subxact(:beid)) AS subxact,
+ (SELECT count(*) > 0
+ FROM pg_stat_get_backend_io(pg_backend_pid())) AS io,
+ (SELECT count(*) > 0
+ FROM pg_stat_get_backend_lock(pg_backend_pid())) AS locks,
+ (SELECT wal_records IS NOT NULL
+ FROM pg_stat_get_backend_wal(pg_backend_pid())) AS wal;
+ subxact | io | locks | wal
+---------+----+-------+-----
+ f | f | f | f
+(1 row)
+
+RESET ROLE;
+-- but a role with privileges of pg_read_all_stats sees them again
+GRANT pg_read_all_stats TO regress_stat_backend_role;
+SET ROLE regress_stat_backend_role;
+SELECT (SELECT subxact_count IS NOT NULL
+ FROM pg_stat_get_backend_subxact(:beid)) AS subxact,
+ (SELECT count(*) > 0
+ FROM pg_stat_get_backend_io(pg_backend_pid())) AS io,
+ (SELECT count(*) > 0
+ FROM pg_stat_get_backend_lock(pg_backend_pid())) AS locks,
+ (SELECT wal_records IS NOT NULL
+ FROM pg_stat_get_backend_wal(pg_backend_pid())) AS wal;
+ subxact | io | locks | wal
+---------+----+-------+-----
+ t | t | t | t
+(1 row)
+
+RESET ROLE;
+DROP ROLE regress_stat_backend_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..17c8e2f5231 100644
--- a/src/test/regress/sql/stats.sql
+++ b/src/test/regress/sql/stats.sql
@@ -535,6 +535,51 @@ 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();
+-- The per-backend statistics functions report the details of a session only
+-- to a caller that is allowed to see them: a superuser, a role with
+-- privileges of pg_read_all_stats, or the role that owns the session.
+SELECT beid FROM pg_stat_get_backend_idset() beid
+WHERE pg_stat_get_backend_pid(beid) = pg_backend_pid() \gset
+SELECT current_user AS regress_stat_backend_owner \gset
+CREATE ROLE regress_stat_backend_role;
+-- a role with privileges of the role that owns this backend sees them
+GRANT :"regress_stat_backend_owner" TO regress_stat_backend_role;
+SET ROLE regress_stat_backend_role;
+SELECT (SELECT subxact_count IS NOT NULL
+ FROM pg_stat_get_backend_subxact(:beid)) AS subxact,
+ (SELECT count(*) > 0
+ FROM pg_stat_get_backend_io(pg_backend_pid())) AS io,
+ (SELECT count(*) > 0
+ FROM pg_stat_get_backend_lock(pg_backend_pid())) AS locks,
+ (SELECT wal_records IS NOT NULL
+ FROM pg_stat_get_backend_wal(pg_backend_pid())) AS wal;
+RESET ROLE;
+REVOKE :"regress_stat_backend_owner" FROM regress_stat_backend_role;
+SET ROLE regress_stat_backend_role;
+-- an unrelated role sees nothing
+SELECT (SELECT subxact_count IS NOT NULL
+ FROM pg_stat_get_backend_subxact(:beid)) AS subxact,
+ (SELECT count(*) > 0
+ FROM pg_stat_get_backend_io(pg_backend_pid())) AS io,
+ (SELECT count(*) > 0
+ FROM pg_stat_get_backend_lock(pg_backend_pid())) AS locks,
+ (SELECT wal_records IS NOT NULL
+ FROM pg_stat_get_backend_wal(pg_backend_pid())) AS wal;
+RESET ROLE;
+-- but a role with privileges of pg_read_all_stats sees them again
+GRANT pg_read_all_stats TO regress_stat_backend_role;
+SET ROLE regress_stat_backend_role;
+SELECT (SELECT subxact_count IS NOT NULL
+ FROM pg_stat_get_backend_subxact(:beid)) AS subxact,
+ (SELECT count(*) > 0
+ FROM pg_stat_get_backend_io(pg_backend_pid())) AS io,
+ (SELECT count(*) > 0
+ FROM pg_stat_get_backend_lock(pg_backend_pid())) AS locks,
+ (SELECT wal_records IS NOT NULL
+ FROM pg_stat_get_backend_wal(pg_backend_pid())) AS wal;
+RESET ROLE;
+DROP ROLE regress_stat_backend_role;
+
-----
-- Test that resetting stats works for reset timestamp
-----
--
2.37.1 (Apple Git-137.1)
[application/octet-stream] v6-0002-Store-the-PID-of-a-backend-in-its-statistics-entr.patch (3.2K, ../../CAGRkXqRRuYSPpVi2_Ycss3QNYDLFgptX+w3yt_qPaMG+dZAOXg@mail.gmail.com/4-v6-0002-Store-the-PID-of-a-backend-in-its-statistics-entr.patch)
download | inline diff:
From 86412fa32be2c2c0736714ebf868c62ed9824d3f Mon Sep 17 00:00:00 2001
From: Shihao <zhong950419@gmail.com>
Date: Mon, 21 Sep 2026 10:10:13 -0400
Subject: [PATCH v6 2/2] Store the PID of a backend in its statistics entry
With stats_fetch_consistency set to "snapshot", the statistics of a
backend could be reported under the PID of a newer backend that reused
its proc number. Store the PID of the backend in PgStat_Backend when the
entry is created, and ignore an entry whose PID does not match the one
requested. A reset zeroes the whole entry, so a copy of the PID is kept
in PgStatShared_Backend and restored by the reset callback.
Reported-by: Bertrand Drouvot <bertranddrouvot.pg@gmail.com>
Author: Shihao Zhong <zhong950419@gmail.com>
Discussion: https://postgr.es/m/arD/w47Ug1GaObfq@bdtpg
---
src/backend/utils/activity/pgstat_backend.c | 20 +++++++++++++++++++-
src/include/pgstat.h | 1 +
src/include/utils/pgstat_internal.h | 6 ++++++
3 files changed, 26 insertions(+), 1 deletion(-)
diff --git a/src/backend/utils/activity/pgstat_backend.c b/src/backend/utils/activity/pgstat_backend.c
index 59bc7e699b5..b75563ca2fc 100644
--- a/src/backend/utils/activity/pgstat_backend.c
+++ b/src/backend/utils/activity/pgstat_backend.c
@@ -189,6 +189,17 @@ pgstat_fetch_stat_backend_by_pid(int pid, BackendType *bktype, Oid *userid)
* value of stats_fetch_consistency, so do not access it from this point.
*/
backend_stats = pgstat_fetch_stat_backend(procNumber);
+
+ if (backend_stats && backend_stats->pid != pid)
+ backend_stats = NULL;
+
if (!backend_stats)
{
if (bktype)
@@ -414,6 +425,8 @@ pgstat_create_backend(ProcNumber procnum)
* e.g. if we previously used this proc number.
*/
memset(&shstatent->stats, 0, sizeof(shstatent->stats));
+ shstatent->stats.pid = MyProcPid;
+ shstatent->pid = MyProcPid;
pgstat_unlock_entry(entry_ref);
MemSet(&PendingBackendStats, 0, sizeof(PgStat_BackendPending));
@@ -483,5 +496,10 @@ pgstat_tracks_backend_bktype(BackendType bktype)
void
pgstat_backend_reset_timestamp_cb(PgStatShared_Common *header, TimestampTz ts)
{
- ((PgStatShared_Backend *) header)->stats.stat_reset_timestamp = ts;
+ PgStatShared_Backend *shstatent = (PgStatShared_Backend *) header;
+
+ shstatent->stats.stat_reset_timestamp = ts;
+
+ /* a reset zeroes the whole entry, so restore the PID of its owner */
+ shstatent->stats.pid = shstatent->pid;
}
diff --git a/src/include/pgstat.h b/src/include/pgstat.h
index 4c3dcc03df5..bce475aacab 100644
--- a/src/include/pgstat.h
+++ b/src/include/pgstat.h
@@ -591,6 +591,7 @@ typedef struct PgStat_WalStats
*/
typedef struct PgStat_Backend
{
+ int pid; /* PID of the backend owning these stats */
TimestampTz stat_reset_timestamp;
PgStat_BktypeIO io_stats;
PgStat_WalCounters wal_counters;
diff --git a/src/include/utils/pgstat_internal.h b/src/include/utils/pgstat_internal.h
index 14369e59a1c..d512635fb30 100644
--- a/src/include/utils/pgstat_internal.h
+++ b/src/include/utils/pgstat_internal.h
@@ -536,6 +536,12 @@ typedef struct PgStatShared_Backend
{
PgStatShared_Common header;
PgStat_Backend stats;
+
+ int pid;
} PgStatShared_Backend;
/*
--
2.37.1 (Apple Git-137.1)
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: Add a permission check to pg_stat_get_backend_subxact()
@ 2026-09-22 01:20 shihao zhong <zhong950419@gmail.com>
parent: shihao zhong <zhong950419@gmail.com>
0 siblings, 0 replies; 21+ messages in thread
From: shihao zhong @ 2026-09-22 01:20 UTC (permalink / raw)
To: Michael Paquier <michael@paquier.xyz>; +Cc: Bertrand Drouvot <bertranddrouvot.pg@gmail.com>; Jim Jones <jim.jones@uni-muenster.de>; pgsql-hackers <pgsql-hackers@lists.postgresql.org>
Rebase with master.
Attachments:
[application/octet-stream] v7-0001-Make-per-backend-statistics-functions-respect-sta.patch (15.1K, ../../CAGRkXqQpxxrvxqA9P=CUw4vPFfR3yUa_T9ytzV9Kj0urot0Gag@mail.gmail.com/3-v7-0001-Make-per-backend-statistics-functions-respect-sta.patch)
download | inline diff:
From 463177f46b9e40c82f51dc36ec5a264ba58e20e9 Mon Sep 17 00:00:00 2001
From: Shihao Zhong <zhong950419@gmail.com>
Date: Fri, 11 Sep 2026 12:26:11 +0200
Subject: [PATCH v7 1/2] Make per-backend statistics functions respect
statistics permissions
The per-backend statistics functions that report the details of a session
call HAS_PGSTAT_PERMISSIONS() first, so that they hide these details from
a caller that is neither a superuser, nor has privileges of
pg_read_all_stats, nor is a member of the role that owns the session.
pg_stat_get_backend_subxact(), pg_stat_get_backend_io(),
pg_stat_get_backend_wal() and pg_stat_get_backend_lock() lacked this
check. Add it, for consistency with the sibling functions. Like
pg_stat_activity, this also hides the statistics of processes owned by
no role, such as autovacuum workers or the WAL writer, from callers
without these privileges.
The last three look up a backend by PID and have no backend status entry
at hand, so pgstat_fetch_stat_backend_by_pid() gains an optional "userid"
output argument, next to the existing "bktype" one, returning the OID of
the role that owns the backend.
The permission rule was documented for the dynamic statistics views but
not for these functions, so state it above the per-backend function table
and on the three functions listed among the additional statistics
functions. While on it, correct the name of the subxact_overflowed
column in the tuple descriptor built by pg_stat_get_backend_subxact().
This changes the output of existing functions for callers lacking the
required privileges, so no backpatch is done.
Author: Shihao Zhong <zhong950419@gmail.com>
Author: Jim Jones <jim.jones@uni-muenster.de>
Reviewed-by: Michael Paquier <michael@paquier.xyz>
Discussion: https://postgr.es/m/CAGRkXqTBZ+zbVuDC8xGEB6Btj61hsui5H5nGqzFBDyOXc=4bjQ@mail.gmail.com
---
doc/src/sgml/monitoring.sgml | 25 +++++++++
src/backend/utils/activity/pgstat_backend.c | 12 ++++-
src/backend/utils/adt/pgstatfuncs.c | 22 +++++---
src/include/pgstat.h | 3 +-
src/test/regress/expected/stats.out | 59 +++++++++++++++++++++
src/test/regress/sql/stats.sql | 45 ++++++++++++++++
6 files changed, 156 insertions(+), 10 deletions(-)
diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml
index 62dadf3e86c..78f224bf9e8 100644
--- a/doc/src/sgml/monitoring.sgml
+++ b/doc/src/sgml/monitoring.sgml
@@ -5799,6 +5799,11 @@ description | Waiting for a newly initialized WAL file to reach durable storage
the background writer, the startup process and the autovacuum launcher
as they are already visible in the <structname>pg_stat_io</structname>
view and there is only one of each.
+ </para>
+ <para>
+ This function returns no rows unless the caller is a superuser, has
+ privileges of the <literal>pg_read_all_stats</literal> role, or is a
+ member of the role that owns the backend.
</para></entry>
</row>
@@ -5834,6 +5839,11 @@ description | Waiting for a newly initialized WAL file to reach durable storage
<para>
The function does not return lock statistics for the checkpointer,
the background writer, the startup process and the autovacuum launcher.
+ </para>
+ <para>
+ This function returns no rows unless the caller is a superuser, has
+ privileges of the <literal>pg_read_all_stats</literal> role, or is a
+ member of the role that owns the backend.
</para></entry>
</row>
@@ -5853,6 +5863,11 @@ description | Waiting for a newly initialized WAL file to reach durable storage
<para>
The function does not return WAL statistics for the checkpointer,
the background writer, the startup process and the autovacuum launcher.
+ </para>
+ <para>
+ This function returns NULL unless the caller is a superuser, has
+ privileges of the <literal>pg_read_all_stats</literal> role, or is a
+ member of the role that owns the backend.
</para></entry>
</row>
@@ -6211,6 +6226,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 details of a session's activity are only shown if
+ 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">
diff --git a/src/backend/utils/activity/pgstat_backend.c b/src/backend/utils/activity/pgstat_backend.c
index b736b2ccc6f..59bc7e699b5 100644
--- a/src/backend/utils/activity/pgstat_backend.c
+++ b/src/backend/utils/activity/pgstat_backend.c
@@ -140,10 +140,12 @@ pgstat_fetch_stat_backend(ProcNumber procNumber)
*
* This routine includes sanity checks to ensure that the backend exists and
* is running. "bktype" can be optionally defined to return the BackendType
- * of the backend whose statistics are returned.
+ * of the backend whose statistics are returned. "userid" can be optionally
+ * defined to return the OID of the role that owns the backend, for callers
+ * that need to check whether they are allowed to report its statistics.
*/
PgStat_Backend *
-pgstat_fetch_stat_backend_by_pid(int pid, BackendType *bktype)
+pgstat_fetch_stat_backend_by_pid(int pid, BackendType *bktype, Oid *userid)
{
PGPROC *proc;
PgBackendStatus *beentry;
@@ -153,6 +155,8 @@ pgstat_fetch_stat_backend_by_pid(int pid, BackendType *bktype)
proc = BackendPidGetProc(pid);
if (bktype)
*bktype = B_INVALID;
+ if (userid)
+ *userid = InvalidOid;
/* this could be an auxiliary process */
if (!proc)
@@ -177,6 +181,8 @@ pgstat_fetch_stat_backend_by_pid(int pid, BackendType *bktype)
if (bktype)
*bktype = beentry->st_backendType;
+ if (userid)
+ *userid = beentry->st_userid;
/*
* Retrieve the entry. Note that "beentry" may be freed depending on the
@@ -187,6 +193,8 @@ pgstat_fetch_stat_backend_by_pid(int pid, BackendType *bktype)
{
if (bktype)
*bktype = B_INVALID;
+ if (userid)
+ *userid = InvalidOid;
return NULL;
}
diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c
index 64b6f60516c..f9fc3b65b2a 100644
--- a/src/backend/utils/adt/pgstatfuncs.c
+++ b/src/backend/utils/adt/pgstatfuncs.c
@@ -838,7 +838,9 @@ pg_stat_get_backend_subxact(PG_FUNCTION_ARGS)
TupleDescFinalize(tupdesc);
BlessTupleDesc(tupdesc);
- if ((local_beentry = pgstat_get_local_beentry_by_proc_number(procNumber)) != NULL)
+ /* Report the details of a session only to a caller 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);
@@ -1673,6 +1675,7 @@ pg_stat_get_backend_io(PG_FUNCTION_ARGS)
ReturnSetInfo *rsinfo;
BackendType bktype;
int pid;
+ Oid userid;
PgStat_Backend *backend_stats;
PgStat_BktypeIO *bktype_stats;
@@ -1680,9 +1683,10 @@ pg_stat_get_backend_io(PG_FUNCTION_ARGS)
rsinfo = (ReturnSetInfo *) fcinfo->resultinfo;
pid = PG_GETARG_INT32(0);
- backend_stats = pgstat_fetch_stat_backend_by_pid(pid, &bktype);
+ backend_stats = pgstat_fetch_stat_backend_by_pid(pid, &bktype, &userid);
- if (!backend_stats)
+ /* Report the details of a session only to a caller allowed to see them */
+ if (!backend_stats || !HAS_PGSTAT_PERMISSIONS(userid))
return (Datum) 0;
bktype_stats = &backend_stats->io_stats;
@@ -1769,13 +1773,15 @@ Datum
pg_stat_get_backend_wal(PG_FUNCTION_ARGS)
{
int pid;
+ Oid userid;
PgStat_Backend *backend_stats;
PgStat_WalCounters bktype_stats;
pid = PG_GETARG_INT32(0);
- backend_stats = pgstat_fetch_stat_backend_by_pid(pid, NULL);
+ backend_stats = pgstat_fetch_stat_backend_by_pid(pid, NULL, &userid);
- if (!backend_stats)
+ /* Report the details of a session only to a caller allowed to see them */
+ if (!backend_stats || !HAS_PGSTAT_PERMISSIONS(userid))
PG_RETURN_NULL();
bktype_stats = backend_stats->wal_counters;
@@ -1857,6 +1863,7 @@ Datum
pg_stat_get_backend_lock(PG_FUNCTION_ARGS)
{
int pid;
+ Oid userid;
ReturnSetInfo *rsinfo;
PgStat_Backend *backend_stats;
@@ -1864,9 +1871,10 @@ pg_stat_get_backend_lock(PG_FUNCTION_ARGS)
rsinfo = (ReturnSetInfo *) fcinfo->resultinfo;
pid = PG_GETARG_INT32(0);
- backend_stats = pgstat_fetch_stat_backend_by_pid(pid, NULL);
+ backend_stats = pgstat_fetch_stat_backend_by_pid(pid, NULL, &userid);
- if (!backend_stats)
+ /* Report the details of a session only to a caller allowed to see them */
+ if (!backend_stats || !HAS_PGSTAT_PERMISSIONS(userid))
return (Datum) 0;
pg_stat_lock_build_tuples(rsinfo, backend_stats->lock_stats.stats,
diff --git a/src/include/pgstat.h b/src/include/pgstat.h
index 187d82c96fe..4c3dcc03df5 100644
--- a/src/include/pgstat.h
+++ b/src/include/pgstat.h
@@ -671,7 +671,8 @@ extern void pgstat_count_backend_lock_fastpath_exceeded(uint8 locktag_type);
extern PgStat_Backend *pgstat_fetch_stat_backend(ProcNumber procNumber);
extern PgStat_Backend *pgstat_fetch_stat_backend_by_pid(int pid,
- BackendType *bktype);
+ BackendType *bktype,
+ Oid *userid);
extern bool pgstat_tracks_backend_bktype(BackendType bktype);
extern void pgstat_create_backend(ProcNumber procnum);
diff --git a/src/test/regress/expected/stats.out b/src/test/regress/expected/stats.out
index 8b15471248b..fc1870bfd4b 100644
--- a/src/test/regress/expected/stats.out
+++ b/src/test/regress/expected/stats.out
@@ -1141,6 +1141,65 @@ WHERE pg_stat_get_backend_pid(beid) = pg_backend_pid();
t
(1 row)
+-- The per-backend statistics functions report the details of a session only
+-- to a caller that is allowed to see them: a superuser, a role with
+-- privileges of pg_read_all_stats, or the role that owns the session.
+SELECT beid FROM pg_stat_get_backend_idset() beid
+WHERE pg_stat_get_backend_pid(beid) = pg_backend_pid() \gset
+SELECT current_user AS regress_stat_backend_owner \gset
+CREATE ROLE regress_stat_backend_role;
+-- a role with privileges of the role that owns this backend sees them
+GRANT :"regress_stat_backend_owner" TO regress_stat_backend_role;
+SET ROLE regress_stat_backend_role;
+SELECT (SELECT subxact_count IS NOT NULL
+ FROM pg_stat_get_backend_subxact(:beid)) AS subxact,
+ (SELECT count(*) > 0
+ FROM pg_stat_get_backend_io(pg_backend_pid())) AS io,
+ (SELECT count(*) > 0
+ FROM pg_stat_get_backend_lock(pg_backend_pid())) AS locks,
+ (SELECT wal_records IS NOT NULL
+ FROM pg_stat_get_backend_wal(pg_backend_pid())) AS wal;
+ subxact | io | locks | wal
+---------+----+-------+-----
+ t | t | t | t
+(1 row)
+
+RESET ROLE;
+REVOKE :"regress_stat_backend_owner" FROM regress_stat_backend_role;
+SET ROLE regress_stat_backend_role;
+-- an unrelated role sees nothing
+SELECT (SELECT subxact_count IS NOT NULL
+ FROM pg_stat_get_backend_subxact(:beid)) AS subxact,
+ (SELECT count(*) > 0
+ FROM pg_stat_get_backend_io(pg_backend_pid())) AS io,
+ (SELECT count(*) > 0
+ FROM pg_stat_get_backend_lock(pg_backend_pid())) AS locks,
+ (SELECT wal_records IS NOT NULL
+ FROM pg_stat_get_backend_wal(pg_backend_pid())) AS wal;
+ subxact | io | locks | wal
+---------+----+-------+-----
+ f | f | f | f
+(1 row)
+
+RESET ROLE;
+-- but a role with privileges of pg_read_all_stats sees them again
+GRANT pg_read_all_stats TO regress_stat_backend_role;
+SET ROLE regress_stat_backend_role;
+SELECT (SELECT subxact_count IS NOT NULL
+ FROM pg_stat_get_backend_subxact(:beid)) AS subxact,
+ (SELECT count(*) > 0
+ FROM pg_stat_get_backend_io(pg_backend_pid())) AS io,
+ (SELECT count(*) > 0
+ FROM pg_stat_get_backend_lock(pg_backend_pid())) AS locks,
+ (SELECT wal_records IS NOT NULL
+ FROM pg_stat_get_backend_wal(pg_backend_pid())) AS wal;
+ subxact | io | locks | wal
+---------+----+-------+-----
+ t | t | t | t
+(1 row)
+
+RESET ROLE;
+DROP ROLE regress_stat_backend_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..17c8e2f5231 100644
--- a/src/test/regress/sql/stats.sql
+++ b/src/test/regress/sql/stats.sql
@@ -535,6 +535,51 @@ 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();
+-- The per-backend statistics functions report the details of a session only
+-- to a caller that is allowed to see them: a superuser, a role with
+-- privileges of pg_read_all_stats, or the role that owns the session.
+SELECT beid FROM pg_stat_get_backend_idset() beid
+WHERE pg_stat_get_backend_pid(beid) = pg_backend_pid() \gset
+SELECT current_user AS regress_stat_backend_owner \gset
+CREATE ROLE regress_stat_backend_role;
+-- a role with privileges of the role that owns this backend sees them
+GRANT :"regress_stat_backend_owner" TO regress_stat_backend_role;
+SET ROLE regress_stat_backend_role;
+SELECT (SELECT subxact_count IS NOT NULL
+ FROM pg_stat_get_backend_subxact(:beid)) AS subxact,
+ (SELECT count(*) > 0
+ FROM pg_stat_get_backend_io(pg_backend_pid())) AS io,
+ (SELECT count(*) > 0
+ FROM pg_stat_get_backend_lock(pg_backend_pid())) AS locks,
+ (SELECT wal_records IS NOT NULL
+ FROM pg_stat_get_backend_wal(pg_backend_pid())) AS wal;
+RESET ROLE;
+REVOKE :"regress_stat_backend_owner" FROM regress_stat_backend_role;
+SET ROLE regress_stat_backend_role;
+-- an unrelated role sees nothing
+SELECT (SELECT subxact_count IS NOT NULL
+ FROM pg_stat_get_backend_subxact(:beid)) AS subxact,
+ (SELECT count(*) > 0
+ FROM pg_stat_get_backend_io(pg_backend_pid())) AS io,
+ (SELECT count(*) > 0
+ FROM pg_stat_get_backend_lock(pg_backend_pid())) AS locks,
+ (SELECT wal_records IS NOT NULL
+ FROM pg_stat_get_backend_wal(pg_backend_pid())) AS wal;
+RESET ROLE;
+-- but a role with privileges of pg_read_all_stats sees them again
+GRANT pg_read_all_stats TO regress_stat_backend_role;
+SET ROLE regress_stat_backend_role;
+SELECT (SELECT subxact_count IS NOT NULL
+ FROM pg_stat_get_backend_subxact(:beid)) AS subxact,
+ (SELECT count(*) > 0
+ FROM pg_stat_get_backend_io(pg_backend_pid())) AS io,
+ (SELECT count(*) > 0
+ FROM pg_stat_get_backend_lock(pg_backend_pid())) AS locks,
+ (SELECT wal_records IS NOT NULL
+ FROM pg_stat_get_backend_wal(pg_backend_pid())) AS wal;
+RESET ROLE;
+DROP ROLE regress_stat_backend_role;
+
-----
-- Test that resetting stats works for reset timestamp
-----
--
2.37.1 (Apple Git-137.1)
[application/octet-stream] v7-0002-Store-the-PID-of-a-backend-in-its-statistics-entr.patch (3.1K, ../../CAGRkXqQpxxrvxqA9P=CUw4vPFfR3yUa_T9ytzV9Kj0urot0Gag@mail.gmail.com/4-v7-0002-Store-the-PID-of-a-backend-in-its-statistics-entr.patch)
download | inline diff:
From 90586d86113e1c7023eeb86f3817f1ae32adccf6 Mon Sep 17 00:00:00 2001
From: Shihao <zhong950419@gmail.com>
Date: Mon, 21 Sep 2026 10:10:13 -0400
Subject: [PATCH v7 2/2] Store the PID of a backend in its statistics entry
With stats_fetch_consistency set to "snapshot", the statistics of a
backend could be reported under the PID of a newer backend that reused
its proc number. Store the PID of the backend in PgStat_Backend when the
entry is created, and ignore an entry whose PID does not match the one
requested. A reset zeroes the whole entry, so a copy of the PID is kept
in PgStatShared_Backend and restored by the reset callback.
Reported-by: Bertrand Drouvot <bertranddrouvot.pg@gmail.com>
Author: Shihao Zhong <zhong950419@gmail.com>
Discussion: https://postgr.es/m/arD/w47Ug1GaObfq@bdtpg
---
src/backend/utils/activity/pgstat_backend.c | 13 ++++++++++++-
src/include/pgstat.h | 1 +
src/include/utils/pgstat_internal.h | 2 ++
3 files changed, 15 insertions(+), 1 deletion(-)
diff --git a/src/backend/utils/activity/pgstat_backend.c b/src/backend/utils/activity/pgstat_backend.c
index 59bc7e699b5..7c8c7aaf95f 100644
--- a/src/backend/utils/activity/pgstat_backend.c
+++ b/src/backend/utils/activity/pgstat_backend.c
@@ -189,6 +189,10 @@ pgstat_fetch_stat_backend_by_pid(int pid, BackendType *bktype, Oid *userid)
* value of stats_fetch_consistency, so do not access it from this point.
*/
backend_stats = pgstat_fetch_stat_backend(procNumber);
+
+ if (backend_stats && backend_stats->pid != pid)
+ backend_stats = NULL;
+
if (!backend_stats)
{
if (bktype)
@@ -414,6 +418,8 @@ pgstat_create_backend(ProcNumber procnum)
* e.g. if we previously used this proc number.
*/
memset(&shstatent->stats, 0, sizeof(shstatent->stats));
+ shstatent->stats.pid = MyProcPid;
+ shstatent->pid = MyProcPid;
pgstat_unlock_entry(entry_ref);
MemSet(&PendingBackendStats, 0, sizeof(PgStat_BackendPending));
@@ -483,5 +489,10 @@ pgstat_tracks_backend_bktype(BackendType bktype)
void
pgstat_backend_reset_timestamp_cb(PgStatShared_Common *header, TimestampTz ts)
{
- ((PgStatShared_Backend *) header)->stats.stat_reset_timestamp = ts;
+ PgStatShared_Backend *shstatent = (PgStatShared_Backend *) header;
+
+ shstatent->stats.stat_reset_timestamp = ts;
+
+ /* a reset zeroes the whole entry, so restore the PID of its owner */
+ shstatent->stats.pid = shstatent->pid;
}
diff --git a/src/include/pgstat.h b/src/include/pgstat.h
index 4c3dcc03df5..bce475aacab 100644
--- a/src/include/pgstat.h
+++ b/src/include/pgstat.h
@@ -591,6 +591,7 @@ typedef struct PgStat_WalStats
*/
typedef struct PgStat_Backend
{
+ int pid; /* PID of the backend owning these stats */
TimestampTz stat_reset_timestamp;
PgStat_BktypeIO io_stats;
PgStat_WalCounters wal_counters;
diff --git a/src/include/utils/pgstat_internal.h b/src/include/utils/pgstat_internal.h
index 14369e59a1c..0ed2830d4a0 100644
--- a/src/include/utils/pgstat_internal.h
+++ b/src/include/utils/pgstat_internal.h
@@ -536,6 +536,8 @@ typedef struct PgStatShared_Backend
{
PgStatShared_Common header;
PgStat_Backend stats;
+
+ int pid;
} PgStatShared_Backend;
/*
--
2.37.1 (Apple Git-137.1)
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: Add a permission check to pg_stat_get_backend_subxact()
@ 2026-09-22 09:24 Michael Paquier <michael@paquier.xyz>
parent: Bertrand Drouvot <bertranddrouvot.pg@gmail.com>
1 sibling, 1 reply; 21+ messages in thread
From: Michael Paquier @ 2026-09-22 09:24 UTC (permalink / raw)
To: Bertrand Drouvot <bertranddrouvot.pg@gmail.com>; +Cc: shihao zhong <zhong950419@gmail.com>; Jim Jones <jim.jones@uni-muenster.de>; pgsql-hackers <pgsql-hackers@lists.postgresql.org>
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
Attachments:
[application/pgp-signature] signature.asc (832B, ../../arJJVOo1mOKvK8US@paquier.xyz/2-signature.asc)
download
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: Add a permission check to pg_stat_get_backend_subxact()
@ 2026-09-22 10:03 Bertrand Drouvot <bertranddrouvot.pg@gmail.com>
parent: Michael Paquier <michael@paquier.xyz>
0 siblings, 1 reply; 21+ messages in thread
From: Bertrand Drouvot @ 2026-09-22 10:03 UTC (permalink / raw)
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>
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
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: Add a permission check to pg_stat_get_backend_subxact()
@ 2026-09-22 11:23 Michael Paquier <michael@paquier.xyz>
parent: Bertrand Drouvot <bertranddrouvot.pg@gmail.com>
0 siblings, 1 reply; 21+ messages in thread
From: Michael Paquier @ 2026-09-22 11:23 UTC (permalink / raw)
To: Bertrand Drouvot <bertranddrouvot.pg@gmail.com>; +Cc: shihao zhong <zhong950419@gmail.com>; Jim Jones <jim.jones@uni-muenster.de>; pgsql-hackers <pgsql-hackers@lists.postgresql.org>
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
Attachments:
[application/pgp-signature] signature.asc (832B, ../../arJlSpeSs8yPKipu@paquier.xyz/2-signature.asc)
download
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: Add a permission check to pg_stat_get_backend_subxact()
@ 2026-09-22 14:03 Bertrand Drouvot <bertranddrouvot.pg@gmail.com>
parent: Michael Paquier <michael@paquier.xyz>
0 siblings, 1 reply; 21+ messages in thread
From: Bertrand Drouvot @ 2026-09-22 14:03 UTC (permalink / raw)
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>
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
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: Add a permission check to pg_stat_get_backend_subxact()
@ 2026-09-23 03:22 shihao zhong <zhong950419@gmail.com>
parent: Bertrand Drouvot <bertranddrouvot.pg@gmail.com>
0 siblings, 1 reply; 21+ messages in thread
From: shihao zhong @ 2026-09-23 03:22 UTC (permalink / raw)
To: Bertrand Drouvot <bertranddrouvot.pg@gmail.com>; +Cc: Michael Paquier <michael@paquier.xyz>; Jim Jones <jim.jones@uni-muenster.de>; pgsql-hackers <pgsql-hackers@lists.postgresql.org>
> 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:
[application/octet-stream] v8-0002-Make-per-backend-statistics-functions-respect-sta.patch (15.8K, ../../CAGRkXqTLuE3PrRn7tXJ8t+7UHcTUJ9cuaFxbZkuhpAySD0RpfA@mail.gmail.com/3-v8-0002-Make-per-backend-statistics-functions-respect-sta.patch)
download | inline diff:
From 6bdf78c18342a960f2c014ac861beaaf7cca397a Mon Sep 17 00:00:00 2001
From: Shihao Zhong <zhong950419@gmail.com>
Date: Fri, 11 Sep 2026 12:26:11 +0200
Subject: [PATCH v8 2/2] Make per-backend statistics functions respect
statistics permissions
The per-backend statistics functions that report the details of a session
call HAS_PGSTAT_PERMISSIONS() first, so that they hide these details from
a caller that is neither a superuser, nor has privileges of
pg_read_all_stats, nor is a member of the role that owns the session.
pg_stat_get_backend_subxact(), pg_stat_get_backend_io(),
pg_stat_get_backend_wal() and pg_stat_get_backend_lock() lacked this
check. Add it, for consistency with the sibling functions. Like
pg_stat_activity, this also hides the statistics of processes owned by
no role, such as autovacuum workers or the WAL writer, from callers
without these privileges.
pg_stat_get_backend_subxact() reads the backend status entry, so it checks
the role recorded there, like its sibling functions. The last three report
cumulative statistics that may come from a snapshot, taken while an older
backend held the same proc number, so the role recorded in the backend
status entry may not be the one that owns the statistics. Store the OID of
the role that owns the backend in PgStat_Backend when its entry is created,
and check the caller against that, based on the statistics data alone.
The permission rule was documented for the dynamic statistics views but
not for these functions, so state it above the per-backend function table
and on the three functions listed among the additional statistics
functions.
This changes the output of existing functions for callers lacking the
required privileges, so no backpatch is done.
Author: Shihao Zhong <zhong950419@gmail.com>
Author: Jim Jones <jim.jones@uni-muenster.de>
Reviewed-by: Michael Paquier <michael@paquier.xyz>
Discussion: https://postgr.es/m/CAGRkXqTBZ+zbVuDC8xGEB6Btj61hsui5H5nGqzFBDyOXc=4bjQ@mail.gmail.com
---
doc/src/sgml/monitoring.sgml | 25 +++++++++
src/backend/utils/activity/backend_status.c | 2 +-
src/backend/utils/activity/pgstat_backend.c | 15 +++++-
src/backend/utils/adt/pgstatfuncs.c | 13 +++--
src/include/pgstat.h | 3 +-
src/include/utils/pgstat_internal.h | 2 +
src/test/regress/expected/stats.out | 59 +++++++++++++++++++++
src/test/regress/sql/stats.sql | 45 ++++++++++++++++
8 files changed, 156 insertions(+), 8 deletions(-)
diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml
index 62dadf3e86c..78f224bf9e8 100644
--- a/doc/src/sgml/monitoring.sgml
+++ b/doc/src/sgml/monitoring.sgml
@@ -5799,6 +5799,11 @@ description | Waiting for a newly initialized WAL file to reach durable storage
the background writer, the startup process and the autovacuum launcher
as they are already visible in the <structname>pg_stat_io</structname>
view and there is only one of each.
+ </para>
+ <para>
+ This function returns no rows unless the caller is a superuser, has
+ privileges of the <literal>pg_read_all_stats</literal> role, or is a
+ member of the role that owns the backend.
</para></entry>
</row>
@@ -5834,6 +5839,11 @@ description | Waiting for a newly initialized WAL file to reach durable storage
<para>
The function does not return lock statistics for the checkpointer,
the background writer, the startup process and the autovacuum launcher.
+ </para>
+ <para>
+ This function returns no rows unless the caller is a superuser, has
+ privileges of the <literal>pg_read_all_stats</literal> role, or is a
+ member of the role that owns the backend.
</para></entry>
</row>
@@ -5853,6 +5863,11 @@ description | Waiting for a newly initialized WAL file to reach durable storage
<para>
The function does not return WAL statistics for the checkpointer,
the background writer, the startup process and the autovacuum launcher.
+ </para>
+ <para>
+ This function returns NULL unless the caller is a superuser, has
+ privileges of the <literal>pg_read_all_stats</literal> role, or is a
+ member of the role that owns the backend.
</para></entry>
</row>
@@ -6211,6 +6226,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 details of a session's activity are only shown if
+ 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">
diff --git a/src/backend/utils/activity/backend_status.c b/src/backend/utils/activity/backend_status.c
index d685fc5cd87..fdeae791227 100644
--- a/src/backend/utils/activity/backend_status.c
+++ b/src/backend/utils/activity/backend_status.c
@@ -460,7 +460,7 @@ pgstat_bestart_final(void)
/* Create the backend statistics entry */
if (pgstat_tracks_backend_bktype(MyBackendType))
- pgstat_create_backend(MyProcNumber);
+ pgstat_create_backend(MyProcNumber, userid);
/* Update app name to current GUC setting */
if (application_name)
diff --git a/src/backend/utils/activity/pgstat_backend.c b/src/backend/utils/activity/pgstat_backend.c
index b54789eac35..c118ca2d3f2 100644
--- a/src/backend/utils/activity/pgstat_backend.c
+++ b/src/backend/utils/activity/pgstat_backend.c
@@ -394,9 +394,14 @@ pgstat_backend_flush_cb(bool nowait)
/*
* Create backend statistics entry for proc number.
+ *
+ * "userid" is the OID of the role that owns the backend, or InvalidOid for a
+ * process that has no session user. It is stored in the entry so that the
+ * functions reporting these statistics can check whether their caller is
+ * allowed to see them, based on the statistics data alone.
*/
void
-pgstat_create_backend(ProcNumber procnum)
+pgstat_create_backend(ProcNumber procnum, Oid userid)
{
PgStat_EntryRef *entry_ref;
PgStatShared_Backend *shstatent;
@@ -411,7 +416,9 @@ pgstat_create_backend(ProcNumber procnum)
*/
memset(&shstatent->stats, 0, sizeof(shstatent->stats));
shstatent->stats.pid = MyProcPid;
+ shstatent->stats.userid = userid;
shstatent->pid = MyProcPid;
+ shstatent->userid = userid;
pgstat_unlock_entry(entry_ref);
MemSet(&PendingBackendStats, 0, sizeof(PgStat_BackendPending));
@@ -485,6 +492,10 @@ pgstat_backend_reset_timestamp_cb(PgStatShared_Common *header, TimestampTz ts)
shstatent->stats.stat_reset_timestamp = ts;
- /* a reset zeroes the whole entry, so restore the PID of its owner */
+ /*
+ * A reset zeroes the whole entry, so restore the PID and the owner of the
+ * backend.
+ */
shstatent->stats.pid = shstatent->pid;
+ shstatent->stats.userid = shstatent->userid;
}
diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c
index 64b6f60516c..1e6702794ed 100644
--- a/src/backend/utils/adt/pgstatfuncs.c
+++ b/src/backend/utils/adt/pgstatfuncs.c
@@ -838,7 +838,9 @@ pg_stat_get_backend_subxact(PG_FUNCTION_ARGS)
TupleDescFinalize(tupdesc);
BlessTupleDesc(tupdesc);
- if ((local_beentry = pgstat_get_local_beentry_by_proc_number(procNumber)) != NULL)
+ /* Report the details of a session only to a caller 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);
@@ -1682,7 +1684,8 @@ pg_stat_get_backend_io(PG_FUNCTION_ARGS)
pid = PG_GETARG_INT32(0);
backend_stats = pgstat_fetch_stat_backend_by_pid(pid, &bktype);
- if (!backend_stats)
+ /* Report the details of a session only to a caller allowed to see them */
+ if (!backend_stats || !HAS_PGSTAT_PERMISSIONS(backend_stats->userid))
return (Datum) 0;
bktype_stats = &backend_stats->io_stats;
@@ -1775,7 +1778,8 @@ pg_stat_get_backend_wal(PG_FUNCTION_ARGS)
pid = PG_GETARG_INT32(0);
backend_stats = pgstat_fetch_stat_backend_by_pid(pid, NULL);
- if (!backend_stats)
+ /* Report the details of a session only to a caller allowed to see them */
+ if (!backend_stats || !HAS_PGSTAT_PERMISSIONS(backend_stats->userid))
PG_RETURN_NULL();
bktype_stats = backend_stats->wal_counters;
@@ -1866,7 +1870,8 @@ pg_stat_get_backend_lock(PG_FUNCTION_ARGS)
pid = PG_GETARG_INT32(0);
backend_stats = pgstat_fetch_stat_backend_by_pid(pid, NULL);
- if (!backend_stats)
+ /* Report the details of a session only to a caller allowed to see them */
+ if (!backend_stats || !HAS_PGSTAT_PERMISSIONS(backend_stats->userid))
return (Datum) 0;
pg_stat_lock_build_tuples(rsinfo, backend_stats->lock_stats.stats,
diff --git a/src/include/pgstat.h b/src/include/pgstat.h
index eb66ae79ee9..410baf8aa18 100644
--- a/src/include/pgstat.h
+++ b/src/include/pgstat.h
@@ -592,6 +592,7 @@ typedef struct PgStat_WalStats
typedef struct PgStat_Backend
{
int pid; /* PID of the backend owning these stats */
+ Oid userid; /* role owning the backend, or InvalidOid */
TimestampTz stat_reset_timestamp;
PgStat_BktypeIO io_stats;
PgStat_WalCounters wal_counters;
@@ -674,7 +675,7 @@ extern PgStat_Backend *pgstat_fetch_stat_backend(ProcNumber procNumber);
extern PgStat_Backend *pgstat_fetch_stat_backend_by_pid(int pid,
BackendType *bktype);
extern bool pgstat_tracks_backend_bktype(BackendType bktype);
-extern void pgstat_create_backend(ProcNumber procnum);
+extern void pgstat_create_backend(ProcNumber procnum, Oid userid);
/*
* Functions in pgstat_bgwriter.c
diff --git a/src/include/utils/pgstat_internal.h b/src/include/utils/pgstat_internal.h
index 0ed2830d4a0..02b2eedb32b 100644
--- a/src/include/utils/pgstat_internal.h
+++ b/src/include/utils/pgstat_internal.h
@@ -537,7 +537,9 @@ typedef struct PgStatShared_Backend
PgStatShared_Common header;
PgStat_Backend stats;
+ /* copies of the fields of "stats" to restore after a reset */
int pid;
+ Oid userid;
} PgStatShared_Backend;
/*
diff --git a/src/test/regress/expected/stats.out b/src/test/regress/expected/stats.out
index 8b15471248b..fc1870bfd4b 100644
--- a/src/test/regress/expected/stats.out
+++ b/src/test/regress/expected/stats.out
@@ -1141,6 +1141,65 @@ WHERE pg_stat_get_backend_pid(beid) = pg_backend_pid();
t
(1 row)
+-- The per-backend statistics functions report the details of a session only
+-- to a caller that is allowed to see them: a superuser, a role with
+-- privileges of pg_read_all_stats, or the role that owns the session.
+SELECT beid FROM pg_stat_get_backend_idset() beid
+WHERE pg_stat_get_backend_pid(beid) = pg_backend_pid() \gset
+SELECT current_user AS regress_stat_backend_owner \gset
+CREATE ROLE regress_stat_backend_role;
+-- a role with privileges of the role that owns this backend sees them
+GRANT :"regress_stat_backend_owner" TO regress_stat_backend_role;
+SET ROLE regress_stat_backend_role;
+SELECT (SELECT subxact_count IS NOT NULL
+ FROM pg_stat_get_backend_subxact(:beid)) AS subxact,
+ (SELECT count(*) > 0
+ FROM pg_stat_get_backend_io(pg_backend_pid())) AS io,
+ (SELECT count(*) > 0
+ FROM pg_stat_get_backend_lock(pg_backend_pid())) AS locks,
+ (SELECT wal_records IS NOT NULL
+ FROM pg_stat_get_backend_wal(pg_backend_pid())) AS wal;
+ subxact | io | locks | wal
+---------+----+-------+-----
+ t | t | t | t
+(1 row)
+
+RESET ROLE;
+REVOKE :"regress_stat_backend_owner" FROM regress_stat_backend_role;
+SET ROLE regress_stat_backend_role;
+-- an unrelated role sees nothing
+SELECT (SELECT subxact_count IS NOT NULL
+ FROM pg_stat_get_backend_subxact(:beid)) AS subxact,
+ (SELECT count(*) > 0
+ FROM pg_stat_get_backend_io(pg_backend_pid())) AS io,
+ (SELECT count(*) > 0
+ FROM pg_stat_get_backend_lock(pg_backend_pid())) AS locks,
+ (SELECT wal_records IS NOT NULL
+ FROM pg_stat_get_backend_wal(pg_backend_pid())) AS wal;
+ subxact | io | locks | wal
+---------+----+-------+-----
+ f | f | f | f
+(1 row)
+
+RESET ROLE;
+-- but a role with privileges of pg_read_all_stats sees them again
+GRANT pg_read_all_stats TO regress_stat_backend_role;
+SET ROLE regress_stat_backend_role;
+SELECT (SELECT subxact_count IS NOT NULL
+ FROM pg_stat_get_backend_subxact(:beid)) AS subxact,
+ (SELECT count(*) > 0
+ FROM pg_stat_get_backend_io(pg_backend_pid())) AS io,
+ (SELECT count(*) > 0
+ FROM pg_stat_get_backend_lock(pg_backend_pid())) AS locks,
+ (SELECT wal_records IS NOT NULL
+ FROM pg_stat_get_backend_wal(pg_backend_pid())) AS wal;
+ subxact | io | locks | wal
+---------+----+-------+-----
+ t | t | t | t
+(1 row)
+
+RESET ROLE;
+DROP ROLE regress_stat_backend_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..17c8e2f5231 100644
--- a/src/test/regress/sql/stats.sql
+++ b/src/test/regress/sql/stats.sql
@@ -535,6 +535,51 @@ 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();
+-- The per-backend statistics functions report the details of a session only
+-- to a caller that is allowed to see them: a superuser, a role with
+-- privileges of pg_read_all_stats, or the role that owns the session.
+SELECT beid FROM pg_stat_get_backend_idset() beid
+WHERE pg_stat_get_backend_pid(beid) = pg_backend_pid() \gset
+SELECT current_user AS regress_stat_backend_owner \gset
+CREATE ROLE regress_stat_backend_role;
+-- a role with privileges of the role that owns this backend sees them
+GRANT :"regress_stat_backend_owner" TO regress_stat_backend_role;
+SET ROLE regress_stat_backend_role;
+SELECT (SELECT subxact_count IS NOT NULL
+ FROM pg_stat_get_backend_subxact(:beid)) AS subxact,
+ (SELECT count(*) > 0
+ FROM pg_stat_get_backend_io(pg_backend_pid())) AS io,
+ (SELECT count(*) > 0
+ FROM pg_stat_get_backend_lock(pg_backend_pid())) AS locks,
+ (SELECT wal_records IS NOT NULL
+ FROM pg_stat_get_backend_wal(pg_backend_pid())) AS wal;
+RESET ROLE;
+REVOKE :"regress_stat_backend_owner" FROM regress_stat_backend_role;
+SET ROLE regress_stat_backend_role;
+-- an unrelated role sees nothing
+SELECT (SELECT subxact_count IS NOT NULL
+ FROM pg_stat_get_backend_subxact(:beid)) AS subxact,
+ (SELECT count(*) > 0
+ FROM pg_stat_get_backend_io(pg_backend_pid())) AS io,
+ (SELECT count(*) > 0
+ FROM pg_stat_get_backend_lock(pg_backend_pid())) AS locks,
+ (SELECT wal_records IS NOT NULL
+ FROM pg_stat_get_backend_wal(pg_backend_pid())) AS wal;
+RESET ROLE;
+-- but a role with privileges of pg_read_all_stats sees them again
+GRANT pg_read_all_stats TO regress_stat_backend_role;
+SET ROLE regress_stat_backend_role;
+SELECT (SELECT subxact_count IS NOT NULL
+ FROM pg_stat_get_backend_subxact(:beid)) AS subxact,
+ (SELECT count(*) > 0
+ FROM pg_stat_get_backend_io(pg_backend_pid())) AS io,
+ (SELECT count(*) > 0
+ FROM pg_stat_get_backend_lock(pg_backend_pid())) AS locks,
+ (SELECT wal_records IS NOT NULL
+ FROM pg_stat_get_backend_wal(pg_backend_pid())) AS wal;
+RESET ROLE;
+DROP ROLE regress_stat_backend_role;
+
-----
-- Test that resetting stats works for reset timestamp
-----
--
2.37.1 (Apple Git-137.1)
[application/octet-stream] v8-0001-Store-the-PID-of-a-backend-in-its-statistics-entr.patch (3.1K, ../../CAGRkXqTLuE3PrRn7tXJ8t+7UHcTUJ9cuaFxbZkuhpAySD0RpfA@mail.gmail.com/4-v8-0001-Store-the-PID-of-a-backend-in-its-statistics-entr.patch)
download | inline diff:
From f70a0c631a62ee157414a493bf68dd4ff0d1104f Mon Sep 17 00:00:00 2001
From: Shihao <zhong950419@gmail.com>
Date: Mon, 21 Sep 2026 10:10:13 -0400
Subject: [PATCH v8 1/2] Store the PID of a backend in its statistics entry
With stats_fetch_consistency set to "snapshot", the statistics of a
backend could be reported under the PID of a newer backend that reused
its proc number. Store the PID of the backend in PgStat_Backend when the
entry is created, and ignore an entry whose PID does not match the one
requested. A reset zeroes the whole entry, so a copy of the PID is kept
in PgStatShared_Backend and restored by the reset callback.
Reported-by: Bertrand Drouvot <bertranddrouvot.pg@gmail.com>
Author: Shihao Zhong <zhong950419@gmail.com>
Discussion: https://postgr.es/m/arD/w47Ug1GaObfq@bdtpg
---
src/backend/utils/activity/pgstat_backend.c | 13 ++++++++++++-
src/include/pgstat.h | 1 +
src/include/utils/pgstat_internal.h | 2 ++
3 files changed, 15 insertions(+), 1 deletion(-)
diff --git a/src/backend/utils/activity/pgstat_backend.c b/src/backend/utils/activity/pgstat_backend.c
index b736b2ccc6f..b54789eac35 100644
--- a/src/backend/utils/activity/pgstat_backend.c
+++ b/src/backend/utils/activity/pgstat_backend.c
@@ -183,6 +183,10 @@ pgstat_fetch_stat_backend_by_pid(int pid, BackendType *bktype)
* value of stats_fetch_consistency, so do not access it from this point.
*/
backend_stats = pgstat_fetch_stat_backend(procNumber);
+
+ if (backend_stats && backend_stats->pid != pid)
+ backend_stats = NULL;
+
if (!backend_stats)
{
if (bktype)
@@ -406,6 +410,8 @@ pgstat_create_backend(ProcNumber procnum)
* e.g. if we previously used this proc number.
*/
memset(&shstatent->stats, 0, sizeof(shstatent->stats));
+ shstatent->stats.pid = MyProcPid;
+ shstatent->pid = MyProcPid;
pgstat_unlock_entry(entry_ref);
MemSet(&PendingBackendStats, 0, sizeof(PgStat_BackendPending));
@@ -475,5 +481,10 @@ pgstat_tracks_backend_bktype(BackendType bktype)
void
pgstat_backend_reset_timestamp_cb(PgStatShared_Common *header, TimestampTz ts)
{
- ((PgStatShared_Backend *) header)->stats.stat_reset_timestamp = ts;
+ PgStatShared_Backend *shstatent = (PgStatShared_Backend *) header;
+
+ shstatent->stats.stat_reset_timestamp = ts;
+
+ /* a reset zeroes the whole entry, so restore the PID of its owner */
+ shstatent->stats.pid = shstatent->pid;
}
diff --git a/src/include/pgstat.h b/src/include/pgstat.h
index 187d82c96fe..eb66ae79ee9 100644
--- a/src/include/pgstat.h
+++ b/src/include/pgstat.h
@@ -591,6 +591,7 @@ typedef struct PgStat_WalStats
*/
typedef struct PgStat_Backend
{
+ int pid; /* PID of the backend owning these stats */
TimestampTz stat_reset_timestamp;
PgStat_BktypeIO io_stats;
PgStat_WalCounters wal_counters;
diff --git a/src/include/utils/pgstat_internal.h b/src/include/utils/pgstat_internal.h
index 14369e59a1c..0ed2830d4a0 100644
--- a/src/include/utils/pgstat_internal.h
+++ b/src/include/utils/pgstat_internal.h
@@ -536,6 +536,8 @@ typedef struct PgStatShared_Backend
{
PgStatShared_Common header;
PgStat_Backend stats;
+
+ int pid;
} PgStatShared_Backend;
/*
--
2.37.1 (Apple Git-137.1)
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: Add a permission check to pg_stat_get_backend_subxact()
@ 2026-09-23 07:23 Bertrand Drouvot <bertranddrouvot.pg@gmail.com>
parent: shihao zhong <zhong950419@gmail.com>
0 siblings, 2 replies; 21+ messages in thread
From: Bertrand Drouvot @ 2026-09-23 07:23 UTC (permalink / raw)
To: shihao zhong <zhong950419@gmail.com>; +Cc: Michael Paquier <michael@paquier.xyz>; Jim Jones <jim.jones@uni-muenster.de>; pgsql-hackers <pgsql-hackers@lists.postgresql.org>
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]. 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
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: Add a permission check to pg_stat_get_backend_subxact()
@ 2026-09-24 00:39 shihao zhong <zhong950419@gmail.com>
parent: Bertrand Drouvot <bertranddrouvot.pg@gmail.com>
1 sibling, 0 replies; 21+ messages in thread
From: shihao zhong @ 2026-09-24 00:39 UTC (permalink / raw)
To: Bertrand Drouvot <bertranddrouvot.pg@gmail.com>; +Cc: Michael Paquier <michael@paquier.xyz>; Jim Jones <jim.jones@uni-muenster.de>; pgsql-hackers <pgsql-hackers@lists.postgresql.org>
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:
[application/octet-stream] v9-0002-Check-the-PID-when-reading-the-transaction-status.patch (3.5K, ../../CAGRkXqSNCCO3Msnw3isDsshw-DO4a9gXYJTmDqaG2LofN1vo5Q@mail.gmail.com/3-v9-0002-Check-the-PID-when-reading-the-transaction-status.patch)
download | inline diff:
From aa65f12b4938e73dd86049bbd979e9c3ec334088 Mon Sep 17 00:00:00 2001
From: Shihao <zhong950419@gmail.com>
Date: Wed, 23 Sep 2026 08:05:20 -0400
Subject: [PATCH v9 2/3] Check the PID when reading the transaction status of a
backend
pgstat_read_current_status() copies the status entry of a backend, then
reads its transaction status from its PGPROC entry, in a second step.
If the backend exited and its proc number was reused in between, the
transaction ID, xmin and subtransaction counters of the new backend were
reported under the PID and the session user of the old one.
Pass the PID copied from the status entry to
ProcNumberGetTransactionIds(), and report nothing if the PGPROC entry
does not hold it anymore, checked under ProcArrayLock.
Reported-by: Bertrand Drouvot <bertranddrouvot.pg@gmail.com>
Author: Shihao Zhong <zhong950419@gmail.com>
Discussion: https://postgr.es/m/arN+VT3xyiTY/iUD@bdtpg
---
src/backend/storage/ipc/procarray.c | 8 ++++++--
src/backend/utils/activity/backend_status.c | 1 +
src/include/storage/procarray.h | 2 +-
3 files changed, 8 insertions(+), 3 deletions(-)
diff --git a/src/backend/storage/ipc/procarray.c b/src/backend/storage/ipc/procarray.c
index b7e03134ed8..7124974332d 100644
--- a/src/backend/storage/ipc/procarray.c
+++ b/src/backend/storage/ipc/procarray.c
@@ -3120,9 +3120,13 @@ ProcNumberGetProc(ProcNumber procNumber)
* Get the xid, xmin, nsubxid and overflow status of the backend. The
* result may be out of date arbitrarily quickly, so the caller must be
* careful about how this information is used.
+ *
+ * "pid" is the PID of the backend the caller expects to find using this
+ * proc number. If the proc number has been reused by a different backend
+ * since the caller looked at it, nothing is reported.
*/
void
-ProcNumberGetTransactionIds(ProcNumber procNumber, TransactionId *xid,
+ProcNumberGetTransactionIds(ProcNumber procNumber, int pid, TransactionId *xid,
TransactionId *xmin, int *nsubxid, bool *overflowed)
{
PGPROC *proc;
@@ -3139,7 +3143,7 @@ ProcNumberGetTransactionIds(ProcNumber procNumber, TransactionId *xid,
/* Need to lock out additions/removals of backends */
LWLockAcquire(ProcArrayLock, LW_SHARED);
- if (proc->pid != 0)
+ if (proc->pid != 0 && proc->pid == pid)
{
*xid = proc->xid;
*xmin = proc->xmin;
diff --git a/src/backend/utils/activity/backend_status.c b/src/backend/utils/activity/backend_status.c
index d685fc5cd87..eb78ca8795b 100644
--- a/src/backend/utils/activity/backend_status.c
+++ b/src/backend/utils/activity/backend_status.c
@@ -912,6 +912,7 @@ pgstat_read_current_status(void)
*/
localentry->proc_number = procNumber;
ProcNumberGetTransactionIds(procNumber,
+ localentry->backendStatus.st_procpid,
&localentry->backend_xid,
&localentry->backend_xmin,
&localentry->backend_subxact_count,
diff --git a/src/include/storage/procarray.h b/src/include/storage/procarray.h
index d718a5b542f..b41514178ad 100644
--- a/src/include/storage/procarray.h
+++ b/src/include/storage/procarray.h
@@ -62,7 +62,7 @@ extern bool HaveVirtualXIDsDelayingChkpt(VirtualTransactionId *vxids,
int nvxids, int type);
extern PGPROC *ProcNumberGetProc(int procNumber);
-extern void ProcNumberGetTransactionIds(int procNumber, TransactionId *xid,
+extern void ProcNumberGetTransactionIds(int procNumber, int pid, TransactionId *xid,
TransactionId *xmin, int *nsubxid,
bool *overflowed);
extern PGPROC *BackendPidGetProc(int pid);
--
2.37.1 (Apple Git-137.1)
[application/octet-stream] v9-0003-Make-per-backend-statistics-functions-respect-sta.patch (15.7K, ../../CAGRkXqSNCCO3Msnw3isDsshw-DO4a9gXYJTmDqaG2LofN1vo5Q@mail.gmail.com/4-v9-0003-Make-per-backend-statistics-functions-respect-sta.patch)
download | inline diff:
From c78a190bee06901d65b4d9b0ae6cdf17f29e25dd Mon Sep 17 00:00:00 2001
From: Shihao Zhong <zhong950419@gmail.com>
Date: Fri, 11 Sep 2026 12:26:11 +0200
Subject: [PATCH v9 3/3] Make per-backend statistics functions respect
statistics permissions
The per-backend statistics functions that report the details of a session
call HAS_PGSTAT_PERMISSIONS() first, so that they hide these details from
a caller that is neither a superuser, nor has privileges of
pg_read_all_stats, nor is a member of the role that owns the session.
pg_stat_get_backend_subxact(), pg_stat_get_backend_io(),
pg_stat_get_backend_wal() and pg_stat_get_backend_lock() lacked this
check. Add it, for consistency with the sibling functions. Like
pg_stat_activity, this also hides the statistics of processes owned by
no role, such as autovacuum workers or the WAL writer, from callers
without these privileges.
pg_stat_get_backend_subxact() reads the backend status entry, so it checks
the role recorded there, like its sibling functions. The last three report
cumulative statistics that may come from a snapshot, taken while an older
backend held the same proc number, so the role recorded in the backend
status entry may not be the one that owns the statistics. Store the OID of
the role that owns the backend in PgStat_Backend when its entry is created,
and check the caller against that, based on the statistics data alone.
The permission rule was documented for the dynamic statistics views but
not for these functions, so state it above the per-backend function table
and on the three functions listed among the additional statistics
functions.
This changes the output of existing functions for callers lacking the
required privileges, so no backpatch is done.
Author: Shihao Zhong <zhong950419@gmail.com>
Author: Jim Jones <jim.jones@uni-muenster.de>
Reviewed-by: Michael Paquier <michael@paquier.xyz>
Discussion: https://postgr.es/m/CAGRkXqTBZ+zbVuDC8xGEB6Btj61hsui5H5nGqzFBDyOXc=4bjQ@mail.gmail.com
---
doc/src/sgml/monitoring.sgml | 25 +++++++++
src/backend/utils/activity/backend_status.c | 2 +-
src/backend/utils/activity/pgstat_backend.c | 15 +++++-
src/backend/utils/adt/pgstatfuncs.c | 13 +++--
src/include/pgstat.h | 3 +-
src/include/utils/pgstat_internal.h | 2 +
src/test/regress/expected/stats.out | 59 +++++++++++++++++++++
src/test/regress/sql/stats.sql | 45 ++++++++++++++++
8 files changed, 156 insertions(+), 8 deletions(-)
diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml
index 62dadf3e86c..78f224bf9e8 100644
--- a/doc/src/sgml/monitoring.sgml
+++ b/doc/src/sgml/monitoring.sgml
@@ -5799,6 +5799,11 @@ description | Waiting for a newly initialized WAL file to reach durable storage
the background writer, the startup process and the autovacuum launcher
as they are already visible in the <structname>pg_stat_io</structname>
view and there is only one of each.
+ </para>
+ <para>
+ This function returns no rows unless the caller is a superuser, has
+ privileges of the <literal>pg_read_all_stats</literal> role, or is a
+ member of the role that owns the backend.
</para></entry>
</row>
@@ -5834,6 +5839,11 @@ description | Waiting for a newly initialized WAL file to reach durable storage
<para>
The function does not return lock statistics for the checkpointer,
the background writer, the startup process and the autovacuum launcher.
+ </para>
+ <para>
+ This function returns no rows unless the caller is a superuser, has
+ privileges of the <literal>pg_read_all_stats</literal> role, or is a
+ member of the role that owns the backend.
</para></entry>
</row>
@@ -5853,6 +5863,11 @@ description | Waiting for a newly initialized WAL file to reach durable storage
<para>
The function does not return WAL statistics for the checkpointer,
the background writer, the startup process and the autovacuum launcher.
+ </para>
+ <para>
+ This function returns NULL unless the caller is a superuser, has
+ privileges of the <literal>pg_read_all_stats</literal> role, or is a
+ member of the role that owns the backend.
</para></entry>
</row>
@@ -6211,6 +6226,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 details of a session's activity are only shown if
+ 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">
diff --git a/src/backend/utils/activity/backend_status.c b/src/backend/utils/activity/backend_status.c
index eb78ca8795b..6a622ef6079 100644
--- a/src/backend/utils/activity/backend_status.c
+++ b/src/backend/utils/activity/backend_status.c
@@ -460,7 +460,7 @@ pgstat_bestart_final(void)
/* Create the backend statistics entry */
if (pgstat_tracks_backend_bktype(MyBackendType))
- pgstat_create_backend(MyProcNumber);
+ pgstat_create_backend(MyProcNumber, userid);
/* Update app name to current GUC setting */
if (application_name)
diff --git a/src/backend/utils/activity/pgstat_backend.c b/src/backend/utils/activity/pgstat_backend.c
index b54789eac35..c118ca2d3f2 100644
--- a/src/backend/utils/activity/pgstat_backend.c
+++ b/src/backend/utils/activity/pgstat_backend.c
@@ -394,9 +394,14 @@ pgstat_backend_flush_cb(bool nowait)
/*
* Create backend statistics entry for proc number.
+ *
+ * "userid" is the OID of the role that owns the backend, or InvalidOid for a
+ * process that has no session user. It is stored in the entry so that the
+ * functions reporting these statistics can check whether their caller is
+ * allowed to see them, based on the statistics data alone.
*/
void
-pgstat_create_backend(ProcNumber procnum)
+pgstat_create_backend(ProcNumber procnum, Oid userid)
{
PgStat_EntryRef *entry_ref;
PgStatShared_Backend *shstatent;
@@ -411,7 +416,9 @@ pgstat_create_backend(ProcNumber procnum)
*/
memset(&shstatent->stats, 0, sizeof(shstatent->stats));
shstatent->stats.pid = MyProcPid;
+ shstatent->stats.userid = userid;
shstatent->pid = MyProcPid;
+ shstatent->userid = userid;
pgstat_unlock_entry(entry_ref);
MemSet(&PendingBackendStats, 0, sizeof(PgStat_BackendPending));
@@ -485,6 +492,10 @@ pgstat_backend_reset_timestamp_cb(PgStatShared_Common *header, TimestampTz ts)
shstatent->stats.stat_reset_timestamp = ts;
- /* a reset zeroes the whole entry, so restore the PID of its owner */
+ /*
+ * A reset zeroes the whole entry, so restore the PID and the owner of the
+ * backend.
+ */
shstatent->stats.pid = shstatent->pid;
+ shstatent->stats.userid = shstatent->userid;
}
diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c
index 64b6f60516c..1e6702794ed 100644
--- a/src/backend/utils/adt/pgstatfuncs.c
+++ b/src/backend/utils/adt/pgstatfuncs.c
@@ -838,7 +838,9 @@ pg_stat_get_backend_subxact(PG_FUNCTION_ARGS)
TupleDescFinalize(tupdesc);
BlessTupleDesc(tupdesc);
- if ((local_beentry = pgstat_get_local_beentry_by_proc_number(procNumber)) != NULL)
+ /* Report the details of a session only to a caller 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);
@@ -1682,7 +1684,8 @@ pg_stat_get_backend_io(PG_FUNCTION_ARGS)
pid = PG_GETARG_INT32(0);
backend_stats = pgstat_fetch_stat_backend_by_pid(pid, &bktype);
- if (!backend_stats)
+ /* Report the details of a session only to a caller allowed to see them */
+ if (!backend_stats || !HAS_PGSTAT_PERMISSIONS(backend_stats->userid))
return (Datum) 0;
bktype_stats = &backend_stats->io_stats;
@@ -1775,7 +1778,8 @@ pg_stat_get_backend_wal(PG_FUNCTION_ARGS)
pid = PG_GETARG_INT32(0);
backend_stats = pgstat_fetch_stat_backend_by_pid(pid, NULL);
- if (!backend_stats)
+ /* Report the details of a session only to a caller allowed to see them */
+ if (!backend_stats || !HAS_PGSTAT_PERMISSIONS(backend_stats->userid))
PG_RETURN_NULL();
bktype_stats = backend_stats->wal_counters;
@@ -1866,7 +1870,8 @@ pg_stat_get_backend_lock(PG_FUNCTION_ARGS)
pid = PG_GETARG_INT32(0);
backend_stats = pgstat_fetch_stat_backend_by_pid(pid, NULL);
- if (!backend_stats)
+ /* Report the details of a session only to a caller allowed to see them */
+ if (!backend_stats || !HAS_PGSTAT_PERMISSIONS(backend_stats->userid))
return (Datum) 0;
pg_stat_lock_build_tuples(rsinfo, backend_stats->lock_stats.stats,
diff --git a/src/include/pgstat.h b/src/include/pgstat.h
index 759fc767052..55dc7a8565d 100644
--- a/src/include/pgstat.h
+++ b/src/include/pgstat.h
@@ -596,6 +596,7 @@ typedef struct PgStat_Backend
PgStat_WalCounters wal_counters;
PgStat_PendingLock lock_stats;
int pid; /* PID of the backend owning these stats */
+ Oid userid; /* role owning the backend, or InvalidOid */
} PgStat_Backend;
/* ---------
@@ -674,7 +675,7 @@ extern PgStat_Backend *pgstat_fetch_stat_backend(ProcNumber procNumber);
extern PgStat_Backend *pgstat_fetch_stat_backend_by_pid(int pid,
BackendType *bktype);
extern bool pgstat_tracks_backend_bktype(BackendType bktype);
-extern void pgstat_create_backend(ProcNumber procnum);
+extern void pgstat_create_backend(ProcNumber procnum, Oid userid);
/*
* Functions in pgstat_bgwriter.c
diff --git a/src/include/utils/pgstat_internal.h b/src/include/utils/pgstat_internal.h
index 0ed2830d4a0..02b2eedb32b 100644
--- a/src/include/utils/pgstat_internal.h
+++ b/src/include/utils/pgstat_internal.h
@@ -537,7 +537,9 @@ typedef struct PgStatShared_Backend
PgStatShared_Common header;
PgStat_Backend stats;
+ /* copies of the fields of "stats" to restore after a reset */
int pid;
+ Oid userid;
} PgStatShared_Backend;
/*
diff --git a/src/test/regress/expected/stats.out b/src/test/regress/expected/stats.out
index 8b15471248b..fc1870bfd4b 100644
--- a/src/test/regress/expected/stats.out
+++ b/src/test/regress/expected/stats.out
@@ -1141,6 +1141,65 @@ WHERE pg_stat_get_backend_pid(beid) = pg_backend_pid();
t
(1 row)
+-- The per-backend statistics functions report the details of a session only
+-- to a caller that is allowed to see them: a superuser, a role with
+-- privileges of pg_read_all_stats, or the role that owns the session.
+SELECT beid FROM pg_stat_get_backend_idset() beid
+WHERE pg_stat_get_backend_pid(beid) = pg_backend_pid() \gset
+SELECT current_user AS regress_stat_backend_owner \gset
+CREATE ROLE regress_stat_backend_role;
+-- a role with privileges of the role that owns this backend sees them
+GRANT :"regress_stat_backend_owner" TO regress_stat_backend_role;
+SET ROLE regress_stat_backend_role;
+SELECT (SELECT subxact_count IS NOT NULL
+ FROM pg_stat_get_backend_subxact(:beid)) AS subxact,
+ (SELECT count(*) > 0
+ FROM pg_stat_get_backend_io(pg_backend_pid())) AS io,
+ (SELECT count(*) > 0
+ FROM pg_stat_get_backend_lock(pg_backend_pid())) AS locks,
+ (SELECT wal_records IS NOT NULL
+ FROM pg_stat_get_backend_wal(pg_backend_pid())) AS wal;
+ subxact | io | locks | wal
+---------+----+-------+-----
+ t | t | t | t
+(1 row)
+
+RESET ROLE;
+REVOKE :"regress_stat_backend_owner" FROM regress_stat_backend_role;
+SET ROLE regress_stat_backend_role;
+-- an unrelated role sees nothing
+SELECT (SELECT subxact_count IS NOT NULL
+ FROM pg_stat_get_backend_subxact(:beid)) AS subxact,
+ (SELECT count(*) > 0
+ FROM pg_stat_get_backend_io(pg_backend_pid())) AS io,
+ (SELECT count(*) > 0
+ FROM pg_stat_get_backend_lock(pg_backend_pid())) AS locks,
+ (SELECT wal_records IS NOT NULL
+ FROM pg_stat_get_backend_wal(pg_backend_pid())) AS wal;
+ subxact | io | locks | wal
+---------+----+-------+-----
+ f | f | f | f
+(1 row)
+
+RESET ROLE;
+-- but a role with privileges of pg_read_all_stats sees them again
+GRANT pg_read_all_stats TO regress_stat_backend_role;
+SET ROLE regress_stat_backend_role;
+SELECT (SELECT subxact_count IS NOT NULL
+ FROM pg_stat_get_backend_subxact(:beid)) AS subxact,
+ (SELECT count(*) > 0
+ FROM pg_stat_get_backend_io(pg_backend_pid())) AS io,
+ (SELECT count(*) > 0
+ FROM pg_stat_get_backend_lock(pg_backend_pid())) AS locks,
+ (SELECT wal_records IS NOT NULL
+ FROM pg_stat_get_backend_wal(pg_backend_pid())) AS wal;
+ subxact | io | locks | wal
+---------+----+-------+-----
+ t | t | t | t
+(1 row)
+
+RESET ROLE;
+DROP ROLE regress_stat_backend_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..17c8e2f5231 100644
--- a/src/test/regress/sql/stats.sql
+++ b/src/test/regress/sql/stats.sql
@@ -535,6 +535,51 @@ 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();
+-- The per-backend statistics functions report the details of a session only
+-- to a caller that is allowed to see them: a superuser, a role with
+-- privileges of pg_read_all_stats, or the role that owns the session.
+SELECT beid FROM pg_stat_get_backend_idset() beid
+WHERE pg_stat_get_backend_pid(beid) = pg_backend_pid() \gset
+SELECT current_user AS regress_stat_backend_owner \gset
+CREATE ROLE regress_stat_backend_role;
+-- a role with privileges of the role that owns this backend sees them
+GRANT :"regress_stat_backend_owner" TO regress_stat_backend_role;
+SET ROLE regress_stat_backend_role;
+SELECT (SELECT subxact_count IS NOT NULL
+ FROM pg_stat_get_backend_subxact(:beid)) AS subxact,
+ (SELECT count(*) > 0
+ FROM pg_stat_get_backend_io(pg_backend_pid())) AS io,
+ (SELECT count(*) > 0
+ FROM pg_stat_get_backend_lock(pg_backend_pid())) AS locks,
+ (SELECT wal_records IS NOT NULL
+ FROM pg_stat_get_backend_wal(pg_backend_pid())) AS wal;
+RESET ROLE;
+REVOKE :"regress_stat_backend_owner" FROM regress_stat_backend_role;
+SET ROLE regress_stat_backend_role;
+-- an unrelated role sees nothing
+SELECT (SELECT subxact_count IS NOT NULL
+ FROM pg_stat_get_backend_subxact(:beid)) AS subxact,
+ (SELECT count(*) > 0
+ FROM pg_stat_get_backend_io(pg_backend_pid())) AS io,
+ (SELECT count(*) > 0
+ FROM pg_stat_get_backend_lock(pg_backend_pid())) AS locks,
+ (SELECT wal_records IS NOT NULL
+ FROM pg_stat_get_backend_wal(pg_backend_pid())) AS wal;
+RESET ROLE;
+-- but a role with privileges of pg_read_all_stats sees them again
+GRANT pg_read_all_stats TO regress_stat_backend_role;
+SET ROLE regress_stat_backend_role;
+SELECT (SELECT subxact_count IS NOT NULL
+ FROM pg_stat_get_backend_subxact(:beid)) AS subxact,
+ (SELECT count(*) > 0
+ FROM pg_stat_get_backend_io(pg_backend_pid())) AS io,
+ (SELECT count(*) > 0
+ FROM pg_stat_get_backend_lock(pg_backend_pid())) AS locks,
+ (SELECT wal_records IS NOT NULL
+ FROM pg_stat_get_backend_wal(pg_backend_pid())) AS wal;
+RESET ROLE;
+DROP ROLE regress_stat_backend_role;
+
-----
-- Test that resetting stats works for reset timestamp
-----
--
2.37.1 (Apple Git-137.1)
[application/octet-stream] v9-0001-Store-the-PID-of-a-backend-in-its-statistics-entr.patch (3.3K, ../../CAGRkXqSNCCO3Msnw3isDsshw-DO4a9gXYJTmDqaG2LofN1vo5Q@mail.gmail.com/5-v9-0001-Store-the-PID-of-a-backend-in-its-statistics-entr.patch)
download | inline diff:
From 77c909686a4e9843e7ac091f083df86c56b5f2f7 Mon Sep 17 00:00:00 2001
From: Shihao <zhong950419@gmail.com>
Date: Mon, 21 Sep 2026 10:10:13 -0400
Subject: [PATCH v9 1/3] Store the PID of a backend in its statistics entry
With stats_fetch_consistency set to "snapshot", the statistics of a
backend could be reported under the PID of a newer backend that reused
its proc number. Store the PID of the backend in PgStat_Backend when the
entry is created, and ignore an entry whose PID does not match the one
requested. A reset zeroes the whole entry, so a copy of the PID is kept
in PgStatShared_Backend and restored by the reset callback.
The new field is added at the end of PgStat_Backend, so that the offsets
of the existing fields do not change in the stable branches.
Reported-by: Bertrand Drouvot <bertranddrouvot.pg@gmail.com>
Author: Shihao Zhong <zhong950419@gmail.com>
Discussion: https://postgr.es/m/arD/w47Ug1GaObfq@bdtpg
Backpatch-through: 18
---
src/backend/utils/activity/pgstat_backend.c | 13 ++++++++++++-
src/include/pgstat.h | 1 +
src/include/utils/pgstat_internal.h | 2 ++
3 files changed, 15 insertions(+), 1 deletion(-)
diff --git a/src/backend/utils/activity/pgstat_backend.c b/src/backend/utils/activity/pgstat_backend.c
index b736b2ccc6f..b54789eac35 100644
--- a/src/backend/utils/activity/pgstat_backend.c
+++ b/src/backend/utils/activity/pgstat_backend.c
@@ -183,6 +183,10 @@ pgstat_fetch_stat_backend_by_pid(int pid, BackendType *bktype)
* value of stats_fetch_consistency, so do not access it from this point.
*/
backend_stats = pgstat_fetch_stat_backend(procNumber);
+
+ if (backend_stats && backend_stats->pid != pid)
+ backend_stats = NULL;
+
if (!backend_stats)
{
if (bktype)
@@ -406,6 +410,8 @@ pgstat_create_backend(ProcNumber procnum)
* e.g. if we previously used this proc number.
*/
memset(&shstatent->stats, 0, sizeof(shstatent->stats));
+ shstatent->stats.pid = MyProcPid;
+ shstatent->pid = MyProcPid;
pgstat_unlock_entry(entry_ref);
MemSet(&PendingBackendStats, 0, sizeof(PgStat_BackendPending));
@@ -475,5 +481,10 @@ pgstat_tracks_backend_bktype(BackendType bktype)
void
pgstat_backend_reset_timestamp_cb(PgStatShared_Common *header, TimestampTz ts)
{
- ((PgStatShared_Backend *) header)->stats.stat_reset_timestamp = ts;
+ PgStatShared_Backend *shstatent = (PgStatShared_Backend *) header;
+
+ shstatent->stats.stat_reset_timestamp = ts;
+
+ /* a reset zeroes the whole entry, so restore the PID of its owner */
+ shstatent->stats.pid = shstatent->pid;
}
diff --git a/src/include/pgstat.h b/src/include/pgstat.h
index 187d82c96fe..759fc767052 100644
--- a/src/include/pgstat.h
+++ b/src/include/pgstat.h
@@ -595,6 +595,7 @@ typedef struct PgStat_Backend
PgStat_BktypeIO io_stats;
PgStat_WalCounters wal_counters;
PgStat_PendingLock lock_stats;
+ int pid; /* PID of the backend owning these stats */
} PgStat_Backend;
/* ---------
diff --git a/src/include/utils/pgstat_internal.h b/src/include/utils/pgstat_internal.h
index 14369e59a1c..0ed2830d4a0 100644
--- a/src/include/utils/pgstat_internal.h
+++ b/src/include/utils/pgstat_internal.h
@@ -536,6 +536,8 @@ typedef struct PgStatShared_Backend
{
PgStatShared_Common header;
PgStat_Backend stats;
+
+ int pid;
} PgStatShared_Backend;
/*
--
2.37.1 (Apple Git-137.1)
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: Add a permission check to pg_stat_get_backend_subxact()
@ 2026-09-24 01:01 Michael Paquier <michael@paquier.xyz>
parent: Bertrand Drouvot <bertranddrouvot.pg@gmail.com>
1 sibling, 1 reply; 21+ messages in thread
From: Michael Paquier @ 2026-09-24 01:01 UTC (permalink / raw)
To: Bertrand Drouvot <bertranddrouvot.pg@gmail.com>; +Cc: shihao zhong <zhong950419@gmail.com>; Jim Jones <jim.jones@uni-muenster.de>; pgsql-hackers <pgsql-hackers@lists.postgresql.org>
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
Attachments:
[application/pgp-signature] signature.asc (832B, ../../arR2TkF9msIowJjh@paquier.xyz/2-signature.asc)
download
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: Add a permission check to pg_stat_get_backend_subxact()
@ 2026-09-24 01:51 shihao zhong <zhong950419@gmail.com>
parent: Michael Paquier <michael@paquier.xyz>
0 siblings, 0 replies; 21+ messages in thread
From: shihao zhong @ 2026-09-24 01:51 UTC (permalink / raw)
To: Michael Paquier <michael@paquier.xyz>; +Cc: Bertrand Drouvot <bertranddrouvot.pg@gmail.com>; Jim Jones <jim.jones@uni-muenster.de>; pgsql-hackers <pgsql-hackers@lists.postgresql.org>
Hi Michael,
> 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!
OK, v10 drops the PID from the stats entry. v10-0001 is the permission
patch, and the entry stores only the role of the backend. That is enough
for the snapshot case upthread. The old entry keeps the role of the old
backend, so u1 gets nothing from it. I checked it with the same script.
> The role ID case is different: we want consistency to check for the
> permissions.
v10-0002 is optional and follows that idea for Bertrand's point on
pg_stat_get_backend_subxact(). The role is checked from the copied status
entry, but the subxact counters are read from PGPROC in a second step.
The PID check makes sure both come from the same backend. Your call.
Thanks,
Shihao
Attachments:
[application/octet-stream] v10-0002-Check-the-PID-when-reading-the-transaction-statu.patch (3.5K, ../../CAGRkXqQsOsB2_M73Y-cgQyzyXVx59qra-xFUo2-zQOTiNMSV8w@mail.gmail.com/3-v10-0002-Check-the-PID-when-reading-the-transaction-statu.patch)
download | inline diff:
From 6eb9ef47cd9494e08bcca8ee37eb7f73f1faa2df Mon Sep 17 00:00:00 2001
From: Shihao <zhong950419@gmail.com>
Date: Wed, 23 Sep 2026 08:05:20 -0400
Subject: [PATCH v10 2/2] Check the PID when reading the transaction status of
a backend
pgstat_read_current_status() copies the status entry of a backend, then
reads its transaction status from its PGPROC entry, in a second step.
If the backend exited and its proc number was reused in between, the
transaction ID, xmin and subtransaction counters of the new backend were
reported under the PID and the session user of the old one.
Pass the PID copied from the status entry to
ProcNumberGetTransactionIds(), and report nothing if the PGPROC entry
does not hold it anymore, checked under ProcArrayLock.
Reported-by: Bertrand Drouvot <bertranddrouvot.pg@gmail.com>
Author: Shihao Zhong <zhong950419@gmail.com>
Discussion: https://postgr.es/m/arN+VT3xyiTY/iUD@bdtpg
---
src/backend/storage/ipc/procarray.c | 8 ++++++--
src/backend/utils/activity/backend_status.c | 1 +
src/include/storage/procarray.h | 2 +-
3 files changed, 8 insertions(+), 3 deletions(-)
diff --git a/src/backend/storage/ipc/procarray.c b/src/backend/storage/ipc/procarray.c
index b7e03134ed8..7124974332d 100644
--- a/src/backend/storage/ipc/procarray.c
+++ b/src/backend/storage/ipc/procarray.c
@@ -3120,9 +3120,13 @@ ProcNumberGetProc(ProcNumber procNumber)
* Get the xid, xmin, nsubxid and overflow status of the backend. The
* result may be out of date arbitrarily quickly, so the caller must be
* careful about how this information is used.
+ *
+ * "pid" is the PID of the backend the caller expects to find using this
+ * proc number. If the proc number has been reused by a different backend
+ * since the caller looked at it, nothing is reported.
*/
void
-ProcNumberGetTransactionIds(ProcNumber procNumber, TransactionId *xid,
+ProcNumberGetTransactionIds(ProcNumber procNumber, int pid, TransactionId *xid,
TransactionId *xmin, int *nsubxid, bool *overflowed)
{
PGPROC *proc;
@@ -3139,7 +3143,7 @@ ProcNumberGetTransactionIds(ProcNumber procNumber, TransactionId *xid,
/* Need to lock out additions/removals of backends */
LWLockAcquire(ProcArrayLock, LW_SHARED);
- if (proc->pid != 0)
+ if (proc->pid != 0 && proc->pid == pid)
{
*xid = proc->xid;
*xmin = proc->xmin;
diff --git a/src/backend/utils/activity/backend_status.c b/src/backend/utils/activity/backend_status.c
index fdeae791227..6a622ef6079 100644
--- a/src/backend/utils/activity/backend_status.c
+++ b/src/backend/utils/activity/backend_status.c
@@ -912,6 +912,7 @@ pgstat_read_current_status(void)
*/
localentry->proc_number = procNumber;
ProcNumberGetTransactionIds(procNumber,
+ localentry->backendStatus.st_procpid,
&localentry->backend_xid,
&localentry->backend_xmin,
&localentry->backend_subxact_count,
diff --git a/src/include/storage/procarray.h b/src/include/storage/procarray.h
index d718a5b542f..b41514178ad 100644
--- a/src/include/storage/procarray.h
+++ b/src/include/storage/procarray.h
@@ -62,7 +62,7 @@ extern bool HaveVirtualXIDsDelayingChkpt(VirtualTransactionId *vxids,
int nvxids, int type);
extern PGPROC *ProcNumberGetProc(int procNumber);
-extern void ProcNumberGetTransactionIds(int procNumber, TransactionId *xid,
+extern void ProcNumberGetTransactionIds(int procNumber, int pid, TransactionId *xid,
TransactionId *xmin, int *nsubxid,
bool *overflowed);
extern PGPROC *BackendPidGetProc(int pid);
--
2.37.1 (Apple Git-137.1)
[application/octet-stream] v10-0001-Make-per-backend-statistics-functions-respect-st.patch (15.7K, ../../CAGRkXqQsOsB2_M73Y-cgQyzyXVx59qra-xFUo2-zQOTiNMSV8w@mail.gmail.com/4-v10-0001-Make-per-backend-statistics-functions-respect-st.patch)
download | inline diff:
From 9c79b803b4c1916d5e28c8b8a54fae2932721f38 Mon Sep 17 00:00:00 2001
From: Shihao Zhong <zhong950419@gmail.com>
Date: Fri, 11 Sep 2026 12:26:11 +0200
Subject: [PATCH v10 1/2] Make per-backend statistics functions respect
statistics permissions
The per-backend statistics functions that report the details of a session
call HAS_PGSTAT_PERMISSIONS() first, so that they hide these details from
a caller that is neither a superuser, nor has privileges of
pg_read_all_stats, nor is a member of the role that owns the session.
pg_stat_get_backend_subxact(), pg_stat_get_backend_io(),
pg_stat_get_backend_wal() and pg_stat_get_backend_lock() lacked this
check. Add it, for consistency with the sibling functions. Like
pg_stat_activity, this also hides the statistics of processes owned by
no role, such as autovacuum workers or the WAL writer, from callers
without these privileges.
pg_stat_get_backend_subxact() reads the backend status entry, so it checks
the role recorded there, like its sibling functions. The last three report
cumulative statistics that may come from a snapshot, taken while an older
backend held the same proc number, so the role recorded in the backend
status entry may not be the one that owns the statistics. Store the OID of
the role that owns the backend in PgStat_Backend when its entry is created,
and check the caller against that, based on the statistics data alone.
The permission rule was documented for the dynamic statistics views but
not for these functions, so state it above the per-backend function table
and on the three functions listed among the additional statistics
functions.
This changes the output of existing functions for callers lacking the
required privileges, so no backpatch is done.
Author: Shihao Zhong <zhong950419@gmail.com>
Author: Jim Jones <jim.jones@uni-muenster.de>
Reviewed-by: Michael Paquier <michael@paquier.xyz>
Discussion: https://postgr.es/m/CAGRkXqTBZ+zbVuDC8xGEB6Btj61hsui5H5nGqzFBDyOXc=4bjQ@mail.gmail.com
---
doc/src/sgml/monitoring.sgml | 25 +++++++++
src/backend/utils/activity/backend_status.c | 2 +-
src/backend/utils/activity/pgstat_backend.c | 16 +++++-
src/backend/utils/adt/pgstatfuncs.c | 13 +++--
src/include/pgstat.h | 3 +-
src/include/utils/pgstat_internal.h | 3 ++
src/test/regress/expected/stats.out | 59 +++++++++++++++++++++
src/test/regress/sql/stats.sql | 45 ++++++++++++++++
8 files changed, 158 insertions(+), 8 deletions(-)
diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml
index 0d038de1a23..5f20efb1cf5 100644
--- a/doc/src/sgml/monitoring.sgml
+++ b/doc/src/sgml/monitoring.sgml
@@ -5799,6 +5799,11 @@ description | Waiting for a newly initialized WAL file to reach durable storage
the background writer, the startup process and the autovacuum launcher
as they are already visible in the <structname>pg_stat_io</structname>
view and there is only one of each.
+ </para>
+ <para>
+ This function returns no rows unless the caller is a superuser, has
+ privileges of the <literal>pg_read_all_stats</literal> role, or is a
+ member of the role that owns the backend.
</para></entry>
</row>
@@ -5834,6 +5839,11 @@ description | Waiting for a newly initialized WAL file to reach durable storage
<para>
The function does not return lock statistics for the checkpointer,
the background writer, the startup process and the autovacuum launcher.
+ </para>
+ <para>
+ This function returns no rows unless the caller is a superuser, has
+ privileges of the <literal>pg_read_all_stats</literal> role, or is a
+ member of the role that owns the backend.
</para></entry>
</row>
@@ -5853,6 +5863,11 @@ description | Waiting for a newly initialized WAL file to reach durable storage
<para>
The function does not return WAL statistics for the checkpointer,
the background writer, the startup process and the autovacuum launcher.
+ </para>
+ <para>
+ This function returns NULL unless the caller is a superuser, has
+ privileges of the <literal>pg_read_all_stats</literal> role, or is a
+ member of the role that owns the backend.
</para></entry>
</row>
@@ -6211,6 +6226,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 details of a session's activity are only shown if
+ 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">
diff --git a/src/backend/utils/activity/backend_status.c b/src/backend/utils/activity/backend_status.c
index d685fc5cd87..fdeae791227 100644
--- a/src/backend/utils/activity/backend_status.c
+++ b/src/backend/utils/activity/backend_status.c
@@ -460,7 +460,7 @@ pgstat_bestart_final(void)
/* Create the backend statistics entry */
if (pgstat_tracks_backend_bktype(MyBackendType))
- pgstat_create_backend(MyProcNumber);
+ pgstat_create_backend(MyProcNumber, userid);
/* Update app name to current GUC setting */
if (application_name)
diff --git a/src/backend/utils/activity/pgstat_backend.c b/src/backend/utils/activity/pgstat_backend.c
index b736b2ccc6f..4da35a43386 100644
--- a/src/backend/utils/activity/pgstat_backend.c
+++ b/src/backend/utils/activity/pgstat_backend.c
@@ -390,9 +390,14 @@ pgstat_backend_flush_cb(bool nowait)
/*
* Create backend statistics entry for proc number.
+ *
+ * "userid" is the OID of the role that owns the backend, or InvalidOid for a
+ * process that has no session user. It is stored in the entry so that the
+ * functions reporting these statistics can check whether their caller is
+ * allowed to see them, based on the statistics data alone.
*/
void
-pgstat_create_backend(ProcNumber procnum)
+pgstat_create_backend(ProcNumber procnum, Oid userid)
{
PgStat_EntryRef *entry_ref;
PgStatShared_Backend *shstatent;
@@ -406,6 +411,8 @@ pgstat_create_backend(ProcNumber procnum)
* e.g. if we previously used this proc number.
*/
memset(&shstatent->stats, 0, sizeof(shstatent->stats));
+ shstatent->stats.userid = userid;
+ shstatent->userid = userid;
pgstat_unlock_entry(entry_ref);
MemSet(&PendingBackendStats, 0, sizeof(PgStat_BackendPending));
@@ -475,5 +482,10 @@ pgstat_tracks_backend_bktype(BackendType bktype)
void
pgstat_backend_reset_timestamp_cb(PgStatShared_Common *header, TimestampTz ts)
{
- ((PgStatShared_Backend *) header)->stats.stat_reset_timestamp = ts;
+ PgStatShared_Backend *shstatent = (PgStatShared_Backend *) header;
+
+ shstatent->stats.stat_reset_timestamp = ts;
+
+ /* a reset zeroes the whole entry, so restore the owner of the backend */
+ shstatent->stats.userid = shstatent->userid;
}
diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c
index 64b6f60516c..1e6702794ed 100644
--- a/src/backend/utils/adt/pgstatfuncs.c
+++ b/src/backend/utils/adt/pgstatfuncs.c
@@ -838,7 +838,9 @@ pg_stat_get_backend_subxact(PG_FUNCTION_ARGS)
TupleDescFinalize(tupdesc);
BlessTupleDesc(tupdesc);
- if ((local_beentry = pgstat_get_local_beentry_by_proc_number(procNumber)) != NULL)
+ /* Report the details of a session only to a caller 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);
@@ -1682,7 +1684,8 @@ pg_stat_get_backend_io(PG_FUNCTION_ARGS)
pid = PG_GETARG_INT32(0);
backend_stats = pgstat_fetch_stat_backend_by_pid(pid, &bktype);
- if (!backend_stats)
+ /* Report the details of a session only to a caller allowed to see them */
+ if (!backend_stats || !HAS_PGSTAT_PERMISSIONS(backend_stats->userid))
return (Datum) 0;
bktype_stats = &backend_stats->io_stats;
@@ -1775,7 +1778,8 @@ pg_stat_get_backend_wal(PG_FUNCTION_ARGS)
pid = PG_GETARG_INT32(0);
backend_stats = pgstat_fetch_stat_backend_by_pid(pid, NULL);
- if (!backend_stats)
+ /* Report the details of a session only to a caller allowed to see them */
+ if (!backend_stats || !HAS_PGSTAT_PERMISSIONS(backend_stats->userid))
PG_RETURN_NULL();
bktype_stats = backend_stats->wal_counters;
@@ -1866,7 +1870,8 @@ pg_stat_get_backend_lock(PG_FUNCTION_ARGS)
pid = PG_GETARG_INT32(0);
backend_stats = pgstat_fetch_stat_backend_by_pid(pid, NULL);
- if (!backend_stats)
+ /* Report the details of a session only to a caller allowed to see them */
+ if (!backend_stats || !HAS_PGSTAT_PERMISSIONS(backend_stats->userid))
return (Datum) 0;
pg_stat_lock_build_tuples(rsinfo, backend_stats->lock_stats.stats,
diff --git a/src/include/pgstat.h b/src/include/pgstat.h
index 187d82c96fe..682efcfe4bd 100644
--- a/src/include/pgstat.h
+++ b/src/include/pgstat.h
@@ -595,6 +595,7 @@ typedef struct PgStat_Backend
PgStat_BktypeIO io_stats;
PgStat_WalCounters wal_counters;
PgStat_PendingLock lock_stats;
+ Oid userid; /* role owning the backend, or InvalidOid */
} PgStat_Backend;
/* ---------
@@ -673,7 +674,7 @@ extern PgStat_Backend *pgstat_fetch_stat_backend(ProcNumber procNumber);
extern PgStat_Backend *pgstat_fetch_stat_backend_by_pid(int pid,
BackendType *bktype);
extern bool pgstat_tracks_backend_bktype(BackendType bktype);
-extern void pgstat_create_backend(ProcNumber procnum);
+extern void pgstat_create_backend(ProcNumber procnum, Oid userid);
/*
* Functions in pgstat_bgwriter.c
diff --git a/src/include/utils/pgstat_internal.h b/src/include/utils/pgstat_internal.h
index 201e57279b1..ea1bc981703 100644
--- a/src/include/utils/pgstat_internal.h
+++ b/src/include/utils/pgstat_internal.h
@@ -536,6 +536,9 @@ typedef struct PgStatShared_Backend
{
PgStatShared_Common header;
PgStat_Backend stats;
+
+ /* copy of stats.userid, to restore it after a reset */
+ Oid userid;
} PgStatShared_Backend;
/*
diff --git a/src/test/regress/expected/stats.out b/src/test/regress/expected/stats.out
index 8b15471248b..fc1870bfd4b 100644
--- a/src/test/regress/expected/stats.out
+++ b/src/test/regress/expected/stats.out
@@ -1141,6 +1141,65 @@ WHERE pg_stat_get_backend_pid(beid) = pg_backend_pid();
t
(1 row)
+-- The per-backend statistics functions report the details of a session only
+-- to a caller that is allowed to see them: a superuser, a role with
+-- privileges of pg_read_all_stats, or the role that owns the session.
+SELECT beid FROM pg_stat_get_backend_idset() beid
+WHERE pg_stat_get_backend_pid(beid) = pg_backend_pid() \gset
+SELECT current_user AS regress_stat_backend_owner \gset
+CREATE ROLE regress_stat_backend_role;
+-- a role with privileges of the role that owns this backend sees them
+GRANT :"regress_stat_backend_owner" TO regress_stat_backend_role;
+SET ROLE regress_stat_backend_role;
+SELECT (SELECT subxact_count IS NOT NULL
+ FROM pg_stat_get_backend_subxact(:beid)) AS subxact,
+ (SELECT count(*) > 0
+ FROM pg_stat_get_backend_io(pg_backend_pid())) AS io,
+ (SELECT count(*) > 0
+ FROM pg_stat_get_backend_lock(pg_backend_pid())) AS locks,
+ (SELECT wal_records IS NOT NULL
+ FROM pg_stat_get_backend_wal(pg_backend_pid())) AS wal;
+ subxact | io | locks | wal
+---------+----+-------+-----
+ t | t | t | t
+(1 row)
+
+RESET ROLE;
+REVOKE :"regress_stat_backend_owner" FROM regress_stat_backend_role;
+SET ROLE regress_stat_backend_role;
+-- an unrelated role sees nothing
+SELECT (SELECT subxact_count IS NOT NULL
+ FROM pg_stat_get_backend_subxact(:beid)) AS subxact,
+ (SELECT count(*) > 0
+ FROM pg_stat_get_backend_io(pg_backend_pid())) AS io,
+ (SELECT count(*) > 0
+ FROM pg_stat_get_backend_lock(pg_backend_pid())) AS locks,
+ (SELECT wal_records IS NOT NULL
+ FROM pg_stat_get_backend_wal(pg_backend_pid())) AS wal;
+ subxact | io | locks | wal
+---------+----+-------+-----
+ f | f | f | f
+(1 row)
+
+RESET ROLE;
+-- but a role with privileges of pg_read_all_stats sees them again
+GRANT pg_read_all_stats TO regress_stat_backend_role;
+SET ROLE regress_stat_backend_role;
+SELECT (SELECT subxact_count IS NOT NULL
+ FROM pg_stat_get_backend_subxact(:beid)) AS subxact,
+ (SELECT count(*) > 0
+ FROM pg_stat_get_backend_io(pg_backend_pid())) AS io,
+ (SELECT count(*) > 0
+ FROM pg_stat_get_backend_lock(pg_backend_pid())) AS locks,
+ (SELECT wal_records IS NOT NULL
+ FROM pg_stat_get_backend_wal(pg_backend_pid())) AS wal;
+ subxact | io | locks | wal
+---------+----+-------+-----
+ t | t | t | t
+(1 row)
+
+RESET ROLE;
+DROP ROLE regress_stat_backend_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..17c8e2f5231 100644
--- a/src/test/regress/sql/stats.sql
+++ b/src/test/regress/sql/stats.sql
@@ -535,6 +535,51 @@ 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();
+-- The per-backend statistics functions report the details of a session only
+-- to a caller that is allowed to see them: a superuser, a role with
+-- privileges of pg_read_all_stats, or the role that owns the session.
+SELECT beid FROM pg_stat_get_backend_idset() beid
+WHERE pg_stat_get_backend_pid(beid) = pg_backend_pid() \gset
+SELECT current_user AS regress_stat_backend_owner \gset
+CREATE ROLE regress_stat_backend_role;
+-- a role with privileges of the role that owns this backend sees them
+GRANT :"regress_stat_backend_owner" TO regress_stat_backend_role;
+SET ROLE regress_stat_backend_role;
+SELECT (SELECT subxact_count IS NOT NULL
+ FROM pg_stat_get_backend_subxact(:beid)) AS subxact,
+ (SELECT count(*) > 0
+ FROM pg_stat_get_backend_io(pg_backend_pid())) AS io,
+ (SELECT count(*) > 0
+ FROM pg_stat_get_backend_lock(pg_backend_pid())) AS locks,
+ (SELECT wal_records IS NOT NULL
+ FROM pg_stat_get_backend_wal(pg_backend_pid())) AS wal;
+RESET ROLE;
+REVOKE :"regress_stat_backend_owner" FROM regress_stat_backend_role;
+SET ROLE regress_stat_backend_role;
+-- an unrelated role sees nothing
+SELECT (SELECT subxact_count IS NOT NULL
+ FROM pg_stat_get_backend_subxact(:beid)) AS subxact,
+ (SELECT count(*) > 0
+ FROM pg_stat_get_backend_io(pg_backend_pid())) AS io,
+ (SELECT count(*) > 0
+ FROM pg_stat_get_backend_lock(pg_backend_pid())) AS locks,
+ (SELECT wal_records IS NOT NULL
+ FROM pg_stat_get_backend_wal(pg_backend_pid())) AS wal;
+RESET ROLE;
+-- but a role with privileges of pg_read_all_stats sees them again
+GRANT pg_read_all_stats TO regress_stat_backend_role;
+SET ROLE regress_stat_backend_role;
+SELECT (SELECT subxact_count IS NOT NULL
+ FROM pg_stat_get_backend_subxact(:beid)) AS subxact,
+ (SELECT count(*) > 0
+ FROM pg_stat_get_backend_io(pg_backend_pid())) AS io,
+ (SELECT count(*) > 0
+ FROM pg_stat_get_backend_lock(pg_backend_pid())) AS locks,
+ (SELECT wal_records IS NOT NULL
+ FROM pg_stat_get_backend_wal(pg_backend_pid())) AS wal;
+RESET ROLE;
+DROP ROLE regress_stat_backend_role;
+
-----
-- Test that resetting stats works for reset timestamp
-----
--
2.37.1 (Apple Git-137.1)
^ permalink raw reply [nested|flat] 21+ messages in thread
end of thread, other threads:[~2026-09-24 01:51 UTC | newest]
Thread overview: 21+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-09-08 00:33 Add a permission check to pg_stat_get_backend_subxact() shihao zhong <zhong950419@gmail.com>
2026-09-10 15:47 ` Jim Jones <jim.jones@uni-muenster.de>
2026-09-11 07:53 ` Michael Paquier <michael@paquier.xyz>
2026-09-11 10:58 ` Jim Jones <jim.jones@uni-muenster.de>
2026-09-12 00:05 ` Michael Paquier <michael@paquier.xyz>
2026-09-12 12:43 ` shihao zhong <zhong950419@gmail.com>
2026-09-14 07:06 ` Michael Paquier <michael@paquier.xyz>
2026-09-21 09:58 ` Bertrand Drouvot <bertranddrouvot.pg@gmail.com>
2026-09-21 23:54 ` shihao zhong <zhong950419@gmail.com>
2026-09-22 00:12 ` Michael Paquier <michael@paquier.xyz>
2026-09-22 00:18 ` shihao zhong <zhong950419@gmail.com>
2026-09-22 01:20 ` shihao zhong <zhong950419@gmail.com>
2026-09-22 09:24 ` Michael Paquier <michael@paquier.xyz>
2026-09-22 10:03 ` Bertrand Drouvot <bertranddrouvot.pg@gmail.com>
2026-09-22 11:23 ` Michael Paquier <michael@paquier.xyz>
2026-09-22 14:03 ` Bertrand Drouvot <bertranddrouvot.pg@gmail.com>
2026-09-23 03:22 ` shihao zhong <zhong950419@gmail.com>
2026-09-23 07:23 ` Bertrand Drouvot <bertranddrouvot.pg@gmail.com>
2026-09-24 00:39 ` shihao zhong <zhong950419@gmail.com>
2026-09-24 01:01 ` Michael Paquier <michael@paquier.xyz>
2026-09-24 01:51 ` shihao zhong <zhong950419@gmail.com>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox