Add basic regression tests for default monitoring roles

The following default roles gain some coverage:
- pg_read_all_stats
- pg_read_all_settings

Author: Alexandra Ryzhevich
Discussion: https://postgr.es/m/CAOt4E5S5WJmDc9YpS1BfyAMQ5C1NEmiYynD6nUz42qVxphqkpA@mail.gmail.com

2 files changed, 80 insertions, 0 deletions

diff --git a/src/test/regress/expected/rolenames.out b/src/test/regress/expected/rolenames.out
index 7daba9fc128..68dacb786a6 100644
--- a/src/test/regress/expected/rolenames.out
+++ b/src/test/regress/expected/rolenames.out
@@ -944,9 +944,56 @@ SELECT proname, proacl FROM pg_proc WHERE proname LIKE 'testagg_';
  testagg9 | 
 (9 rows)
 
+-- DEFAULT MONITORING ROLES
+CREATE ROLE regress_role_haspriv;
+CREATE ROLE regress_role_nopriv;
+-- pg_read_all_stats
+GRANT pg_read_all_stats TO regress_role_haspriv;
+SET SESSION AUTHORIZATION regress_role_haspriv;
+-- returns true with role member of pg_read_all_stats
+SELECT COUNT(*) = 0 AS haspriv FROM pg_stat_activity
+  WHERE query = '<insufficient privilege>';
+ haspriv 
+---------
+ t
+(1 row)
+
+SET SESSION AUTHORIZATION regress_role_nopriv;
+-- returns false with role not member of pg_read_all_stats
+SELECT COUNT(*) = 0 AS haspriv FROM pg_stat_activity
+  WHERE query = '<insufficient privilege>';
+ haspriv 
+---------
+ f
+(1 row)
+
+RESET SESSION AUTHORIZATION;
+REVOKE pg_read_all_stats FROM regress_role_haspriv;
+-- pg_read_all_settings
+GRANT pg_read_all_settings TO regress_role_haspriv;
+BEGIN;
+-- A GUC using GUC_SUPERUSER_ONLY is useful for negative tests.
+SET LOCAL session_preload_libraries TO 'path-to-preload-libraries';
+SET SESSION AUTHORIZATION regress_role_haspriv;
+-- passes with role member of pg_read_all_settings
+SHOW session_preload_libraries;
+  session_preload_libraries  
+-----------------------------
+ "path-to-preload-libraries"
+(1 row)
+
+SET SESSION AUTHORIZATION regress_role_nopriv;
+-- fails with role not member of pg_read_all_settings
+SHOW session_preload_libraries;
+ERROR:  must be superuser or a member of pg_read_all_settings to examine "session_preload_libraries"
+RESET SESSION AUTHORIZATION;
+ERROR:  current transaction is aborted, commands ignored until end of transaction block
+ROLLBACK;
+REVOKE pg_read_all_settings FROM regress_role_haspriv;
 -- clean up
 \c
 DROP SCHEMA test_roles_schema;
 DROP OWNED BY regress_testrol0, "Public", "current_user", regress_testrol1, regress_testrol2, regress_testrolx CASCADE;
 DROP ROLE regress_testrol0, regress_testrol1, regress_testrol2, regress_testrolx;
 DROP ROLE "Public", "None", "current_user", "session_user", "user";
+DROP ROLE regress_role_haspriv, regress_role_nopriv;
diff --git a/src/test/regress/sql/rolenames.sql b/src/test/regress/sql/rolenames.sql
index 5fe8bc8bca2..1246d19715f 100644
--- a/src/test/regress/sql/rolenames.sql
+++ b/src/test/regress/sql/rolenames.sql
@@ -438,6 +438,38 @@ REVOKE ALL PRIVILEGES ON FUNCTION testagg9(int2) FROM "none"; --error
 
 SELECT proname, proacl FROM pg_proc WHERE proname LIKE 'testagg_';
 
+-- DEFAULT MONITORING ROLES
+CREATE ROLE regress_role_haspriv;
+CREATE ROLE regress_role_nopriv;
+
+-- pg_read_all_stats
+GRANT pg_read_all_stats TO regress_role_haspriv;
+SET SESSION AUTHORIZATION regress_role_haspriv;
+-- returns true with role member of pg_read_all_stats
+SELECT COUNT(*) = 0 AS haspriv FROM pg_stat_activity
+  WHERE query = '<insufficient privilege>';
+SET SESSION AUTHORIZATION regress_role_nopriv;
+-- returns false with role not member of pg_read_all_stats
+SELECT COUNT(*) = 0 AS haspriv FROM pg_stat_activity
+  WHERE query = '<insufficient privilege>';
+RESET SESSION AUTHORIZATION;
+REVOKE pg_read_all_stats FROM regress_role_haspriv;
+
+-- pg_read_all_settings
+GRANT pg_read_all_settings TO regress_role_haspriv;
+BEGIN;
+-- A GUC using GUC_SUPERUSER_ONLY is useful for negative tests.
+SET LOCAL session_preload_libraries TO 'path-to-preload-libraries';
+SET SESSION AUTHORIZATION regress_role_haspriv;
+-- passes with role member of pg_read_all_settings
+SHOW session_preload_libraries;
+SET SESSION AUTHORIZATION regress_role_nopriv;
+-- fails with role not member of pg_read_all_settings
+SHOW session_preload_libraries;
+RESET SESSION AUTHORIZATION;
+ROLLBACK;
+REVOKE pg_read_all_settings FROM regress_role_haspriv;
+
 -- clean up
 \c
 
@@ -445,3 +477,4 @@ DROP SCHEMA test_roles_schema;
 DROP OWNED BY regress_testrol0, "Public", "current_user", regress_testrol1, regress_testrol2, regress_testrolx CASCADE;
 DROP ROLE regress_testrol0, regress_testrol1, regress_testrol2, regress_testrolx;
 DROP ROLE "Public", "None", "current_user", "session_user", "user";
+DROP ROLE regress_role_haspriv, regress_role_nopriv;