Skip to content

Commit 97f2bc5

Browse files
MasaoFujiiMasaoFujii
committed
pg_stat_statements: Add regression test for privilege handling.
This commit adds a regression test to verify that pg_stat_statements correctly handles privileges, improving its test coverage. Author: Keisuke Kuroda Reviewed-by: Michael Paquier, Fujii Masao Discussion: https://postgr.es/m/2224ccf2e12c41ccb81702ef3303d5ac@nttcom.co.jp
1 parent 3dd637f commit 97f2bc5

File tree

4 files changed

+160
-1
lines changed

4 files changed

+160
-1
lines changed

‎contrib/pg_stat_statements/Makefile

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -19,7 +19,8 @@ LDFLAGS_SL += $(filter -lm, $(LIBS))
1919

2020
REGRESS_OPTS = --temp-config $(top_srcdir)/contrib/pg_stat_statements/pg_stat_statements.conf
2121
REGRESS = select dml cursors utility level_tracking planning \
22-
user_activity wal entry_timestamp cleanup oldextversions
22+
user_activity wal entry_timestamp privileges cleanup \
23+
oldextversions
2324
# Disabled because these tests require "shared_preload_libraries=pg_stat_statements",
2425
# which typical installcheck users do not have (e.g. buildfarm clients).
2526
NO_INSTALLCHECK = 1

‎contrib/pg_stat_statements/expected/privileges.out

Lines changed: 97 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,97 @@
1+
--
2+
-- Only superusers and roles with privileges of the pg_read_all_stats role
3+
-- are allowed to see the SQL text and queryid of queries executed by
4+
-- other users. Other users can see the statistics.
5+
--
6+
SET pg_stat_statements.track_utility = FALSE;
7+
CREATE ROLE regress_stats_superuser SUPERUSER;
8+
CREATE ROLE regress_stats_user1;
9+
CREATE ROLE regress_stats_user2;
10+
GRANT pg_read_all_stats TO regress_stats_user2;
11+
SET ROLE regress_stats_superuser;
12+
SELECT pg_stat_statements_reset() IS NOT NULL AS t;
13+
t
14+
---
15+
t
16+
(1 row)
17+
18+
SELECT 1 AS "ONE";
19+
ONE
20+
-----
21+
1
22+
(1 row)
23+
24+
SET ROLE regress_stats_user1;
25+
SELECT 1+1 AS "TWO";
26+
TWO
27+
-----
28+
2
29+
(1 row)
30+
31+
--
32+
-- A superuser can read all columns of queries executed by others,
33+
-- including query text and queryid.
34+
--
35+
SET ROLE regress_stats_superuser;
36+
SELECT r.rolname, ss.queryid <> 0 AS queryid_bool, ss.query, ss.calls, ss.rows
37+
FROM pg_stat_statements ss JOIN pg_roles r ON ss.userid = r.oid
38+
ORDER BY r.rolname, ss.query COLLATE "C", ss.calls, ss.rows;
39+
rolname | queryid_bool | query | calls | rows
40+
-------------------------+--------------+----------------------------------------------------+-------+------
41+
regress_stats_superuser | t | SELECT $1 AS "ONE" | 1 | 1
42+
regress_stats_superuser | t | SELECT pg_stat_statements_reset() IS NOT NULL AS t | 1 | 1
43+
regress_stats_user1 | t | SELECT $1+$2 AS "TWO" | 1 | 1
44+
(3 rows)
45+
46+
--
47+
-- regress_stats_user1 has no privileges to read the query text or
48+
-- queryid of queries executed by others but can see statistics
49+
-- like calls and rows.
50+
--
51+
SET ROLE regress_stats_user1;
52+
SELECT r.rolname, ss.queryid <> 0 AS queryid_bool, ss.query, ss.calls, ss.rows
53+
FROM pg_stat_statements ss JOIN pg_roles r ON ss.userid = r.oid
54+
ORDER BY r.rolname, ss.query COLLATE "C", ss.calls, ss.rows;
55+
rolname | queryid_bool | query | calls | rows
56+
-------------------------+--------------+--------------------------+-------+------
57+
regress_stats_superuser | | <insufficient privilege> | 1 | 1
58+
regress_stats_superuser | | <insufficient privilege> | 1 | 1
59+
regress_stats_superuser | | <insufficient privilege> | 1 | 3
60+
regress_stats_user1 | t | SELECT $1+$2 AS "TWO" | 1 | 1
61+
(4 rows)
62+
63+
--
64+
-- regress_stats_user2, with pg_read_all_stats role privileges, can
65+
-- read all columns, including query text and queryid, of queries
66+
-- executed by others.
67+
--
68+
SET ROLE regress_stats_user2;
69+
SELECT r.rolname, ss.queryid <> 0 AS queryid_bool, ss.query, ss.calls, ss.rows
70+
FROM pg_stat_statements ss JOIN pg_roles r ON ss.userid = r.oid
71+
ORDER BY r.rolname, ss.query COLLATE "C", ss.calls, ss.rows;
72+
rolname | queryid_bool | query | calls | rows
73+
-------------------------+--------------+---------------------------------------------------------------------------------+-------+------
74+
regress_stats_superuser | t | SELECT $1 AS "ONE" | 1 | 1
75+
regress_stats_superuser | t | SELECT pg_stat_statements_reset() IS NOT NULL AS t | 1 | 1
76+
regress_stats_superuser | t | SELECT r.rolname, ss.queryid <> $1 AS queryid_bool, ss.query, ss.calls, ss.rows+| 1 | 3
77+
| | FROM pg_stat_statements ss JOIN pg_roles r ON ss.userid = r.oid +| |
78+
| | ORDER BY r.rolname, ss.query COLLATE "C", ss.calls, ss.rows | |
79+
regress_stats_user1 | t | SELECT $1+$2 AS "TWO" | 1 | 1
80+
regress_stats_user1 | t | SELECT r.rolname, ss.queryid <> $1 AS queryid_bool, ss.query, ss.calls, ss.rows+| 1 | 4
81+
| | FROM pg_stat_statements ss JOIN pg_roles r ON ss.userid = r.oid +| |
82+
| | ORDER BY r.rolname, ss.query COLLATE "C", ss.calls, ss.rows | |
83+
(5 rows)
84+
85+
--
86+
-- cleanup
87+
--
88+
RESET ROLE;
89+
DROP ROLE regress_stats_superuser;
90+
DROP ROLE regress_stats_user1;
91+
DROP ROLE regress_stats_user2;
92+
SELECT pg_stat_statements_reset() IS NOT NULL AS t;
93+
t
94+
---
95+
t
96+
(1 row)
97+

