Hi hackers,
I proposal adding privileges test to improve
test coverage of pg_stat_statements.
## test procedure
./configure --enable-coverage --enable-tap-tests --with-llvm CFLAGS=-O0
make check-world
make coverage-html
## coverage
before Line Coverage 74.0 %(702/949 lines)
after Line Coverage 74.4 %(706/949 lines)
Although the improvement is small, I think that test regarding
privileges is necessary.
As a side note,
Initially, I was considering adding a test for dealloc.
However, after reading the thread below, I confirmed that
it is difficult to create tests due to differences due to endian.
(https://www.postgresql.org/message-id/flat/40d1e4f2-835f-448f-a541-8ff5db75bf3d%40eisentraut.org)
For this reason, I first added a test about privileges.
Best Regards,
Keisuke Kuroda
NTT Comware
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..383895c87d
--- /dev/null
+++ b/contrib/pg_stat_statements/expected/privileges.out
@@ -0,0 +1,102 @@
+--
+-- Superusers or roles with the privileges of pg_read_all_stats members
+-- can read query text and queryid
+--
+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;
+SET ROLE regress_stats_user1;
+SELECT 1 AS "ONE";
+ ONE
+-----
+ 1
+(1 row)
+
+-- Superuser can read query text and queryid
+RESET ROLE;
+SELECT
+ CASE
+ WHEN queryid <> 0 THEN TRUE ELSE FALSE
+ END AS queryid_bool
+ ,query FROM pg_stat_statements ORDER BY query COLLATE "C";
+ queryid_bool | query
+--------------+----------------------------------------------------
+ t | CREATE ROLE regress_stats_user1
+ t | CREATE ROLE regress_stats_user2
+ t | GRANT pg_read_all_stats TO regress_stats_user2
+ t | RESET ROLE
+ t | SELECT $1 AS "ONE"
+ t | SELECT pg_stat_statements_reset() IS NOT NULL AS t
+ t | SET ROLE regress_stats_user1
+(7 rows)
+
+--
+-- regress_stats_user1 can not read query text and queryid
+-- executed by other users
+--
+SET ROLE regress_stats_user1;
+SELECT
+ CASE
+ WHEN queryid <> 0 THEN TRUE ELSE FALSE
+ END AS queryid_bool
+ ,query FROM pg_stat_statements ORDER BY query COLLATE "C";
+ queryid_bool | query
+--------------+------------------------------
+ f | <insufficient privilege>
+ f | <insufficient privilege>
+ f | <insufficient privilege>
+ f | <insufficient privilege>
+ f | <insufficient privilege>
+ f | <insufficient privilege>
+ t | SELECT $1 AS "ONE"
+ t | SET ROLE regress_stats_user1
+(8 rows)
+
+RESET ROLE;
+-- regress_stats_user2 can read query text and queryid
+SET ROLE regress_stats_user2;
+SELECT
+ CASE
+ WHEN queryid <> 0 THEN TRUE ELSE FALSE
+ END AS queryid_bool
+ ,query FROM pg_stat_statements ORDER BY query COLLATE "C";
+ queryid_bool | query
+--------------+-------------------------------------------------------------
+ t | CREATE ROLE regress_stats_user1
+ t | CREATE ROLE regress_stats_user2
+ t | GRANT pg_read_all_stats TO regress_stats_user2
+ t | RESET ROLE
+ t | SELECT +
+ | CASE +
+ | WHEN queryid <> $1 THEN $2 ELSE $3 +
+ | END AS queryid_bool +
+ | ,query FROM pg_stat_statements ORDER BY query COLLATE "C"
+ t | SELECT +
+ | CASE +
+ | WHEN queryid <> $1 THEN $2 ELSE $3 +
+ | END AS queryid_bool +
+ | ,query FROM pg_stat_statements ORDER BY query COLLATE "C"
+ t | SELECT $1 AS "ONE"
+ t | SELECT pg_stat_statements_reset() IS NOT NULL AS t
+ t | SET ROLE regress_stats_user1
+ t | SET ROLE regress_stats_user2
+(10 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/sql/privileges.sql b/contrib/pg_stat_statements/sql/privileges.sql
new file mode 100644
index 0000000000..69f4abf234
--- /dev/null
+++ b/contrib/pg_stat_statements/sql/privileges.sql
@@ -0,0 +1,52 @@
+--
+-- Superusers or roles with the privileges of pg_read_all_stats members
+-- can read query text and queryid
+--
+
+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;
+
+SET ROLE regress_stats_user1;
+SELECT 1 AS "ONE";
+
+-- Superuser can read query text and queryid
+
+RESET ROLE;
+SELECT
+ CASE
+ WHEN queryid <> 0 THEN TRUE ELSE FALSE
+ END AS queryid_bool
+ ,query FROM pg_stat_statements ORDER BY query COLLATE "C";
+
+--
+-- regress_stats_user1 can not read query text and queryid
+-- executed by other users
+--
+
+SET ROLE regress_stats_user1;
+SELECT
+ CASE
+ WHEN queryid <> 0 THEN TRUE ELSE FALSE
+ END AS queryid_bool
+ ,query FROM pg_stat_statements ORDER BY query COLLATE "C";
+RESET ROLE;
+
+-- regress_stats_user2 can read query text and queryid
+
+SET ROLE regress_stats_user2;
+SELECT
+ CASE
+ WHEN queryid <> 0 THEN TRUE ELSE FALSE
+ END AS queryid_bool
+ ,query FROM pg_stat_statements ORDER BY query COLLATE "C";
+
+--
+-- cleanup
+--
+
+RESET ROLE;
+DROP ROLE regress_stats_user1;
+DROP ROLE regress_stats_user2;
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
