Hello,
I have noticed that there is no regression tests for default monitoring
roles such as pg_read_all_stats.
In this patch the test called defroles is added. It tests permissions of
pg_read_all_stats role to query database size without CONNECT permission on
it, to query tablespace size without CREATE permission on it, and to read
"query" column of pg_stat_activity table without SUPERUSER privilege.
Best regards,
Alexandra Ryzhevich
From 3cf3d1b9e252902e0e8f05436c703d1ec2d90125 Mon Sep 17 00:00:00 2001
From: Alexandra Ryzhevich <aryzhev...@google.com>
Date: Thu, 2 Aug 2018 18:06:13 +0100
Subject: [PATCH 1/1] Add regress test for pg_read_all_stats role
---
src/test/regress/expected/defroles.out | 68 ++++++++++++++++++++++++++
src/test/regress/parallel_schedule | 2 +-
src/test/regress/serial_schedule | 1 +
src/test/regress/sql/defroles.sql | 46 +++++++++++++++++
4 files changed, 116 insertions(+), 1 deletion(-)
create mode 100644 src/test/regress/expected/defroles.out
create mode 100644 src/test/regress/sql/defroles.sql
diff --git a/src/test/regress/expected/defroles.out b/src/test/regress/expected/defroles.out
new file mode 100644
index 0000000000..f3bec0910a
--- /dev/null
+++ b/src/test/regress/expected/defroles.out
@@ -0,0 +1,68 @@
+-- DEFAULT MONITORING ROLES
+DROP ROLE IF EXISTS tmp_role_stats;
+NOTICE: role "tmp_role_stats" does not exist, skipping
+DROP ROLE IF EXISTS tmp_role_no_stats;
+NOTICE: role "tmp_role_no_stats" does not exist, skipping
+DROP DATABASE IF EXISTS tmp_db;
+NOTICE: database "tmp_db" does not exist, skipping
+CREATE ROLE tmp_role_stats;
+CREATE ROLE tmp_role_no_stats;
+-- check pg_read_all_stats default role permissions
+GRANT pg_read_all_stats TO tmp_role_stats;
+CREATE DATABASE tmp_db;
+-- CONNECT is granted by default
+REVOKE CONNECT ON DATABASE tmp_db FROM public;
+SET SESSION AUTHORIZATION tmp_role_stats;
+-- should not fail because tmp_role_stats is member of pg_read_all_stats
+SELECT pg_database_size('tmp_db') > 0 AS canread;
+ canread
+---------
+ t
+(1 row)
+
+-- should not fail because tmp_role_stats is member of pg_read_all_stats
+SELECT pg_tablespace_size('pg_global') > 0 AS canread;
+ canread
+---------
+ t
+(1 row)
+
+-- should not fail because it is a default tablespace
+SELECT pg_tablespace_size('pg_default') > 0 AS canread;
+ canread
+---------
+ t
+(1 row)
+
+-- should be true because tmp_role_stats is member of pg_read_all_stats
+SELECT COUNT(*) = 0 AS haspriv FROM pg_stat_activity WHERE "query" LIKE '<insufficient privilege>';
+ haspriv
+---------
+ t
+(1 row)
+
+SET SESSION AUTHORIZATION tmp_role_no_stats;
+-- should fail because tmp_role_no_stats has not CONNECT permission on this db
+SELECT pg_database_size('tmp_db') > 0 AS canread;
+ERROR: permission denied for database tmp_db
+-- should fail because tmp_role_no_stats has not CREATE permission on this tablespace
+SELECT pg_tablespace_size('pg_global') > 0 AS canread;
+ERROR: permission denied for tablespace pg_global
+-- should not fail because it is a default tablespace
+SELECT pg_tablespace_size('pg_default') > 0 AS canread;
+ canread
+---------
+ t
+(1 row)
+
+-- should be false because current session belongs to superuser
+SELECT COUNT(*) = 0 AS haspriv FROM pg_stat_activity WHERE "query" LIKE '<insufficient privilege>';
+ haspriv
+---------
+ f
+(1 row)
+
+RESET SESSION AUTHORIZATION;
+DROP ROLE tmp_role_stats;
+DROP ROLE tmp_role_no_stats;
+DROP DATABASE tmp_db;
diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule
index 16f979c8d9..d6cf7b8226 100644
--- a/src/test/regress/parallel_schedule
+++ b/src/test/regress/parallel_schedule
@@ -89,7 +89,7 @@ test: brin gin gist spgist privileges init_privs security_label collate matview
# ----------
# Another group of parallel tests
# ----------
-test: alter_generic alter_operator misc psql async dbsize misc_functions sysviews tsrf tidscan stats_ext
+test: alter_generic alter_operator misc psql async dbsize misc_functions sysviews tsrf tidscan stats_ext defroles
# rules cannot run concurrently with any test that creates a view
test: rules psql_crosstab amutils
diff --git a/src/test/regress/serial_schedule b/src/test/regress/serial_schedule
index 42632be675..b12aa09904 100644
--- a/src/test/regress/serial_schedule
+++ b/src/test/regress/serial_schedule
@@ -135,6 +135,7 @@ test: sysviews
test: tsrf
test: tidscan
test: stats_ext
+test: defroles
test: rules
test: psql_crosstab
test: select_parallel
diff --git a/src/test/regress/sql/defroles.sql b/src/test/regress/sql/defroles.sql
new file mode 100644
index 0000000000..8c91ee9c95
--- /dev/null
+++ b/src/test/regress/sql/defroles.sql
@@ -0,0 +1,46 @@
+-- DEFAULT MONITORING ROLES
+
+DROP ROLE IF EXISTS tmp_role_stats;
+DROP ROLE IF EXISTS tmp_role_no_stats;
+
+DROP DATABASE IF EXISTS tmp_db;
+
+CREATE ROLE tmp_role_stats;
+CREATE ROLE tmp_role_no_stats;
+
+
+-- check pg_read_all_stats default role permissions
+GRANT pg_read_all_stats TO tmp_role_stats;
+
+CREATE DATABASE tmp_db;
+-- CONNECT is granted by default
+REVOKE CONNECT ON DATABASE tmp_db FROM public;
+
+SET SESSION AUTHORIZATION tmp_role_stats;
+-- should not fail because tmp_role_stats is member of pg_read_all_stats
+SELECT pg_database_size('tmp_db') > 0 AS canread;
+-- should not fail because tmp_role_stats is member of pg_read_all_stats
+SELECT pg_tablespace_size('pg_global') > 0 AS canread;
+-- should not fail because it is a default tablespace
+SELECT pg_tablespace_size('pg_default') > 0 AS canread;
+-- should be true because tmp_role_stats is member of pg_read_all_stats
+SELECT COUNT(*) = 0 AS haspriv FROM pg_stat_activity WHERE "query" LIKE '<insufficient privilege>';
+
+SET SESSION AUTHORIZATION tmp_role_no_stats;
+-- should fail because tmp_role_no_stats has not CONNECT permission on this db
+SELECT pg_database_size('tmp_db') > 0 AS canread;
+-- should fail because tmp_role_no_stats has not CREATE permission on this tablespace
+SELECT pg_tablespace_size('pg_global') > 0 AS canread;
+-- should not fail because it is a default tablespace
+SELECT pg_tablespace_size('pg_default') > 0 AS canread;
+-- should be false because current session belongs to superuser
+SELECT COUNT(*) = 0 AS haspriv FROM pg_stat_activity WHERE "query" LIKE '<insufficient privilege>';
+
+
+RESET SESSION AUTHORIZATION;
+
+DROP ROLE tmp_role_stats;
+DROP ROLE tmp_role_no_stats;
+
+DROP DATABASE tmp_db;
+
--
2.18.0.597.ga71716f1ad-goog
