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
Appearance settings

Commit f535d5f

Browse filesBrowse files
michaelpqmichaelpq
committed
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
1 parent 8d28bf5 commit f535d5f
Copy full SHA for f535d5f

File tree

Expand file treeCollapse file tree

2 files changed

+80
-0
lines changed
Filter options
Expand file treeCollapse file tree

2 files changed

+80
-0
lines changed

‎src/test/regress/expected/rolenames.out

Copy file name to clipboardExpand all lines: src/test/regress/expected/rolenames.out
+47Lines changed: 47 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -944,9 +944,56 @@ SELECT proname, proacl FROM pg_proc WHERE proname LIKE 'testagg_';
944944
testagg9 |
945945
(9 rows)
946946

947+
-- DEFAULT MONITORING ROLES
948+
CREATE ROLE regress_role_haspriv;
949+
CREATE ROLE regress_role_nopriv;
950+
-- pg_read_all_stats
951+
GRANT pg_read_all_stats TO regress_role_haspriv;
952+
SET SESSION AUTHORIZATION regress_role_haspriv;
953+
-- returns true with role member of pg_read_all_stats
954+
SELECT COUNT(*) = 0 AS haspriv FROM pg_stat_activity
955+
WHERE query = '<insufficient privilege>';
956+
haspriv
957+
---------
958+
t
959+
(1 row)
960+
961+
SET SESSION AUTHORIZATION regress_role_nopriv;
962+
-- returns false with role not member of pg_read_all_stats
963+
SELECT COUNT(*) = 0 AS haspriv FROM pg_stat_activity
964+
WHERE query = '<insufficient privilege>';
965+
haspriv
966+
---------
967+
f
968+
(1 row)
969+
970+
RESET SESSION AUTHORIZATION;
971+
REVOKE pg_read_all_stats FROM regress_role_haspriv;
972+
-- pg_read_all_settings
973+
GRANT pg_read_all_settings TO regress_role_haspriv;
974+
BEGIN;
975+
-- A GUC using GUC_SUPERUSER_ONLY is useful for negative tests.
976+
SET LOCAL session_preload_libraries TO 'path-to-preload-libraries';
977+
SET SESSION AUTHORIZATION regress_role_haspriv;
978+
-- passes with role member of pg_read_all_settings
979+
SHOW session_preload_libraries;
980+
session_preload_libraries
981+
-----------------------------
982+
"path-to-preload-libraries"
983+
(1 row)
984+
985+
SET SESSION AUTHORIZATION regress_role_nopriv;
986+
-- fails with role not member of pg_read_all_settings
987+
SHOW session_preload_libraries;
988+
ERROR: must be superuser or a member of pg_read_all_settings to examine "session_preload_libraries"
989+
RESET SESSION AUTHORIZATION;
990+
ERROR: current transaction is aborted, commands ignored until end of transaction block
991+
ROLLBACK;
992+
REVOKE pg_read_all_settings FROM regress_role_haspriv;
947993
-- clean up
948994
\c
949995
DROP SCHEMA test_roles_schema;
950996
DROP OWNED BY regress_testrol0, "Public", "current_user", regress_testrol1, regress_testrol2, regress_testrolx CASCADE;
951997
DROP ROLE regress_testrol0, regress_testrol1, regress_testrol2, regress_testrolx;
952998
DROP ROLE "Public", "None", "current_user", "session_user", "user";
999+
DROP ROLE regress_role_haspriv, regress_role_nopriv;

‎src/test/regress/sql/rolenames.sql

Copy file name to clipboardExpand all lines: src/test/regress/sql/rolenames.sql
+33Lines changed: 33 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -438,10 +438,43 @@ REVOKE ALL PRIVILEGES ON FUNCTION testagg9(int2) FROM "none"; --error
438438

439439
SELECT proname, proacl FROM pg_proc WHERE proname LIKE 'testagg_';
440440

441+
-- DEFAULT MONITORING ROLES
442+
CREATE ROLE regress_role_haspriv;
443+
CREATE ROLE regress_role_nopriv;
444+
445+
-- pg_read_all_stats
446+
GRANT pg_read_all_stats TO regress_role_haspriv;
447+
SET SESSION AUTHORIZATION regress_role_haspriv;
448+
-- returns true with role member of pg_read_all_stats
449+
SELECT COUNT(*) = 0 AS haspriv FROM pg_stat_activity
450+
WHERE query = '<insufficient privilege>';
451+
SET SESSION AUTHORIZATION regress_role_nopriv;
452+
-- returns false with role not member of pg_read_all_stats
453+
SELECT COUNT(*) = 0 AS haspriv FROM pg_stat_activity
454+
WHERE query = '<insufficient privilege>';
455+
RESET SESSION AUTHORIZATION;
456+
REVOKE pg_read_all_stats FROM regress_role_haspriv;
457+
458+
-- pg_read_all_settings
459+
GRANT pg_read_all_settings TO regress_role_haspriv;
460+
BEGIN;
461+
-- A GUC using GUC_SUPERUSER_ONLY is useful for negative tests.
462+
SET LOCAL session_preload_libraries TO 'path-to-preload-libraries';
463+
SET SESSION AUTHORIZATION regress_role_haspriv;
464+
-- passes with role member of pg_read_all_settings
465+
SHOW session_preload_libraries;
466+
SET SESSION AUTHORIZATION regress_role_nopriv;
467+
-- fails with role not member of pg_read_all_settings
468+
SHOW session_preload_libraries;
469+
RESET SESSION AUTHORIZATION;
470+
ROLLBACK;
471+
REVOKE pg_read_all_settings FROM regress_role_haspriv;
472+
441473
-- clean up
442474
\c
443475

444476
DROP SCHEMA test_roles_schema;
445477
DROP OWNED BY regress_testrol0, "Public", "current_user", regress_testrol1, regress_testrol2, regress_testrolx CASCADE;
446478
DROP ROLE regress_testrol0, regress_testrol1, regress_testrol2, regress_testrolx;
447479
DROP ROLE "Public", "None", "current_user", "session_user", "user";
480+
DROP ROLE regress_role_haspriv, regress_role_nopriv;

0 commit comments

Comments
0 (0)
Morty Proxy This is a proxified and sanitized view of the page, visit original site.