8000 Add basic regression tests for default monitoring roles · postgrespro/postgres@f535d5f · GitHub
[go: up one dir, main page]
Skip to content

    • Commit f535d5f

    Browse 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

    File tree

    2 files changed

    +80
    -0
    lines changed

    2 files changed

    +80
    -0
    lines changed

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

    Lines 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

    Lines 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