diff options
Diffstat (limited to 'src/test/modules/unsafe_tests')
| -rw-r--r-- | src/test/modules/unsafe_tests/.gitignore | 4 | ||||
| -rw-r--r-- | src/test/modules/unsafe_tests/Makefile | 17 | ||||
| -rw-r--r-- | src/test/modules/unsafe_tests/README | 8 | ||||
| -rw-r--r-- | src/test/modules/unsafe_tests/expected/alter_system_table.out | 179 | ||||
| -rw-r--r-- | src/test/modules/unsafe_tests/expected/guc_privs.out | 562 | ||||
| -rw-r--r-- | src/test/modules/unsafe_tests/expected/rolenames.out | 1091 | ||||
| -rw-r--r-- | src/test/modules/unsafe_tests/sql/alter_system_table.sql | 194 | ||||
| -rw-r--r-- | src/test/modules/unsafe_tests/sql/guc_privs.sql | 253 | ||||
| -rw-r--r-- | src/test/modules/unsafe_tests/sql/rolenames.sql | 504 |
9 files changed, 2812 insertions, 0 deletions
diff --git a/src/test/modules/unsafe_tests/.gitignore b/src/test/modules/unsafe_tests/.gitignore new file mode 100644 index 0000000..5dcb3ff --- /dev/null +++ b/src/test/modules/unsafe_tests/.gitignore @@ -0,0 +1,4 @@ +# Generated subdirectories +/log/ +/results/ +/tmp_check/ diff --git a/src/test/modules/unsafe_tests/Makefile b/src/test/modules/unsafe_tests/Makefile new file mode 100644 index 0000000..90d1979 --- /dev/null +++ b/src/test/modules/unsafe_tests/Makefile @@ -0,0 +1,17 @@ +# src/test/modules/unsafe_tests/Makefile + +REGRESS = rolenames alter_system_table guc_privs + +# the whole point of these tests is to not run installcheck +NO_INSTALLCHECK = 1 + +ifdef USE_PGXS +PG_CONFIG = pg_config +PGXS := $(shell $(PG_CONFIG) --pgxs) +include $(PGXS) +else +subdir = src/test/modules/unsafe_tests +top_builddir = ../../../.. +include $(top_builddir)/src/Makefile.global +include $(top_srcdir)/contrib/contrib-global.mk +endif diff --git a/src/test/modules/unsafe_tests/README b/src/test/modules/unsafe_tests/README new file mode 100644 index 0000000..d9dbd03 --- /dev/null +++ b/src/test/modules/unsafe_tests/README @@ -0,0 +1,8 @@ +This directory doesn't actually contain any extension module. + +Instead it is a home for regression tests that we don't want to run +during "make installcheck" because they could have side-effects that +seem undesirable for a production installation. + +An example is that rolenames.sql tests ALTER USER ALL and so could +have effects on pre-existing roles. diff --git a/src/test/modules/unsafe_tests/expected/alter_system_table.out b/src/test/modules/unsafe_tests/expected/alter_system_table.out new file mode 100644 index 0000000..be05595 --- /dev/null +++ b/src/test/modules/unsafe_tests/expected/alter_system_table.out @@ -0,0 +1,179 @@ +-- +-- Tests for things affected by allow_system_table_mods +-- +-- We run the same set of commands once with allow_system_table_mods +-- off and then again with on. +-- +-- The "on" tests should where possible be wrapped in BEGIN/ROLLBACK +-- blocks so as to not leave a mess around. +CREATE USER regress_user_ast; +SET allow_system_table_mods = off; +-- create new table in pg_catalog +CREATE TABLE pg_catalog.test (a int); +ERROR: permission denied to create "pg_catalog.test" +DETAIL: System catalog modifications are currently disallowed. +-- anyarray column +CREATE TABLE t1x (a int, b anyarray); +ERROR: column "b" has pseudo-type anyarray +-- index on system catalog +ALTER TABLE pg_namespace ADD CONSTRAINT foo UNIQUE USING INDEX pg_namespace_nspname_index; +ERROR: permission denied: "pg_namespace" is a system catalog +-- write to system catalog table as superuser +-- (allowed even without allow_system_table_mods) +INSERT INTO pg_description (objoid, classoid, objsubid, description) VALUES (0, 0, 0, 'foo'); +-- write to system catalog table as normal user +GRANT INSERT ON pg_description TO regress_user_ast; +SET ROLE regress_user_ast; +INSERT INTO pg_description (objoid, classoid, objsubid, description) VALUES (0, 0, 1, 'foo'); +ERROR: permission denied for table pg_description +RESET ROLE; +-- policy on system catalog +CREATE POLICY foo ON pg_description FOR SELECT USING (description NOT LIKE 'secret%'); +ERROR: permission denied: "pg_description" is a system catalog +-- reserved schema name +CREATE SCHEMA pg_foo; +ERROR: unacceptable schema name "pg_foo" +DETAIL: The prefix "pg_" is reserved for system schemas. +-- drop system table +DROP TABLE pg_description; +ERROR: permission denied: "pg_description" is a system catalog +-- truncate of system table +TRUNCATE pg_description; +ERROR: permission denied: "pg_description" is a system catalog +-- rename column of system table +ALTER TABLE pg_description RENAME COLUMN description TO comment; +ERROR: permission denied: "pg_description" is a system catalog +-- ATSimplePermissions() +ALTER TABLE pg_description ALTER COLUMN description SET NOT NULL; +ERROR: permission denied: "pg_description" is a system catalog +-- SET STATISTICS +ALTER TABLE pg_description ALTER COLUMN description SET STATISTICS -1; +ERROR: permission denied: "pg_description" is a system catalog +-- foreign key referencing catalog +CREATE TABLE foo (a oid, b oid, c int, FOREIGN KEY (a, b, c) REFERENCES pg_description); +ERROR: permission denied: "pg_description" is a system catalog +-- RangeVarCallbackOwnsRelation() +CREATE INDEX pg_description_test_index ON pg_description (description); +ERROR: permission denied: "pg_description" is a system catalog +-- RangeVarCallbackForAlterRelation() +ALTER TABLE pg_description RENAME TO pg_comment; +ERROR: permission denied: "pg_description" is a system catalog +ALTER TABLE pg_description SET SCHEMA public; +ERROR: permission denied: "pg_description" is a system catalog +-- reserved tablespace name +CREATE TABLESPACE pg_foo LOCATION '/no/such/location'; +ERROR: unacceptable tablespace name "pg_foo" +DETAIL: The prefix "pg_" is reserved for system tablespaces. +-- triggers +CREATE FUNCTION tf1() RETURNS trigger +LANGUAGE plpgsql +AS $$ +BEGIN + RETURN NULL; +END $$; +CREATE TRIGGER t1 BEFORE INSERT ON pg_description EXECUTE FUNCTION tf1(); +ERROR: permission denied: "pg_description" is a system catalog +ALTER TRIGGER t1 ON pg_description RENAME TO t2; +ERROR: permission denied: "pg_description" is a system catalog +--DROP TRIGGER t2 ON pg_description; +-- rules +CREATE RULE r1 AS ON INSERT TO pg_description DO INSTEAD NOTHING; +ERROR: permission denied: "pg_description" is a system catalog +ALTER RULE r1 ON pg_description RENAME TO r2; +ERROR: permission denied: "pg_description" is a system catalog +-- now make one to test dropping: +SET allow_system_table_mods TO on; +CREATE RULE r2 AS ON INSERT TO pg_description DO INSTEAD NOTHING; +RESET allow_system_table_mods; +DROP RULE r2 ON pg_description; +ERROR: permission denied: "pg_description" is a system catalog +-- cleanup: +SET allow_system_table_mods TO on; +DROP RULE r2 ON pg_description; +RESET allow_system_table_mods; +SET allow_system_table_mods = on; +-- create new table in pg_catalog +BEGIN; +CREATE TABLE pg_catalog.test (a int); +ROLLBACK; +-- anyarray column +BEGIN; +CREATE TABLE t1 (a int, b anyarray); +ROLLBACK; +-- index on system catalog +BEGIN; +ALTER TABLE pg_namespace ADD CONSTRAINT foo UNIQUE USING INDEX pg_namespace_nspname_index; +NOTICE: ALTER TABLE / ADD CONSTRAINT USING INDEX will rename index "pg_namespace_nspname_index" to "foo" +ROLLBACK; +-- write to system catalog table as superuser +BEGIN; +INSERT INTO pg_description (objoid, classoid, objsubid, description) VALUES (0, 0, 2, 'foo'); +ROLLBACK; +-- write to system catalog table as normal user +-- (not allowed) +SET ROLE regress_user_ast; +INSERT INTO pg_description (objoid, classoid, objsubid, description) VALUES (0, 0, 3, 'foo'); +ERROR: permission denied for table pg_description +RESET ROLE; +-- policy on system catalog +BEGIN; +CREATE POLICY foo ON pg_description FOR SELECT USING (description NOT LIKE 'secret%'); +ROLLBACK; +-- reserved schema name +BEGIN; +CREATE SCHEMA pg_foo; +ROLLBACK; +-- drop system table +-- (This will fail anyway because it's pinned.) +BEGIN; +DROP TABLE pg_description; +ERROR: cannot drop table pg_description because it is required by the database system +ROLLBACK; +-- truncate of system table +BEGIN; +TRUNCATE pg_description; +ROLLBACK; +-- rename column of system table +BEGIN; +ALTER TABLE pg_description RENAME COLUMN description TO comment; +ROLLBACK; +-- ATSimplePermissions() +BEGIN; +ALTER TABLE pg_description ALTER COLUMN description SET NOT NULL; +ROLLBACK; +-- SET STATISTICS +BEGIN; +ALTER TABLE pg_description ALTER COLUMN description SET STATISTICS -1; +ROLLBACK; +-- foreign key referencing catalog +BEGIN; +CREATE TABLE foo (a oid, b oid, c int, FOREIGN KEY (a, b, c) REFERENCES pg_description); +ROLLBACK; +-- RangeVarCallbackOwnsRelation() +BEGIN; +CREATE INDEX pg_description_test_index ON pg_description (description); +ROLLBACK; +-- RangeVarCallbackForAlterRelation() +BEGIN; +ALTER TABLE pg_description RENAME TO pg_comment; +ROLLBACK; +BEGIN; +ALTER TABLE pg_description SET SCHEMA public; +ROLLBACK; +-- reserved tablespace name +SET client_min_messages = error; -- disable ENFORCE_REGRESSION_TEST_NAME_RESTRICTIONS warning +CREATE TABLESPACE pg_foo LOCATION '/no/such/location'; +ERROR: directory "/no/such/location" does not exist +RESET client_min_messages; +-- triggers +CREATE TRIGGER t1 BEFORE INSERT ON pg_description EXECUTE FUNCTION tf1(); +ALTER TRIGGER t1 ON pg_description RENAME TO t2; +DROP TRIGGER t2 ON pg_description; +-- rules +CREATE RULE r1 AS ON INSERT TO pg_description DO INSTEAD NOTHING; +ALTER RULE r1 ON pg_description RENAME TO r2; +DROP RULE r2 ON pg_description; +-- cleanup +REVOKE ALL ON pg_description FROM regress_user_ast; +DROP USER regress_user_ast; +DROP FUNCTION tf1; 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 0000000..54f95b2 --- /dev/null +++ b/src/test/modules/unsafe_tests/expected/guc_privs.out @@ -0,0 +1,562 @@ +-- +-- 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) + +-- Make a per-role setting that regress_host_resource_admin can't change +ALTER ROLE regress_host_resource_admin SET lc_messages = 'C'; +-- 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 = '100kB'; -- ok, privileges have been granted +RESET max_stack_depth; -- ok, privileges have been granted +ALTER SYSTEM SET max_stack_depth = '100kB'; -- 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 +ALTER ROLE regress_host_resource_admin SET lc_messages = 'POSIX'; -- fail +ERROR: permission denied to set parameter "lc_messages" +ALTER ROLE regress_host_resource_admin SET max_stack_depth = '1MB'; -- ok +SELECT setconfig FROM pg_db_role_setting + WHERE setrole = 'regress_host_resource_admin'::regrole; + setconfig +------------------------------------- + {lc_messages=C,max_stack_depth=1MB} +(1 row) + +ALTER ROLE regress_host_resource_admin RESET max_stack_depth; -- ok +SELECT setconfig FROM pg_db_role_setting + WHERE setrole = 'regress_host_resource_admin'::regrole; + setconfig +----------------- + {lc_messages=C} +(1 row) + +ALTER ROLE regress_host_resource_admin SET max_stack_depth = '1MB'; -- ok +SELECT setconfig FROM pg_db_role_setting + WHERE setrole = 'regress_host_resource_admin'::regrole; + setconfig +------------------------------------- + {lc_messages=C,max_stack_depth=1MB} +(1 row) + +ALTER ROLE regress_host_resource_admin RESET ALL; -- doesn't reset lc_messages +SELECT setconfig FROM pg_db_role_setting + WHERE setrole = 'regress_host_resource_admin'::regrole; + setconfig +----------------- + {lc_messages=C} +(1 row) + +-- 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/expected/rolenames.out b/src/test/modules/unsafe_tests/expected/rolenames.out new file mode 100644 index 0000000..88b1ff8 --- /dev/null +++ b/src/test/modules/unsafe_tests/expected/rolenames.out @@ -0,0 +1,1091 @@ +CREATE FUNCTION chkrolattr() + RETURNS TABLE ("role" name, rolekeyword text, canlogin bool, replication bool) + AS $$ +SELECT r.rolname, v.keyword, r.rolcanlogin, r.rolreplication + FROM pg_roles r + JOIN (VALUES(CURRENT_ROLE, 'current_role'), + (CURRENT_USER, 'current_user'), + (SESSION_USER, 'session_user'), + ('current_role', '-'), + ('current_user', '-'), + ('session_user', '-'), + ('Public', '-'), + ('None', '-')) + AS v(uname, keyword) + ON (r.rolname = v.uname) + ORDER BY 1, 2; +$$ LANGUAGE SQL; +CREATE FUNCTION chksetconfig() + RETURNS TABLE (db name, "role" name, rolkeyword text, setconfig text[]) + AS $$ +SELECT COALESCE(d.datname, 'ALL'), COALESCE(r.rolname, 'ALL'), + COALESCE(v.keyword, '-'), s.setconfig + FROM pg_db_role_setting s + LEFT JOIN pg_roles r ON (r.oid = s.setrole) + LEFT JOIN pg_database d ON (d.oid = s.setdatabase) + LEFT JOIN (VALUES(CURRENT_ROLE, 'current_role'), + (CURRENT_USER, 'current_user'), + (SESSION_USER, 'session_user')) + AS v(uname, keyword) + ON (r.rolname = v.uname) + WHERE (r.rolname) IN ('Public', 'current_user', 'regress_testrol1', 'regress_testrol2') +ORDER BY 1, 2, 3; +$$ LANGUAGE SQL; +CREATE FUNCTION chkumapping() + RETURNS TABLE (umname name, umserver name, umoptions text[]) + AS $$ +SELECT r.rolname, s.srvname, m.umoptions + FROM pg_user_mapping m + LEFT JOIN pg_roles r ON (r.oid = m.umuser) + JOIN pg_foreign_server s ON (s.oid = m.umserver) + ORDER BY 2, 1; +$$ LANGUAGE SQL; +-- +-- We test creation and use of these role names to ensure that the server +-- correctly distinguishes role keywords from quoted names that look like +-- those keywords. In a test environment, creation of these roles may +-- provoke warnings, so hide the warnings by raising client_min_messages. +-- +SET client_min_messages = ERROR; +CREATE ROLE "Public"; +CREATE ROLE "None"; +CREATE ROLE "current_role"; +CREATE ROLE "current_user"; +CREATE ROLE "session_user"; +CREATE ROLE "user"; +RESET client_min_messages; +CREATE ROLE current_user; -- error +ERROR: CURRENT_USER cannot be used as a role name here +LINE 1: CREATE ROLE current_user; + ^ +CREATE ROLE current_role; -- error +ERROR: CURRENT_ROLE cannot be used as a role name here +LINE 1: CREATE ROLE current_role; + ^ +CREATE ROLE session_user; -- error +ERROR: SESSION_USER cannot be used as a role name here +LINE 1: CREATE ROLE session_user; + ^ +CREATE ROLE user; -- error +ERROR: syntax error at or near "user" +LINE 1: CREATE ROLE user; + ^ +CREATE ROLE all; -- error +ERROR: syntax error at or near "all" +LINE 1: CREATE ROLE all; + ^ +CREATE ROLE public; -- error +ERROR: role name "public" is reserved +LINE 1: CREATE ROLE public; + ^ +CREATE ROLE "public"; -- error +ERROR: role name "public" is reserved +LINE 1: CREATE ROLE "public"; + ^ +CREATE ROLE none; -- error +ERROR: role name "none" is reserved +LINE 1: CREATE ROLE none; + ^ +CREATE ROLE "none"; -- error +ERROR: role name "none" is reserved +LINE 1: CREATE ROLE "none"; + ^ +CREATE ROLE pg_abc; -- error +ERROR: role name "pg_abc" is reserved +DETAIL: Role names starting with "pg_" are reserved. +CREATE ROLE "pg_abc"; -- error +ERROR: role name "pg_abc" is reserved +DETAIL: Role names starting with "pg_" are reserved. +CREATE ROLE pg_abcdef; -- error +ERROR: role name "pg_abcdef" is reserved +DETAIL: Role names starting with "pg_" are reserved. +CREATE ROLE "pg_abcdef"; -- error +ERROR: role name "pg_abcdef" is reserved +DETAIL: Role names starting with "pg_" are reserved. +CREATE ROLE regress_testrol0 SUPERUSER LOGIN; +CREATE ROLE regress_testrolx SUPERUSER LOGIN; +CREATE ROLE regress_testrol2 SUPERUSER; +CREATE ROLE regress_testrol1 SUPERUSER LOGIN IN ROLE regress_testrol2; +\c - +SET SESSION AUTHORIZATION regress_testrol1; +SET ROLE regress_testrol2; +-- ALTER ROLE +BEGIN; +SELECT * FROM chkrolattr(); + role | rolekeyword | canlogin | replication +------------------+--------------+----------+------------- + None | - | f | f + Public | - | f | f + current_role | - | f | f + current_user | - | f | f + regress_testrol1 | session_user | t | f + regress_testrol2 | current_role | f | f + regress_testrol2 | current_user | f | f + session_user | - | f | f +(8 rows) + +ALTER ROLE CURRENT_ROLE WITH REPLICATION; +SELECT * FROM chkrolattr(); + role | rolekeyword | canlogin | replication +------------------+--------------+----------+------------- + None | - | f | f + Public | - | f | f + current_role | - | f | f + current_user | - | f | f + regress_testrol1 | session_user | t | f + regress_testrol2 | current_role | f | t + regress_testrol2 | current_user | f | t + session_user | - | f | f +(8 rows) + +ALTER ROLE "current_role" WITH REPLICATION; +SELECT * FROM chkrolattr(); + role | rolekeyword | canlogin | replication +------------------+--------------+----------+------------- + None | - | f | f + Public | - | f | f + current_role | - | f | t + current_user | - | f | f + regress_testrol1 | session_user | t | f + regress_testrol2 | current_role | f | t + regress_testrol2 | current_user | f | t + session_user | - | f | f +(8 rows) + +ALTER ROLE CURRENT_ROLE WITH NOREPLICATION; +ALTER ROLE CURRENT_USER WITH REPLICATION; +SELECT * FROM chkrolattr(); + role | rolekeyword | canlogin | replication +------------------+--------------+----------+------------- + None | - | f | f + Public | - | f | f + current_role | - | f | t + current_user | - | f | f + regress_testrol1 | session_user | t | f + regress_testrol2 | current_role | f | t + regress_testrol2 | current_user | f | t + session_user | - | f | f +(8 rows) + +ALTER ROLE "current_user" WITH REPLICATION; +SELECT * FROM chkrolattr(); + role | rolekeyword | canlogin | replication +------------------+--------------+----------+------------- + None | - | f | f + Public | - | f | f + current_role | - | f | t + current_user | - | f | t + regress_testrol1 | session_user | t | f + regress_testrol2 | current_role | f | t + regress_testrol2 | current_user | f | t + session_user | - | f | f +(8 rows) + +ALTER ROLE SESSION_USER WITH REPLICATION; +SELECT * FROM chkrolattr(); + role | rolekeyword | canlogin | replication +------------------+--------------+----------+------------- + None | - | f | f + Public | - | f | f + current_role | - | f | t + current_user | - | f | t + regress_testrol1 | session_user | t | t + regress_testrol2 | current_role | f | t + regress_testrol2 | current_user | f | t + session_user | - | f | f +(8 rows) + +ALTER ROLE "session_user" WITH REPLICATION; +SELECT * FROM chkrolattr(); + role | rolekeyword | canlogin | replication +------------------+--------------+----------+------------- + None | - | f | f + Public | - | f | f + current_role | - | f | t + current_user | - | f | t + regress_testrol1 | session_user | t | t + regress_testrol2 | current_role | f | t + regress_testrol2 | current_user | f | t + session_user | - | f | t +(8 rows) + +ALTER USER "Public" WITH REPLICATION; +ALTER USER "None" WITH REPLICATION; +SELECT * FROM chkrolattr(); + role | rolekeyword | canlogin | replication +------------------+--------------+----------+------------- + None | - | f | t + Public | - | f | t + current_role | - | f | t + current_user | - | f | t + regress_testrol1 | session_user | t | t + regress_testrol2 | current_role | f | t + regress_testrol2 | current_user | f | t + session_user | - | f | t +(8 rows) + +ALTER USER regress_testrol1 WITH NOREPLICATION; +ALTER USER regress_testrol2 WITH NOREPLICATION; +SELECT * FROM chkrolattr(); + role | rolekeyword | canlogin | replication +------------------+--------------+----------+------------- + None | - | f | t + Public | - | f | t + current_role | - | f | t + current_user | - | f | t + regress_testrol1 | session_user | t | f + regress_testrol2 | current_role | f | f + regress_testrol2 | current_user | f | f + session_user | - | f | t +(8 rows) + +ROLLBACK; +ALTER ROLE USER WITH LOGIN; -- error +ERROR: syntax error at or near "USER" +LINE 1: ALTER ROLE USER WITH LOGIN; + ^ +ALTER ROLE ALL WITH REPLICATION; -- error +ERROR: syntax error at or near "WITH" +LINE 1: ALTER ROLE ALL WITH REPLICATION; + ^ +ALTER ROLE SESSION_ROLE WITH NOREPLICATION; -- error +ERROR: role "session_role" does not exist +ALTER ROLE PUBLIC WITH NOREPLICATION; -- error +ERROR: role "public" does not exist +ALTER ROLE "public" WITH NOREPLICATION; -- error +ERROR: role "public" does not exist +ALTER ROLE NONE WITH NOREPLICATION; -- error +ERROR: role name "none" is reserved +LINE 1: ALTER ROLE NONE WITH NOREPLICATION; + ^ +ALTER ROLE "none" WITH NOREPLICATION; -- error +ERROR: role name "none" is reserved +LINE 1: ALTER ROLE "none" WITH NOREPLICATION; + ^ +ALTER ROLE nonexistent WITH NOREPLICATION; -- error +ERROR: role "nonexistent" does not exist +-- ALTER USER +BEGIN; +SELECT * FROM chkrolattr(); + role | rolekeyword | canlogin | replication +------------------+--------------+----------+------------- + None | - | f | f + Public | - | f | f + current_role | - | f | f + current_user | - | f | f + regress_testrol1 | session_user | t | f + regress_testrol2 | current_role | f | f + regress_testrol2 | current_user | f | f + session_user | - | f | f +(8 rows) + +ALTER USER CURRENT_ROLE WITH REPLICATION; +SELECT * FROM chkrolattr(); + role | rolekeyword | canlogin | replication +------------------+--------------+----------+------------- + None | - | f | f + Public | - | f | f + current_role | - | f | f + current_user | - | f | f + regress_testrol1 | session_user | t | f + regress_testrol2 | current_role | f | t + regress_testrol2 | current_user | f | t + session_user | - | f | f +(8 rows) + +ALTER USER "current_role" WITH REPLICATION; +SELECT * FROM chkrolattr(); + role | rolekeyword | canlogin | replication +------------------+--------------+----------+------------- + None | - | f | f + Public | - | f | f + current_role | - | f | t + current_user | - | f | f + regress_testrol1 | session_user | t | f + regress_testrol2 | current_role | f | t + regress_testrol2 | current_user | f | t + session_user | - | f | f +(8 rows) + +ALTER USER CURRENT_ROLE WITH NOREPLICATION; +ALTER USER CURRENT_USER WITH REPLICATION; +SELECT * FROM chkrolattr(); + role | rolekeyword | canlogin | replication +------------------+--------------+----------+------------- + None | - | f | f + Public | - | f | f + current_role | - | f | t + current_user | - | f | f + regress_testrol1 | session_user | t | f + regress_testrol2 | current_role | f | t + regress_testrol2 | current_user | f | t + session_user | - | f | f +(8 rows) + +ALTER USER "current_user" WITH REPLICATION; +SELECT * FROM chkrolattr(); + role | rolekeyword | canlogin | replication +------------------+--------------+----------+------------- + None | - | f | f + Public | - | f | f + current_role | - | f | t + current_user | - | f | t + regress_testrol1 | session_user | t | f + regress_testrol2 | current_role | f | t + regress_testrol2 | current_user | f | t + session_user | - | f | f +(8 rows) + +ALTER USER SESSION_USER WITH REPLICATION; +SELECT * FROM chkrolattr(); + role | rolekeyword | canlogin | replication +------------------+--------------+----------+------------- + None | - | f | f + Public | - | f | f + current_role | - | f | t + current_user | - | f | t + regress_testrol1 | session_user | t | t + regress_testrol2 | current_role | f | t + regress_testrol2 | current_user | f | t + session_user | - | f | f +(8 rows) + +ALTER USER "session_user" WITH REPLICATION; +SELECT * FROM chkrolattr(); + role | rolekeyword | canlogin | replication +------------------+--------------+----------+------------- + None | - | f | f + Public | - | f | f + current_role | - | f | t + current_user | - | f | t + regress_testrol1 | session_user | t | t + regress_testrol2 | current_role | f | t + regress_testrol2 | current_user | f | t + session_user | - | f | t +(8 rows) + +ALTER USER "Public" WITH REPLICATION; +ALTER USER "None" WITH REPLICATION; +SELECT * FROM chkrolattr(); + role | rolekeyword | canlogin | replication +------------------+--------------+----------+------------- + None | - | f | t + Public | - | f | t + current_role | - | f | t + current_user | - | f | t + regress_testrol1 | session_user | t | t + regress_testrol2 | current_role | f | t + regress_testrol2 | current_user | f | t + session_user | - | f | t +(8 rows) + +ALTER USER regress_testrol1 WITH NOREPLICATION; +ALTER USER regress_testrol2 WITH NOREPLICATION; +SELECT * FROM chkrolattr(); + role | rolekeyword | canlogin | replication +------------------+--------------+----------+------------- + None | - | f | t + Public | - | f | t + current_role | - | f | t + current_user | - | f | t + regress_testrol1 | session_user | t | f + regress_testrol2 | current_role | f | f + regress_testrol2 | current_user | f | f + session_user | - | f | t +(8 rows) + +ROLLBACK; +ALTER USER USER WITH LOGIN; -- error +ERROR: syntax error at or near "USER" +LINE 1: ALTER USER USER WITH LOGIN; + ^ +ALTER USER ALL WITH REPLICATION; -- error +ERROR: syntax error at or near "WITH" +LINE 1: ALTER USER ALL WITH REPLICATION; + ^ +ALTER USER SESSION_ROLE WITH NOREPLICATION; -- error +ERROR: role "session_role" does not exist +ALTER USER PUBLIC WITH NOREPLICATION; -- error +ERROR: role "public" does not exist +ALTER USER "public" WITH NOREPLICATION; -- error +ERROR: role "public" does not exist +ALTER USER NONE WITH NOREPLICATION; -- error +ERROR: role name "none" is reserved +LINE 1: ALTER USER NONE WITH NOREPLICATION; + ^ +ALTER USER "none" WITH NOREPLICATION; -- error +ERROR: role name "none" is reserved +LINE 1: ALTER USER "none" WITH NOREPLICATION; + ^ +ALTER USER nonexistent WITH NOREPLICATION; -- error +ERROR: role "nonexistent" does not exist +-- ALTER ROLE SET/RESET +SELECT * FROM chksetconfig(); + db | role | rolkeyword | setconfig +----+------+------------+----------- +(0 rows) + +ALTER ROLE CURRENT_ROLE SET application_name to 'BAZ'; +ALTER ROLE CURRENT_USER SET application_name to 'FOO'; +ALTER ROLE SESSION_USER SET application_name to 'BAR'; +ALTER ROLE "current_user" SET application_name to 'FOOFOO'; +ALTER ROLE "Public" SET application_name to 'BARBAR'; +ALTER ROLE ALL SET application_name to 'SLAP'; +SELECT * FROM chksetconfig(); + db | role | rolkeyword | setconfig +-----+------------------+--------------+--------------------------- + ALL | Public | - | {application_name=BARBAR} + ALL | current_user | - | {application_name=FOOFOO} + ALL | regress_testrol1 | session_user | {application_name=BAR} + ALL | regress_testrol2 | current_role | {application_name=FOO} + ALL | regress_testrol2 | current_user | {application_name=FOO} +(5 rows) + +ALTER ROLE regress_testrol1 SET application_name to 'SLAM'; +SELECT * FROM chksetconfig(); + db | role | rolkeyword | setconfig +-----+------------------+--------------+--------------------------- + ALL | Public | - | {application_name=BARBAR} + ALL | current_user | - | {application_name=FOOFOO} + ALL | regress_testrol1 | session_user | {application_name=SLAM} + ALL | regress_testrol2 | current_role | {application_name=FOO} + ALL | regress_testrol2 | current_user | {application_name=FOO} +(5 rows) + +ALTER ROLE CURRENT_ROLE RESET application_name; +ALTER ROLE CURRENT_USER RESET application_name; +ALTER ROLE SESSION_USER RESET application_name; +ALTER ROLE "current_user" RESET application_name; +ALTER ROLE "Public" RESET application_name; +ALTER ROLE ALL RESET application_name; +SELECT * FROM chksetconfig(); + db | role | rolkeyword | setconfig +----+------+------------+----------- +(0 rows) + +ALTER ROLE USER SET application_name to 'BOOM'; -- error +ERROR: syntax error at or near "USER" +LINE 1: ALTER ROLE USER SET application_name to 'BOOM'; + ^ +ALTER ROLE PUBLIC SET application_name to 'BOMB'; -- error +ERROR: role "public" does not exist +ALTER ROLE nonexistent SET application_name to 'BOMB'; -- error +ERROR: role "nonexistent" does not exist +-- ALTER USER SET/RESET +SELECT * FROM chksetconfig(); + db | role | rolkeyword | setconfig +----+------+------------+----------- +(0 rows) + +ALTER USER CURRENT_ROLE SET application_name to 'BAZ'; +ALTER USER CURRENT_USER SET application_name to 'FOO'; +ALTER USER SESSION_USER SET application_name to 'BAR'; +ALTER USER "current_user" SET application_name to 'FOOFOO'; +ALTER USER "Public" SET application_name to 'BARBAR'; +ALTER USER ALL SET application_name to 'SLAP'; +SELECT * FROM chksetconfig(); + db | role | rolkeyword | setconfig +-----+------------------+--------------+--------------------------- + ALL | Public | - | {application_name=BARBAR} + ALL | current_user | - | {application_name=FOOFOO} + ALL | regress_testrol1 | session_user | {application_name=BAR} + ALL | regress_testrol2 | current_role | {application_name=FOO} + ALL | regress_testrol2 | current_user | {application_name=FOO} +(5 rows) + +ALTER USER regress_testrol1 SET application_name to 'SLAM'; +SELECT * FROM chksetconfig(); + db | role | rolkeyword | setconfig +-----+------------------+--------------+--------------------------- + ALL | Public | - | {application_name=BARBAR} + ALL | current_user | - | {application_name=FOOFOO} + ALL | regress_testrol1 | session_user | {application_name=SLAM} + ALL | regress_testrol2 | current_role | {application_name=FOO} + ALL | regress_testrol2 | current_user | {application_name=FOO} +(5 rows) + +ALTER USER CURRENT_ROLE RESET application_name; +ALTER USER CURRENT_USER RESET application_name; +ALTER USER SESSION_USER RESET application_name; +ALTER USER "current_user" RESET application_name; +ALTER USER "Public" RESET application_name; +ALTER USER ALL RESET application_name; +SELECT * FROM chksetconfig(); + db | role | rolkeyword | setconfig +----+------+------------+----------- +(0 rows) + +ALTER USER USER SET application_name to 'BOOM'; -- error +ERROR: syntax error at or near "USER" +LINE 1: ALTER USER USER SET application_name to 'BOOM'; + ^ +ALTER USER PUBLIC SET application_name to 'BOMB'; -- error +ERROR: role "public" does not exist +ALTER USER NONE SET application_name to 'BOMB'; -- error +ERROR: role name "none" is reserved +LINE 1: ALTER USER NONE SET application_name to 'BOMB'; + ^ +ALTER USER nonexistent SET application_name to 'BOMB'; -- error +ERROR: role "nonexistent" does not exist +-- CREATE SCHEMA +CREATE SCHEMA newschema1 AUTHORIZATION CURRENT_USER; +CREATE SCHEMA newschema2 AUTHORIZATION "current_user"; +CREATE SCHEMA newschema3 AUTHORIZATION CURRENT_ROLE; +CREATE SCHEMA newschema4 AUTHORIZATION SESSION_USER; +CREATE SCHEMA newschema5 AUTHORIZATION regress_testrolx; +CREATE SCHEMA newschema6 AUTHORIZATION "Public"; +CREATE SCHEMA newschemax AUTHORIZATION USER; -- error +ERROR: syntax error at or near "USER" +LINE 1: CREATE SCHEMA newschemax AUTHORIZATION USER; + ^ +CREATE SCHEMA newschemax AUTHORIZATION PUBLIC; -- error +ERROR: role "public" does not exist +CREATE SCHEMA newschemax AUTHORIZATION "public"; -- error +ERROR: role "public" does not exist +CREATE SCHEMA newschemax AUTHORIZATION NONE; -- error +ERROR: role name "none" is reserved +LINE 1: CREATE SCHEMA newschemax AUTHORIZATION NONE; + ^ +CREATE SCHEMA newschemax AUTHORIZATION nonexistent; -- error +ERROR: role "nonexistent" does not exist +SELECT n.nspname, r.rolname FROM pg_namespace n + JOIN pg_roles r ON (r.oid = n.nspowner) + WHERE n.nspname LIKE 'newschema_' ORDER BY 1; + nspname | rolname +------------+------------------ + newschema1 | regress_testrol2 + newschema2 | current_user + newschema3 | regress_testrol2 + newschema4 | regress_testrol1 + newschema5 | regress_testrolx + newschema6 | Public +(6 rows) + +CREATE SCHEMA IF NOT EXISTS newschema1 AUTHORIZATION CURRENT_USER; +NOTICE: schema "newschema1" already exists, skipping +CREATE SCHEMA IF NOT EXISTS newschema2 AUTHORIZATION "current_user"; +NOTICE: schema "newschema2" already exists, skipping +CREATE SCHEMA IF NOT EXISTS newschema3 AUTHORIZATION CURRENT_ROLE; +NOTICE: schema "newschema3" already exists, skipping +CREATE SCHEMA IF NOT EXISTS newschema4 AUTHORIZATION SESSION_USER; +NOTICE: schema "newschema4" already exists, skipping +CREATE SCHEMA IF NOT EXISTS newschema5 AUTHORIZATION regress_testrolx; +NOTICE: schema "newschema5" already exists, skipping +CREATE SCHEMA IF NOT EXISTS newschema6 AUTHORIZATION "Public"; +NOTICE: schema "newschema6" already exists, skipping +CREATE SCHEMA IF NOT EXISTS newschemax AUTHORIZATION USER; -- error +ERROR: syntax error at or near "USER" +LINE 1: CREATE SCHEMA IF NOT EXISTS newschemax AUTHORIZATION USER; + ^ +CREATE SCHEMA IF NOT EXISTS newschemax AUTHORIZATION PUBLIC; -- error +ERROR: role "public" does not exist +CREATE SCHEMA IF NOT EXISTS newschemax AUTHORIZATION "public"; -- error +ERROR: role "public" does not exist +CREATE SCHEMA IF NOT EXISTS newschemax AUTHORIZATION NONE; -- error +ERROR: role name "none" is reserved +LINE 1: CREATE SCHEMA IF NOT EXISTS newschemax AUTHORIZATION NONE; + ^ +CREATE SCHEMA IF NOT EXISTS newschemax AUTHORIZATION nonexistent; -- error +ERROR: role "nonexistent" does not exist +SELECT n.nspname, r.rolname FROM pg_namespace n + JOIN pg_roles r ON (r.oid = n.nspowner) + WHERE n.nspname LIKE 'newschema_' ORDER BY 1; + nspname | rolname +------------+------------------ + newschema1 | regress_testrol2 + newschema2 | current_user + newschema3 | regress_testrol2 + newschema4 | regress_testrol1 + newschema5 | regress_testrolx + newschema6 | Public +(6 rows) + +-- ALTER TABLE OWNER TO +\c - +SET SESSION AUTHORIZATION regress_testrol0; +CREATE TABLE testtab1 (a int); +CREATE TABLE testtab2 (a int); +CREATE TABLE testtab3 (a int); +CREATE TABLE testtab4 (a int); +CREATE TABLE testtab5 (a int); +CREATE TABLE testtab6 (a int); +CREATE TABLE testtab7 (a int); +\c - +SET SESSION AUTHORIZATION regress_testrol1; +SET ROLE regress_testrol2; +ALTER TABLE testtab1 OWNER TO CURRENT_USER; +ALTER TABLE testtab2 OWNER TO "current_user"; +ALTER TABLE testtab3 OWNER TO CURRENT_ROLE; +ALTER TABLE testtab4 OWNER TO SESSION_USER; +ALTER TABLE testtab5 OWNER TO regress_testrolx; +ALTER TABLE testtab6 OWNER TO "Public"; +ALTER TABLE testtab7 OWNER TO USER; --error +ERROR: syntax error at or near "USER" +LINE 1: ALTER TABLE testtab7 OWNER TO USER; + ^ +ALTER TABLE testtab7 OWNER TO PUBLIC; -- error +ERROR: role "public" does not exist +ALTER TABLE testtab7 OWNER TO "public"; -- error +ERROR: role "public" does not exist +ALTER TABLE testtab7 OWNER TO nonexistent; -- error +ERROR: role "nonexistent" does not exist +SELECT c.relname, r.rolname + FROM pg_class c JOIN pg_roles r ON (r.oid = c.relowner) + WHERE relname LIKE 'testtab_' + ORDER BY 1; + relname | rolname +----------+------------------ + testtab1 | regress_testrol2 + testtab2 | current_user + testtab3 | regress_testrol2 + testtab4 | regress_testrol1 + testtab5 | regress_testrolx + testtab6 | Public + testtab7 | regress_testrol0 +(7 rows) + +-- ALTER TABLE, VIEW, MATERIALIZED VIEW, FOREIGN TABLE, SEQUENCE are +-- changed their owner in the same way. +-- ALTER AGGREGATE +\c - +SET SESSION AUTHORIZATION regress_testrol0; +CREATE AGGREGATE testagg1(int2) (SFUNC = int2_sum, STYPE = int8); +CREATE AGGREGATE testagg2(int2) (SFUNC = int2_sum, STYPE = int8); +CREATE AGGREGATE testagg3(int2) (SFUNC = int2_sum, STYPE = int8); +CREATE AGGREGATE testagg4(int2) (SFUNC = int2_sum, STYPE = int8); +CREATE AGGREGATE testagg5(int2) (SFUNC = int2_sum, STYPE = int8); +CREATE AGGREGATE testagg6(int2) (SFUNC = int2_sum, STYPE = int8); +CREATE AGGREGATE testagg7(int2) (SFUNC = int2_sum, STYPE = int8); +CREATE AGGREGATE testagg8(int2) (SFUNC = int2_sum, STYPE = int8); +CREATE AGGREGATE testagg9(int2) (SFUNC = int2_sum, STYPE = int8); +CREATE AGGREGATE testagga(int2) (SFUNC = int2_sum, STYPE = int8); +\c - +SET SESSION AUTHORIZATION regress_testrol1; +SET ROLE regress_testrol2; +ALTER AGGREGATE testagg1(int2) OWNER TO CURRENT_USER; +ALTER AGGREGATE testagg2(int2) OWNER TO "current_user"; +ALTER AGGREGATE testagg3(int2) OWNER TO CURRENT_ROLE; +ALTER AGGREGATE testagg4(int2) OWNER TO SESSION_USER; +ALTER AGGREGATE testagg5(int2) OWNER TO regress_testrolx; +ALTER AGGREGATE testagg6(int2) OWNER TO "Public"; +ALTER AGGREGATE testagg6(int2) OWNER TO USER; -- error +ERROR: syntax error at or near "USER" +LINE 1: ALTER AGGREGATE testagg6(int2) OWNER TO USER; + ^ +ALTER AGGREGATE testagg6(int2) OWNER TO PUBLIC; -- error +ERROR: role "public" does not exist +ALTER AGGREGATE testagg6(int2) OWNER TO "public"; -- error +ERROR: role "public" does not exist +ALTER AGGREGATE testagg6(int2) OWNER TO nonexistent; -- error +ERROR: role "nonexistent" does not exist +SELECT p.proname, r.rolname + FROM pg_proc p JOIN pg_roles r ON (r.oid = p.proowner) + WHERE proname LIKE 'testagg_' + ORDER BY 1; + proname | rolname +----------+------------------ + testagg1 | regress_testrol2 + testagg2 | current_user + testagg3 | regress_testrol2 + testagg4 | regress_testrol1 + testagg5 | regress_testrolx + testagg6 | Public + testagg7 | regress_testrol0 + testagg8 | regress_testrol0 + testagg9 | regress_testrol0 + testagga | regress_testrol0 +(10 rows) + +-- CREATE USER MAPPING +CREATE FOREIGN DATA WRAPPER test_wrapper; +CREATE SERVER sv1 FOREIGN DATA WRAPPER test_wrapper; +CREATE SERVER sv2 FOREIGN DATA WRAPPER test_wrapper; +CREATE SERVER sv3 FOREIGN DATA WRAPPER test_wrapper; +CREATE SERVER sv4 FOREIGN DATA WRAPPER test_wrapper; +CREATE SERVER sv5 FOREIGN DATA WRAPPER test_wrapper; +CREATE SERVER sv6 FOREIGN DATA WRAPPER test_wrapper; +CREATE SERVER sv7 FOREIGN DATA WRAPPER test_wrapper; +CREATE SERVER sv8 FOREIGN DATA WRAPPER test_wrapper; +CREATE SERVER sv9 FOREIGN DATA WRAPPER test_wrapper; +CREATE SERVER sv10 FOREIGN DATA WRAPPER test_wrapper; +CREATE USER MAPPING FOR CURRENT_USER SERVER sv1 OPTIONS (user 'CURRENT_USER'); +CREATE USER MAPPING FOR "current_user" SERVER sv2 OPTIONS (user '"current_user"'); +CREATE USER MAPPING FOR CURRENT_ROLE SERVER sv3 OPTIONS (user 'CURRENT_ROLE'); +CREATE USER MAPPING FOR USER SERVER sv4 OPTIONS (user 'USER'); +CREATE USER MAPPING FOR "user" SERVER sv5 OPTIONS (user '"USER"'); +CREATE USER MAPPING FOR SESSION_USER SERVER sv6 OPTIONS (user 'SESSION_USER'); +CREATE USER MAPPING FOR PUBLIC SERVER sv7 OPTIONS (user 'PUBLIC'); +CREATE USER MAPPING FOR "Public" SERVER sv8 OPTIONS (user '"Public"'); +CREATE USER MAPPING FOR regress_testrolx SERVER sv9 OPTIONS (user 'regress_testrolx'); +CREATE USER MAPPING FOR nonexistent SERVER sv10 OPTIONS (user 'nonexistent'); -- error; +ERROR: role "nonexistent" does not exist +SELECT * FROM chkumapping(); + umname | umserver | umoptions +------------------+----------+--------------------------- + regress_testrol2 | sv1 | {user=CURRENT_USER} + current_user | sv2 | {"user=\"current_user\""} + regress_testrol2 | sv3 | {user=CURRENT_ROLE} + regress_testrol2 | sv4 | {user=USER} + user | sv5 | {"user=\"USER\""} + regress_testrol1 | sv6 | {user=SESSION_USER} + | sv7 | {user=PUBLIC} + Public | sv8 | {"user=\"Public\""} + regress_testrolx | sv9 | {user=regress_testrolx} +(9 rows) + +-- ALTER USER MAPPING +ALTER USER MAPPING FOR CURRENT_USER SERVER sv1 + OPTIONS (SET user 'CURRENT_USER_alt'); +ALTER USER MAPPING FOR "current_user" SERVER sv2 + OPTIONS (SET user '"current_user"_alt'); +ALTER USER MAPPING FOR CURRENT_ROLE SERVER sv3 + OPTIONS (SET user 'CURRENT_ROLE_alt'); +ALTER USER MAPPING FOR USER SERVER sv4 + OPTIONS (SET user 'USER_alt'); +ALTER USER MAPPING FOR "user" SERVER sv5 + OPTIONS (SET user '"user"_alt'); +ALTER USER MAPPING FOR SESSION_USER SERVER sv6 + OPTIONS (SET user 'SESSION_USER_alt'); +ALTER USER MAPPING FOR PUBLIC SERVER sv7 + OPTIONS (SET user 'public_alt'); +ALTER USER MAPPING FOR "Public" SERVER sv8 + OPTIONS (SET user '"Public"_alt'); +ALTER USER MAPPING FOR regress_testrolx SERVER sv9 + OPTIONS (SET user 'regress_testrolx_alt'); +ALTER USER MAPPING FOR nonexistent SERVER sv10 + OPTIONS (SET user 'nonexistent_alt'); -- error +ERROR: role "nonexistent" does not exist +SELECT * FROM chkumapping(); + umname | umserver | umoptions +------------------+----------+------------------------------- + regress_testrol2 | sv1 | {user=CURRENT_USER_alt} + current_user | sv2 | {"user=\"current_user\"_alt"} + regress_testrol2 | sv3 | {user=CURRENT_ROLE_alt} + regress_testrol2 | sv4 | {user=USER_alt} + user | sv5 | {"user=\"user\"_alt"} + regress_testrol1 | sv6 | {user=SESSION_USER_alt} + | sv7 | {user=public_alt} + Public | sv8 | {"user=\"Public\"_alt"} + regress_testrolx | sv9 | {user=regress_testrolx_alt} +(9 rows) + +-- DROP USER MAPPING +DROP USER MAPPING FOR CURRENT_USER SERVER sv1; +DROP USER MAPPING FOR "current_user" SERVER sv2; +DROP USER MAPPING FOR CURRENT_ROLE SERVER sv3; +DROP USER MAPPING FOR USER SERVER sv4; +DROP USER MAPPING FOR "user" SERVER sv5; +DROP USER MAPPING FOR SESSION_USER SERVER sv6; +DROP USER MAPPING FOR PUBLIC SERVER sv7; +DROP USER MAPPING FOR "Public" SERVER sv8; +DROP USER MAPPING FOR regress_testrolx SERVER sv9; +DROP USER MAPPING FOR nonexistent SERVER sv10; -- error +ERROR: role "nonexistent" does not exist +SELECT * FROM chkumapping(); + umname | umserver | umoptions +--------+----------+----------- +(0 rows) + +CREATE USER MAPPING FOR CURRENT_USER SERVER sv1 OPTIONS (user 'CURRENT_USER'); +CREATE USER MAPPING FOR "current_user" SERVER sv2 OPTIONS (user '"current_user"'); +CREATE USER MAPPING FOR CURRENT_ROLE SERVER sv3 OPTIONS (user 'CURRENT_ROLE'); +CREATE USER MAPPING FOR USER SERVER sv4 OPTIONS (user 'USER'); +CREATE USER MAPPING FOR "user" SERVER sv5 OPTIONS (user '"USER"'); +CREATE USER MAPPING FOR SESSION_USER SERVER sv6 OPTIONS (user 'SESSION_USER'); +CREATE USER MAPPING FOR PUBLIC SERVER sv7 OPTIONS (user 'PUBLIC'); +CREATE USER MAPPING FOR "Public" SERVER sv8 OPTIONS (user '"Public"'); +CREATE USER MAPPING FOR regress_testrolx SERVER sv9 OPTIONS (user 'regress_testrolx'); +SELECT * FROM chkumapping(); + umname | umserver | umoptions +------------------+----------+--------------------------- + regress_testrol2 | sv1 | {user=CURRENT_USER} + current_user | sv2 | {"user=\"current_user\""} + regress_testrol2 | sv3 | {user=CURRENT_ROLE} + regress_testrol2 | sv4 | {user=USER} + user | sv5 | {"user=\"USER\""} + regress_testrol1 | sv6 | {user=SESSION_USER} + | sv7 | {user=PUBLIC} + Public | sv8 | {"user=\"Public\""} + regress_testrolx | sv9 | {user=regress_testrolx} +(9 rows) + +-- DROP USER MAPPING IF EXISTS +DROP USER MAPPING IF EXISTS FOR CURRENT_USER SERVER sv1; +SELECT * FROM chkumapping(); + umname | umserver | umoptions +------------------+----------+--------------------------- + current_user | sv2 | {"user=\"current_user\""} + regress_testrol2 | sv3 | {user=CURRENT_ROLE} + regress_testrol2 | sv4 | {user=USER} + user | sv5 | {"user=\"USER\""} + regress_testrol1 | sv6 | {user=SESSION_USER} + | sv7 | {user=PUBLIC} + Public | sv8 | {"user=\"Public\""} + regress_testrolx | sv9 | {user=regress_testrolx} +(8 rows) + +DROP USER MAPPING IF EXISTS FOR "current_user" SERVER sv2; +SELECT * FROM chkumapping(); + umname | umserver | umoptions +------------------+----------+------------------------- + regress_testrol2 | sv3 | {user=CURRENT_ROLE} + regress_testrol2 | sv4 | {user=USER} + user | sv5 | {"user=\"USER\""} + regress_testrol1 | sv6 | {user=SESSION_USER} + | sv7 | {user=PUBLIC} + Public | sv8 | {"user=\"Public\""} + regress_testrolx | sv9 | {user=regress_testrolx} +(7 rows) + +DROP USER MAPPING IF EXISTS FOR CURRENT_USER SERVER sv3; +SELECT * FROM chkumapping(); + umname | umserver | umoptions +------------------+----------+------------------------- + regress_testrol2 | sv4 | {user=USER} + user | sv5 | {"user=\"USER\""} + regress_testrol1 | sv6 | {user=SESSION_USER} + | sv7 | {user=PUBLIC} + Public | sv8 | {"user=\"Public\""} + regress_testrolx | sv9 | {user=regress_testrolx} +(6 rows) + +DROP USER MAPPING IF EXISTS FOR USER SERVER sv4; +SELECT * FROM chkumapping(); + umname | umserver | umoptions +------------------+----------+------------------------- + user | sv5 | {"user=\"USER\""} + regress_testrol1 | sv6 | {user=SESSION_USER} + | sv7 | {user=PUBLIC} + Public | sv8 | {"user=\"Public\""} + regress_testrolx | sv9 | {user=regress_testrolx} +(5 rows) + +DROP USER MAPPING IF EXISTS FOR "user" SERVER sv5; +SELECT * FROM chkumapping(); + umname | umserver | umoptions +------------------+----------+------------------------- + regress_testrol1 | sv6 | {user=SESSION_USER} + | sv7 | {user=PUBLIC} + Public | sv8 | {"user=\"Public\""} + regress_testrolx | sv9 | {user=regress_testrolx} +(4 rows) + +DROP USER MAPPING IF EXISTS FOR SESSION_USER SERVER sv6; +SELECT * FROM chkumapping(); + umname | umserver | umoptions +------------------+----------+------------------------- + | sv7 | {user=PUBLIC} + Public | sv8 | {"user=\"Public\""} + regress_testrolx | sv9 | {user=regress_testrolx} +(3 rows) + +DROP USER MAPPING IF EXISTS FOR PUBLIC SERVER sv7; +SELECT * FROM chkumapping(); + umname | umserver | umoptions +------------------+----------+------------------------- + Public | sv8 | {"user=\"Public\""} + regress_testrolx | sv9 | {user=regress_testrolx} +(2 rows) + +DROP USER MAPPING IF EXISTS FOR "Public" SERVER sv8; +SELECT * FROM chkumapping(); + umname | umserver | umoptions +------------------+----------+------------------------- + regress_testrolx | sv9 | {user=regress_testrolx} +(1 row) + +DROP USER MAPPING IF EXISTS FOR regress_testrolx SERVER sv9; +SELECT * FROM chkumapping(); + umname | umserver | umoptions +--------+----------+----------- +(0 rows) + +DROP USER MAPPING IF EXISTS FOR nonexistent SERVER sv10; -- error +NOTICE: role "nonexistent" does not exist, skipping +-- GRANT/REVOKE +GRANT regress_testrol0 TO pg_signal_backend; -- success +SET ROLE pg_signal_backend; --success +RESET ROLE; +CREATE SCHEMA test_roles_schema AUTHORIZATION pg_signal_backend; --success +SET ROLE regress_testrol2; +UPDATE pg_proc SET proacl = null WHERE proname LIKE 'testagg_'; +SELECT proname, proacl FROM pg_proc WHERE proname LIKE 'testagg_'; + proname | proacl +----------+-------- + testagg1 | + testagg2 | + testagg3 | + testagg4 | + testagg5 | + testagg6 | + testagg7 | + testagg8 | + testagg9 | + testagga | +(10 rows) + +REVOKE ALL PRIVILEGES ON FUNCTION testagg1(int2) FROM PUBLIC; +REVOKE ALL PRIVILEGES ON FUNCTION testagg2(int2) FROM PUBLIC; +REVOKE ALL PRIVILEGES ON FUNCTION testagg3(int2) FROM PUBLIC; +REVOKE ALL PRIVILEGES ON FUNCTION testagg4(int2) FROM PUBLIC; +REVOKE ALL PRIVILEGES ON FUNCTION testagg5(int2) FROM PUBLIC; +REVOKE ALL PRIVILEGES ON FUNCTION testagg6(int2) FROM PUBLIC; +REVOKE ALL PRIVILEGES ON FUNCTION testagg7(int2) FROM PUBLIC; +REVOKE ALL PRIVILEGES ON FUNCTION testagg8(int2) FROM PUBLIC; +GRANT ALL PRIVILEGES ON FUNCTION testagg1(int2) TO PUBLIC; +GRANT ALL PRIVILEGES ON FUNCTION testagg2(int2) TO CURRENT_USER; +GRANT ALL PRIVILEGES ON FUNCTION testagg3(int2) TO "current_user"; +GRANT ALL PRIVILEGES ON FUNCTION testagg4(int2) TO CURRENT_ROLE; +GRANT ALL PRIVILEGES ON FUNCTION testagg5(int2) TO SESSION_USER; +GRANT ALL PRIVILEGES ON FUNCTION testagg6(int2) TO "Public"; +GRANT ALL PRIVILEGES ON FUNCTION testagg7(int2) TO regress_testrolx; +GRANT ALL PRIVILEGES ON FUNCTION testagg8(int2) TO "public"; +GRANT ALL PRIVILEGES ON FUNCTION testagg9(int2) + TO current_user, public, regress_testrolx; +SELECT proname, proacl FROM pg_proc WHERE proname LIKE 'testagg_'; + proname | proacl +----------+----------------------------------------------------------------------------------------------------------------------------------- + testagg1 | {regress_testrol2=X/regress_testrol2,=X/regress_testrol2} + testagg2 | {current_user=X/current_user,regress_testrol2=X/current_user} + testagg3 | {regress_testrol2=X/regress_testrol2,current_user=X/regress_testrol2} + testagg4 | {regress_testrol1=X/regress_testrol1,regress_testrol2=X/regress_testrol1} + testagg5 | {regress_testrolx=X/regress_testrolx,regress_testrol1=X/regress_testrolx} + testagg6 | {Public=X/Public} + testagg7 | {regress_testrol0=X/regress_testrol0,regress_testrolx=X/regress_testrol0} + testagg8 | {regress_testrol0=X/regress_testrol0,=X/regress_testrol0} + testagg9 | {=X/regress_testrol0,regress_testrol0=X/regress_testrol0,regress_testrol2=X/regress_testrol0,regress_testrolx=X/regress_testrol0} + testagga | +(10 rows) + +GRANT ALL PRIVILEGES ON FUNCTION testagga(int2) TO USER; --error +ERROR: syntax error at or near "USER" +LINE 1: GRANT ALL PRIVILEGES ON FUNCTION testagga(int2) TO USER; + ^ +GRANT ALL PRIVILEGES ON FUNCTION testagga(int2) TO NONE; --error +ERROR: role name "none" is reserved +LINE 1: GRANT ALL PRIVILEGES ON FUNCTION testagga(int2) TO NONE; + ^ +GRANT ALL PRIVILEGES ON FUNCTION testagga(int2) TO "none"; --error +ERROR: role name "none" is reserved +LINE 1: GRANT ALL PRIVILEGES ON FUNCTION testagga(int2) TO "none"; + ^ +SELECT proname, proacl FROM pg_proc WHERE proname LIKE 'testagg_'; + proname | proacl +----------+----------------------------------------------------------------------------------------------------------------------------------- + testagg1 | {regress_testrol2=X/regress_testrol2,=X/regress_testrol2} + testagg2 | {current_user=X/current_user,regress_testrol2=X/current_user} + testagg3 | {regress_testrol2=X/regress_testrol2,current_user=X/regress_testrol2} + testagg4 | {regress_testrol1=X/regress_testrol1,regress_testrol2=X/regress_testrol1} + testagg5 | {regress_testrolx=X/regress_testrolx,regress_testrol1=X/regress_testrolx} + testagg6 | {Public=X/Public} + testagg7 | {regress_testrol0=X/regress_testrol0,regress_testrolx=X/regress_testrol0} + testagg8 | {regress_testrol0=X/regress_testrol0,=X/regress_testrol0} + testagg9 | {=X/regress_testrol0,regress_testrol0=X/regress_testrol0,regress_testrol2=X/regress_testrol0,regress_testrolx=X/regress_testrol0} + testagga | +(10 rows) + +REVOKE ALL PRIVILEGES ON FUNCTION testagg1(int2) FROM PUBLIC; +REVOKE ALL PRIVILEGES ON FUNCTION testagg2(int2) FROM CURRENT_USER; +REVOKE ALL PRIVILEGES ON FUNCTION testagg3(int2) FROM "current_user"; +REVOKE ALL PRIVILEGES ON FUNCTION testagg4(int2) FROM CURRENT_ROLE; +REVOKE ALL PRIVILEGES ON FUNCTION testagg5(int2) FROM SESSION_USER; +REVOKE ALL PRIVILEGES ON FUNCTION testagg6(int2) FROM "Public"; +REVOKE ALL PRIVILEGES ON FUNCTION testagg7(int2) FROM regress_testrolx; +REVOKE ALL PRIVILEGES ON FUNCTION testagg8(int2) FROM "public"; +REVOKE ALL PRIVILEGES ON FUNCTION testagg9(int2) + FROM current_user, public, regress_testrolx; +SELECT proname, proacl FROM pg_proc WHERE proname LIKE 'testagg_'; + proname | proacl +----------+--------------------------------------- + testagg1 | {regress_testrol2=X/regress_testrol2} + testagg2 | {current_user=X/current_user} + testagg3 | {regress_testrol2=X/regress_testrol2} + testagg4 | {regress_testrol1=X/regress_testrol1} + testagg5 | {regress_testrolx=X/regress_testrolx} + testagg6 | {} + testagg7 | {regress_testrol0=X/regress_testrol0} + testagg8 | {regress_testrol0=X/regress_testrol0} + testagg9 | {regress_testrol0=X/regress_testrol0} + testagga | +(10 rows) + +REVOKE ALL PRIVILEGES ON FUNCTION testagga(int2) FROM USER; --error +ERROR: syntax error at or near "USER" +LINE 1: REVOKE ALL PRIVILEGES ON FUNCTION testagga(int2) FROM USER; + ^ +REVOKE ALL PRIVILEGES ON FUNCTION testagga(int2) FROM NONE; --error +ERROR: role name "none" is reserved +LINE 1: REVOKE ALL PRIVILEGES ON FUNCTION testagga(int2) FROM NONE; + ^ +REVOKE ALL PRIVILEGES ON FUNCTION testagga(int2) FROM "none"; --error +ERROR: role name "none" is reserved +LINE 1: ...EVOKE ALL PRIVILEGES ON FUNCTION testagga(int2) FROM "none"; + ^ +SELECT proname, proacl FROM pg_proc WHERE proname LIKE 'testagg_'; + proname | proacl +----------+--------------------------------------- + testagg1 | {regress_testrol2=X/regress_testrol2} + testagg2 | {current_user=X/current_user} + testagg3 | {regress_testrol2=X/regress_testrol2} + testagg4 | {regress_testrol1=X/regress_testrol1} + testagg5 | {regress_testrolx=X/regress_testrolx} + testagg6 | {} + testagg7 | {regress_testrol0=X/regress_testrol0} + testagg8 | {regress_testrol0=X/regress_testrol0} + testagg9 | {regress_testrol0=X/regress_testrol0} + testagga | +(10 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 have privileges 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_role", "current_user", regress_testrol1, regress_testrol2, regress_testrolx CASCADE; +DROP ROLE regress_testrol0, regress_testrol1, regress_testrol2, regress_testrolx; +DROP ROLE "Public", "None", "current_role", "current_user", "session_user", "user"; +DROP ROLE regress_role_haspriv, regress_role_nopriv; diff --git a/src/test/modules/unsafe_tests/sql/alter_system_table.sql b/src/test/modules/unsafe_tests/sql/alter_system_table.sql new file mode 100644 index 0000000..b77b68c --- /dev/null +++ b/src/test/modules/unsafe_tests/sql/alter_system_table.sql @@ -0,0 +1,194 @@ +-- +-- Tests for things affected by allow_system_table_mods +-- +-- We run the same set of commands once with allow_system_table_mods +-- off and then again with on. +-- +-- The "on" tests should where possible be wrapped in BEGIN/ROLLBACK +-- blocks so as to not leave a mess around. + +CREATE USER regress_user_ast; + +SET allow_system_table_mods = off; + +-- create new table in pg_catalog +CREATE TABLE pg_catalog.test (a int); + +-- anyarray column +CREATE TABLE t1x (a int, b anyarray); + +-- index on system catalog +ALTER TABLE pg_namespace ADD CONSTRAINT foo UNIQUE USING INDEX pg_namespace_nspname_index; + +-- write to system catalog table as superuser +-- (allowed even without allow_system_table_mods) +INSERT INTO pg_description (objoid, classoid, objsubid, description) VALUES (0, 0, 0, 'foo'); + +-- write to system catalog table as normal user +GRANT INSERT ON pg_description TO regress_user_ast; +SET ROLE regress_user_ast; +INSERT INTO pg_description (objoid, classoid, objsubid, description) VALUES (0, 0, 1, 'foo'); +RESET ROLE; + +-- policy on system catalog +CREATE POLICY foo ON pg_description FOR SELECT USING (description NOT LIKE 'secret%'); + +-- reserved schema name +CREATE SCHEMA pg_foo; + +-- drop system table +DROP TABLE pg_description; + +-- truncate of system table +TRUNCATE pg_description; + +-- rename column of system table +ALTER TABLE pg_description RENAME COLUMN description TO comment; + +-- ATSimplePermissions() +ALTER TABLE pg_description ALTER COLUMN description SET NOT NULL; + +-- SET STATISTICS +ALTER TABLE pg_description ALTER COLUMN description SET STATISTICS -1; + +-- foreign key referencing catalog +CREATE TABLE foo (a oid, b oid, c int, FOREIGN KEY (a, b, c) REFERENCES pg_description); + +-- RangeVarCallbackOwnsRelation() +CREATE INDEX pg_description_test_index ON pg_description (description); + +-- RangeVarCallbackForAlterRelation() +ALTER TABLE pg_description RENAME TO pg_comment; +ALTER TABLE pg_description SET SCHEMA public; + +-- reserved tablespace name +CREATE TABLESPACE pg_foo LOCATION '/no/such/location'; + +-- triggers +CREATE FUNCTION tf1() RETURNS trigger +LANGUAGE plpgsql +AS $$ +BEGIN + RETURN NULL; +END $$; + +CREATE TRIGGER t1 BEFORE INSERT ON pg_description EXECUTE FUNCTION tf1(); +ALTER TRIGGER t1 ON pg_description RENAME TO t2; +--DROP TRIGGER t2 ON pg_description; + +-- rules +CREATE RULE r1 AS ON INSERT TO pg_description DO INSTEAD NOTHING; +ALTER RULE r1 ON pg_description RENAME TO r2; +-- now make one to test dropping: +SET allow_system_table_mods TO on; +CREATE RULE r2 AS ON INSERT TO pg_description DO INSTEAD NOTHING; +RESET allow_system_table_mods; +DROP RULE r2 ON pg_description; +-- cleanup: +SET allow_system_table_mods TO on; +DROP RULE r2 ON pg_description; +RESET allow_system_table_mods; + + +SET allow_system_table_mods = on; + +-- create new table in pg_catalog +BEGIN; +CREATE TABLE pg_catalog.test (a int); +ROLLBACK; + +-- anyarray column +BEGIN; +CREATE TABLE t1 (a int, b anyarray); +ROLLBACK; + +-- index on system catalog +BEGIN; +ALTER TABLE pg_namespace ADD CONSTRAINT foo UNIQUE USING INDEX pg_namespace_nspname_index; +ROLLBACK; + +-- write to system catalog table as superuser +BEGIN; +INSERT INTO pg_description (objoid, classoid, objsubid, description) VALUES (0, 0, 2, 'foo'); +ROLLBACK; + +-- write to system catalog table as normal user +-- (not allowed) +SET ROLE regress_user_ast; +INSERT INTO pg_description (objoid, classoid, objsubid, description) VALUES (0, 0, 3, 'foo'); +RESET ROLE; + +-- policy on system catalog +BEGIN; +CREATE POLICY foo ON pg_description FOR SELECT USING (description NOT LIKE 'secret%'); +ROLLBACK; + +-- reserved schema name +BEGIN; +CREATE SCHEMA pg_foo; +ROLLBACK; + +-- drop system table +-- (This will fail anyway because it's pinned.) +BEGIN; +DROP TABLE pg_description; +ROLLBACK; + +-- truncate of system table +BEGIN; +TRUNCATE pg_description; +ROLLBACK; + +-- rename column of system table +BEGIN; +ALTER TABLE pg_description RENAME COLUMN description TO comment; +ROLLBACK; + +-- ATSimplePermissions() +BEGIN; +ALTER TABLE pg_description ALTER COLUMN description SET NOT NULL; +ROLLBACK; + +-- SET STATISTICS +BEGIN; +ALTER TABLE pg_description ALTER COLUMN description SET STATISTICS -1; +ROLLBACK; + +-- foreign key referencing catalog +BEGIN; +CREATE TABLE foo (a oid, b oid, c int, FOREIGN KEY (a, b, c) REFERENCES pg_description); +ROLLBACK; + +-- RangeVarCallbackOwnsRelation() +BEGIN; +CREATE INDEX pg_description_test_index ON pg_description (description); +ROLLBACK; + +-- RangeVarCallbackForAlterRelation() +BEGIN; +ALTER TABLE pg_description RENAME TO pg_comment; +ROLLBACK; +BEGIN; +ALTER TABLE pg_description SET SCHEMA public; +ROLLBACK; + +-- reserved tablespace name +SET client_min_messages = error; -- disable ENFORCE_REGRESSION_TEST_NAME_RESTRICTIONS warning +CREATE TABLESPACE pg_foo LOCATION '/no/such/location'; +RESET client_min_messages; + +-- triggers +CREATE TRIGGER t1 BEFORE INSERT ON pg_description EXECUTE FUNCTION tf1(); +ALTER TRIGGER t1 ON pg_description RENAME TO t2; +DROP TRIGGER t2 ON pg_description; + +-- rules +CREATE RULE r1 AS ON INSERT TO pg_description DO INSTEAD NOTHING; +ALTER RULE r1 ON pg_description RENAME TO r2; +DROP RULE r2 ON pg_description; + + +-- cleanup +REVOKE ALL ON pg_description FROM regress_user_ast; +DROP USER regress_user_ast; +DROP FUNCTION tf1; 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 0000000..6c7733f --- /dev/null +++ b/src/test/modules/unsafe_tests/sql/guc_privs.sql @@ -0,0 +1,253 @@ +-- +-- 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; + +-- Make a per-role setting that regress_host_resource_admin can't change +ALTER ROLE regress_host_resource_admin SET lc_messages = 'C'; + +-- 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 = '100kB'; -- ok, privileges have been granted +RESET max_stack_depth; -- ok, privileges have been granted +ALTER SYSTEM SET max_stack_depth = '100kB'; -- 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 +ALTER ROLE regress_host_resource_admin SET lc_messages = 'POSIX'; -- fail +ALTER ROLE regress_host_resource_admin SET max_stack_depth = '1MB'; -- ok +SELECT setconfig FROM pg_db_role_setting + WHERE setrole = 'regress_host_resource_admin'::regrole; +ALTER ROLE regress_host_resource_admin RESET max_stack_depth; -- ok +SELECT setconfig FROM pg_db_role_setting + WHERE setrole = 'regress_host_resource_admin'::regrole; +ALTER ROLE regress_host_resource_admin SET max_stack_depth = '1MB'; -- ok +SELECT setconfig FROM pg_db_role_setting + WHERE setrole = 'regress_host_resource_admin'::regrole; +ALTER ROLE regress_host_resource_admin RESET ALL; -- doesn't reset lc_messages +SELECT setconfig FROM pg_db_role_setting + WHERE setrole = 'regress_host_resource_admin'::regrole; + +-- 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 diff --git a/src/test/modules/unsafe_tests/sql/rolenames.sql b/src/test/modules/unsafe_tests/sql/rolenames.sql new file mode 100644 index 0000000..adac365 --- /dev/null +++ b/src/test/modules/unsafe_tests/sql/rolenames.sql @@ -0,0 +1,504 @@ +CREATE FUNCTION chkrolattr() + RETURNS TABLE ("role" name, rolekeyword text, canlogin bool, replication bool) + AS $$ +SELECT r.rolname, v.keyword, r.rolcanlogin, r.rolreplication + FROM pg_roles r + JOIN (VALUES(CURRENT_ROLE, 'current_role'), + (CURRENT_USER, 'current_user'), + (SESSION_USER, 'session_user'), + ('current_role', '-'), + ('current_user', '-'), + ('session_user', '-'), + ('Public', '-'), + ('None', '-')) + AS v(uname, keyword) + ON (r.rolname = v.uname) + ORDER BY 1, 2; +$$ LANGUAGE SQL; + +CREATE FUNCTION chksetconfig() + RETURNS TABLE (db name, "role" name, rolkeyword text, setconfig text[]) + AS $$ +SELECT COALESCE(d.datname, 'ALL'), COALESCE(r.rolname, 'ALL'), + COALESCE(v.keyword, '-'), s.setconfig + FROM pg_db_role_setting s + LEFT JOIN pg_roles r ON (r.oid = s.setrole) + LEFT JOIN pg_database d ON (d.oid = s.setdatabase) + LEFT JOIN (VALUES(CURRENT_ROLE, 'current_role'), + (CURRENT_USER, 'current_user'), + (SESSION_USER, 'session_user')) + AS v(uname, keyword) + ON (r.rolname = v.uname) + WHERE (r.rolname) IN ('Public', 'current_user', 'regress_testrol1', 'regress_testrol2') +ORDER BY 1, 2, 3; +$$ LANGUAGE SQL; + +CREATE FUNCTION chkumapping() + RETURNS TABLE (umname name, umserver name, umoptions text[]) + AS $$ +SELECT r.rolname, s.srvname, m.umoptions + FROM pg_user_mapping m + LEFT JOIN pg_roles r ON (r.oid = m.umuser) + JOIN pg_foreign_server s ON (s.oid = m.umserver) + ORDER BY 2, 1; +$$ LANGUAGE SQL; + +-- +-- We test creation and use of these role names to ensure that the server +-- correctly distinguishes role keywords from quoted names that look like +-- those keywords. In a test environment, creation of these roles may +-- provoke warnings, so hide the warnings by raising client_min_messages. +-- +SET client_min_messages = ERROR; + +CREATE ROLE "Public"; +CREATE ROLE "None"; +CREATE ROLE "current_role"; +CREATE ROLE "current_user"; +CREATE ROLE "session_user"; +CREATE ROLE "user"; + +RESET client_min_messages; + +CREATE ROLE current_user; -- error +CREATE ROLE current_role; -- error +CREATE ROLE session_user; -- error +CREATE ROLE user; -- error +CREATE ROLE all; -- error + +CREATE ROLE public; -- error +CREATE ROLE "public"; -- error +CREATE ROLE none; -- error +CREATE ROLE "none"; -- error + +CREATE ROLE pg_abc; -- error +CREATE ROLE "pg_abc"; -- error +CREATE ROLE pg_abcdef; -- error +CREATE ROLE "pg_abcdef"; -- error + +CREATE ROLE regress_testrol0 SUPERUSER LOGIN; +CREATE ROLE regress_testrolx SUPERUSER LOGIN; +CREATE ROLE regress_testrol2 SUPERUSER; +CREATE ROLE regress_testrol1 SUPERUSER LOGIN IN ROLE regress_testrol2; + +\c - +SET SESSION AUTHORIZATION regress_testrol1; +SET ROLE regress_testrol2; + +-- ALTER ROLE +BEGIN; +SELECT * FROM chkrolattr(); +ALTER ROLE CURRENT_ROLE WITH REPLICATION; +SELECT * FROM chkrolattr(); +ALTER ROLE "current_role" WITH REPLICATION; +SELECT * FROM chkrolattr(); +ALTER ROLE CURRENT_ROLE WITH NOREPLICATION; +ALTER ROLE CURRENT_USER WITH REPLICATION; +SELECT * FROM chkrolattr(); +ALTER ROLE "current_user" WITH REPLICATION; +SELECT * FROM chkrolattr(); +ALTER ROLE SESSION_USER WITH REPLICATION; +SELECT * FROM chkrolattr(); +ALTER ROLE "session_user" WITH REPLICATION; +SELECT * FROM chkrolattr(); +ALTER USER "Public" WITH REPLICATION; +ALTER USER "None" WITH REPLICATION; +SELECT * FROM chkrolattr(); +ALTER USER regress_testrol1 WITH NOREPLICATION; +ALTER USER regress_testrol2 WITH NOREPLICATION; +SELECT * FROM chkrolattr(); +ROLLBACK; + +ALTER ROLE USER WITH LOGIN; -- error +ALTER ROLE ALL WITH REPLICATION; -- error +ALTER ROLE SESSION_ROLE WITH NOREPLICATION; -- error +ALTER ROLE PUBLIC WITH NOREPLICATION; -- error +ALTER ROLE "public" WITH NOREPLICATION; -- error +ALTER ROLE NONE WITH NOREPLICATION; -- error +ALTER ROLE "none" WITH NOREPLICATION; -- error +ALTER ROLE nonexistent WITH NOREPLICATION; -- error + +-- ALTER USER +BEGIN; +SELECT * FROM chkrolattr(); +ALTER USER CURRENT_ROLE WITH REPLICATION; +SELECT * FROM chkrolattr(); +ALTER USER "current_role" WITH REPLICATION; +SELECT * FROM chkrolattr(); +ALTER USER CURRENT_ROLE WITH NOREPLICATION; +ALTER USER CURRENT_USER WITH REPLICATION; +SELECT * FROM chkrolattr(); +ALTER USER "current_user" WITH REPLICATION; +SELECT * FROM chkrolattr(); +ALTER USER SESSION_USER WITH REPLICATION; +SELECT * FROM chkrolattr(); +ALTER USER "session_user" WITH REPLICATION; +SELECT * FROM chkrolattr(); +ALTER USER "Public" WITH REPLICATION; +ALTER USER "None" WITH REPLICATION; +SELECT * FROM chkrolattr(); +ALTER USER regress_testrol1 WITH NOREPLICATION; +ALTER USER regress_testrol2 WITH NOREPLICATION; +SELECT * FROM chkrolattr(); +ROLLBACK; + +ALTER USER USER WITH LOGIN; -- error +ALTER USER ALL WITH REPLICATION; -- error +ALTER USER SESSION_ROLE WITH NOREPLICATION; -- error +ALTER USER PUBLIC WITH NOREPLICATION; -- error +ALTER USER "public" WITH NOREPLICATION; -- error +ALTER USER NONE WITH NOREPLICATION; -- error +ALTER USER "none" WITH NOREPLICATION; -- error +ALTER USER nonexistent WITH NOREPLICATION; -- error + +-- ALTER ROLE SET/RESET +SELECT * FROM chksetconfig(); +ALTER ROLE CURRENT_ROLE SET application_name to 'BAZ'; +ALTER ROLE CURRENT_USER SET application_name to 'FOO'; +ALTER ROLE SESSION_USER SET application_name to 'BAR'; +ALTER ROLE "current_user" SET application_name to 'FOOFOO'; +ALTER ROLE "Public" SET application_name to 'BARBAR'; +ALTER ROLE ALL SET application_name to 'SLAP'; +SELECT * FROM chksetconfig(); +ALTER ROLE regress_testrol1 SET application_name to 'SLAM'; +SELECT * FROM chksetconfig(); +ALTER ROLE CURRENT_ROLE RESET application_name; +ALTER ROLE CURRENT_USER RESET application_name; +ALTER ROLE SESSION_USER RESET application_name; +ALTER ROLE "current_user" RESET application_name; +ALTER ROLE "Public" RESET application_name; +ALTER ROLE ALL RESET application_name; +SELECT * FROM chksetconfig(); + + +ALTER ROLE USER SET application_name to 'BOOM'; -- error +ALTER ROLE PUBLIC SET application_name to 'BOMB'; -- error +ALTER ROLE nonexistent SET application_name to 'BOMB'; -- error + +-- ALTER USER SET/RESET +SELECT * FROM chksetconfig(); +ALTER USER CURRENT_ROLE SET application_name to 'BAZ'; +ALTER USER CURRENT_USER SET application_name to 'FOO'; +ALTER USER SESSION_USER SET application_name to 'BAR'; +ALTER USER "current_user" SET application_name to 'FOOFOO'; +ALTER USER "Public" SET application_name to 'BARBAR'; +ALTER USER ALL SET application_name to 'SLAP'; +SELECT * FROM chksetconfig(); +ALTER USER regress_testrol1 SET application_name to 'SLAM'; +SELECT * FROM chksetconfig(); +ALTER USER CURRENT_ROLE RESET application_name; +ALTER USER CURRENT_USER RESET application_name; +ALTER USER SESSION_USER RESET application_name; +ALTER USER "current_user" RESET application_name; +ALTER USER "Public" RESET application_name; +ALTER USER ALL RESET application_name; +SELECT * FROM chksetconfig(); + + +ALTER USER USER SET application_name to 'BOOM'; -- error +ALTER USER PUBLIC SET application_name to 'BOMB'; -- error +ALTER USER NONE SET application_name to 'BOMB'; -- error +ALTER USER nonexistent SET application_name to 'BOMB'; -- error + +-- CREATE SCHEMA +CREATE SCHEMA newschema1 AUTHORIZATION CURRENT_USER; +CREATE SCHEMA newschema2 AUTHORIZATION "current_user"; +CREATE SCHEMA newschema3 AUTHORIZATION CURRENT_ROLE; +CREATE SCHEMA newschema4 AUTHORIZATION SESSION_USER; +CREATE SCHEMA newschema5 AUTHORIZATION regress_testrolx; +CREATE SCHEMA newschema6 AUTHORIZATION "Public"; + +CREATE SCHEMA newschemax AUTHORIZATION USER; -- error +CREATE SCHEMA newschemax AUTHORIZATION PUBLIC; -- error +CREATE SCHEMA newschemax AUTHORIZATION "public"; -- error +CREATE SCHEMA newschemax AUTHORIZATION NONE; -- error +CREATE SCHEMA newschemax AUTHORIZATION nonexistent; -- error + +SELECT n.nspname, r.rolname FROM pg_namespace n + JOIN pg_roles r ON (r.oid = n.nspowner) + WHERE n.nspname LIKE 'newschema_' ORDER BY 1; + +CREATE SCHEMA IF NOT EXISTS newschema1 AUTHORIZATION CURRENT_USER; +CREATE SCHEMA IF NOT EXISTS newschema2 AUTHORIZATION "current_user"; +CREATE SCHEMA IF NOT EXISTS newschema3 AUTHORIZATION CURRENT_ROLE; +CREATE SCHEMA IF NOT EXISTS newschema4 AUTHORIZATION SESSION_USER; +CREATE SCHEMA IF NOT EXISTS newschema5 AUTHORIZATION regress_testrolx; +CREATE SCHEMA IF NOT EXISTS newschema6 AUTHORIZATION "Public"; + +CREATE SCHEMA IF NOT EXISTS newschemax AUTHORIZATION USER; -- error +CREATE SCHEMA IF NOT EXISTS newschemax AUTHORIZATION PUBLIC; -- error +CREATE SCHEMA IF NOT EXISTS newschemax AUTHORIZATION "public"; -- error +CREATE SCHEMA IF NOT EXISTS newschemax AUTHORIZATION NONE; -- error +CREATE SCHEMA IF NOT EXISTS newschemax AUTHORIZATION nonexistent; -- error + +SELECT n.nspname, r.rolname FROM pg_namespace n + JOIN pg_roles r ON (r.oid = n.nspowner) + WHERE n.nspname LIKE 'newschema_' ORDER BY 1; + +-- ALTER TABLE OWNER TO +\c - +SET SESSION AUTHORIZATION regress_testrol0; +CREATE TABLE testtab1 (a int); +CREATE TABLE testtab2 (a int); +CREATE TABLE testtab3 (a int); +CREATE TABLE testtab4 (a int); +CREATE TABLE testtab5 (a int); +CREATE TABLE testtab6 (a int); +CREATE TABLE testtab7 (a int); + +\c - +SET SESSION AUTHORIZATION regress_testrol1; +SET ROLE regress_testrol2; + +ALTER TABLE testtab1 OWNER TO CURRENT_USER; +ALTER TABLE testtab2 OWNER TO "current_user"; +ALTER TABLE testtab3 OWNER TO CURRENT_ROLE; +ALTER TABLE testtab4 OWNER TO SESSION_USER; +ALTER TABLE testtab5 OWNER TO regress_testrolx; +ALTER TABLE testtab6 OWNER TO "Public"; + +ALTER TABLE testtab7 OWNER TO USER; --error +ALTER TABLE testtab7 OWNER TO PUBLIC; -- error +ALTER TABLE testtab7 OWNER TO "public"; -- error +ALTER TABLE testtab7 OWNER TO nonexistent; -- error + +SELECT c.relname, r.rolname + FROM pg_class c JOIN pg_roles r ON (r.oid = c.relowner) + WHERE relname LIKE 'testtab_' + ORDER BY 1; + +-- ALTER TABLE, VIEW, MATERIALIZED VIEW, FOREIGN TABLE, SEQUENCE are +-- changed their owner in the same way. + +-- ALTER AGGREGATE +\c - +SET SESSION AUTHORIZATION regress_testrol0; +CREATE AGGREGATE testagg1(int2) (SFUNC = int2_sum, STYPE = int8); +CREATE AGGREGATE testagg2(int2) (SFUNC = int2_sum, STYPE = int8); +CREATE AGGREGATE testagg3(int2) (SFUNC = int2_sum, STYPE = int8); +CREATE AGGREGATE testagg4(int2) (SFUNC = int2_sum, STYPE = int8); +CREATE AGGREGATE testagg5(int2) (SFUNC = int2_sum, STYPE = int8); +CREATE AGGREGATE testagg6(int2) (SFUNC = int2_sum, STYPE = int8); +CREATE AGGREGATE testagg7(int2) (SFUNC = int2_sum, STYPE = int8); +CREATE AGGREGATE testagg8(int2) (SFUNC = int2_sum, STYPE = int8); +CREATE AGGREGATE testagg9(int2) (SFUNC = int2_sum, STYPE = int8); +CREATE AGGREGATE testagga(int2) (SFUNC = int2_sum, STYPE = int8); + +\c - +SET SESSION AUTHORIZATION regress_testrol1; +SET ROLE regress_testrol2; + +ALTER AGGREGATE testagg1(int2) OWNER TO CURRENT_USER; +ALTER AGGREGATE testagg2(int2) OWNER TO "current_user"; +ALTER AGGREGATE testagg3(int2) OWNER TO CURRENT_ROLE; +ALTER AGGREGATE testagg4(int2) OWNER TO SESSION_USER; +ALTER AGGREGATE testagg5(int2) OWNER TO regress_testrolx; +ALTER AGGREGATE testagg6(int2) OWNER TO "Public"; + +ALTER AGGREGATE testagg6(int2) OWNER TO USER; -- error +ALTER AGGREGATE testagg6(int2) OWNER TO PUBLIC; -- error +ALTER AGGREGATE testagg6(int2) OWNER TO "public"; -- error +ALTER AGGREGATE testagg6(int2) OWNER TO nonexistent; -- error + +SELECT p.proname, r.rolname + FROM pg_proc p JOIN pg_roles r ON (r.oid = p.proowner) + WHERE proname LIKE 'testagg_' + ORDER BY 1; + +-- CREATE USER MAPPING +CREATE FOREIGN DATA WRAPPER test_wrapper; +CREATE SERVER sv1 FOREIGN DATA WRAPPER test_wrapper; +CREATE SERVER sv2 FOREIGN DATA WRAPPER test_wrapper; +CREATE SERVER sv3 FOREIGN DATA WRAPPER test_wrapper; +CREATE SERVER sv4 FOREIGN DATA WRAPPER test_wrapper; +CREATE SERVER sv5 FOREIGN DATA WRAPPER test_wrapper; +CREATE SERVER sv6 FOREIGN DATA WRAPPER test_wrapper; +CREATE SERVER sv7 FOREIGN DATA WRAPPER test_wrapper; +CREATE SERVER sv8 FOREIGN DATA WRAPPER test_wrapper; +CREATE SERVER sv9 FOREIGN DATA WRAPPER test_wrapper; +CREATE SERVER sv10 FOREIGN DATA WRAPPER test_wrapper; + +CREATE USER MAPPING FOR CURRENT_USER SERVER sv1 OPTIONS (user 'CURRENT_USER'); +CREATE USER MAPPING FOR "current_user" SERVER sv2 OPTIONS (user '"current_user"'); +CREATE USER MAPPING FOR CURRENT_ROLE SERVER sv3 OPTIONS (user 'CURRENT_ROLE'); +CREATE USER MAPPING FOR USER SERVER sv4 OPTIONS (user 'USER'); +CREATE USER MAPPING FOR "user" SERVER sv5 OPTIONS (user '"USER"'); +CREATE USER MAPPING FOR SESSION_USER SERVER sv6 OPTIONS (user 'SESSION_USER'); +CREATE USER MAPPING FOR PUBLIC SERVER sv7 OPTIONS (user 'PUBLIC'); +CREATE USER MAPPING FOR "Public" SERVER sv8 OPTIONS (user '"Public"'); +CREATE USER MAPPING FOR regress_testrolx SERVER sv9 OPTIONS (user 'regress_testrolx'); + +CREATE USER MAPPING FOR nonexistent SERVER sv10 OPTIONS (user 'nonexistent'); -- error; + +SELECT * FROM chkumapping(); + +-- ALTER USER MAPPING +ALTER USER MAPPING FOR CURRENT_USER SERVER sv1 + OPTIONS (SET user 'CURRENT_USER_alt'); +ALTER USER MAPPING FOR "current_user" SERVER sv2 + OPTIONS (SET user '"current_user"_alt'); +ALTER USER MAPPING FOR CURRENT_ROLE SERVER sv3 + OPTIONS (SET user 'CURRENT_ROLE_alt'); +ALTER USER MAPPING FOR USER SERVER sv4 + OPTIONS (SET user 'USER_alt'); +ALTER USER MAPPING FOR "user" SERVER sv5 + OPTIONS (SET user '"user"_alt'); +ALTER USER MAPPING FOR SESSION_USER SERVER sv6 + OPTIONS (SET user 'SESSION_USER_alt'); +ALTER USER MAPPING FOR PUBLIC SERVER sv7 + OPTIONS (SET user 'public_alt'); +ALTER USER MAPPING FOR "Public" SERVER sv8 + OPTIONS (SET user '"Public"_alt'); +ALTER USER MAPPING FOR regress_testrolx SERVER sv9 + OPTIONS (SET user 'regress_testrolx_alt'); + +ALTER USER MAPPING FOR nonexistent SERVER sv10 + OPTIONS (SET user 'nonexistent_alt'); -- error + +SELECT * FROM chkumapping(); + +-- DROP USER MAPPING +DROP USER MAPPING FOR CURRENT_USER SERVER sv1; +DROP USER MAPPING FOR "current_user" SERVER sv2; +DROP USER MAPPING FOR CURRENT_ROLE SERVER sv3; +DROP USER MAPPING FOR USER SERVER sv4; +DROP USER MAPPING FOR "user" SERVER sv5; +DROP USER MAPPING FOR SESSION_USER SERVER sv6; +DROP USER MAPPING FOR PUBLIC SERVER sv7; +DROP USER MAPPING FOR "Public" SERVER sv8; +DROP USER MAPPING FOR regress_testrolx SERVER sv9; + +DROP USER MAPPING FOR nonexistent SERVER sv10; -- error +SELECT * FROM chkumapping(); + +CREATE USER MAPPING FOR CURRENT_USER SERVER sv1 OPTIONS (user 'CURRENT_USER'); +CREATE USER MAPPING FOR "current_user" SERVER sv2 OPTIONS (user '"current_user"'); +CREATE USER MAPPING FOR CURRENT_ROLE SERVER sv3 OPTIONS (user 'CURRENT_ROLE'); +CREATE USER MAPPING FOR USER SERVER sv4 OPTIONS (user 'USER'); +CREATE USER MAPPING FOR "user" SERVER sv5 OPTIONS (user '"USER"'); +CREATE USER MAPPING FOR SESSION_USER SERVER sv6 OPTIONS (user 'SESSION_USER'); +CREATE USER MAPPING FOR PUBLIC SERVER sv7 OPTIONS (user 'PUBLIC'); +CREATE USER MAPPING FOR "Public" SERVER sv8 OPTIONS (user '"Public"'); +CREATE USER MAPPING FOR regress_testrolx SERVER sv9 OPTIONS (user 'regress_testrolx'); +SELECT * FROM chkumapping(); + +-- DROP USER MAPPING IF EXISTS +DROP USER MAPPING IF EXISTS FOR CURRENT_USER SERVER sv1; +SELECT * FROM chkumapping(); +DROP USER MAPPING IF EXISTS FOR "current_user" SERVER sv2; +SELECT * FROM chkumapping(); +DROP USER MAPPING IF EXISTS FOR CURRENT_USER SERVER sv3; +SELECT * FROM chkumapping(); +DROP USER MAPPING IF EXISTS FOR USER SERVER sv4; +SELECT * FROM chkumapping(); +DROP USER MAPPING IF EXISTS FOR "user" SERVER sv5; +SELECT * FROM chkumapping(); +DROP USER MAPPING IF EXISTS FOR SESSION_USER SERVER sv6; +SELECT * FROM chkumapping(); +DROP USER MAPPING IF EXISTS FOR PUBLIC SERVER sv7; +SELECT * FROM chkumapping(); +DROP USER MAPPING IF EXISTS FOR "Public" SERVER sv8; +SELECT * FROM chkumapping(); +DROP USER MAPPING IF EXISTS FOR regress_testrolx SERVER sv9; +SELECT * FROM chkumapping(); + +DROP USER MAPPING IF EXISTS FOR nonexistent SERVER sv10; -- error + +-- GRANT/REVOKE +GRANT regress_testrol0 TO pg_signal_backend; -- success + +SET ROLE pg_signal_backend; --success +RESET ROLE; +CREATE SCHEMA test_roles_schema AUTHORIZATION pg_signal_backend; --success +SET ROLE regress_testrol2; + +UPDATE pg_proc SET proacl = null WHERE proname LIKE 'testagg_'; +SELECT proname, proacl FROM pg_proc WHERE proname LIKE 'testagg_'; + +REVOKE ALL PRIVILEGES ON FUNCTION testagg1(int2) FROM PUBLIC; +REVOKE ALL PRIVILEGES ON FUNCTION testagg2(int2) FROM PUBLIC; +REVOKE ALL PRIVILEGES ON FUNCTION testagg3(int2) FROM PUBLIC; +REVOKE ALL PRIVILEGES ON FUNCTION testagg4(int2) FROM PUBLIC; +REVOKE ALL PRIVILEGES ON FUNCTION testagg5(int2) FROM PUBLIC; +REVOKE ALL PRIVILEGES ON FUNCTION testagg6(int2) FROM PUBLIC; +REVOKE ALL PRIVILEGES ON FUNCTION testagg7(int2) FROM PUBLIC; +REVOKE ALL PRIVILEGES ON FUNCTION testagg8(int2) FROM PUBLIC; + +GRANT ALL PRIVILEGES ON FUNCTION testagg1(int2) TO PUBLIC; +GRANT ALL PRIVILEGES ON FUNCTION testagg2(int2) TO CURRENT_USER; +GRANT ALL PRIVILEGES ON FUNCTION testagg3(int2) TO "current_user"; +GRANT ALL PRIVILEGES ON FUNCTION testagg4(int2) TO CURRENT_ROLE; +GRANT ALL PRIVILEGES ON FUNCTION testagg5(int2) TO SESSION_USER; +GRANT ALL PRIVILEGES ON FUNCTION testagg6(int2) TO "Public"; +GRANT ALL PRIVILEGES ON FUNCTION testagg7(int2) TO regress_testrolx; +GRANT ALL PRIVILEGES ON FUNCTION testagg8(int2) TO "public"; +GRANT ALL PRIVILEGES ON FUNCTION testagg9(int2) + TO current_user, public, regress_testrolx; + +SELECT proname, proacl FROM pg_proc WHERE proname LIKE 'testagg_'; + +GRANT ALL PRIVILEGES ON FUNCTION testagga(int2) TO USER; --error +GRANT ALL PRIVILEGES ON FUNCTION testagga(int2) TO NONE; --error +GRANT ALL PRIVILEGES ON FUNCTION testagga(int2) TO "none"; --error + +SELECT proname, proacl FROM pg_proc WHERE proname LIKE 'testagg_'; + +REVOKE ALL PRIVILEGES ON FUNCTION testagg1(int2) FROM PUBLIC; +REVOKE ALL PRIVILEGES ON FUNCTION testagg2(int2) FROM CURRENT_USER; +REVOKE ALL PRIVILEGES ON FUNCTION testagg3(int2) FROM "current_user"; +REVOKE ALL PRIVILEGES ON FUNCTION testagg4(int2) FROM CURRENT_ROLE; +REVOKE ALL PRIVILEGES ON FUNCTION testagg5(int2) FROM SESSION_USER; +REVOKE ALL PRIVILEGES ON FUNCTION testagg6(int2) FROM "Public"; +REVOKE ALL PRIVILEGES ON FUNCTION testagg7(int2) FROM regress_testrolx; +REVOKE ALL PRIVILEGES ON FUNCTION testagg8(int2) FROM "public"; +REVOKE ALL PRIVILEGES ON FUNCTION testagg9(int2) + FROM current_user, public, regress_testrolx; + +SELECT proname, proacl FROM pg_proc WHERE proname LIKE 'testagg_'; + +REVOKE ALL PRIVILEGES ON FUNCTION testagga(int2) FROM USER; --error +REVOKE ALL PRIVILEGES ON FUNCTION testagga(int2) FROM NONE; --error +REVOKE ALL PRIVILEGES ON FUNCTION testagga(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 + +DROP SCHEMA test_roles_schema; +DROP OWNED BY regress_testrol0, "Public", "current_role", "current_user", regress_testrol1, regress_testrol2, regress_testrolx CASCADE; +DROP ROLE regress_testrol0, regress_testrol1, regress_testrol2, regress_testrolx; +DROP ROLE "Public", "None", "current_role", "current_user", "session_user", "user"; +DROP ROLE regress_role_haspriv, regress_role_nopriv; |