diff options
Diffstat (limited to 'src/test')
| -rw-r--r-- | src/test/modules/test_oat_hooks/expected/test_oat_hooks.out | 215 | ||||
| -rw-r--r-- | src/test/modules/test_oat_hooks/sql/test_oat_hooks.sql | 49 | ||||
| -rw-r--r-- | src/test/modules/test_oat_hooks/test_oat_hooks.c | 80 | ||||
| -rw-r--r-- | src/test/modules/test_pg_dump/t/001_base.pl | 32 | ||||
| -rw-r--r-- | src/test/modules/unsafe_tests/Makefile | 2 | ||||
| -rw-r--r-- | src/test/modules/unsafe_tests/expected/guc_privs.out | 526 | ||||
| -rw-r--r-- | src/test/modules/unsafe_tests/sql/guc_privs.sql | 237 |
7 files changed, 1071 insertions, 70 deletions
diff --git a/src/test/modules/test_oat_hooks/expected/test_oat_hooks.out b/src/test/modules/test_oat_hooks/expected/test_oat_hooks.out index 45ff276f7e9..39b274b8fa0 100644 --- a/src/test/modules/test_oat_hooks/expected/test_oat_hooks.out +++ b/src/test/modules/test_oat_hooks/expected/test_oat_hooks.out @@ -1,36 +1,84 @@ +-- Creating privileges on a placeholder GUC should create entries in the +-- pg_parameter_acl catalog which conservatively grant no privileges to public. +CREATE ROLE regress_role_joe; +GRANT SET ON PARAMETER test_oat_hooks.user_var1 TO regress_role_joe; +GRANT SET ON PARAMETER test_oat_hooks.super_var1 TO regress_role_joe; -- SET commands fire both the ProcessUtility_hook and the -- object_access_hook_str. Since the auditing GUC starts out false, we miss the -- initial "attempting" audit message from the ProcessUtility_hook, but we --- should thereafter see the audit messages +-- should thereafter see the audit messages. LOAD 'test_oat_hooks'; SET test_oat_hooks.audit = true; -NOTICE: in object_access_hook_str: superuser attempting alter (set) [test_oat_hooks.audit] -NOTICE: in object_access_hook_str: superuser finished alter (set) [test_oat_hooks.audit] +NOTICE: in object_access_hook_str: superuser attempting alter (subId=0x1000, set) [test_oat_hooks.audit] +NOTICE: in object_access_hook_str: superuser finished alter (subId=0x1000, set) [test_oat_hooks.audit] NOTICE: in process utility: superuser finished set +-- Creating privileges on an existent custom GUC should create precisely the +-- right privileges, not overly conservative ones. +GRANT SET ON PARAMETER test_oat_hooks.user_var2 TO regress_role_joe; +NOTICE: in process utility: superuser attempting GrantStmt +NOTICE: in process utility: superuser finished GrantStmt +GRANT SET ON PARAMETER test_oat_hooks.super_var2 TO regress_role_joe; +NOTICE: in process utility: superuser attempting GrantStmt +NOTICE: in process utility: superuser finished GrantStmt +-- Granting multiple privileges on a parameter should be reported correctly to +-- the OAT hook, but beware that WITH GRANT OPTION is not represented. +GRANT SET, ALTER SYSTEM ON PARAMETER none.such TO regress_role_joe; +NOTICE: in process utility: superuser attempting GrantStmt +NOTICE: in process utility: superuser finished GrantStmt +GRANT SET, ALTER SYSTEM ON PARAMETER another.bogus TO regress_role_joe WITH GRANT OPTION; +NOTICE: in process utility: superuser attempting GrantStmt +NOTICE: in process utility: superuser finished GrantStmt +-- Check when the hooks fire relative to dependency based abort of a drop +DROP ROLE regress_role_joe; +NOTICE: in process utility: superuser attempting DropRoleStmt +NOTICE: in object access: superuser attempting drop (subId=0x0) [] +NOTICE: in object access: superuser finished drop (subId=0x0) [] +ERROR: role "regress_role_joe" cannot be dropped because some objects depend on it +DETAIL: privileges for parameter test_oat_hooks.user_var1 +privileges for parameter test_oat_hooks.super_var1 +privileges for parameter test_oat_hooks.user_var2 +privileges for parameter test_oat_hooks.super_var2 +privileges for parameter none.such +privileges for parameter another.bogus +-- Check the behavior of the hooks relative to do-nothing grants and revokes +GRANT SET ON PARAMETER work_mem TO PUBLIC; +NOTICE: in process utility: superuser attempting GrantStmt +NOTICE: in process utility: superuser finished GrantStmt +REVOKE ALTER SYSTEM ON PARAMETER work_mem FROM PUBLIC; +NOTICE: in process utility: superuser attempting GrantStmt +NOTICE: in process utility: superuser finished GrantStmt +-- Check the behavior of the hooks relative to unrecognized parameters +GRANT ALL ON PARAMETER "none.such" TO PUBLIC; +NOTICE: in process utility: superuser attempting GrantStmt +NOTICE: in process utility: superuser finished GrantStmt +-- Check relative to an operation that causes the catalog entry to be deleted +REVOKE ALL ON PARAMETER "none.such" FROM PUBLIC; +NOTICE: in process utility: superuser attempting GrantStmt +NOTICE: in process utility: superuser finished GrantStmt -- Create objects for use in the test CREATE USER regress_test_user; NOTICE: in process utility: superuser attempting CreateRoleStmt -NOTICE: in object access: superuser attempting create (subId=0) [explicit] -NOTICE: in object access: superuser finished create (subId=0) [explicit] +NOTICE: in object access: superuser attempting create (subId=0x0) [explicit] +NOTICE: in object access: superuser finished create (subId=0x0) [explicit] NOTICE: in process utility: superuser finished CreateRoleStmt CREATE TABLE regress_test_table (t text); NOTICE: in process utility: superuser attempting CreateStmt -NOTICE: in object access: superuser attempting namespace search (subId=0) [no report on violation, allowed] +NOTICE: in object access: superuser attempting namespace search (subId=0x0) [no report on violation, allowed] LINE 1: CREATE TABLE regress_test_table (t text); ^ -NOTICE: in object access: superuser finished namespace search (subId=0) [no report on violation, allowed] +NOTICE: in object access: superuser finished namespace search (subId=0x0) [no report on violation, allowed] LINE 1: CREATE TABLE regress_test_table (t text); ^ -NOTICE: in object access: superuser attempting create (subId=0) [explicit] -NOTICE: in object access: superuser finished create (subId=0) [explicit] -NOTICE: in object access: superuser attempting create (subId=0) [explicit] -NOTICE: in object access: superuser finished create (subId=0) [explicit] -NOTICE: in object access: superuser attempting create (subId=0) [explicit] -NOTICE: in object access: superuser finished create (subId=0) [explicit] -NOTICE: in object access: superuser attempting create (subId=0) [internal] -NOTICE: in object access: superuser finished create (subId=0) [internal] -NOTICE: in object access: superuser attempting create (subId=0) [internal] -NOTICE: in object access: superuser finished create (subId=0) [internal] +NOTICE: in object access: superuser attempting create (subId=0x0) [explicit] +NOTICE: in object access: superuser finished create (subId=0x0) [explicit] +NOTICE: in object access: superuser attempting create (subId=0x0) [explicit] +NOTICE: in object access: superuser finished create (subId=0x0) [explicit] +NOTICE: in object access: superuser attempting create (subId=0x0) [explicit] +NOTICE: in object access: superuser finished create (subId=0x0) [explicit] +NOTICE: in object access: superuser attempting create (subId=0x0) [internal] +NOTICE: in object access: superuser finished create (subId=0x0) [internal] +NOTICE: in object access: superuser attempting create (subId=0x0) [internal] +NOTICE: in object access: superuser finished create (subId=0x0) [internal] NOTICE: in process utility: superuser finished CreateStmt GRANT SELECT ON Table regress_test_table TO public; NOTICE: in process utility: superuser attempting GrantStmt @@ -39,8 +87,8 @@ CREATE FUNCTION regress_test_func (t text) RETURNS text AS $$ SELECT $1; $$ LANGUAGE sql; NOTICE: in process utility: superuser attempting CreateFunctionStmt -NOTICE: in object access: superuser attempting create (subId=0) [explicit] -NOTICE: in object access: superuser finished create (subId=0) [explicit] +NOTICE: in object access: superuser attempting create (subId=0x0) [explicit] +NOTICE: in object access: superuser finished create (subId=0x0) [explicit] NOTICE: in process utility: superuser finished CreateFunctionStmt GRANT EXECUTE ON FUNCTION regress_test_func (text) TO public; NOTICE: in process utility: superuser attempting GrantStmt @@ -63,35 +111,35 @@ NOTICE: in executor check perms: superuser finished execute SET work_mem = 8192; NOTICE: in process utility: superuser attempting set -NOTICE: in object_access_hook_str: superuser attempting alter (set) [work_mem] -NOTICE: in object_access_hook_str: superuser finished alter (set) [work_mem] +NOTICE: in object_access_hook_str: superuser attempting alter (subId=0x1000, set) [work_mem] +NOTICE: in object_access_hook_str: superuser finished alter (subId=0x1000, set) [work_mem] NOTICE: in process utility: superuser finished set RESET work_mem; NOTICE: in process utility: superuser attempting set -NOTICE: in object_access_hook_str: superuser attempting alter (set) [work_mem] -NOTICE: in object_access_hook_str: superuser finished alter (set) [work_mem] +NOTICE: in object_access_hook_str: superuser attempting alter (subId=0x1000, set) [work_mem] +NOTICE: in object_access_hook_str: superuser finished alter (subId=0x1000, set) [work_mem] NOTICE: in process utility: superuser finished set ALTER SYSTEM SET work_mem = 8192; NOTICE: in process utility: superuser attempting alter system -NOTICE: in object_access_hook_str: superuser attempting alter (alter system set) [work_mem] -NOTICE: in object_access_hook_str: superuser finished alter (alter system set) [work_mem] +NOTICE: in object_access_hook_str: superuser attempting alter (subId=0x2000, alter system) [work_mem] +NOTICE: in object_access_hook_str: superuser finished alter (subId=0x2000, alter system) [work_mem] NOTICE: in process utility: superuser finished alter system ALTER SYSTEM RESET work_mem; NOTICE: in process utility: superuser attempting alter system -NOTICE: in object_access_hook_str: superuser attempting alter (alter system set) [work_mem] -NOTICE: in object_access_hook_str: superuser finished alter (alter system set) [work_mem] +NOTICE: in object_access_hook_str: superuser attempting alter (subId=0x2000, alter system) [work_mem] +NOTICE: in object_access_hook_str: superuser finished alter (subId=0x2000, alter system) [work_mem] NOTICE: in process utility: superuser finished alter system -- Do those same things as non-superuser SET SESSION AUTHORIZATION regress_test_user; NOTICE: in process utility: superuser attempting set -NOTICE: in object_access_hook_str: non-superuser attempting alter (set) [session_authorization] -NOTICE: in object_access_hook_str: non-superuser finished alter (set) [session_authorization] +NOTICE: in object_access_hook_str: non-superuser attempting alter (subId=0x1000, set) [session_authorization] +NOTICE: in object_access_hook_str: non-superuser finished alter (subId=0x1000, set) [session_authorization] NOTICE: in process utility: non-superuser finished set SELECT * FROM regress_test_table; -NOTICE: in object access: non-superuser attempting namespace search (subId=0) [no report on violation, allowed] +NOTICE: in object access: non-superuser attempting namespace search (subId=0x0) [no report on violation, allowed] LINE 1: SELECT * FROM regress_test_table; ^ -NOTICE: in object access: non-superuser finished namespace search (subId=0) [no report on violation, allowed] +NOTICE: in object access: non-superuser finished namespace search (subId=0x0) [no report on violation, allowed] LINE 1: SELECT * FROM regress_test_table; ^ NOTICE: in executor check perms: non-superuser attempting execute @@ -110,61 +158,89 @@ NOTICE: in executor check perms: non-superuser finished execute SET work_mem = 8192; NOTICE: in process utility: non-superuser attempting set -NOTICE: in object_access_hook_str: non-superuser attempting alter (set) [work_mem] -NOTICE: in object_access_hook_str: non-superuser finished alter (set) [work_mem] +NOTICE: in object_access_hook_str: non-superuser attempting alter (subId=0x1000, set) [work_mem] +NOTICE: in object_access_hook_str: non-superuser finished alter (subId=0x1000, set) [work_mem] NOTICE: in process utility: non-superuser finished set RESET work_mem; NOTICE: in process utility: non-superuser attempting set -NOTICE: in object_access_hook_str: non-superuser attempting alter (set) [work_mem] -NOTICE: in object_access_hook_str: non-superuser finished alter (set) [work_mem] +NOTICE: in object_access_hook_str: non-superuser attempting alter (subId=0x1000, set) [work_mem] +NOTICE: in object_access_hook_str: non-superuser finished alter (subId=0x1000, set) [work_mem] NOTICE: in process utility: non-superuser finished set ALTER SYSTEM SET work_mem = 8192; NOTICE: in process utility: non-superuser attempting alter system -ERROR: must be superuser to execute ALTER SYSTEM command +ERROR: permission denied to set parameter "work_mem" ALTER SYSTEM RESET work_mem; NOTICE: in process utility: non-superuser attempting alter system -ERROR: must be superuser to execute ALTER SYSTEM command +ERROR: permission denied to set parameter "work_mem" +SET test_oat_hooks.user_var1 = true; +NOTICE: in process utility: non-superuser attempting set +NOTICE: in object_access_hook_str: non-superuser attempting alter (subId=0x1000, set) [test_oat_hooks.user_var1] +NOTICE: in object_access_hook_str: non-superuser finished alter (subId=0x1000, set) [test_oat_hooks.user_var1] +NOTICE: in process utility: non-superuser finished set +SET test_oat_hooks.super_var1 = true; +NOTICE: in process utility: non-superuser attempting set +ERROR: permission denied to set parameter "test_oat_hooks.super_var1" +ALTER SYSTEM SET test_oat_hooks.user_var1 = true; +NOTICE: in process utility: non-superuser attempting alter system +ERROR: permission denied to set parameter "test_oat_hooks.user_var1" +ALTER SYSTEM SET test_oat_hooks.super_var1 = true; +NOTICE: in process utility: non-superuser attempting alter system +ERROR: permission denied to set parameter "test_oat_hooks.super_var1" +SET test_oat_hooks.user_var2 = true; +NOTICE: in process utility: non-superuser attempting set +NOTICE: in object_access_hook_str: non-superuser attempting alter (subId=0x1000, set) [test_oat_hooks.user_var2] +NOTICE: in object_access_hook_str: non-superuser finished alter (subId=0x1000, set) [test_oat_hooks.user_var2] +NOTICE: in process utility: non-superuser finished set +SET test_oat_hooks.super_var2 = true; +NOTICE: in process utility: non-superuser attempting set +ERROR: permission denied to set parameter "test_oat_hooks.super_var2" +ALTER SYSTEM SET test_oat_hooks.user_var2 = true; +NOTICE: in process utility: non-superuser attempting alter system +ERROR: permission denied to set parameter "test_oat_hooks.user_var2" +ALTER SYSTEM SET test_oat_hooks.super_var2 = true; +NOTICE: in process utility: non-superuser attempting alter system +ERROR: permission denied to set parameter "test_oat_hooks.super_var2" RESET SESSION AUTHORIZATION; NOTICE: in process utility: non-superuser attempting set -NOTICE: in object_access_hook_str: superuser attempting alter (set) [session_authorization] -NOTICE: in object_access_hook_str: superuser finished alter (set) [session_authorization] +NOTICE: in object_access_hook_str: superuser attempting alter (subId=0x1000, set) [session_authorization] +NOTICE: in object_access_hook_str: superuser finished alter (subId=0x1000, set) [session_authorization] NOTICE: in process utility: superuser finished set -- Turn off non-superuser permissions SET test_oat_hooks.deny_set_variable = true; NOTICE: in process utility: superuser attempting set -NOTICE: in object_access_hook_str: superuser attempting alter (set) [test_oat_hooks.deny_set_variable] -NOTICE: in object_access_hook_str: superuser finished alter (set) [test_oat_hooks.deny_set_variable] +NOTICE: in object_access_hook_str: superuser attempting alter (subId=0x1000, set) [test_oat_hooks.deny_set_variable] +NOTICE: in object_access_hook_str: superuser finished alter (subId=0x1000, set) [test_oat_hooks.deny_set_variable] NOTICE: in process utility: superuser finished set SET test_oat_hooks.deny_alter_system = true; NOTICE: in process utility: superuser attempting set -NOTICE: in object_access_hook_str: superuser attempting alter (set) [test_oat_hooks.deny_alter_system] -NOTICE: in object_access_hook_str: superuser finished alter (set) [test_oat_hooks.deny_alter_system] +NOTICE: in object_access_hook_str: superuser attempting alter (subId=0x1000, set) [test_oat_hooks.deny_alter_system] +NOTICE: in object_access_hook_str: superuser finished alter (subId=0x1000, set) [test_oat_hooks.deny_alter_system] NOTICE: in process utility: superuser finished set SET test_oat_hooks.deny_object_access = true; NOTICE: in process utility: superuser attempting set -NOTICE: in object_access_hook_str: superuser attempting alter (set) [test_oat_hooks.deny_object_access] -NOTICE: in object_access_hook_str: superuser finished alter (set) [test_oat_hooks.deny_object_access] +NOTICE: in object_access_hook_str: superuser attempting alter (subId=0x1000, set) [test_oat_hooks.deny_object_access] +NOTICE: in object_access_hook_str: superuser finished alter (subId=0x1000, set) [test_oat_hooks.deny_object_access] NOTICE: in process utility: superuser finished set SET test_oat_hooks.deny_exec_perms = true; NOTICE: in process utility: superuser attempting set -NOTICE: in object_access_hook_str: superuser attempting alter (set) [test_oat_hooks.deny_exec_perms] -NOTICE: in object_access_hook_str: superuser finished alter (set) [test_oat_hooks.deny_exec_perms] +NOTICE: in object_access_hook_str: superuser attempting alter (subId=0x1000, set) [test_oat_hooks.deny_exec_perms] +NOTICE: in object_access_hook_str: superuser finished alter (subId=0x1000, set) [test_oat_hooks.deny_exec_perms] NOTICE: in process utility: superuser finished set SET test_oat_hooks.deny_utility_commands = true; NOTICE: in process utility: superuser attempting set -NOTICE: in object_access_hook_str: superuser attempting alter (set) [test_oat_hooks.deny_utility_commands] -NOTICE: in object_access_hook_str: superuser finished alter (set) [test_oat_hooks.deny_utility_commands] +NOTICE: in object_access_hook_str: superuser attempting alter (subId=0x1000, set) [test_oat_hooks.deny_utility_commands] +NOTICE: in object_access_hook_str: superuser finished alter (subId=0x1000, set) [test_oat_hooks.deny_utility_commands] NOTICE: in process utility: superuser finished set -- Try again as non-superuser with permissions denied SET SESSION AUTHORIZATION regress_test_user; NOTICE: in process utility: superuser attempting set -NOTICE: in object_access_hook_str: non-superuser attempting alter (set) [session_authorization] +NOTICE: in object_access_hook_str: non-superuser attempting alter (subId=0x1000, set) [session_authorization] ERROR: permission denied: set session_authorization SELECT * FROM regress_test_table; -NOTICE: in object access: superuser attempting namespace search (subId=0) [no report on violation, allowed] +NOTICE: in object access: superuser attempting namespace search (subId=0x0) [no report on violation, allowed] LINE 1: SELECT * FROM regress_test_table; ^ -NOTICE: in object access: superuser finished namespace search (subId=0) [no report on violation, allowed] +NOTICE: in object access: superuser finished namespace search (subId=0x0) [no report on violation, allowed] LINE 1: SELECT * FROM regress_test_table; ^ NOTICE: in executor check perms: superuser attempting execute @@ -183,28 +259,43 @@ NOTICE: in executor check perms: superuser finished execute SET work_mem = 8192; NOTICE: in process utility: superuser attempting set -NOTICE: in object_access_hook_str: superuser attempting alter (set) [work_mem] -NOTICE: in object_access_hook_str: superuser finished alter (set) [work_mem] +NOTICE: in object_access_hook_str: superuser attempting alter (subId=0x1000, set) [work_mem] +NOTICE: in object_access_hook_str: superuser finished alter (subId=0x1000, set) [work_mem] NOTICE: in process utility: superuser finished set RESET work_mem; NOTICE: in process utility: superuser attempting set -NOTICE: in object_access_hook_str: superuser attempting alter (set) [work_mem] -NOTICE: in object_access_hook_str: superuser finished alter (set) [work_mem] +NOTICE: in object_access_hook_str: superuser attempting alter (subId=0x1000, set) [work_mem] +NOTICE: in object_access_hook_str: superuser finished alter (subId=0x1000, set) [work_mem] NOTICE: in process utility: superuser finished set ALTER SYSTEM SET work_mem = 8192; NOTICE: in process utility: superuser attempting alter system -NOTICE: in object_access_hook_str: superuser attempting alter (alter system set) [work_mem] -NOTICE: in object_access_hook_str: superuser finished alter (alter system set) [work_mem] +NOTICE: in object_access_hook_str: superuser attempting alter (subId=0x2000, alter system) [work_mem] +NOTICE: in object_access_hook_str: superuser finished alter (subId=0x2000, alter system) [work_mem] NOTICE: in process utility: superuser finished alter system ALTER SYSTEM RESET work_mem; NOTICE: in process utility: superuser attempting alter system -NOTICE: in object_access_hook_str: superuser attempting alter (alter system set) [work_mem] -NOTICE: in object_access_hook_str: superuser finished alter (alter system set) [work_mem] +NOTICE: in object_access_hook_str: superuser attempting alter (subId=0x2000, alter system) [work_mem] +NOTICE: in object_access_hook_str: superuser finished alter (subId=0x2000, alter system) [work_mem] NOTICE: in process utility: superuser finished alter system +-- Clean up RESET SESSION AUTHORIZATION; NOTICE: in process utility: superuser attempting set -NOTICE: in object_access_hook_str: superuser attempting alter (set) [session_authorization] -NOTICE: in object_access_hook_str: superuser finished alter (set) [session_authorization] +NOTICE: in object_access_hook_str: superuser attempting alter (subId=0x1000, set) [session_authorization] +NOTICE: in object_access_hook_str: superuser finished alter (subId=0x1000, set) [session_authorization] NOTICE: in process utility: superuser finished set SET test_oat_hooks.audit = false; NOTICE: in process utility: superuser attempting set +DROP ROLE regress_role_joe; -- fails +ERROR: role "regress_role_joe" cannot be dropped because some objects depend on it +DETAIL: privileges for parameter test_oat_hooks.user_var1 +privileges for parameter test_oat_hooks.super_var1 +privileges for parameter test_oat_hooks.user_var2 +privileges for parameter test_oat_hooks.super_var2 +privileges for parameter none.such +privileges for parameter another.bogus +REVOKE ALL PRIVILEGES ON PARAMETER + none.such, another.bogus, + test_oat_hooks.user_var1, test_oat_hooks.super_var1, + test_oat_hooks.user_var2, test_oat_hooks.super_var2 + FROM regress_role_joe; +DROP ROLE regress_role_joe; diff --git a/src/test/modules/test_oat_hooks/sql/test_oat_hooks.sql b/src/test/modules/test_oat_hooks/sql/test_oat_hooks.sql index 09e61864ee2..8b6d5373aa5 100644 --- a/src/test/modules/test_oat_hooks/sql/test_oat_hooks.sql +++ b/src/test/modules/test_oat_hooks/sql/test_oat_hooks.sql @@ -1,10 +1,39 @@ +-- Creating privileges on a placeholder GUC should create entries in the +-- pg_parameter_acl catalog which conservatively grant no privileges to public. +CREATE ROLE regress_role_joe; +GRANT SET ON PARAMETER test_oat_hooks.user_var1 TO regress_role_joe; +GRANT SET ON PARAMETER test_oat_hooks.super_var1 TO regress_role_joe; + -- SET commands fire both the ProcessUtility_hook and the -- object_access_hook_str. Since the auditing GUC starts out false, we miss the -- initial "attempting" audit message from the ProcessUtility_hook, but we --- should thereafter see the audit messages +-- should thereafter see the audit messages. LOAD 'test_oat_hooks'; SET test_oat_hooks.audit = true; +-- Creating privileges on an existent custom GUC should create precisely the +-- right privileges, not overly conservative ones. +GRANT SET ON PARAMETER test_oat_hooks.user_var2 TO regress_role_joe; +GRANT SET ON PARAMETER test_oat_hooks.super_var2 TO regress_role_joe; + +-- Granting multiple privileges on a parameter should be reported correctly to +-- the OAT hook, but beware that WITH GRANT OPTION is not represented. +GRANT SET, ALTER SYSTEM ON PARAMETER none.such TO regress_role_joe; +GRANT SET, ALTER SYSTEM ON PARAMETER another.bogus TO regress_role_joe WITH GRANT OPTION; + +-- Check when the hooks fire relative to dependency based abort of a drop +DROP ROLE regress_role_joe; + +-- Check the behavior of the hooks relative to do-nothing grants and revokes +GRANT SET ON PARAMETER work_mem TO PUBLIC; +REVOKE ALTER SYSTEM ON PARAMETER work_mem FROM PUBLIC; + +-- Check the behavior of the hooks relative to unrecognized parameters +GRANT ALL ON PARAMETER "none.such" TO PUBLIC; + +-- Check relative to an operation that causes the catalog entry to be deleted +REVOKE ALL ON PARAMETER "none.such" FROM PUBLIC; + -- Create objects for use in the test CREATE USER regress_test_user; CREATE TABLE regress_test_table (t text); @@ -30,6 +59,16 @@ SET work_mem = 8192; RESET work_mem; ALTER SYSTEM SET work_mem = 8192; ALTER SYSTEM RESET work_mem; + +SET test_oat_hooks.user_var1 = true; +SET test_oat_hooks.super_var1 = true; +ALTER SYSTEM SET test_oat_hooks.user_var1 = true; +ALTER SYSTEM SET test_oat_hooks.super_var1 = true; +SET test_oat_hooks.user_var2 = true; +SET test_oat_hooks.super_var2 = true; +ALTER SYSTEM SET test_oat_hooks.user_var2 = true; +ALTER SYSTEM SET test_oat_hooks.super_var2 = true; + RESET SESSION AUTHORIZATION; -- Turn off non-superuser permissions @@ -48,6 +87,14 @@ RESET work_mem; ALTER SYSTEM SET work_mem = 8192; ALTER SYSTEM RESET work_mem; +-- Clean up RESET SESSION AUTHORIZATION; SET test_oat_hooks.audit = false; +DROP ROLE regress_role_joe; -- fails +REVOKE ALL PRIVILEGES ON PARAMETER + none.such, another.bogus, + test_oat_hooks.user_var1, test_oat_hooks.super_var1, + test_oat_hooks.user_var2, test_oat_hooks.super_var2 + FROM regress_role_joe; +DROP ROLE regress_role_joe; diff --git a/src/test/modules/test_oat_hooks/test_oat_hooks.c b/src/test/modules/test_oat_hooks/test_oat_hooks.c index eb7564ce22e..551da5d4986 100644 --- a/src/test/modules/test_oat_hooks/test_oat_hooks.c +++ b/src/test/modules/test_oat_hooks/test_oat_hooks.c @@ -34,6 +34,15 @@ static bool REGRESS_deny_exec_perms = false; static bool REGRESS_deny_utility_commands = false; static bool REGRESS_audit = false; +/* + * GUCs for testing privileges on USERSET and SUSET variables, + * with and without privileges granted prior to module load. + */ +static bool REGRESS_userset_variable1 = false; +static bool REGRESS_userset_variable2 = false; +static bool REGRESS_suset_variable1 = false; +static bool REGRESS_suset_variable2 = false; + /* Saved hook values in case of unload */ static object_access_hook_type next_object_access_hook = NULL; static object_access_hook_type_str next_object_access_hook_str = NULL; @@ -153,6 +162,56 @@ _PG_init(void) NULL, NULL); + /* + * test_oat_hooks.user_var{1,2} = (on|off) + */ + DefineCustomBoolVariable("test_oat_hooks.user_var1", + "Dummy parameter settable by public", + NULL, + ®RESS_userset_variable1, + false, + PGC_USERSET, + GUC_NOT_IN_SAMPLE, + NULL, + NULL, + NULL); + + DefineCustomBoolVariable("test_oat_hooks.user_var2", + "Dummy parameter settable by public", + NULL, + ®RESS_userset_variable2, + false, + PGC_USERSET, + GUC_NOT_IN_SAMPLE, + NULL, + NULL, + NULL); + + /* + * test_oat_hooks.super_var{1,2} = (on|off) + */ + DefineCustomBoolVariable("test_oat_hooks.super_var1", + "Dummy parameter settable by superuser", + NULL, + ®RESS_suset_variable1, + false, + PGC_SUSET, + GUC_NOT_IN_SAMPLE, + NULL, + NULL, + NULL); + + DefineCustomBoolVariable("test_oat_hooks.super_var2", + "Dummy parameter settable by superuser", + NULL, + ®RESS_suset_variable2, + false, + PGC_SUSET, + GUC_NOT_IN_SAMPLE, + NULL, + NULL, + NULL); + MarkGUCPrefixReserved("test_oat_hooks"); /* Object access hook */ @@ -250,7 +309,14 @@ REGRESS_object_access_hook_str(ObjectAccessType access, Oid classId, const char switch (access) { case OAT_POST_ALTER: - if (subId & ACL_SET_VALUE) + if ((subId & ACL_SET) && (subId & ACL_ALTER_SYSTEM)) + { + if (REGRESS_deny_set_variable && !superuser_arg(GetUserId())) + ereport(ERROR, + (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), + errmsg("permission denied: all privileges %s", objName))); + } + else if (subId & ACL_SET) { if (REGRESS_deny_set_variable && !superuser_arg(GetUserId())) ereport(ERROR, @@ -265,7 +331,7 @@ REGRESS_object_access_hook_str(ObjectAccessType access, Oid classId, const char errmsg("permission denied: alter system set %s", objName))); } else - elog(ERROR, "Unknown SettingAclRelationId subId: %d", subId); + elog(ERROR, "Unknown ParameterAclRelationId subId: %d", subId); break; default: break; @@ -860,12 +926,14 @@ accesstype_to_string(ObjectAccessType access, int subId) type = "UNRECOGNIZED ObjectAccessType"; } - if (subId & ACL_SET_VALUE) - return psprintf("%s (set)", type); + if ((subId & ACL_SET) && (subId & ACL_ALTER_SYSTEM)) + return psprintf("%s (subId=0x%x, all privileges)", type, subId); + if (subId & ACL_SET) + return psprintf("%s (subId=0x%x, set)", type, subId); if (subId & ACL_ALTER_SYSTEM) - return psprintf("%s (alter system set)", type); + return psprintf("%s (subId=0x%x, alter system)", type, subId); - return psprintf("%s (subId=%d)", type, subId); + return psprintf("%s (subId=0x%x)", type, subId); } static char * diff --git a/src/test/modules/test_pg_dump/t/001_base.pl b/src/test/modules/test_pg_dump/t/001_base.pl index 84a35590b75..d842f934a3a 100644 --- a/src/test/modules/test_pg_dump/t/001_base.pl +++ b/src/test/modules/test_pg_dump/t/001_base.pl @@ -316,6 +316,38 @@ my %tests = ( like => { pg_dumpall_globals => 1, }, }, + 'GRANT ALTER SYSTEM ON PARAMETER full_page_writes TO regress_dump_test_role' + => { + create_order => 2, + create_sql => + 'GRANT ALTER SYSTEM ON PARAMETER full_page_writes TO regress_dump_test_role;', + regexp => + + qr/^GRANT ALTER SYSTEM ON PARAMETER full_page_writes TO regress_dump_test_role;/m, + like => { pg_dumpall_globals => 1, }, + }, + + 'GRANT ALL ON PARAMETER Custom.Knob TO regress_dump_test_role WITH GRANT OPTION' + => { + create_order => 2, + create_sql => + 'GRANT SET, ALTER SYSTEM ON PARAMETER Custom.Knob TO regress_dump_test_role WITH GRANT OPTION;', + regexp => + # "set" plus "alter system" is "all" privileges on parameters + qr/^GRANT ALL ON PARAMETER "custom.knob" TO regress_dump_test_role WITH GRANT OPTION;/m, + like => { pg_dumpall_globals => 1, }, + }, + + 'GRANT ALL ON PARAMETER DateStyle TO regress_dump_test_role' => { + create_order => 2, + create_sql => + 'GRANT ALL ON PARAMETER "DateStyle" TO regress_dump_test_role WITH GRANT OPTION; REVOKE GRANT OPTION FOR ALL ON PARAMETER DateStyle FROM regress_dump_test_role;', + regexp => + # The revoke simplifies the ultimate grant so as to not include "with grant option" + qr/^GRANT ALL ON PARAMETER datestyle TO regress_dump_test_role;/m, + like => { pg_dumpall_globals => 1, }, + }, + 'CREATE SCHEMA public' => { regexp => qr/^CREATE SCHEMA public;/m, like => { diff --git a/src/test/modules/unsafe_tests/Makefile b/src/test/modules/unsafe_tests/Makefile index 3ecf5fcfc5b..df582736884 100644 --- a/src/test/modules/unsafe_tests/Makefile +++ b/src/test/modules/unsafe_tests/Makefile @@ -1,6 +1,6 @@ # src/test/modules/unsafe_tests/Makefile -REGRESS = rolenames alter_system_table +REGRESS = rolenames alter_system_table guc_privs ifdef USE_PGXS PG_CONFIG = pg_config diff --git a/src/test/modules/unsafe_tests/expected/guc_privs.out b/src/test/modules/unsafe_tests/expected/guc_privs.out new file mode 100644 index 00000000000..58dc87f958e --- /dev/null +++ b/src/test/modules/unsafe_tests/expected/guc_privs.out @@ -0,0 +1,526 @@ +-- +-- Tests for privileges on GUCs. +-- This is unsafe because changes will affect other databases in the cluster. +-- +-- Test with a superuser role. +CREATE ROLE regress_admin SUPERUSER; +-- Perform operations as user 'regress_admin'. +SET SESSION AUTHORIZATION regress_admin; +-- PGC_BACKEND +SET ignore_system_indexes = OFF; -- fail, cannot be set after connection start +ERROR: parameter "ignore_system_indexes" cannot be set after connection start +RESET ignore_system_indexes; -- fail, cannot be set after connection start +ERROR: parameter "ignore_system_indexes" cannot be set after connection start +ALTER SYSTEM SET ignore_system_indexes = OFF; -- ok +ALTER SYSTEM RESET ignore_system_indexes; -- ok +-- PGC_INTERNAL +SET block_size = 50; -- fail, cannot be changed +ERROR: parameter "block_size" cannot be changed +RESET block_size; -- fail, cannot be changed +ERROR: parameter "block_size" cannot be changed +ALTER SYSTEM SET block_size = 50; -- fail, cannot be changed +ERROR: parameter "block_size" cannot be changed +ALTER SYSTEM RESET block_size; -- fail, cannot be changed +ERROR: parameter "block_size" cannot be changed +-- PGC_POSTMASTER +SET autovacuum_freeze_max_age = 1000050000; -- fail, requires restart +ERROR: parameter "autovacuum_freeze_max_age" cannot be changed without restarting the server +RESET autovacuum_freeze_max_age; -- fail, requires restart +ERROR: parameter "autovacuum_freeze_max_age" cannot be changed without restarting the server +ALTER SYSTEM SET autovacuum_freeze_max_age = 1000050000; -- ok +ALTER SYSTEM RESET autovacuum_freeze_max_age; -- ok +ALTER SYSTEM SET config_file = '/usr/local/data/postgresql.conf'; -- fail, cannot be changed +ERROR: parameter "config_file" cannot be changed +ALTER SYSTEM RESET config_file; -- fail, cannot be changed +ERROR: parameter "config_file" cannot be changed +-- PGC_SIGHUP +SET autovacuum = OFF; -- fail, requires reload +ERROR: parameter "autovacuum" cannot be changed now +RESET autovacuum; -- fail, requires reload +ERROR: parameter "autovacuum" cannot be changed now +ALTER SYSTEM SET autovacuum = OFF; -- ok +ALTER SYSTEM RESET autovacuum; -- ok +-- PGC_SUSET +SET lc_messages = 'C'; -- ok +RESET lc_messages; -- ok +ALTER SYSTEM SET lc_messages = 'C'; -- ok +ALTER SYSTEM RESET lc_messages; -- ok +-- PGC_SU_BACKEND +SET jit_debugging_support = OFF; -- fail, cannot be set after connection start +ERROR: parameter "jit_debugging_support" cannot be set after connection start +RESET jit_debugging_support; -- fail, cannot be set after connection start +ERROR: parameter "jit_debugging_support" cannot be set after connection start +ALTER SYSTEM SET jit_debugging_support = OFF; -- ok +ALTER SYSTEM RESET jit_debugging_support; -- ok +-- PGC_USERSET +SET DateStyle = 'ISO, MDY'; -- ok +RESET DateStyle; -- ok +ALTER SYSTEM SET DateStyle = 'ISO, MDY'; -- ok +ALTER SYSTEM RESET DateStyle; -- ok +ALTER SYSTEM SET ssl_renegotiation_limit = 0; -- fail, cannot be changed +ERROR: parameter "ssl_renegotiation_limit" cannot be changed +ALTER SYSTEM RESET ssl_renegotiation_limit; -- fail, cannot be changed +ERROR: parameter "ssl_renegotiation_limit" cannot be changed +-- Finished testing superuser +-- Create non-superuser with privileges to configure host resource usage +CREATE ROLE regress_host_resource_admin NOSUPERUSER; +-- Revoke privileges not yet granted +REVOKE SET, ALTER SYSTEM ON PARAMETER work_mem FROM regress_host_resource_admin; +REVOKE SET, ALTER SYSTEM ON PARAMETER zero_damaged_pages FROM regress_host_resource_admin; +-- Check the new role does not yet have privileges on parameters +SELECT has_parameter_privilege('regress_host_resource_admin', 'work_mem', 'SET, ALTER SYSTEM'); + has_parameter_privilege +------------------------- + f +(1 row) + +SELECT has_parameter_privilege('regress_host_resource_admin', 'work_mem', 'SET'); + has_parameter_privilege +------------------------- + f +(1 row) + +SELECT has_parameter_privilege('regress_host_resource_admin', 'work_mem', 'ALTER SYSTEM'); + has_parameter_privilege +------------------------- + f +(1 row) + +-- Check inappropriate and nonsense privilege types +SELECT has_parameter_privilege('regress_host_resource_admin', 'work_mem', 'SELECT, UPDATE, CREATE'); +ERROR: unrecognized privilege type: "SELECT" +SELECT has_parameter_privilege('regress_host_resource_admin', 'work_mem', 'USAGE'); +ERROR: unrecognized privilege type: "USAGE" +SELECT has_parameter_privilege('regress_host_resource_admin', 'work_mem', 'WHATEVER'); +ERROR: unrecognized privilege type: "WHATEVER" +-- Revoke, grant, and revoke again a SUSET parameter not yet granted +SELECT has_parameter_privilege('regress_host_resource_admin', 'zero_damaged_pages', 'SET'); + has_parameter_privilege +------------------------- + f +(1 row) + +SELECT has_parameter_privilege('regress_host_resource_admin', 'zero_damaged_pages', 'ALTER SYSTEM'); + has_parameter_privilege +------------------------- + f +(1 row) + +REVOKE SET ON PARAMETER zero_damaged_pages FROM regress_host_resource_admin; +SELECT has_parameter_privilege('regress_host_resource_admin', 'zero_damaged_pages', 'SET'); + has_parameter_privilege +------------------------- + f +(1 row) + +SELECT has_parameter_privilege('regress_host_resource_admin', 'zero_damaged_pages', 'ALTER SYSTEM'); + has_parameter_privilege +------------------------- + f +(1 row) + +GRANT SET ON PARAMETER zero_damaged_pages TO regress_host_resource_admin; +SELECT has_parameter_privilege('regress_host_resource_admin', 'zero_damaged_pages', 'SET'); + has_parameter_privilege +------------------------- + t +(1 row) + +SELECT has_parameter_privilege('regress_host_resource_admin', 'zero_damaged_pages', 'ALTER SYSTEM'); + has_parameter_privilege +------------------------- + f +(1 row) + +REVOKE SET ON PARAMETER zero_damaged_pages FROM regress_host_resource_admin; +SELECT has_parameter_privilege('regress_host_resource_admin', 'zero_damaged_pages', 'SET'); + has_parameter_privilege +------------------------- + f +(1 row) + +SELECT has_parameter_privilege('regress_host_resource_admin', 'zero_damaged_pages', 'ALTER SYSTEM'); + has_parameter_privilege +------------------------- + f +(1 row) + +-- Revoke, grant, and revoke again a USERSET parameter not yet granted +SELECT has_parameter_privilege('regress_host_resource_admin', 'work_mem', 'SET'); + has_parameter_privilege +------------------------- + f +(1 row) + +SELECT has_parameter_privilege('regress_host_resource_admin', 'work_mem', 'ALTER SYSTEM'); + has_parameter_privilege +------------------------- + f +(1 row) + +REVOKE SET ON PARAMETER work_mem FROM regress_host_resource_admin; +SELECT has_parameter_privilege('regress_host_resource_admin', 'work_mem', 'SET'); + has_parameter_privilege +------------------------- + f +(1 row) + +SELECT has_parameter_privilege('regress_host_resource_admin', 'work_mem', 'ALTER SYSTEM'); + has_parameter_privilege +------------------------- + f +(1 row) + +GRANT SET ON PARAMETER work_mem TO regress_host_resource_admin; +SELECT has_parameter_privilege('regress_host_resource_admin', 'work_mem', 'SET'); + has_parameter_privilege +------------------------- + t +(1 row) + +SELECT has_parameter_privilege('regress_host_resource_admin', 'work_mem', 'ALTER SYSTEM'); + has_parameter_privilege +------------------------- + f +(1 row) + +REVOKE SET ON PARAMETER work_mem FROM regress_host_resource_admin; +SELECT has_parameter_privilege('regress_host_resource_admin', 'work_mem', 'SET'); + has_parameter_privilege +------------------------- + f +(1 row) + +SELECT has_parameter_privilege('regress_host_resource_admin', 'work_mem', 'ALTER SYSTEM'); + has_parameter_privilege +------------------------- + f +(1 row) + +-- Revoke privileges from a non-existent custom GUC. This should not create +-- entries in the catalog. +REVOKE ALL ON PARAMETER "none.such" FROM regress_host_resource_admin; +SELECT 1 FROM pg_parameter_acl WHERE parname = 'none.such'; + ?column? +---------- +(0 rows) + +-- Grant and then revoke privileges on the non-existent custom GUC. Check that +-- a do-nothing entry is not left in the catalogs after the revoke. +GRANT ALL ON PARAMETER none.such TO regress_host_resource_admin; +SELECT 1 FROM pg_parameter_acl WHERE parname = 'none.such'; + ?column? +---------- + 1 +(1 row) + +REVOKE ALL ON PARAMETER "None.Such" FROM regress_host_resource_admin; +SELECT 1 FROM pg_parameter_acl WHERE parname = 'none.such'; + ?column? +---------- +(0 rows) + +-- Can't grant on a non-existent core GUC. +GRANT ALL ON PARAMETER no_such_guc TO regress_host_resource_admin; -- fail +ERROR: invalid parameter name "no_such_guc" +-- Initially there are no privileges and no catalog entry for this GUC. +SELECT has_parameter_privilege('regress_host_resource_admin', 'enable_material', 'SET'); + has_parameter_privilege +------------------------- + f +(1 row) + +SELECT has_parameter_privilege('regress_host_resource_admin', 'enable_material', 'ALTER SYSTEM'); + has_parameter_privilege +------------------------- + f +(1 row) + +SELECT has_parameter_privilege('regress_host_resource_admin', 'enable_material', 'SET, ALTER SYSTEM'); + has_parameter_privilege +------------------------- + f +(1 row) + +SELECT 1 FROM pg_parameter_acl WHERE parname = 'enable_material'; + ?column? +---------- +(0 rows) + +-- GRANT SET creates an entry: +GRANT SET ON PARAMETER enable_material TO PUBLIC; +SELECT has_parameter_privilege('regress_host_resource_admin', 'enable_material', 'SET'); + has_parameter_privilege +------------------------- + t +(1 row) + +SELECT has_parameter_privilege('regress_host_resource_admin', 'enable_material', 'ALTER SYSTEM'); + has_parameter_privilege +------------------------- + f +(1 row) + +SELECT has_parameter_privilege('regress_host_resource_admin', 'enable_material', 'SET, ALTER SYSTEM'); + has_parameter_privilege +------------------------- + t +(1 row) + +SELECT 1 FROM pg_parameter_acl WHERE parname = 'enable_material'; + ?column? +---------- + 1 +(1 row) + +-- Now grant ALTER SYSTEM: +GRANT ALL ON PARAMETER enable_material TO PUBLIC; +SELECT has_parameter_privilege('regress_host_resource_admin', 'enable_material', 'SET'); + has_parameter_privilege +------------------------- + t +(1 row) + +SELECT has_parameter_privilege('regress_host_resource_admin', 'enable_material', 'ALTER SYSTEM'); + has_parameter_privilege +------------------------- + t +(1 row) + +SELECT has_parameter_privilege('regress_host_resource_admin', 'enable_material', 'SET, ALTER SYSTEM'); + has_parameter_privilege +------------------------- + t +(1 row) + +SELECT 1 FROM pg_parameter_acl WHERE parname = 'enable_material'; + ?column? +---------- + 1 +(1 row) + +-- REVOKE ALTER SYSTEM brings us back to just the SET privilege: +REVOKE ALTER SYSTEM ON PARAMETER enable_material FROM PUBLIC; +SELECT has_parameter_privilege('regress_host_resource_admin', 'enable_material', 'SET'); + has_parameter_privilege +------------------------- + t +(1 row) + +SELECT has_parameter_privilege('regress_host_resource_admin', 'enable_material', 'ALTER SYSTEM'); + has_parameter_privilege +------------------------- + f +(1 row) + +SELECT has_parameter_privilege('regress_host_resource_admin', 'enable_material', 'SET, ALTER SYSTEM'); + has_parameter_privilege +------------------------- + t +(1 row) + +SELECT 1 FROM pg_parameter_acl WHERE parname = 'enable_material'; + ?column? +---------- + 1 +(1 row) + +-- And this should remove the entry altogether: +REVOKE SET ON PARAMETER enable_material FROM PUBLIC; +SELECT 1 FROM pg_parameter_acl WHERE parname = 'enable_material'; + ?column? +---------- +(0 rows) + +-- Grant privileges on parameters to the new non-superuser role +GRANT SET, ALTER SYSTEM ON PARAMETER + autovacuum_work_mem, hash_mem_multiplier, max_stack_depth, + shared_buffers, temp_file_limit, work_mem +TO regress_host_resource_admin; +-- Check the new role now has privilges on parameters +SELECT has_parameter_privilege('regress_host_resource_admin', 'work_mem', 'SET, ALTER SYSTEM'); + has_parameter_privilege +------------------------- + t +(1 row) + +SELECT has_parameter_privilege('regress_host_resource_admin', 'work_mem', 'SET'); + has_parameter_privilege +------------------------- + t +(1 row) + +SELECT has_parameter_privilege('regress_host_resource_admin', 'work_mem', 'ALTER SYSTEM'); + has_parameter_privilege +------------------------- + t +(1 row) + +SELECT has_parameter_privilege('regress_host_resource_admin', 'work_mem', 'SET WITH GRANT OPTION, ALTER SYSTEM WITH GRANT OPTION'); + has_parameter_privilege +------------------------- + f +(1 row) + +-- Check again the inappropriate and nonsense privilege types. The prior +-- similar check was performed before any entry for work_mem existed. +SELECT has_parameter_privilege('regress_host_resource_admin', 'work_mem', 'SELECT, UPDATE, CREATE'); +ERROR: unrecognized privilege type: "SELECT" +SELECT has_parameter_privilege('regress_host_resource_admin', 'work_mem', 'USAGE'); +ERROR: unrecognized privilege type: "USAGE" +SELECT has_parameter_privilege('regress_host_resource_admin', 'work_mem', 'WHATEVER'); +ERROR: unrecognized privilege type: "WHATEVER" +SELECT has_parameter_privilege('regress_host_resource_admin', 'work_mem', 'WHATEVER WITH GRANT OPTION'); +ERROR: unrecognized privilege type: "WHATEVER WITH GRANT OPTION" +-- Check other function signatures +SELECT has_parameter_privilege((SELECT oid FROM pg_catalog.pg_authid WHERE rolname = 'regress_host_resource_admin'), + 'max_stack_depth', + 'SET'); + has_parameter_privilege +------------------------- + t +(1 row) + +SELECT has_parameter_privilege('hash_mem_multiplier', 'set'); + has_parameter_privilege +------------------------- + t +(1 row) + +-- Check object identity functions +SELECT pg_describe_object(tableoid, oid, 0) +FROM pg_parameter_acl WHERE parname = 'work_mem'; + pg_describe_object +-------------------- + parameter work_mem +(1 row) + +SELECT pg_identify_object(tableoid, oid, 0) +FROM pg_parameter_acl WHERE parname = 'work_mem'; + pg_identify_object +------------------------------ + ("parameter ACL",,,work_mem) +(1 row) + +SELECT pg_identify_object_as_address(tableoid, oid, 0) +FROM pg_parameter_acl WHERE parname = 'work_mem'; + pg_identify_object_as_address +--------------------------------- + ("parameter ACL",{work_mem},{}) +(1 row) + +SELECT classid::regclass, + (SELECT parname FROM pg_parameter_acl WHERE oid = goa.objid) AS parname, + objsubid +FROM pg_get_object_address('parameter ACL', '{work_mem}', '{}') goa; + classid | parname | objsubid +------------------+----------+---------- + pg_parameter_acl | work_mem | 0 +(1 row) + +-- Perform some operations as user 'regress_host_resource_admin' +SET SESSION AUTHORIZATION regress_host_resource_admin; +ALTER SYSTEM SET autovacuum_work_mem = 32; -- ok, privileges have been granted +ALTER SYSTEM SET ignore_system_indexes = OFF; -- fail, insufficient privileges +ERROR: permission denied to set parameter "ignore_system_indexes" +ALTER SYSTEM RESET autovacuum_multixact_freeze_max_age; -- fail, insufficient privileges +ERROR: permission denied to set parameter "autovacuum_multixact_freeze_max_age" +SET jit_provider = 'llvmjit'; -- fail, insufficient privileges +ERROR: parameter "jit_provider" cannot be changed without restarting the server +SELECT set_config ('jit_provider', 'llvmjit', true); -- fail, insufficient privileges +ERROR: parameter "jit_provider" cannot be changed without restarting the server +ALTER SYSTEM SET shared_buffers = 50; -- ok +ALTER SYSTEM RESET shared_buffers; -- ok +SET autovacuum_work_mem = 50; -- cannot be changed now +ERROR: parameter "autovacuum_work_mem" cannot be changed now +ALTER SYSTEM RESET temp_file_limit; -- ok +SET TimeZone = 'Europe/Helsinki'; -- ok +RESET TimeZone; -- ok +SET max_stack_depth = 2048; -- ok, privileges have been granted +RESET max_stack_depth; -- ok, privileges have been granted +ALTER SYSTEM SET max_stack_depth = 2048; -- ok, privileges have been granted +ALTER SYSTEM RESET max_stack_depth; -- ok, privileges have been granted +SET lc_messages = 'C'; -- fail, insufficient privileges +ERROR: permission denied to set parameter "lc_messages" +RESET lc_messages; -- fail, insufficient privileges +ERROR: permission denied to set parameter "lc_messages" +ALTER SYSTEM SET lc_messages = 'C'; -- fail, insufficient privileges +ERROR: permission denied to set parameter "lc_messages" +ALTER SYSTEM RESET lc_messages; -- fail, insufficient privileges +ERROR: permission denied to set parameter "lc_messages" +SELECT set_config ('temp_buffers', '8192', false); -- ok + set_config +------------ + 64MB +(1 row) + +ALTER SYSTEM RESET autovacuum_work_mem; -- ok, privileges have been granted +ALTER SYSTEM RESET ALL; -- fail, insufficient privileges +ERROR: permission denied to perform ALTER SYSTEM RESET ALL +-- Check dropping/revoking behavior +SET SESSION AUTHORIZATION regress_admin; +DROP ROLE regress_host_resource_admin; -- fail, privileges remain +ERROR: role "regress_host_resource_admin" cannot be dropped because some objects depend on it +DETAIL: privileges for parameter autovacuum_work_mem +privileges for parameter hash_mem_multiplier +privileges for parameter max_stack_depth +privileges for parameter shared_buffers +privileges for parameter temp_file_limit +privileges for parameter work_mem +-- Use "revoke" to remove the privileges and allow the role to be dropped +REVOKE SET, ALTER SYSTEM ON PARAMETER + autovacuum_work_mem, hash_mem_multiplier, max_stack_depth, + shared_buffers, temp_file_limit, work_mem +FROM regress_host_resource_admin; +DROP ROLE regress_host_resource_admin; -- ok +-- Try that again, but use "drop owned by" instead of "revoke" +CREATE ROLE regress_host_resource_admin NOSUPERUSER; +SET SESSION AUTHORIZATION regress_host_resource_admin; +ALTER SYSTEM SET autovacuum_work_mem = 32; -- fail, privileges not yet granted +ERROR: permission denied to set parameter "autovacuum_work_mem" +SET SESSION AUTHORIZATION regress_admin; +GRANT SET, ALTER SYSTEM ON PARAMETER + autovacuum_work_mem, hash_mem_multiplier, max_stack_depth, + shared_buffers, temp_file_limit, work_mem +TO regress_host_resource_admin; +DROP ROLE regress_host_resource_admin; -- fail, privileges remain +ERROR: role "regress_host_resource_admin" cannot be dropped because some objects depend on it +DETAIL: privileges for parameter autovacuum_work_mem +privileges for parameter hash_mem_multiplier +privileges for parameter max_stack_depth +privileges for parameter shared_buffers +privileges for parameter temp_file_limit +privileges for parameter work_mem +DROP OWNED BY regress_host_resource_admin RESTRICT; -- cascade should not be needed +SET SESSION AUTHORIZATION regress_host_resource_admin; +ALTER SYSTEM SET autovacuum_work_mem = 32; -- fail, "drop owned" has dropped privileges +ERROR: permission denied to set parameter "autovacuum_work_mem" +SET SESSION AUTHORIZATION regress_admin; +DROP ROLE regress_host_resource_admin; -- ok +-- Check that "reassign owned" doesn't affect privileges +CREATE ROLE regress_host_resource_admin NOSUPERUSER; +CREATE ROLE regress_host_resource_newadmin NOSUPERUSER; +GRANT SET, ALTER SYSTEM ON PARAMETER + autovacuum_work_mem, hash_mem_multiplier, max_stack_depth, + shared_buffers, temp_file_limit, work_mem +TO regress_host_resource_admin; +REASSIGN OWNED BY regress_host_resource_admin TO regress_host_resource_newadmin; +SET SESSION AUTHORIZATION regress_host_resource_admin; +ALTER SYSTEM SET autovacuum_work_mem = 32; -- ok, "reassign owned" did not change privileges +ALTER SYSTEM RESET autovacuum_work_mem; -- ok +SET SESSION AUTHORIZATION regress_admin; +DROP ROLE regress_host_resource_admin; -- fail, privileges remain +ERROR: role "regress_host_resource_admin" cannot be dropped because some objects depend on it +DETAIL: privileges for parameter autovacuum_work_mem +privileges for parameter hash_mem_multiplier +privileges for parameter max_stack_depth +privileges for parameter shared_buffers +privileges for parameter temp_file_limit +privileges for parameter work_mem +DROP ROLE regress_host_resource_newadmin; -- ok, nothing was transferred +-- Use "drop owned by" so we can drop the role +DROP OWNED BY regress_host_resource_admin; -- ok +DROP ROLE regress_host_resource_admin; -- ok +-- Clean up +RESET SESSION AUTHORIZATION; +DROP ROLE regress_admin; -- ok diff --git a/src/test/modules/unsafe_tests/sql/guc_privs.sql b/src/test/modules/unsafe_tests/sql/guc_privs.sql new file mode 100644 index 00000000000..12b22548f06 --- /dev/null +++ b/src/test/modules/unsafe_tests/sql/guc_privs.sql @@ -0,0 +1,237 @@ +-- +-- Tests for privileges on GUCs. +-- This is unsafe because changes will affect other databases in the cluster. +-- + +-- Test with a superuser role. +CREATE ROLE regress_admin SUPERUSER; + +-- Perform operations as user 'regress_admin'. +SET SESSION AUTHORIZATION regress_admin; + +-- PGC_BACKEND +SET ignore_system_indexes = OFF; -- fail, cannot be set after connection start +RESET ignore_system_indexes; -- fail, cannot be set after connection start +ALTER SYSTEM SET ignore_system_indexes = OFF; -- ok +ALTER SYSTEM RESET ignore_system_indexes; -- ok +-- PGC_INTERNAL +SET block_size = 50; -- fail, cannot be changed +RESET block_size; -- fail, cannot be changed +ALTER SYSTEM SET block_size = 50; -- fail, cannot be changed +ALTER SYSTEM RESET block_size; -- fail, cannot be changed +-- PGC_POSTMASTER +SET autovacuum_freeze_max_age = 1000050000; -- fail, requires restart +RESET autovacuum_freeze_max_age; -- fail, requires restart +ALTER SYSTEM SET autovacuum_freeze_max_age = 1000050000; -- ok +ALTER SYSTEM RESET autovacuum_freeze_max_age; -- ok +ALTER SYSTEM SET config_file = '/usr/local/data/postgresql.conf'; -- fail, cannot be changed +ALTER SYSTEM RESET config_file; -- fail, cannot be changed +-- PGC_SIGHUP +SET autovacuum = OFF; -- fail, requires reload +RESET autovacuum; -- fail, requires reload +ALTER SYSTEM SET autovacuum = OFF; -- ok +ALTER SYSTEM RESET autovacuum; -- ok +-- PGC_SUSET +SET lc_messages = 'C'; -- ok +RESET lc_messages; -- ok +ALTER SYSTEM SET lc_messages = 'C'; -- ok +ALTER SYSTEM RESET lc_messages; -- ok +-- PGC_SU_BACKEND +SET jit_debugging_support = OFF; -- fail, cannot be set after connection start +RESET jit_debugging_support; -- fail, cannot be set after connection start +ALTER SYSTEM SET jit_debugging_support = OFF; -- ok +ALTER SYSTEM RESET jit_debugging_support; -- ok +-- PGC_USERSET +SET DateStyle = 'ISO, MDY'; -- ok +RESET DateStyle; -- ok +ALTER SYSTEM SET DateStyle = 'ISO, MDY'; -- ok +ALTER SYSTEM RESET DateStyle; -- ok +ALTER SYSTEM SET ssl_renegotiation_limit = 0; -- fail, cannot be changed +ALTER SYSTEM RESET ssl_renegotiation_limit; -- fail, cannot be changed +-- Finished testing superuser + +-- Create non-superuser with privileges to configure host resource usage +CREATE ROLE regress_host_resource_admin NOSUPERUSER; +-- Revoke privileges not yet granted +REVOKE SET, ALTER SYSTEM ON PARAMETER work_mem FROM regress_host_resource_admin; +REVOKE SET, ALTER SYSTEM ON PARAMETER zero_damaged_pages FROM regress_host_resource_admin; +-- Check the new role does not yet have privileges on parameters +SELECT has_parameter_privilege('regress_host_resource_admin', 'work_mem', 'SET, ALTER SYSTEM'); +SELECT has_parameter_privilege('regress_host_resource_admin', 'work_mem', 'SET'); +SELECT has_parameter_privilege('regress_host_resource_admin', 'work_mem', 'ALTER SYSTEM'); +-- Check inappropriate and nonsense privilege types +SELECT has_parameter_privilege('regress_host_resource_admin', 'work_mem', 'SELECT, UPDATE, CREATE'); +SELECT has_parameter_privilege('regress_host_resource_admin', 'work_mem', 'USAGE'); +SELECT has_parameter_privilege('regress_host_resource_admin', 'work_mem', 'WHATEVER'); +-- Revoke, grant, and revoke again a SUSET parameter not yet granted +SELECT has_parameter_privilege('regress_host_resource_admin', 'zero_damaged_pages', 'SET'); +SELECT has_parameter_privilege('regress_host_resource_admin', 'zero_damaged_pages', 'ALTER SYSTEM'); +REVOKE SET ON PARAMETER zero_damaged_pages FROM regress_host_resource_admin; +SELECT has_parameter_privilege('regress_host_resource_admin', 'zero_damaged_pages', 'SET'); +SELECT has_parameter_privilege('regress_host_resource_admin', 'zero_damaged_pages', 'ALTER SYSTEM'); +GRANT SET ON PARAMETER zero_damaged_pages TO regress_host_resource_admin; +SELECT has_parameter_privilege('regress_host_resource_admin', 'zero_damaged_pages', 'SET'); +SELECT has_parameter_privilege('regress_host_resource_admin', 'zero_damaged_pages', 'ALTER SYSTEM'); +REVOKE SET ON PARAMETER zero_damaged_pages FROM regress_host_resource_admin; +SELECT has_parameter_privilege('regress_host_resource_admin', 'zero_damaged_pages', 'SET'); +SELECT has_parameter_privilege('regress_host_resource_admin', 'zero_damaged_pages', 'ALTER SYSTEM'); +-- Revoke, grant, and revoke again a USERSET parameter not yet granted +SELECT has_parameter_privilege('regress_host_resource_admin', 'work_mem', 'SET'); +SELECT has_parameter_privilege('regress_host_resource_admin', 'work_mem', 'ALTER SYSTEM'); +REVOKE SET ON PARAMETER work_mem FROM regress_host_resource_admin; +SELECT has_parameter_privilege('regress_host_resource_admin', 'work_mem', 'SET'); +SELECT has_parameter_privilege('regress_host_resource_admin', 'work_mem', 'ALTER SYSTEM'); +GRANT SET ON PARAMETER work_mem TO regress_host_resource_admin; +SELECT has_parameter_privilege('regress_host_resource_admin', 'work_mem', 'SET'); +SELECT has_parameter_privilege('regress_host_resource_admin', 'work_mem', 'ALTER SYSTEM'); +REVOKE SET ON PARAMETER work_mem FROM regress_host_resource_admin; +SELECT has_parameter_privilege('regress_host_resource_admin', 'work_mem', 'SET'); +SELECT has_parameter_privilege('regress_host_resource_admin', 'work_mem', 'ALTER SYSTEM'); + +-- Revoke privileges from a non-existent custom GUC. This should not create +-- entries in the catalog. +REVOKE ALL ON PARAMETER "none.such" FROM regress_host_resource_admin; +SELECT 1 FROM pg_parameter_acl WHERE parname = 'none.such'; +-- Grant and then revoke privileges on the non-existent custom GUC. Check that +-- a do-nothing entry is not left in the catalogs after the revoke. +GRANT ALL ON PARAMETER none.such TO regress_host_resource_admin; +SELECT 1 FROM pg_parameter_acl WHERE parname = 'none.such'; +REVOKE ALL ON PARAMETER "None.Such" FROM regress_host_resource_admin; +SELECT 1 FROM pg_parameter_acl WHERE parname = 'none.such'; +-- Can't grant on a non-existent core GUC. +GRANT ALL ON PARAMETER no_such_guc TO regress_host_resource_admin; -- fail + +-- Initially there are no privileges and no catalog entry for this GUC. +SELECT has_parameter_privilege('regress_host_resource_admin', 'enable_material', 'SET'); +SELECT has_parameter_privilege('regress_host_resource_admin', 'enable_material', 'ALTER SYSTEM'); +SELECT has_parameter_privilege('regress_host_resource_admin', 'enable_material', 'SET, ALTER SYSTEM'); +SELECT 1 FROM pg_parameter_acl WHERE parname = 'enable_material'; +-- GRANT SET creates an entry: +GRANT SET ON PARAMETER enable_material TO PUBLIC; +SELECT has_parameter_privilege('regress_host_resource_admin', 'enable_material', 'SET'); +SELECT has_parameter_privilege('regress_host_resource_admin', 'enable_material', 'ALTER SYSTEM'); +SELECT has_parameter_privilege('regress_host_resource_admin', 'enable_material', 'SET, ALTER SYSTEM'); +SELECT 1 FROM pg_parameter_acl WHERE parname = 'enable_material'; +-- Now grant ALTER SYSTEM: +GRANT ALL ON PARAMETER enable_material TO PUBLIC; +SELECT has_parameter_privilege('regress_host_resource_admin', 'enable_material', 'SET'); +SELECT has_parameter_privilege('regress_host_resource_admin', 'enable_material', 'ALTER SYSTEM'); +SELECT has_parameter_privilege('regress_host_resource_admin', 'enable_material', 'SET, ALTER SYSTEM'); +SELECT 1 FROM pg_parameter_acl WHERE parname = 'enable_material'; +-- REVOKE ALTER SYSTEM brings us back to just the SET privilege: +REVOKE ALTER SYSTEM ON PARAMETER enable_material FROM PUBLIC; +SELECT has_parameter_privilege('regress_host_resource_admin', 'enable_material', 'SET'); +SELECT has_parameter_privilege('regress_host_resource_admin', 'enable_material', 'ALTER SYSTEM'); +SELECT has_parameter_privilege('regress_host_resource_admin', 'enable_material', 'SET, ALTER SYSTEM'); +SELECT 1 FROM pg_parameter_acl WHERE parname = 'enable_material'; +-- And this should remove the entry altogether: +REVOKE SET ON PARAMETER enable_material FROM PUBLIC; +SELECT 1 FROM pg_parameter_acl WHERE parname = 'enable_material'; + +-- Grant privileges on parameters to the new non-superuser role +GRANT SET, ALTER SYSTEM ON PARAMETER + autovacuum_work_mem, hash_mem_multiplier, max_stack_depth, + shared_buffers, temp_file_limit, work_mem +TO regress_host_resource_admin; +-- Check the new role now has privilges on parameters +SELECT has_parameter_privilege('regress_host_resource_admin', 'work_mem', 'SET, ALTER SYSTEM'); +SELECT has_parameter_privilege('regress_host_resource_admin', 'work_mem', 'SET'); +SELECT has_parameter_privilege('regress_host_resource_admin', 'work_mem', 'ALTER SYSTEM'); +SELECT has_parameter_privilege('regress_host_resource_admin', 'work_mem', 'SET WITH GRANT OPTION, ALTER SYSTEM WITH GRANT OPTION'); +-- Check again the inappropriate and nonsense privilege types. The prior +-- similar check was performed before any entry for work_mem existed. +SELECT has_parameter_privilege('regress_host_resource_admin', 'work_mem', 'SELECT, UPDATE, CREATE'); +SELECT has_parameter_privilege('regress_host_resource_admin', 'work_mem', 'USAGE'); +SELECT has_parameter_privilege('regress_host_resource_admin', 'work_mem', 'WHATEVER'); +SELECT has_parameter_privilege('regress_host_resource_admin', 'work_mem', 'WHATEVER WITH GRANT OPTION'); + +-- Check other function signatures +SELECT has_parameter_privilege((SELECT oid FROM pg_catalog.pg_authid WHERE rolname = 'regress_host_resource_admin'), + 'max_stack_depth', + 'SET'); +SELECT has_parameter_privilege('hash_mem_multiplier', 'set'); + +-- Check object identity functions +SELECT pg_describe_object(tableoid, oid, 0) +FROM pg_parameter_acl WHERE parname = 'work_mem'; +SELECT pg_identify_object(tableoid, oid, 0) +FROM pg_parameter_acl WHERE parname = 'work_mem'; +SELECT pg_identify_object_as_address(tableoid, oid, 0) +FROM pg_parameter_acl WHERE parname = 'work_mem'; +SELECT classid::regclass, + (SELECT parname FROM pg_parameter_acl WHERE oid = goa.objid) AS parname, + objsubid +FROM pg_get_object_address('parameter ACL', '{work_mem}', '{}') goa; + +-- Perform some operations as user 'regress_host_resource_admin' +SET SESSION AUTHORIZATION regress_host_resource_admin; +ALTER SYSTEM SET autovacuum_work_mem = 32; -- ok, privileges have been granted +ALTER SYSTEM SET ignore_system_indexes = OFF; -- fail, insufficient privileges +ALTER SYSTEM RESET autovacuum_multixact_freeze_max_age; -- fail, insufficient privileges +SET jit_provider = 'llvmjit'; -- fail, insufficient privileges +SELECT set_config ('jit_provider', 'llvmjit', true); -- fail, insufficient privileges +ALTER SYSTEM SET shared_buffers = 50; -- ok +ALTER SYSTEM RESET shared_buffers; -- ok +SET autovacuum_work_mem = 50; -- cannot be changed now +ALTER SYSTEM RESET temp_file_limit; -- ok +SET TimeZone = 'Europe/Helsinki'; -- ok +RESET TimeZone; -- ok +SET max_stack_depth = 2048; -- ok, privileges have been granted +RESET max_stack_depth; -- ok, privileges have been granted +ALTER SYSTEM SET max_stack_depth = 2048; -- ok, privileges have been granted +ALTER SYSTEM RESET max_stack_depth; -- ok, privileges have been granted +SET lc_messages = 'C'; -- fail, insufficient privileges +RESET lc_messages; -- fail, insufficient privileges +ALTER SYSTEM SET lc_messages = 'C'; -- fail, insufficient privileges +ALTER SYSTEM RESET lc_messages; -- fail, insufficient privileges +SELECT set_config ('temp_buffers', '8192', false); -- ok +ALTER SYSTEM RESET autovacuum_work_mem; -- ok, privileges have been granted +ALTER SYSTEM RESET ALL; -- fail, insufficient privileges + +-- Check dropping/revoking behavior +SET SESSION AUTHORIZATION regress_admin; +DROP ROLE regress_host_resource_admin; -- fail, privileges remain +-- Use "revoke" to remove the privileges and allow the role to be dropped +REVOKE SET, ALTER SYSTEM ON PARAMETER + autovacuum_work_mem, hash_mem_multiplier, max_stack_depth, + shared_buffers, temp_file_limit, work_mem +FROM regress_host_resource_admin; +DROP ROLE regress_host_resource_admin; -- ok + +-- Try that again, but use "drop owned by" instead of "revoke" +CREATE ROLE regress_host_resource_admin NOSUPERUSER; +SET SESSION AUTHORIZATION regress_host_resource_admin; +ALTER SYSTEM SET autovacuum_work_mem = 32; -- fail, privileges not yet granted +SET SESSION AUTHORIZATION regress_admin; +GRANT SET, ALTER SYSTEM ON PARAMETER + autovacuum_work_mem, hash_mem_multiplier, max_stack_depth, + shared_buffers, temp_file_limit, work_mem +TO regress_host_resource_admin; +DROP ROLE regress_host_resource_admin; -- fail, privileges remain +DROP OWNED BY regress_host_resource_admin RESTRICT; -- cascade should not be needed +SET SESSION AUTHORIZATION regress_host_resource_admin; +ALTER SYSTEM SET autovacuum_work_mem = 32; -- fail, "drop owned" has dropped privileges +SET SESSION AUTHORIZATION regress_admin; +DROP ROLE regress_host_resource_admin; -- ok + +-- Check that "reassign owned" doesn't affect privileges +CREATE ROLE regress_host_resource_admin NOSUPERUSER; +CREATE ROLE regress_host_resource_newadmin NOSUPERUSER; +GRANT SET, ALTER SYSTEM ON PARAMETER + autovacuum_work_mem, hash_mem_multiplier, max_stack_depth, + shared_buffers, temp_file_limit, work_mem +TO regress_host_resource_admin; +REASSIGN OWNED BY regress_host_resource_admin TO regress_host_resource_newadmin; +SET SESSION AUTHORIZATION regress_host_resource_admin; +ALTER SYSTEM SET autovacuum_work_mem = 32; -- ok, "reassign owned" did not change privileges +ALTER SYSTEM RESET autovacuum_work_mem; -- ok +SET SESSION AUTHORIZATION regress_admin; +DROP ROLE regress_host_resource_admin; -- fail, privileges remain +DROP ROLE regress_host_resource_newadmin; -- ok, nothing was transferred +-- Use "drop owned by" so we can drop the role +DROP OWNED BY regress_host_resource_admin; -- ok +DROP ROLE regress_host_resource_admin; -- ok + +-- Clean up +RESET SESSION AUTHORIZATION; +DROP ROLE regress_admin; -- ok |