‎contrib/pg_stat_statements/meson.build

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -50,6 +50,7 @@ tests += {
5050
'user_activity',
5151
'wal',
5252
'entry_timestamp',
53+
'privileges',
5354
'cleanup',
5455
'oldextversions',
5556
],

‎contrib/pg_stat_statements/sql/privileges.sql

Lines changed: 60 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,60 @@
1+
--
2+
-- Only superusers and roles with privileges of the pg_read_all_stats role
3+
-- are allowed to see the SQL text and queryid of queries executed by
4+
-- other users. Other users can see the statistics.
5+
--
6+
7+
SET pg_stat_statements.track_utility = FALSE;
8+
CREATE ROLE regress_stats_superuser SUPERUSER;
9+
CREATE ROLE regress_stats_user1;
10+
CREATE ROLE regress_stats_user2;
11+
GRANT pg_read_all_stats TO regress_stats_user2;
12+
13+
SET ROLE regress_stats_superuser;
14+
SELECT pg_stat_statements_reset() IS NOT NULL AS t;
15+
SELECT 1 AS "ONE";
16+
17+
SET ROLE regress_stats_user1;
18+
SELECT 1+1 AS "TWO";
19+
20+
--
21+
-- A superuser can read all columns of queries executed by others,
22+
-- including query text and queryid.
23+
--
24+
25+
SET ROLE regress_stats_superuser;
26+
SELECT r.rolname, ss.queryid <> 0 AS queryid_bool, ss.query, ss.calls, ss.rows
27+
FROM pg_stat_statements ss JOIN pg_roles r ON ss.userid = r.oid
28+
ORDER BY r.rolname, ss.query COLLATE "C", ss.calls, ss.rows;
29+
30+
--
31+
-- regress_stats_user1 has no privileges to read the query text or
32+
-- queryid of queries executed by others but can see statistics
33+
-- like calls and rows.
34+
--
35+
36+
SET ROLE regress_stats_user1;
37+
SELECT r.rolname, ss.queryid <> 0 AS queryid_bool, ss.query, ss.calls, ss.rows
38+
FROM pg_stat_statements ss JOIN pg_roles r ON ss.userid = r.oid
39+
ORDER BY r.rolname, ss.query COLLATE "C", ss.calls, ss.rows;
40+
41+
--
42+
-- regress_stats_user2, with pg_read_all_stats role privileges, can
43+
-- read all columns, including query text and queryid, of queries
44+
-- executed by others.
45+
--
46+
47+
SET ROLE regress_stats_user2;
48+
SELECT r.rolname, ss.queryid <> 0 AS queryid_bool, ss.query, ss.calls, ss.rows
49+
FROM pg_stat_statements ss JOIN pg_roles r ON ss.userid = r.oid
50+
ORDER BY r.rolname, ss.query COLLATE "C", ss.calls, ss.rows;
51+
52+
--
53+
-- cleanup
54+
--
55+
56+
RESET ROLE;
57+
DROP ROLE regress_stats_superuser;
58+
DROP ROLE regress_stats_user1;
59+
DROP ROLE regress_stats_user2;
60+
SELECT pg_stat_statements_reset() IS NOT NULL AS t;

0 commit comments

Comments
 (0)