Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit97f2bc5

Browse files
committed
pg_stat_statements: Add regression test for privilege handling.
This commit adds a regression test to verify that pg_stat_statementscorrectly handles privileges, improving its test coverage.Author: Keisuke KurodaReviewed-by: Michael Paquier, Fujii MasaoDiscussion:https://postgr.es/m/2224ccf2e12c41ccb81702ef3303d5ac@nttcom.co.jp
1 parent3dd637f commit97f2bc5

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
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
],
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+
SETpg_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 NULLAS t;
15+
SELECT1AS"ONE";
16+
17+
SET ROLE regress_stats_user1;
18+
SELECT1+1AS"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+
SELECTr.rolname,ss.queryid<>0AS queryid_bool,ss.query,ss.calls,ss.rows
27+
FROM pg_stat_statements ssJOIN pg_roles rONss.userid=r.oid
28+
ORDER BYr.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+
SELECTr.rolname,ss.queryid<>0AS queryid_bool,ss.query,ss.calls,ss.rows
38+
FROM pg_stat_statements ssJOIN pg_roles rONss.userid=r.oid
39+
ORDER BYr.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+
SELECTr.rolname,ss.queryid<>0AS queryid_bool,ss.query,ss.calls,ss.rows
49+
FROM pg_stat_statements ssJOIN pg_roles rONss.userid=r.oid
50+
ORDER BYr.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 NULLAS t;

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp