Hi Michael-san,
Thank you for your reply and comment!
attach v4 fixed patch.
We have two entries here with the same query and the same query ID,
because they have a different userid. Shouldn't this query reflect
this information rather than have the reader guess it? This is going
to require a join with pg_authid to grab the role name, and an ORDER
BY on the role name.
I agree.
The information of different userids is mixed up.
It is easier to understand if the role name is displayed.
Join with pg_roles (view of pg_authid) to output the role name.
I'd recommend to add a GROUP BY on calls and rows, with a
count(query), rather than print the same row without the query text
multiple times.
Indeed, same row have been output multiple times.
If we use GROUP BY, we would expect the following.
```
SELECT r.rolname, ss.queryid <> 0 AS queryid_bool, count(ss.query),
ss.calls, ss.rows
FROM pg_stat_statements ss JOIN pg_roles r ON ss.userid = r.oid
GROUP BY r.rolname, queryid_bool, ss.calls, ss.rows
ORDER BY r.rolname, count(ss.query), ss.calls, ss.rows;
rolname | queryid_bool | count | calls | rows
---------------------+--------------+-------+-------+------
postgres | | 1 | 1 | 3
postgres | | 2 | 1 | 1
regress_stats_user1 | t | 1 | 1 | 1
(3 rows)
```
However, in this test I would like to see '<insufficient permissions>'
output
and the SQL text 'SELECT $1+$2 AS “TWO”' executed by
regress_stats_user1.
The attached patch executes the following SQL.
What do you think?
```
SELECT r.rolname, ss.queryid <> 0 AS queryid_bool, ss.query, ss.calls,
ss.rows
FROM pg_stat_statements ss JOIN pg_roles r ON ss.userid = r.oid
ORDER BY r.rolname, ss.query COLLATE "C", ss.calls, ss.rows;
rolname | queryid_bool | query | calls |
rows
---------------------+--------------+--------------------------+-------+------
postgres | | <insufficient privilege> | 1 |
1
postgres | | <insufficient privilege> | 1 |
1
postgres | | <insufficient privilege> | 1 |
3
regress_stats_user1 | t | SELECT $1+$2 AS "TWO" | 1 |
1
(4 rows)
```
diff --git a/contrib/pg_stat_statements/Makefile b/contrib/pg_stat_statements/Makefile
index 414a30856e..c19ccad77e 100644
--- a/contrib/pg_stat_statements/Makefile
+++ b/contrib/pg_stat_statements/Makefile
@@ -19,7 +19,8 @@ LDFLAGS_SL += $(filter -lm, $(LIBS))
REGRESS_OPTS = --temp-config $(top_srcdir)/contrib/pg_stat_statements/pg_stat_statements.conf
REGRESS = select dml cursors utility level_tracking planning \
- user_activity wal entry_timestamp cleanup oldextversions
+ user_activity wal entry_timestamp privileges cleanup \
+ oldextversions
# Disabled because these tests require "shared_preload_libraries=pg_stat_statements",
# which typical installcheck users do not have (e.g. buildfarm clients).
NO_INSTALLCHECK = 1
diff --git a/contrib/pg_stat_statements/expected/privileges.out b/contrib/pg_stat_statements/expected/privileges.out
new file mode 100644
index 0000000000..4c7794ce0c
--- /dev/null
+++ b/contrib/pg_stat_statements/expected/privileges.out
@@ -0,0 +1,86 @@
+--
+-- Superusers or roles with the privileges of pg_read_all_stats members
+-- can read query text and queryid
+--
+SET pg_stat_statements.track_utility = FALSE;
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
+ t
+---
+ t
+(1 row)
+
+CREATE ROLE regress_stats_user1;
+CREATE ROLE regress_stats_user2;
+GRANT pg_read_all_stats TO regress_stats_user2;
+SELECT 1 AS "ONE";
+ ONE
+-----
+ 1
+(1 row)
+
+SET ROLE regress_stats_user1;
+SELECT 1+1 AS "TWO";
+ TWO
+-----
+ 2
+(1 row)
+
+-- Superuser can read query text and queryid
+RESET ROLE;
+SELECT r.rolname, ss.queryid <> 0 AS queryid_bool, ss.query, ss.calls, ss.rows
+ FROM pg_stat_statements ss JOIN pg_roles r ON ss.userid = r.oid
+ ORDER BY r.rolname, ss.query COLLATE "C", ss.calls, ss.rows;
+ rolname | queryid_bool | query | calls | rows
+---------------------+--------------+----------------------------------------------------+-------+------
+ postgres | t | SELECT $1 AS "ONE" | 1 | 1
+ postgres | t | SELECT pg_stat_statements_reset() IS NOT NULL AS t | 1 | 1
+ regress_stats_user1 | t | SELECT $1+$2 AS "TWO" | 1 | 1
+(3 rows)
+
+--
+-- regress_stats_user1 can not read query text and queryid
+-- executed by other users,
+-- but can read calls and rows
+--
+SET ROLE regress_stats_user1;
+SELECT r.rolname, ss.queryid <> 0 AS queryid_bool, ss.query, ss.calls, ss.rows
+ FROM pg_stat_statements ss JOIN pg_roles r ON ss.userid = r.oid
+ ORDER BY r.rolname, ss.query COLLATE "C", ss.calls, ss.rows;
+ rolname | queryid_bool | query | calls | rows
+---------------------+--------------+--------------------------+-------+------
+ postgres | | <insufficient privilege> | 1 | 1
+ postgres | | <insufficient privilege> | 1 | 1
+ postgres | | <insufficient privilege> | 1 | 3
+ regress_stats_user1 | t | SELECT $1+$2 AS "TWO" | 1 | 1
+(4 rows)
+
+-- regress_stats_user2 can read query text and queryid
+SET ROLE regress_stats_user2;
+SELECT r.rolname, ss.queryid <> 0 AS queryid_bool, ss.query, ss.calls, ss.rows
+ FROM pg_stat_statements ss JOIN pg_roles r ON ss.userid = r.oid
+ ORDER BY r.rolname, ss.query COLLATE "C", ss.calls, ss.rows;
+ rolname | queryid_bool | query | calls | rows
+---------------------+--------------+---------------------------------------------------------------------------------+-------+------
+ postgres | t | SELECT $1 AS "ONE" | 1 | 1
+ postgres | t | SELECT pg_stat_statements_reset() IS NOT NULL AS t | 1 | 1
+ postgres | t | SELECT r.rolname, ss.queryid <> $1 AS queryid_bool, ss.query, ss.calls, ss.rows+| 1 | 3
+ | | FROM pg_stat_statements ss JOIN pg_roles r ON ss.userid = r.oid +| |
+ | | ORDER BY r.rolname, ss.query COLLATE "C", ss.calls, ss.rows | |
+ regress_stats_user1 | t | SELECT $1+$2 AS "TWO" | 1 | 1
+ regress_stats_user1 | t | SELECT r.rolname, ss.queryid <> $1 AS queryid_bool, ss.query, ss.calls, ss.rows+| 1 | 4
+ | | FROM pg_stat_statements ss JOIN pg_roles r ON ss.userid = r.oid +| |
+ | | ORDER BY r.rolname, ss.query COLLATE "C", ss.calls, ss.rows | |
+(5 rows)
+
+--
+-- cleanup
+--
+RESET ROLE;
+DROP ROLE regress_stats_user1;
+DROP ROLE regress_stats_user2;
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
+ t
+---
+ t
+(1 row)
+
diff --git a/contrib/pg_stat_statements/meson.build b/contrib/pg_stat_statements/meson.build
index 9bfc9657e1..5cf926d1f8 100644
--- a/contrib/pg_stat_statements/meson.build
+++ b/contrib/pg_stat_statements/meson.build
@@ -50,6 +50,7 @@ tests += {
'user_activity',
'wal',
'entry_timestamp',
+ 'privileges',
'cleanup',
'oldextversions',
],
diff --git a/contrib/pg_stat_statements/sql/privileges.sql b/contrib/pg_stat_statements/sql/privileges.sql
new file mode 100644
index 0000000000..8ed99a32ee
--- /dev/null
+++ b/contrib/pg_stat_statements/sql/privileges.sql
@@ -0,0 +1,48 @@
+--
+-- Superusers or roles with the privileges of pg_read_all_stats members
+-- can read query text and queryid
+--
+
+SET pg_stat_statements.track_utility = FALSE;
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
+CREATE ROLE regress_stats_user1;
+CREATE ROLE regress_stats_user2;
+GRANT pg_read_all_stats TO regress_stats_user2;
+SELECT 1 AS "ONE";
+
+SET ROLE regress_stats_user1;
+SELECT 1+1 AS "TWO";
+
+-- Superuser can read query text and queryid
+
+RESET ROLE;
+SELECT r.rolname, ss.queryid <> 0 AS queryid_bool, ss.query, ss.calls, ss.rows
+ FROM pg_stat_statements ss JOIN pg_roles r ON ss.userid = r.oid
+ ORDER BY r.rolname, ss.query COLLATE "C", ss.calls, ss.rows;
+
+--
+-- regress_stats_user1 can not read query text and queryid
+-- executed by other users,
+-- but can read calls and rows
+--
+
+SET ROLE regress_stats_user1;
+SELECT r.rolname, ss.queryid <> 0 AS queryid_bool, ss.query, ss.calls, ss.rows
+ FROM pg_stat_statements ss JOIN pg_roles r ON ss.userid = r.oid
+ ORDER BY r.rolname, ss.query COLLATE "C", ss.calls, ss.rows;
+
+-- regress_stats_user2 can read query text and queryid
+
+SET ROLE regress_stats_user2;
+SELECT r.rolname, ss.queryid <> 0 AS queryid_bool, ss.query, ss.calls, ss.rows
+ FROM pg_stat_statements ss JOIN pg_roles r ON ss.userid = r.oid
+ ORDER BY r.rolname, ss.query COLLATE "C", ss.calls, ss.rows;
+
+--
+-- cleanup
+--
+
+RESET ROLE;
+DROP ROLE regress_stats_user1;
+DROP ROLE regress_stats_user2;
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
