diff options
| -rw-r--r-- | contrib/pg_stat_statements/Makefile | 3 | ||||
| -rw-r--r-- | contrib/pg_stat_statements/expected/privileges.out | 97 | ||||
| -rw-r--r-- | contrib/pg_stat_statements/meson.build | 1 | ||||
| -rw-r--r-- | contrib/pg_stat_statements/sql/privileges.sql | 60 |
4 files changed, 160 insertions, 1 deletions
diff --git a/contrib/pg_stat_statements/Makefile b/contrib/pg_stat_statements/Makefile index 414a30856e4..c19ccad77ed 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 00000000000..daaaa0bb835 --- /dev/null +++ b/contrib/pg_stat_statements/expected/privileges.out @@ -0,0 +1,97 @@ +-- +-- Only superusers and roles with privileges of the pg_read_all_stats role +-- are allowed to see the SQL text and queryid of queries executed by +-- other users. Other users can see the statistics. +-- +SET pg_stat_statements.track_utility = FALSE; +CREATE ROLE regress_stats_superuser SUPERUSER; +CREATE ROLE regress_stats_user1; +CREATE ROLE regress_stats_user2; +GRANT pg_read_all_stats TO regress_stats_user2; +SET ROLE regress_stats_superuser; +SELECT pg_stat_statements_reset() IS NOT NULL AS t; + t +--- + t +(1 row) + +SELECT 1 AS "ONE"; + ONE +----- + 1 +(1 row) + +SET ROLE regress_stats_user1; +SELECT 1+1 AS "TWO"; + TWO +----- + 2 +(1 row) + +-- +-- A superuser can read all columns of queries executed by others, +-- including query text and queryid. +-- +SET ROLE regress_stats_superuser; +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 +-------------------------+--------------+----------------------------------------------------+-------+------ + regress_stats_superuser | t | SELECT $1 AS "ONE" | 1 | 1 + regress_stats_superuser | 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 has no privileges to read the query text or +-- queryid of queries executed by others but can see statistics +-- like 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 +-------------------------+--------------+--------------------------+-------+------ + regress_stats_superuser | | <insufficient privilege> | 1 | 1 + regress_stats_superuser | | <insufficient privilege> | 1 | 1 + regress_stats_superuser | | <insufficient privilege> | 1 | 3 + regress_stats_user1 | t | SELECT $1+$2 AS "TWO" | 1 | 1 +(4 rows) + +-- +-- regress_stats_user2, with pg_read_all_stats role privileges, can +-- read all columns, including query text and queryid, of queries +-- executed by others. +-- +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 +-------------------------+--------------+---------------------------------------------------------------------------------+-------+------ + regress_stats_superuser | t | SELECT $1 AS "ONE" | 1 | 1 + regress_stats_superuser | t | SELECT pg_stat_statements_reset() IS NOT NULL AS t | 1 | 1 + regress_stats_superuser | 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_superuser; +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 9bfc9657e1a..5cf926d1f85 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 00000000000..75b1489a474 --- /dev/null +++ b/contrib/pg_stat_statements/sql/privileges.sql @@ -0,0 +1,60 @@ +-- +-- Only superusers and roles with privileges of the pg_read_all_stats role +-- are allowed to see the SQL text and queryid of queries executed by +-- other users. Other users can see the statistics. +-- + +SET pg_stat_statements.track_utility = FALSE; +CREATE ROLE regress_stats_superuser SUPERUSER; +CREATE ROLE regress_stats_user1; +CREATE ROLE regress_stats_user2; +GRANT pg_read_all_stats TO regress_stats_user2; + +SET ROLE regress_stats_superuser; +SELECT pg_stat_statements_reset() IS NOT NULL AS t; +SELECT 1 AS "ONE"; + +SET ROLE regress_stats_user1; +SELECT 1+1 AS "TWO"; + +-- +-- A superuser can read all columns of queries executed by others, +-- including query text and queryid. +-- + +SET ROLE regress_stats_superuser; +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 has no privileges to read the query text or +-- queryid of queries executed by others but can see statistics +-- like 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, with pg_read_all_stats role privileges, can +-- read all columns, including query text and queryid, of queries +-- executed by others. +-- + +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_superuser; +DROP ROLE regress_stats_user1; +DROP ROLE regress_stats_user2; +SELECT pg_stat_statements_reset() IS NOT NULL AS t; |