diff --git a/src/test/modules/Makefile b/src/test/modules/Makefile index dfd0956..60d6d7b 100644 --- a/src/test/modules/Makefile +++ b/src/test/modules/Makefile @@ -19,6 +19,7 @@ SUBDIRS = \ test_rbtree \ test_rls_hooks \ test_shm_mq \ + unsafe_tests \ worker_spi $(recurse) 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..321252f --- /dev/null +++ b/src/test/modules/unsafe_tests/Makefile @@ -0,0 +1,14 @@ +# src/test/modules/unsafe_tests/Makefile + +REGRESS = rolenames + +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..a7e5b2a --- /dev/null +++ b/src/test/modules/unsafe_tests/README @@ -0,0 +1,8 @@ +This directory doesn't actually contain any extension module. + +What it is 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/rolenames.out b/src/test/modules/unsafe_tests/expected/rolenames.out new file mode 100644 index 0000000..03c1a25 --- /dev/null +++ b/src/test/modules/unsafe_tests/expected/rolenames.out @@ -0,0 +1,1010 @@ +CREATE OR REPLACE 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_USER, 'current_user'), + (SESSION_USER, 'session_user'), + ('current_user', '-'), + ('session_user', '-'), + ('Public', '-'), + ('None', '-')) + AS v(uname, keyword) + ON (r.rolname = v.uname) + ORDER BY 1; +$$ LANGUAGE SQL; +CREATE OR REPLACE 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_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; +$$ LANGUAGE SQL; +CREATE OR REPLACE 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; +$$ 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_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: syntax error at or near "current_role" +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_user | - | f | f + regress_testrol1 | session_user | t | f + regress_testrol2 | current_user | f | f + session_user | - | f | f +(6 rows) + +ALTER ROLE CURRENT_USER WITH REPLICATION; +SELECT * FROM chkrolattr(); + role | rolekeyword | canlogin | replication +------------------+--------------+----------+------------- + None | - | f | f + Public | - | f | f + current_user | - | f | f + regress_testrol1 | session_user | t | f + regress_testrol2 | current_user | f | t + session_user | - | f | f +(6 rows) + +ALTER ROLE "current_user" WITH REPLICATION; +SELECT * FROM chkrolattr(); + role | rolekeyword | canlogin | replication +------------------+--------------+----------+------------- + None | - | f | f + Public | - | f | f + current_user | - | f | t + regress_testrol1 | session_user | t | f + regress_testrol2 | current_user | f | t + session_user | - | f | f +(6 rows) + +ALTER ROLE SESSION_USER WITH REPLICATION; +SELECT * FROM chkrolattr(); + role | rolekeyword | canlogin | replication +------------------+--------------+----------+------------- + None | - | f | f + Public | - | f | f + current_user | - | f | t + regress_testrol1 | session_user | t | t + regress_testrol2 | current_user | f | t + session_user | - | f | f +(6 rows) + +ALTER ROLE "session_user" WITH REPLICATION; +SELECT * FROM chkrolattr(); + role | rolekeyword | canlogin | replication +------------------+--------------+----------+------------- + None | - | f | f + Public | - | f | f + current_user | - | f | t + regress_testrol1 | session_user | t | t + regress_testrol2 | current_user | f | t + session_user | - | f | t +(6 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_user | - | f | t + regress_testrol1 | session_user | t | t + regress_testrol2 | current_user | f | t + session_user | - | f | t +(6 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_user | - | f | t + regress_testrol1 | session_user | t | f + regress_testrol2 | current_user | f | f + session_user | - | f | t +(6 rows) + +ROLLBACK; +ALTER ROLE USER WITH LOGIN; -- error +ERROR: syntax error at or near "USER" +LINE 1: ALTER ROLE USER WITH LOGIN; + ^ +ALTER ROLE CURRENT_ROLE WITH LOGIN; --error +ERROR: syntax error at or near "CURRENT_ROLE" +LINE 1: ALTER ROLE CURRENT_ROLE 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_user | - | f | f + regress_testrol1 | session_user | t | f + regress_testrol2 | current_user | f | f + session_user | - | f | f +(6 rows) + +ALTER USER CURRENT_USER WITH REPLICATION; +SELECT * FROM chkrolattr(); + role | rolekeyword | canlogin | replication +------------------+--------------+----------+------------- + None | - | f | f + Public | - | f | f + current_user | - | f | f + regress_testrol1 | session_user | t | f + regress_testrol2 | current_user | f | t + session_user | - | f | f +(6 rows) + +ALTER USER "current_user" WITH REPLICATION; +SELECT * FROM chkrolattr(); + role | rolekeyword | canlogin | replication +------------------+--------------+----------+------------- + None | - | f | f + Public | - | f | f + current_user | - | f | t + regress_testrol1 | session_user | t | f + regress_testrol2 | current_user | f | t + session_user | - | f | f +(6 rows) + +ALTER USER SESSION_USER WITH REPLICATION; +SELECT * FROM chkrolattr(); + role | rolekeyword | canlogin | replication +------------------+--------------+----------+------------- + None | - | f | f + Public | - | f | f + current_user | - | f | t + regress_testrol1 | session_user | t | t + regress_testrol2 | current_user | f | t + session_user | - | f | f +(6 rows) + +ALTER USER "session_user" WITH REPLICATION; +SELECT * FROM chkrolattr(); + role | rolekeyword | canlogin | replication +------------------+--------------+----------+------------- + None | - | f | f + Public | - | f | f + current_user | - | f | t + regress_testrol1 | session_user | t | t + regress_testrol2 | current_user | f | t + session_user | - | f | t +(6 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_user | - | f | t + regress_testrol1 | session_user | t | t + regress_testrol2 | current_user | f | t + session_user | - | f | t +(6 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_user | - | f | t + regress_testrol1 | session_user | t | f + regress_testrol2 | current_user | f | f + session_user | - | f | t +(6 rows) + +ROLLBACK; +ALTER USER USER WITH LOGIN; -- error +ERROR: syntax error at or near "USER" +LINE 1: ALTER USER USER WITH LOGIN; + ^ +ALTER USER CURRENT_ROLE WITH LOGIN; -- error +ERROR: syntax error at or near "CURRENT_ROLE" +LINE 1: ALTER USER CURRENT_ROLE 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_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_user | {application_name=FOO} +(4 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_user | {application_name=FOO} +(4 rows) + +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 CURRENT_ROLE SET application_name to 'BAZ'; -- error +ERROR: syntax error at or near "CURRENT_ROLE" +LINE 1: ALTER ROLE CURRENT_ROLE SET application_name to 'BAZ'; + ^ +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_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_user | {application_name=FOO} +(4 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_user | {application_name=FOO} +(4 rows) + +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 CURRENT_USER SET application_name to 'BAZ'; -- error +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 SESSION_USER; +CREATE SCHEMA newschema4 AUTHORIZATION regress_testrolx; +CREATE SCHEMA newschema5 AUTHORIZATION "Public"; +CREATE SCHEMA newschema6 AUTHORIZATION USER; -- error +ERROR: syntax error at or near "USER" +LINE 1: CREATE SCHEMA newschema6 AUTHORIZATION USER; + ^ +CREATE SCHEMA newschema6 AUTHORIZATION CURRENT_ROLE; -- error +ERROR: syntax error at or near "CURRENT_ROLE" +LINE 1: CREATE SCHEMA newschema6 AUTHORIZATION CURRENT_ROLE; + ^ +CREATE SCHEMA newschema6 AUTHORIZATION PUBLIC; -- error +ERROR: role "public" does not exist +CREATE SCHEMA newschema6 AUTHORIZATION "public"; -- error +ERROR: role "public" does not exist +CREATE SCHEMA newschema6 AUTHORIZATION NONE; -- error +ERROR: role name "none" is reserved +LINE 1: CREATE SCHEMA newschema6 AUTHORIZATION NONE; + ^ +CREATE SCHEMA newschema6 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_testrol1 + newschema4 | regress_testrolx + newschema5 | Public +(5 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 SESSION_USER; +NOTICE: schema "newschema3" already exists, skipping +CREATE SCHEMA IF NOT EXISTS newschema4 AUTHORIZATION regress_testrolx; +NOTICE: schema "newschema4" already exists, skipping +CREATE SCHEMA IF NOT EXISTS newschema5 AUTHORIZATION "Public"; +NOTICE: schema "newschema5" already exists, skipping +CREATE SCHEMA IF NOT EXISTS newschema6 AUTHORIZATION USER; -- error +ERROR: syntax error at or near "USER" +LINE 1: CREATE SCHEMA IF NOT EXISTS newschema6 AUTHORIZATION USER; + ^ +CREATE SCHEMA IF NOT EXISTS newschema6 AUTHORIZATION CURRENT_ROLE; -- error +ERROR: syntax error at or near "CURRENT_ROLE" +LINE 1: ...ATE SCHEMA IF NOT EXISTS newschema6 AUTHORIZATION CURRENT_RO... + ^ +CREATE SCHEMA IF NOT EXISTS newschema6 AUTHORIZATION PUBLIC; -- error +ERROR: role "public" does not exist +CREATE SCHEMA IF NOT EXISTS newschema6 AUTHORIZATION "public"; -- error +ERROR: role "public" does not exist +CREATE SCHEMA IF NOT EXISTS newschema6 AUTHORIZATION NONE; -- error +ERROR: role name "none" is reserved +LINE 1: CREATE SCHEMA IF NOT EXISTS newschema6 AUTHORIZATION NONE; + ^ +CREATE SCHEMA IF NOT EXISTS newschema6 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_testrol1 + newschema4 | regress_testrolx + newschema5 | Public +(5 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); +\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 SESSION_USER; +ALTER TABLE testtab4 OWNER TO regress_testrolx; +ALTER TABLE testtab5 OWNER TO "Public"; +ALTER TABLE testtab6 OWNER TO CURRENT_ROLE; -- error +ERROR: syntax error at or near "CURRENT_ROLE" +LINE 1: ALTER TABLE testtab6 OWNER TO CURRENT_ROLE; + ^ +ALTER TABLE testtab6 OWNER TO USER; --error +ERROR: syntax error at or near "USER" +LINE 1: ALTER TABLE testtab6 OWNER TO USER; + ^ +ALTER TABLE testtab6 OWNER TO PUBLIC; -- error +ERROR: role "public" does not exist +ALTER TABLE testtab6 OWNER TO "public"; -- error +ERROR: role "public" does not exist +ALTER TABLE testtab6 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_testrol1 + testtab4 | regress_testrolx + testtab5 | Public + testtab6 | regress_testrol0 +(6 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 testagg5(int2) (SFUNC = int2_sum, STYPE = int8); +ERROR: function "testagg5" already exists with same argument types +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); +\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 SESSION_USER; +ALTER AGGREGATE testagg4(int2) OWNER TO regress_testrolx; +ALTER AGGREGATE testagg5(int2) OWNER TO "Public"; +ALTER AGGREGATE testagg5(int2) OWNER TO CURRENT_ROLE; -- error +ERROR: syntax error at or near "CURRENT_ROLE" +LINE 1: ALTER AGGREGATE testagg5(int2) OWNER TO CURRENT_ROLE; + ^ +ALTER AGGREGATE testagg5(int2) OWNER TO USER; -- error +ERROR: syntax error at or near "USER" +LINE 1: ALTER AGGREGATE testagg5(int2) OWNER TO USER; + ^ +ALTER AGGREGATE testagg5(int2) OWNER TO PUBLIC; -- error +ERROR: role "public" does not exist +ALTER AGGREGATE testagg5(int2) OWNER TO "public"; -- error +ERROR: role "public" does not exist +ALTER AGGREGATE testagg5(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_testrol1 + testagg4 | regress_testrolx + testagg5 | Public + testagg6 | regress_testrol0 + testagg7 | regress_testrol0 + testagg8 | regress_testrol0 + testagg9 | regress_testrol0 +(9 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 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 USER SERVER sv3 OPTIONS (user 'USER'); +CREATE USER MAPPING FOR "user" SERVER sv4 OPTIONS (user '"USER"'); +CREATE USER MAPPING FOR SESSION_USER SERVER sv5 OPTIONS (user 'SESSION_USER'); +CREATE USER MAPPING FOR PUBLIC SERVER sv6 OPTIONS (user 'PUBLIC'); +CREATE USER MAPPING FOR "Public" SERVER sv7 OPTIONS (user '"Public"'); +CREATE USER MAPPING FOR regress_testrolx SERVER sv8 OPTIONS (user 'regress_testrolx'); +CREATE USER MAPPING FOR CURRENT_ROLE SERVER sv9 + OPTIONS (user 'CURRENT_ROLE'); -- error +ERROR: syntax error at or near "CURRENT_ROLE" +LINE 1: CREATE USER MAPPING FOR CURRENT_ROLE SERVER sv9 + ^ +CREATE USER MAPPING FOR nonexistent SERVER sv9 + 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=USER} + user | sv4 | {"user=\"USER\""} + regress_testrol1 | sv5 | {user=SESSION_USER} + | sv6 | {user=PUBLIC} + Public | sv7 | {"user=\"Public\""} + regress_testrolx | sv8 | {user=regress_testrolx} +(8 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 USER SERVER sv3 + OPTIONS (SET user 'USER_alt'); +ALTER USER MAPPING FOR "user" SERVER sv4 + OPTIONS (SET user '"user"_alt'); +ALTER USER MAPPING FOR SESSION_USER SERVER sv5 + OPTIONS (SET user 'SESSION_USER_alt'); +ALTER USER MAPPING FOR PUBLIC SERVER sv6 + OPTIONS (SET user 'public_alt'); +ALTER USER MAPPING FOR "Public" SERVER sv7 + OPTIONS (SET user '"Public"_alt'); +ALTER USER MAPPING FOR regress_testrolx SERVER sv8 + OPTIONS (SET user 'regress_testrolx_alt'); +ALTER USER MAPPING FOR CURRENT_ROLE SERVER sv9 + OPTIONS (SET user 'CURRENT_ROLE_alt'); +ERROR: syntax error at or near "CURRENT_ROLE" +LINE 1: ALTER USER MAPPING FOR CURRENT_ROLE SERVER sv9 + ^ +ALTER USER MAPPING FOR nonexistent SERVER sv9 + 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=USER_alt} + user | sv4 | {"user=\"user\"_alt"} + regress_testrol1 | sv5 | {user=SESSION_USER_alt} + | sv6 | {user=public_alt} + Public | sv7 | {"user=\"Public\"_alt"} + regress_testrolx | sv8 | {user=regress_testrolx_alt} +(8 rows) + +-- DROP USER MAPPING +DROP USER MAPPING FOR CURRENT_USER SERVER sv1; +DROP USER MAPPING FOR "current_user" SERVER sv2; +DROP USER MAPPING FOR USER SERVER sv3; +DROP USER MAPPING FOR "user" SERVER sv4; +DROP USER MAPPING FOR SESSION_USER SERVER sv5; +DROP USER MAPPING FOR PUBLIC SERVER sv6; +DROP USER MAPPING FOR "Public" SERVER sv7; +DROP USER MAPPING FOR regress_testrolx SERVER sv8; +DROP USER MAPPING FOR CURRENT_ROLE SERVER sv9; -- error +ERROR: syntax error at or near "CURRENT_ROLE" +LINE 1: DROP USER MAPPING FOR CURRENT_ROLE SERVER sv9; + ^ +DROP USER MAPPING FOR nonexistent SERVER sv; -- 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 USER SERVER sv3 OPTIONS (user 'USER'); +CREATE USER MAPPING FOR "user" SERVER sv4 OPTIONS (user '"USER"'); +CREATE USER MAPPING FOR SESSION_USER SERVER sv5 OPTIONS (user 'SESSION_USER'); +CREATE USER MAPPING FOR PUBLIC SERVER sv6 OPTIONS (user 'PUBLIC'); +CREATE USER MAPPING FOR "Public" SERVER sv7 OPTIONS (user '"Public"'); +CREATE USER MAPPING FOR regress_testrolx SERVER sv8 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=USER} + user | sv4 | {"user=\"USER\""} + regress_testrol1 | sv5 | {user=SESSION_USER} + | sv6 | {user=PUBLIC} + Public | sv7 | {"user=\"Public\""} + regress_testrolx | sv8 | {user=regress_testrolx} +(8 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=USER} + user | sv4 | {"user=\"USER\""} + regress_testrol1 | sv5 | {user=SESSION_USER} + | sv6 | {user=PUBLIC} + Public | sv7 | {"user=\"Public\""} + regress_testrolx | sv8 | {user=regress_testrolx} +(7 rows) + +DROP USER MAPPING IF EXISTS FOR "current_user" SERVER sv2; +SELECT * FROM chkumapping(); + umname | umserver | umoptions +------------------+----------+------------------------- + regress_testrol2 | sv3 | {user=USER} + user | sv4 | {"user=\"USER\""} + regress_testrol1 | sv5 | {user=SESSION_USER} + | sv6 | {user=PUBLIC} + Public | sv7 | {"user=\"Public\""} + regress_testrolx | sv8 | {user=regress_testrolx} +(6 rows) + +DROP USER MAPPING IF EXISTS FOR USER SERVER sv3; +SELECT * FROM chkumapping(); + umname | umserver | umoptions +------------------+----------+------------------------- + user | sv4 | {"user=\"USER\""} + regress_testrol1 | sv5 | {user=SESSION_USER} + | sv6 | {user=PUBLIC} + Public | sv7 | {"user=\"Public\""} + regress_testrolx | sv8 | {user=regress_testrolx} +(5 rows) + +DROP USER MAPPING IF EXISTS FOR "user" SERVER sv4; +SELECT * FROM chkumapping(); + umname | umserver | umoptions +------------------+----------+------------------------- + regress_testrol1 | sv5 | {user=SESSION_USER} + | sv6 | {user=PUBLIC} + Public | sv7 | {"user=\"Public\""} + regress_testrolx | sv8 | {user=regress_testrolx} +(4 rows) + +DROP USER MAPPING IF EXISTS FOR SESSION_USER SERVER sv5; +SELECT * FROM chkumapping(); + umname | umserver | umoptions +------------------+----------+------------------------- + | sv6 | {user=PUBLIC} + Public | sv7 | {"user=\"Public\""} + regress_testrolx | sv8 | {user=regress_testrolx} +(3 rows) + +DROP USER MAPPING IF EXISTS FOR PUBLIC SERVER sv6; +SELECT * FROM chkumapping(); + umname | umserver | umoptions +------------------+----------+------------------------- + Public | sv7 | {"user=\"Public\""} + regress_testrolx | sv8 | {user=regress_testrolx} +(2 rows) + +DROP USER MAPPING IF EXISTS FOR "Public" SERVER sv7; +SELECT * FROM chkumapping(); + umname | umserver | umoptions +------------------+----------+------------------------- + regress_testrolx | sv8 | {user=regress_testrolx} +(1 row) + +DROP USER MAPPING IF EXISTS FOR regress_testrolx SERVER sv8; +SELECT * FROM chkumapping(); + umname | umserver | umoptions +--------+----------+----------- +(0 rows) + +DROP USER MAPPING IF EXISTS FOR CURRENT_ROLE SERVER sv9; --error +ERROR: syntax error at or near "CURRENT_ROLE" +LINE 1: DROP USER MAPPING IF EXISTS FOR CURRENT_ROLE SERVER sv9; + ^ +DROP USER MAPPING IF EXISTS FOR nonexistent SERVER sv9; -- 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 | +(9 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 SESSION_USER; +GRANT ALL PRIVILEGES ON FUNCTION testagg5(int2) TO "Public"; +GRANT ALL PRIVILEGES ON FUNCTION testagg6(int2) TO regress_testrolx; +GRANT ALL PRIVILEGES ON FUNCTION testagg7(int2) TO "public"; +GRANT ALL PRIVILEGES ON FUNCTION testagg8(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_testrol1=X/regress_testrol1,current_user=X/regress_testrol1} + testagg4 | {regress_testrolx=X/regress_testrolx,regress_testrol1=X/regress_testrolx} + testagg5 | {Public=X/Public} + testagg6 | {regress_testrol0=X/regress_testrol0,regress_testrolx=X/regress_testrol0} + testagg7 | {regress_testrol0=X/regress_testrol0,=X/regress_testrol0} + testagg8 | {regress_testrol0=X/regress_testrol0,regress_testrol2=X/regress_testrol0,=X/regress_testrol0,regress_testrolx=X/regress_testrol0} + testagg9 | +(9 rows) + +GRANT ALL PRIVILEGES ON FUNCTION testagg9(int2) TO CURRENT_ROLE; --error +ERROR: syntax error at or near "CURRENT_ROLE" +LINE 1: ...RANT ALL PRIVILEGES ON FUNCTION testagg9(int2) TO CURRENT_RO... + ^ +GRANT ALL PRIVILEGES ON FUNCTION testagg9(int2) TO USER; --error +ERROR: syntax error at or near "USER" +LINE 1: GRANT ALL PRIVILEGES ON FUNCTION testagg9(int2) TO USER; + ^ +GRANT ALL PRIVILEGES ON FUNCTION testagg9(int2) TO NONE; --error +ERROR: role name "none" is reserved +LINE 1: GRANT ALL PRIVILEGES ON FUNCTION testagg9(int2) TO NONE; + ^ +GRANT ALL PRIVILEGES ON FUNCTION testagg9(int2) TO "none"; --error +ERROR: role name "none" is reserved +LINE 1: GRANT ALL PRIVILEGES ON FUNCTION testagg9(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_testrol1=X/regress_testrol1,current_user=X/regress_testrol1} + testagg4 | {regress_testrolx=X/regress_testrolx,regress_testrol1=X/regress_testrolx} + testagg5 | {Public=X/Public} + testagg6 | {regress_testrol0=X/regress_testrol0,regress_testrolx=X/regress_testrol0} + testagg7 | {regress_testrol0=X/regress_testrol0,=X/regress_testrol0} + testagg8 | {regress_testrol0=X/regress_testrol0,regress_testrol2=X/regress_testrol0,=X/regress_testrol0,regress_testrolx=X/regress_testrol0} + testagg9 | +(9 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 SESSION_USER; +REVOKE ALL PRIVILEGES ON FUNCTION testagg5(int2) FROM "Public"; +REVOKE ALL PRIVILEGES ON FUNCTION testagg6(int2) FROM regress_testrolx; +REVOKE ALL PRIVILEGES ON FUNCTION testagg7(int2) FROM "public"; +REVOKE ALL PRIVILEGES ON FUNCTION testagg8(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_testrol1=X/regress_testrol1} + testagg4 | {regress_testrolx=X/regress_testrolx} + testagg5 | {} + testagg6 | {regress_testrol0=X/regress_testrol0} + testagg7 | {regress_testrol0=X/regress_testrol0} + testagg8 | {regress_testrol0=X/regress_testrol0} + testagg9 | +(9 rows) + +REVOKE ALL PRIVILEGES ON FUNCTION testagg9(int2) FROM CURRENT_ROLE; --error +ERROR: syntax error at or near "CURRENT_ROLE" +LINE 1: ...KE ALL PRIVILEGES ON FUNCTION testagg9(int2) FROM CURRENT_RO... + ^ +REVOKE ALL PRIVILEGES ON FUNCTION testagg9(int2) FROM USER; --error +ERROR: syntax error at or near "USER" +LINE 1: REVOKE ALL PRIVILEGES ON FUNCTION testagg9(int2) FROM USER; + ^ +REVOKE ALL PRIVILEGES ON FUNCTION testagg9(int2) FROM NONE; --error +ERROR: role name "none" is reserved +LINE 1: REVOKE ALL PRIVILEGES ON FUNCTION testagg9(int2) FROM NONE; + ^ +REVOKE ALL PRIVILEGES ON FUNCTION testagg9(int2) FROM "none"; --error +ERROR: role name "none" is reserved +LINE 1: ...EVOKE ALL PRIVILEGES ON FUNCTION testagg9(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_testrol1=X/regress_testrol1} + testagg4 | {regress_testrolx=X/regress_testrolx} + testagg5 | {} + testagg6 | {regress_testrol0=X/regress_testrol0} + testagg7 | {regress_testrol0=X/regress_testrol0} + testagg8 | {regress_testrol0=X/regress_testrol0} + testagg9 | +(9 rows) + +-- DEFAULT MONITORING ROLES +CREATE ROLE regress_role_haspriv; +CREATE ROLE regress_role_nopriv; +-- pg_read_all_stats +GRANT pg_read_all_stats TO regress_role_haspriv; +SET SESSION AUTHORIZATION regress_role_haspriv; +-- returns true with role member of pg_read_all_stats +SELECT COUNT(*) = 0 AS haspriv FROM pg_stat_activity + WHERE query = ''; + 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 = ''; + haspriv +--------- + f +(1 row) + +RESET SESSION AUTHORIZATION; +REVOKE pg_read_all_stats FROM regress_role_haspriv; +-- pg_read_all_settings +GRANT pg_read_all_settings TO regress_role_haspriv; +BEGIN; +-- A GUC using GUC_SUPERUSER_ONLY is useful for negative tests. +SET LOCAL session_preload_libraries TO 'path-to-preload-libraries'; +SET SESSION AUTHORIZATION regress_role_haspriv; +-- passes with role member of pg_read_all_settings +SHOW session_preload_libraries; + session_preload_libraries +----------------------------- + "path-to-preload-libraries" +(1 row) + +SET SESSION AUTHORIZATION regress_role_nopriv; +-- fails with role not member of pg_read_all_settings +SHOW session_preload_libraries; +ERROR: must be superuser or a member of pg_read_all_settings to examine "session_preload_libraries" +RESET SESSION AUTHORIZATION; +ERROR: current transaction is aborted, commands ignored until end of transaction block +ROLLBACK; +REVOKE pg_read_all_settings FROM regress_role_haspriv; +-- clean up +\c +DROP SCHEMA test_roles_schema; +DROP OWNED BY regress_testrol0, "Public", "current_user", regress_testrol1, regress_testrol2, regress_testrolx CASCADE; +DROP ROLE regress_testrol0, regress_testrol1, regress_testrol2, regress_testrolx; +DROP ROLE "Public", "None", "current_user", "session_user", "user"; +DROP ROLE regress_role_haspriv, regress_role_nopriv; diff --git a/src/test/modules/unsafe_tests/sql/rolenames.sql b/src/test/modules/unsafe_tests/sql/rolenames.sql new file mode 100644 index 0000000..5a3cf44 --- /dev/null +++ b/src/test/modules/unsafe_tests/sql/rolenames.sql @@ -0,0 +1,488 @@ +CREATE OR REPLACE 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_USER, 'current_user'), + (SESSION_USER, 'session_user'), + ('current_user', '-'), + ('session_user', '-'), + ('Public', '-'), + ('None', '-')) + AS v(uname, keyword) + ON (r.rolname = v.uname) + ORDER BY 1; +$$ LANGUAGE SQL; + +CREATE OR REPLACE 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_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; +$$ LANGUAGE SQL; + +CREATE OR REPLACE 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; +$$ 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_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_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 CURRENT_ROLE 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_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 CURRENT_ROLE 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_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_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 CURRENT_ROLE SET application_name to 'BAZ'; -- error +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_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_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 CURRENT_USER SET application_name to 'BAZ'; -- error +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 SESSION_USER; +CREATE SCHEMA newschema4 AUTHORIZATION regress_testrolx; +CREATE SCHEMA newschema5 AUTHORIZATION "Public"; + +CREATE SCHEMA newschema6 AUTHORIZATION USER; -- error +CREATE SCHEMA newschema6 AUTHORIZATION CURRENT_ROLE; -- error +CREATE SCHEMA newschema6 AUTHORIZATION PUBLIC; -- error +CREATE SCHEMA newschema6 AUTHORIZATION "public"; -- error +CREATE SCHEMA newschema6 AUTHORIZATION NONE; -- error +CREATE SCHEMA newschema6 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 SESSION_USER; +CREATE SCHEMA IF NOT EXISTS newschema4 AUTHORIZATION regress_testrolx; +CREATE SCHEMA IF NOT EXISTS newschema5 AUTHORIZATION "Public"; + +CREATE SCHEMA IF NOT EXISTS newschema6 AUTHORIZATION USER; -- error +CREATE SCHEMA IF NOT EXISTS newschema6 AUTHORIZATION CURRENT_ROLE; -- error +CREATE SCHEMA IF NOT EXISTS newschema6 AUTHORIZATION PUBLIC; -- error +CREATE SCHEMA IF NOT EXISTS newschema6 AUTHORIZATION "public"; -- error +CREATE SCHEMA IF NOT EXISTS newschema6 AUTHORIZATION NONE; -- error +CREATE SCHEMA IF NOT EXISTS newschema6 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); + +\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 SESSION_USER; +ALTER TABLE testtab4 OWNER TO regress_testrolx; +ALTER TABLE testtab5 OWNER TO "Public"; + +ALTER TABLE testtab6 OWNER TO CURRENT_ROLE; -- error +ALTER TABLE testtab6 OWNER TO USER; --error +ALTER TABLE testtab6 OWNER TO PUBLIC; -- error +ALTER TABLE testtab6 OWNER TO "public"; -- error +ALTER TABLE testtab6 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 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); + +\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 SESSION_USER; +ALTER AGGREGATE testagg4(int2) OWNER TO regress_testrolx; +ALTER AGGREGATE testagg5(int2) OWNER TO "Public"; + +ALTER AGGREGATE testagg5(int2) OWNER TO CURRENT_ROLE; -- error +ALTER AGGREGATE testagg5(int2) OWNER TO USER; -- error +ALTER AGGREGATE testagg5(int2) OWNER TO PUBLIC; -- error +ALTER AGGREGATE testagg5(int2) OWNER TO "public"; -- error +ALTER AGGREGATE testagg5(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 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 USER SERVER sv3 OPTIONS (user 'USER'); +CREATE USER MAPPING FOR "user" SERVER sv4 OPTIONS (user '"USER"'); +CREATE USER MAPPING FOR SESSION_USER SERVER sv5 OPTIONS (user 'SESSION_USER'); +CREATE USER MAPPING FOR PUBLIC SERVER sv6 OPTIONS (user 'PUBLIC'); +CREATE USER MAPPING FOR "Public" SERVER sv7 OPTIONS (user '"Public"'); +CREATE USER MAPPING FOR regress_testrolx SERVER sv8 OPTIONS (user 'regress_testrolx'); + +CREATE USER MAPPING FOR CURRENT_ROLE SERVER sv9 + OPTIONS (user 'CURRENT_ROLE'); -- error +CREATE USER MAPPING FOR nonexistent SERVER sv9 + 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 USER SERVER sv3 + OPTIONS (SET user 'USER_alt'); +ALTER USER MAPPING FOR "user" SERVER sv4 + OPTIONS (SET user '"user"_alt'); +ALTER USER MAPPING FOR SESSION_USER SERVER sv5 + OPTIONS (SET user 'SESSION_USER_alt'); +ALTER USER MAPPING FOR PUBLIC SERVER sv6 + OPTIONS (SET user 'public_alt'); +ALTER USER MAPPING FOR "Public" SERVER sv7 + OPTIONS (SET user '"Public"_alt'); +ALTER USER MAPPING FOR regress_testrolx SERVER sv8 + OPTIONS (SET user 'regress_testrolx_alt'); + +ALTER USER MAPPING FOR CURRENT_ROLE SERVER sv9 + OPTIONS (SET user 'CURRENT_ROLE_alt'); +ALTER USER MAPPING FOR nonexistent SERVER sv9 + 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 USER SERVER sv3; +DROP USER MAPPING FOR "user" SERVER sv4; +DROP USER MAPPING FOR SESSION_USER SERVER sv5; +DROP USER MAPPING FOR PUBLIC SERVER sv6; +DROP USER MAPPING FOR "Public" SERVER sv7; +DROP USER MAPPING FOR regress_testrolx SERVER sv8; + +DROP USER MAPPING FOR CURRENT_ROLE SERVER sv9; -- error +DROP USER MAPPING FOR nonexistent SERVER sv; -- 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 USER SERVER sv3 OPTIONS (user 'USER'); +CREATE USER MAPPING FOR "user" SERVER sv4 OPTIONS (user '"USER"'); +CREATE USER MAPPING FOR SESSION_USER SERVER sv5 OPTIONS (user 'SESSION_USER'); +CREATE USER MAPPING FOR PUBLIC SERVER sv6 OPTIONS (user 'PUBLIC'); +CREATE USER MAPPING FOR "Public" SERVER sv7 OPTIONS (user '"Public"'); +CREATE USER MAPPING FOR regress_testrolx SERVER sv8 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 USER SERVER sv3; +SELECT * FROM chkumapping(); +DROP USER MAPPING IF EXISTS FOR "user" SERVER sv4; +SELECT * FROM chkumapping(); +DROP USER MAPPING IF EXISTS FOR SESSION_USER SERVER sv5; +SELECT * FROM chkumapping(); +DROP USER MAPPING IF EXISTS FOR PUBLIC SERVER sv6; +SELECT * FROM chkumapping(); +DROP USER MAPPING IF EXISTS FOR "Public" SERVER sv7; +SELECT * FROM chkumapping(); +DROP USER MAPPING IF EXISTS FOR regress_testrolx SERVER sv8; +SELECT * FROM chkumapping(); + +DROP USER MAPPING IF EXISTS FOR CURRENT_ROLE SERVER sv9; --error +DROP USER MAPPING IF EXISTS FOR nonexistent SERVER sv9; -- 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 SESSION_USER; +GRANT ALL PRIVILEGES ON FUNCTION testagg5(int2) TO "Public"; +GRANT ALL PRIVILEGES ON FUNCTION testagg6(int2) TO regress_testrolx; +GRANT ALL PRIVILEGES ON FUNCTION testagg7(int2) TO "public"; +GRANT ALL PRIVILEGES ON FUNCTION testagg8(int2) + TO current_user, public, regress_testrolx; + +SELECT proname, proacl FROM pg_proc WHERE proname LIKE 'testagg_'; + +GRANT ALL PRIVILEGES ON FUNCTION testagg9(int2) TO CURRENT_ROLE; --error +GRANT ALL PRIVILEGES ON FUNCTION testagg9(int2) TO USER; --error +GRANT ALL PRIVILEGES ON FUNCTION testagg9(int2) TO NONE; --error +GRANT ALL PRIVILEGES ON FUNCTION testagg9(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 SESSION_USER; +REVOKE ALL PRIVILEGES ON FUNCTION testagg5(int2) FROM "Public"; +REVOKE ALL PRIVILEGES ON FUNCTION testagg6(int2) FROM regress_testrolx; +REVOKE ALL PRIVILEGES ON FUNCTION testagg7(int2) FROM "public"; +REVOKE ALL PRIVILEGES ON FUNCTION testagg8(int2) + FROM current_user, public, regress_testrolx; + +SELECT proname, proacl FROM pg_proc WHERE proname LIKE 'testagg_'; + +REVOKE ALL PRIVILEGES ON FUNCTION testagg9(int2) FROM CURRENT_ROLE; --error +REVOKE ALL PRIVILEGES ON FUNCTION testagg9(int2) FROM USER; --error +REVOKE ALL PRIVILEGES ON FUNCTION testagg9(int2) FROM NONE; --error +REVOKE ALL PRIVILEGES ON FUNCTION testagg9(int2) FROM "none"; --error + +SELECT proname, proacl FROM pg_proc WHERE proname LIKE 'testagg_'; + +-- DEFAULT MONITORING ROLES +CREATE ROLE regress_role_haspriv; +CREATE ROLE regress_role_nopriv; + +-- pg_read_all_stats +GRANT pg_read_all_stats TO regress_role_haspriv; +SET SESSION AUTHORIZATION regress_role_haspriv; +-- returns true with role member of pg_read_all_stats +SELECT COUNT(*) = 0 AS haspriv FROM pg_stat_activity + WHERE query = ''; +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 = ''; +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_user", regress_testrol1, regress_testrol2, regress_testrolx CASCADE; +DROP ROLE regress_testrol0, regress_testrol1, regress_testrol2, regress_testrolx; +DROP ROLE "Public", "None", "current_user", "session_user", "user"; +DROP ROLE regress_role_haspriv, regress_role_nopriv; diff --git a/src/test/regress/expected/rolenames.out b/src/test/regress/expected/rolenames.out deleted file mode 100644 index 03c1a25..0000000 --- a/src/test/regress/expected/rolenames.out +++ /dev/null @@ -1,1010 +0,0 @@ -CREATE OR REPLACE 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_USER, 'current_user'), - (SESSION_USER, 'session_user'), - ('current_user', '-'), - ('session_user', '-'), - ('Public', '-'), - ('None', '-')) - AS v(uname, keyword) - ON (r.rolname = v.uname) - ORDER BY 1; -$$ LANGUAGE SQL; -CREATE OR REPLACE 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_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; -$$ LANGUAGE SQL; -CREATE OR REPLACE 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; -$$ 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_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: syntax error at or near "current_role" -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_user | - | f | f - regress_testrol1 | session_user | t | f - regress_testrol2 | current_user | f | f - session_user | - | f | f -(6 rows) - -ALTER ROLE CURRENT_USER WITH REPLICATION; -SELECT * FROM chkrolattr(); - role | rolekeyword | canlogin | replication -------------------+--------------+----------+------------- - None | - | f | f - Public | - | f | f - current_user | - | f | f - regress_testrol1 | session_user | t | f - regress_testrol2 | current_user | f | t - session_user | - | f | f -(6 rows) - -ALTER ROLE "current_user" WITH REPLICATION; -SELECT * FROM chkrolattr(); - role | rolekeyword | canlogin | replication -------------------+--------------+----------+------------- - None | - | f | f - Public | - | f | f - current_user | - | f | t - regress_testrol1 | session_user | t | f - regress_testrol2 | current_user | f | t - session_user | - | f | f -(6 rows) - -ALTER ROLE SESSION_USER WITH REPLICATION; -SELECT * FROM chkrolattr(); - role | rolekeyword | canlogin | replication -------------------+--------------+----------+------------- - None | - | f | f - Public | - | f | f - current_user | - | f | t - regress_testrol1 | session_user | t | t - regress_testrol2 | current_user | f | t - session_user | - | f | f -(6 rows) - -ALTER ROLE "session_user" WITH REPLICATION; -SELECT * FROM chkrolattr(); - role | rolekeyword | canlogin | replication -------------------+--------------+----------+------------- - None | - | f | f - Public | - | f | f - current_user | - | f | t - regress_testrol1 | session_user | t | t - regress_testrol2 | current_user | f | t - session_user | - | f | t -(6 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_user | - | f | t - regress_testrol1 | session_user | t | t - regress_testrol2 | current_user | f | t - session_user | - | f | t -(6 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_user | - | f | t - regress_testrol1 | session_user | t | f - regress_testrol2 | current_user | f | f - session_user | - | f | t -(6 rows) - -ROLLBACK; -ALTER ROLE USER WITH LOGIN; -- error -ERROR: syntax error at or near "USER" -LINE 1: ALTER ROLE USER WITH LOGIN; - ^ -ALTER ROLE CURRENT_ROLE WITH LOGIN; --error -ERROR: syntax error at or near "CURRENT_ROLE" -LINE 1: ALTER ROLE CURRENT_ROLE 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_user | - | f | f - regress_testrol1 | session_user | t | f - regress_testrol2 | current_user | f | f - session_user | - | f | f -(6 rows) - -ALTER USER CURRENT_USER WITH REPLICATION; -SELECT * FROM chkrolattr(); - role | rolekeyword | canlogin | replication -------------------+--------------+----------+------------- - None | - | f | f - Public | - | f | f - current_user | - | f | f - regress_testrol1 | session_user | t | f - regress_testrol2 | current_user | f | t - session_user | - | f | f -(6 rows) - -ALTER USER "current_user" WITH REPLICATION; -SELECT * FROM chkrolattr(); - role | rolekeyword | canlogin | replication -------------------+--------------+----------+------------- - None | - | f | f - Public | - | f | f - current_user | - | f | t - regress_testrol1 | session_user | t | f - regress_testrol2 | current_user | f | t - session_user | - | f | f -(6 rows) - -ALTER USER SESSION_USER WITH REPLICATION; -SELECT * FROM chkrolattr(); - role | rolekeyword | canlogin | replication -------------------+--------------+----------+------------- - None | - | f | f - Public | - | f | f - current_user | - | f | t - regress_testrol1 | session_user | t | t - regress_testrol2 | current_user | f | t - session_user | - | f | f -(6 rows) - -ALTER USER "session_user" WITH REPLICATION; -SELECT * FROM chkrolattr(); - role | rolekeyword | canlogin | replication -------------------+--------------+----------+------------- - None | - | f | f - Public | - | f | f - current_user | - | f | t - regress_testrol1 | session_user | t | t - regress_testrol2 | current_user | f | t - session_user | - | f | t -(6 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_user | - | f | t - regress_testrol1 | session_user | t | t - regress_testrol2 | current_user | f | t - session_user | - | f | t -(6 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_user | - | f | t - regress_testrol1 | session_user | t | f - regress_testrol2 | current_user | f | f - session_user | - | f | t -(6 rows) - -ROLLBACK; -ALTER USER USER WITH LOGIN; -- error -ERROR: syntax error at or near "USER" -LINE 1: ALTER USER USER WITH LOGIN; - ^ -ALTER USER CURRENT_ROLE WITH LOGIN; -- error -ERROR: syntax error at or near "CURRENT_ROLE" -LINE 1: ALTER USER CURRENT_ROLE 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_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_user | {application_name=FOO} -(4 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_user | {application_name=FOO} -(4 rows) - -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 CURRENT_ROLE SET application_name to 'BAZ'; -- error -ERROR: syntax error at or near "CURRENT_ROLE" -LINE 1: ALTER ROLE CURRENT_ROLE SET application_name to 'BAZ'; - ^ -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_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_user | {application_name=FOO} -(4 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_user | {application_name=FOO} -(4 rows) - -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 CURRENT_USER SET application_name to 'BAZ'; -- error -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 SESSION_USER; -CREATE SCHEMA newschema4 AUTHORIZATION regress_testrolx; -CREATE SCHEMA newschema5 AUTHORIZATION "Public"; -CREATE SCHEMA newschema6 AUTHORIZATION USER; -- error -ERROR: syntax error at or near "USER" -LINE 1: CREATE SCHEMA newschema6 AUTHORIZATION USER; - ^ -CREATE SCHEMA newschema6 AUTHORIZATION CURRENT_ROLE; -- error -ERROR: syntax error at or near "CURRENT_ROLE" -LINE 1: CREATE SCHEMA newschema6 AUTHORIZATION CURRENT_ROLE; - ^ -CREATE SCHEMA newschema6 AUTHORIZATION PUBLIC; -- error -ERROR: role "public" does not exist -CREATE SCHEMA newschema6 AUTHORIZATION "public"; -- error -ERROR: role "public" does not exist -CREATE SCHEMA newschema6 AUTHORIZATION NONE; -- error -ERROR: role name "none" is reserved -LINE 1: CREATE SCHEMA newschema6 AUTHORIZATION NONE; - ^ -CREATE SCHEMA newschema6 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_testrol1 - newschema4 | regress_testrolx - newschema5 | Public -(5 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 SESSION_USER; -NOTICE: schema "newschema3" already exists, skipping -CREATE SCHEMA IF NOT EXISTS newschema4 AUTHORIZATION regress_testrolx; -NOTICE: schema "newschema4" already exists, skipping -CREATE SCHEMA IF NOT EXISTS newschema5 AUTHORIZATION "Public"; -NOTICE: schema "newschema5" already exists, skipping -CREATE SCHEMA IF NOT EXISTS newschema6 AUTHORIZATION USER; -- error -ERROR: syntax error at or near "USER" -LINE 1: CREATE SCHEMA IF NOT EXISTS newschema6 AUTHORIZATION USER; - ^ -CREATE SCHEMA IF NOT EXISTS newschema6 AUTHORIZATION CURRENT_ROLE; -- error -ERROR: syntax error at or near "CURRENT_ROLE" -LINE 1: ...ATE SCHEMA IF NOT EXISTS newschema6 AUTHORIZATION CURRENT_RO... - ^ -CREATE SCHEMA IF NOT EXISTS newschema6 AUTHORIZATION PUBLIC; -- error -ERROR: role "public" does not exist -CREATE SCHEMA IF NOT EXISTS newschema6 AUTHORIZATION "public"; -- error -ERROR: role "public" does not exist -CREATE SCHEMA IF NOT EXISTS newschema6 AUTHORIZATION NONE; -- error -ERROR: role name "none" is reserved -LINE 1: CREATE SCHEMA IF NOT EXISTS newschema6 AUTHORIZATION NONE; - ^ -CREATE SCHEMA IF NOT EXISTS newschema6 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_testrol1 - newschema4 | regress_testrolx - newschema5 | Public -(5 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); -\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 SESSION_USER; -ALTER TABLE testtab4 OWNER TO regress_testrolx; -ALTER TABLE testtab5 OWNER TO "Public"; -ALTER TABLE testtab6 OWNER TO CURRENT_ROLE; -- error -ERROR: syntax error at or near "CURRENT_ROLE" -LINE 1: ALTER TABLE testtab6 OWNER TO CURRENT_ROLE; - ^ -ALTER TABLE testtab6 OWNER TO USER; --error -ERROR: syntax error at or near "USER" -LINE 1: ALTER TABLE testtab6 OWNER TO USER; - ^ -ALTER TABLE testtab6 OWNER TO PUBLIC; -- error -ERROR: role "public" does not exist -ALTER TABLE testtab6 OWNER TO "public"; -- error -ERROR: role "public" does not exist -ALTER TABLE testtab6 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_testrol1 - testtab4 | regress_testrolx - testtab5 | Public - testtab6 | regress_testrol0 -(6 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 testagg5(int2) (SFUNC = int2_sum, STYPE = int8); -ERROR: function "testagg5" already exists with same argument types -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); -\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 SESSION_USER; -ALTER AGGREGATE testagg4(int2) OWNER TO regress_testrolx; -ALTER AGGREGATE testagg5(int2) OWNER TO "Public"; -ALTER AGGREGATE testagg5(int2) OWNER TO CURRENT_ROLE; -- error -ERROR: syntax error at or near "CURRENT_ROLE" -LINE 1: ALTER AGGREGATE testagg5(int2) OWNER TO CURRENT_ROLE; - ^ -ALTER AGGREGATE testagg5(int2) OWNER TO USER; -- error -ERROR: syntax error at or near "USER" -LINE 1: ALTER AGGREGATE testagg5(int2) OWNER TO USER; - ^ -ALTER AGGREGATE testagg5(int2) OWNER TO PUBLIC; -- error -ERROR: role "public" does not exist -ALTER AGGREGATE testagg5(int2) OWNER TO "public"; -- error -ERROR: role "public" does not exist -ALTER AGGREGATE testagg5(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_testrol1 - testagg4 | regress_testrolx - testagg5 | Public - testagg6 | regress_testrol0 - testagg7 | regress_testrol0 - testagg8 | regress_testrol0 - testagg9 | regress_testrol0 -(9 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 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 USER SERVER sv3 OPTIONS (user 'USER'); -CREATE USER MAPPING FOR "user" SERVER sv4 OPTIONS (user '"USER"'); -CREATE USER MAPPING FOR SESSION_USER SERVER sv5 OPTIONS (user 'SESSION_USER'); -CREATE USER MAPPING FOR PUBLIC SERVER sv6 OPTIONS (user 'PUBLIC'); -CREATE USER MAPPING FOR "Public" SERVER sv7 OPTIONS (user '"Public"'); -CREATE USER MAPPING FOR regress_testrolx SERVER sv8 OPTIONS (user 'regress_testrolx'); -CREATE USER MAPPING FOR CURRENT_ROLE SERVER sv9 - OPTIONS (user 'CURRENT_ROLE'); -- error -ERROR: syntax error at or near "CURRENT_ROLE" -LINE 1: CREATE USER MAPPING FOR CURRENT_ROLE SERVER sv9 - ^ -CREATE USER MAPPING FOR nonexistent SERVER sv9 - 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=USER} - user | sv4 | {"user=\"USER\""} - regress_testrol1 | sv5 | {user=SESSION_USER} - | sv6 | {user=PUBLIC} - Public | sv7 | {"user=\"Public\""} - regress_testrolx | sv8 | {user=regress_testrolx} -(8 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 USER SERVER sv3 - OPTIONS (SET user 'USER_alt'); -ALTER USER MAPPING FOR "user" SERVER sv4 - OPTIONS (SET user '"user"_alt'); -ALTER USER MAPPING FOR SESSION_USER SERVER sv5 - OPTIONS (SET user 'SESSION_USER_alt'); -ALTER USER MAPPING FOR PUBLIC SERVER sv6 - OPTIONS (SET user 'public_alt'); -ALTER USER MAPPING FOR "Public" SERVER sv7 - OPTIONS (SET user '"Public"_alt'); -ALTER USER MAPPING FOR regress_testrolx SERVER sv8 - OPTIONS (SET user 'regress_testrolx_alt'); -ALTER USER MAPPING FOR CURRENT_ROLE SERVER sv9 - OPTIONS (SET user 'CURRENT_ROLE_alt'); -ERROR: syntax error at or near "CURRENT_ROLE" -LINE 1: ALTER USER MAPPING FOR CURRENT_ROLE SERVER sv9 - ^ -ALTER USER MAPPING FOR nonexistent SERVER sv9 - 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=USER_alt} - user | sv4 | {"user=\"user\"_alt"} - regress_testrol1 | sv5 | {user=SESSION_USER_alt} - | sv6 | {user=public_alt} - Public | sv7 | {"user=\"Public\"_alt"} - regress_testrolx | sv8 | {user=regress_testrolx_alt} -(8 rows) - --- DROP USER MAPPING -DROP USER MAPPING FOR CURRENT_USER SERVER sv1; -DROP USER MAPPING FOR "current_user" SERVER sv2; -DROP USER MAPPING FOR USER SERVER sv3; -DROP USER MAPPING FOR "user" SERVER sv4; -DROP USER MAPPING FOR SESSION_USER SERVER sv5; -DROP USER MAPPING FOR PUBLIC SERVER sv6; -DROP USER MAPPING FOR "Public" SERVER sv7; -DROP USER MAPPING FOR regress_testrolx SERVER sv8; -DROP USER MAPPING FOR CURRENT_ROLE SERVER sv9; -- error -ERROR: syntax error at or near "CURRENT_ROLE" -LINE 1: DROP USER MAPPING FOR CURRENT_ROLE SERVER sv9; - ^ -DROP USER MAPPING FOR nonexistent SERVER sv; -- 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 USER SERVER sv3 OPTIONS (user 'USER'); -CREATE USER MAPPING FOR "user" SERVER sv4 OPTIONS (user '"USER"'); -CREATE USER MAPPING FOR SESSION_USER SERVER sv5 OPTIONS (user 'SESSION_USER'); -CREATE USER MAPPING FOR PUBLIC SERVER sv6 OPTIONS (user 'PUBLIC'); -CREATE USER MAPPING FOR "Public" SERVER sv7 OPTIONS (user '"Public"'); -CREATE USER MAPPING FOR regress_testrolx SERVER sv8 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=USER} - user | sv4 | {"user=\"USER\""} - regress_testrol1 | sv5 | {user=SESSION_USER} - | sv6 | {user=PUBLIC} - Public | sv7 | {"user=\"Public\""} - regress_testrolx | sv8 | {user=regress_testrolx} -(8 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=USER} - user | sv4 | {"user=\"USER\""} - regress_testrol1 | sv5 | {user=SESSION_USER} - | sv6 | {user=PUBLIC} - Public | sv7 | {"user=\"Public\""} - regress_testrolx | sv8 | {user=regress_testrolx} -(7 rows) - -DROP USER MAPPING IF EXISTS FOR "current_user" SERVER sv2; -SELECT * FROM chkumapping(); - umname | umserver | umoptions -------------------+----------+------------------------- - regress_testrol2 | sv3 | {user=USER} - user | sv4 | {"user=\"USER\""} - regress_testrol1 | sv5 | {user=SESSION_USER} - | sv6 | {user=PUBLIC} - Public | sv7 | {"user=\"Public\""} - regress_testrolx | sv8 | {user=regress_testrolx} -(6 rows) - -DROP USER MAPPING IF EXISTS FOR USER SERVER sv3; -SELECT * FROM chkumapping(); - umname | umserver | umoptions -------------------+----------+------------------------- - user | sv4 | {"user=\"USER\""} - regress_testrol1 | sv5 | {user=SESSION_USER} - | sv6 | {user=PUBLIC} - Public | sv7 | {"user=\"Public\""} - regress_testrolx | sv8 | {user=regress_testrolx} -(5 rows) - -DROP USER MAPPING IF EXISTS FOR "user" SERVER sv4; -SELECT * FROM chkumapping(); - umname | umserver | umoptions -------------------+----------+------------------------- - regress_testrol1 | sv5 | {user=SESSION_USER} - | sv6 | {user=PUBLIC} - Public | sv7 | {"user=\"Public\""} - regress_testrolx | sv8 | {user=regress_testrolx} -(4 rows) - -DROP USER MAPPING IF EXISTS FOR SESSION_USER SERVER sv5; -SELECT * FROM chkumapping(); - umname | umserver | umoptions -------------------+----------+------------------------- - | sv6 | {user=PUBLIC} - Public | sv7 | {"user=\"Public\""} - regress_testrolx | sv8 | {user=regress_testrolx} -(3 rows) - -DROP USER MAPPING IF EXISTS FOR PUBLIC SERVER sv6; -SELECT * FROM chkumapping(); - umname | umserver | umoptions -------------------+----------+------------------------- - Public | sv7 | {"user=\"Public\""} - regress_testrolx | sv8 | {user=regress_testrolx} -(2 rows) - -DROP USER MAPPING IF EXISTS FOR "Public" SERVER sv7; -SELECT * FROM chkumapping(); - umname | umserver | umoptions -------------------+----------+------------------------- - regress_testrolx | sv8 | {user=regress_testrolx} -(1 row) - -DROP USER MAPPING IF EXISTS FOR regress_testrolx SERVER sv8; -SELECT * FROM chkumapping(); - umname | umserver | umoptions ---------+----------+----------- -(0 rows) - -DROP USER MAPPING IF EXISTS FOR CURRENT_ROLE SERVER sv9; --error -ERROR: syntax error at or near "CURRENT_ROLE" -LINE 1: DROP USER MAPPING IF EXISTS FOR CURRENT_ROLE SERVER sv9; - ^ -DROP USER MAPPING IF EXISTS FOR nonexistent SERVER sv9; -- 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 | -(9 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 SESSION_USER; -GRANT ALL PRIVILEGES ON FUNCTION testagg5(int2) TO "Public"; -GRANT ALL PRIVILEGES ON FUNCTION testagg6(int2) TO regress_testrolx; -GRANT ALL PRIVILEGES ON FUNCTION testagg7(int2) TO "public"; -GRANT ALL PRIVILEGES ON FUNCTION testagg8(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_testrol1=X/regress_testrol1,current_user=X/regress_testrol1} - testagg4 | {regress_testrolx=X/regress_testrolx,regress_testrol1=X/regress_testrolx} - testagg5 | {Public=X/Public} - testagg6 | {regress_testrol0=X/regress_testrol0,regress_testrolx=X/regress_testrol0} - testagg7 | {regress_testrol0=X/regress_testrol0,=X/regress_testrol0} - testagg8 | {regress_testrol0=X/regress_testrol0,regress_testrol2=X/regress_testrol0,=X/regress_testrol0,regress_testrolx=X/regress_testrol0} - testagg9 | -(9 rows) - -GRANT ALL PRIVILEGES ON FUNCTION testagg9(int2) TO CURRENT_ROLE; --error -ERROR: syntax error at or near "CURRENT_ROLE" -LINE 1: ...RANT ALL PRIVILEGES ON FUNCTION testagg9(int2) TO CURRENT_RO... - ^ -GRANT ALL PRIVILEGES ON FUNCTION testagg9(int2) TO USER; --error -ERROR: syntax error at or near "USER" -LINE 1: GRANT ALL PRIVILEGES ON FUNCTION testagg9(int2) TO USER; - ^ -GRANT ALL PRIVILEGES ON FUNCTION testagg9(int2) TO NONE; --error -ERROR: role name "none" is reserved -LINE 1: GRANT ALL PRIVILEGES ON FUNCTION testagg9(int2) TO NONE; - ^ -GRANT ALL PRIVILEGES ON FUNCTION testagg9(int2) TO "none"; --error -ERROR: role name "none" is reserved -LINE 1: GRANT ALL PRIVILEGES ON FUNCTION testagg9(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_testrol1=X/regress_testrol1,current_user=X/regress_testrol1} - testagg4 | {regress_testrolx=X/regress_testrolx,regress_testrol1=X/regress_testrolx} - testagg5 | {Public=X/Public} - testagg6 | {regress_testrol0=X/regress_testrol0,regress_testrolx=X/regress_testrol0} - testagg7 | {regress_testrol0=X/regress_testrol0,=X/regress_testrol0} - testagg8 | {regress_testrol0=X/regress_testrol0,regress_testrol2=X/regress_testrol0,=X/regress_testrol0,regress_testrolx=X/regress_testrol0} - testagg9 | -(9 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 SESSION_USER; -REVOKE ALL PRIVILEGES ON FUNCTION testagg5(int2) FROM "Public"; -REVOKE ALL PRIVILEGES ON FUNCTION testagg6(int2) FROM regress_testrolx; -REVOKE ALL PRIVILEGES ON FUNCTION testagg7(int2) FROM "public"; -REVOKE ALL PRIVILEGES ON FUNCTION testagg8(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_testrol1=X/regress_testrol1} - testagg4 | {regress_testrolx=X/regress_testrolx} - testagg5 | {} - testagg6 | {regress_testrol0=X/regress_testrol0} - testagg7 | {regress_testrol0=X/regress_testrol0} - testagg8 | {regress_testrol0=X/regress_testrol0} - testagg9 | -(9 rows) - -REVOKE ALL PRIVILEGES ON FUNCTION testagg9(int2) FROM CURRENT_ROLE; --error -ERROR: syntax error at or near "CURRENT_ROLE" -LINE 1: ...KE ALL PRIVILEGES ON FUNCTION testagg9(int2) FROM CURRENT_RO... - ^ -REVOKE ALL PRIVILEGES ON FUNCTION testagg9(int2) FROM USER; --error -ERROR: syntax error at or near "USER" -LINE 1: REVOKE ALL PRIVILEGES ON FUNCTION testagg9(int2) FROM USER; - ^ -REVOKE ALL PRIVILEGES ON FUNCTION testagg9(int2) FROM NONE; --error -ERROR: role name "none" is reserved -LINE 1: REVOKE ALL PRIVILEGES ON FUNCTION testagg9(int2) FROM NONE; - ^ -REVOKE ALL PRIVILEGES ON FUNCTION testagg9(int2) FROM "none"; --error -ERROR: role name "none" is reserved -LINE 1: ...EVOKE ALL PRIVILEGES ON FUNCTION testagg9(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_testrol1=X/regress_testrol1} - testagg4 | {regress_testrolx=X/regress_testrolx} - testagg5 | {} - testagg6 | {regress_testrol0=X/regress_testrol0} - testagg7 | {regress_testrol0=X/regress_testrol0} - testagg8 | {regress_testrol0=X/regress_testrol0} - testagg9 | -(9 rows) - --- DEFAULT MONITORING ROLES -CREATE ROLE regress_role_haspriv; -CREATE ROLE regress_role_nopriv; --- pg_read_all_stats -GRANT pg_read_all_stats TO regress_role_haspriv; -SET SESSION AUTHORIZATION regress_role_haspriv; --- returns true with role member of pg_read_all_stats -SELECT COUNT(*) = 0 AS haspriv FROM pg_stat_activity - WHERE query = ''; - 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 = ''; - haspriv ---------- - f -(1 row) - -RESET SESSION AUTHORIZATION; -REVOKE pg_read_all_stats FROM regress_role_haspriv; --- pg_read_all_settings -GRANT pg_read_all_settings TO regress_role_haspriv; -BEGIN; --- A GUC using GUC_SUPERUSER_ONLY is useful for negative tests. -SET LOCAL session_preload_libraries TO 'path-to-preload-libraries'; -SET SESSION AUTHORIZATION regress_role_haspriv; --- passes with role member of pg_read_all_settings -SHOW session_preload_libraries; - session_preload_libraries ------------------------------ - "path-to-preload-libraries" -(1 row) - -SET SESSION AUTHORIZATION regress_role_nopriv; --- fails with role not member of pg_read_all_settings -SHOW session_preload_libraries; -ERROR: must be superuser or a member of pg_read_all_settings to examine "session_preload_libraries" -RESET SESSION AUTHORIZATION; -ERROR: current transaction is aborted, commands ignored until end of transaction block -ROLLBACK; -REVOKE pg_read_all_settings FROM regress_role_haspriv; --- clean up -\c -DROP SCHEMA test_roles_schema; -DROP OWNED BY regress_testrol0, "Public", "current_user", regress_testrol1, regress_testrol2, regress_testrolx CASCADE; -DROP ROLE regress_testrol0, regress_testrol1, regress_testrol2, regress_testrolx; -DROP ROLE "Public", "None", "current_user", "session_user", "user"; -DROP ROLE regress_role_haspriv, regress_role_nopriv; diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule index f23fe8d..8fb55f0 100644 --- a/src/test/regress/parallel_schedule +++ b/src/test/regress/parallel_schedule @@ -55,7 +55,7 @@ test: create_index create_index_spgist create_view index_including index_includi # ---------- # Another group of parallel tests # ---------- -test: create_aggregate create_function_3 create_cast constraints triggers select inherit typed_table vacuum drop_if_exists updatable_views rolenames roleattributes create_am hash_func errors +test: create_aggregate create_function_3 create_cast constraints triggers select inherit typed_table vacuum drop_if_exists updatable_views roleattributes create_am hash_func errors # ---------- # sanity_check does a vacuum, affecting the sort order of SELECT * diff --git a/src/test/regress/serial_schedule b/src/test/regress/serial_schedule index ca200eb..a39ca10 100644 --- a/src/test/regress/serial_schedule +++ b/src/test/regress/serial_schedule @@ -77,7 +77,6 @@ test: typed_table test: vacuum test: drop_if_exists test: updatable_views -test: rolenames test: roleattributes test: create_am test: hash_func diff --git a/src/test/regress/sql/rolenames.sql b/src/test/regress/sql/rolenames.sql deleted file mode 100644 index 5a3cf44..0000000 --- a/src/test/regress/sql/rolenames.sql +++ /dev/null @@ -1,488 +0,0 @@ -CREATE OR REPLACE 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_USER, 'current_user'), - (SESSION_USER, 'session_user'), - ('current_user', '-'), - ('session_user', '-'), - ('Public', '-'), - ('None', '-')) - AS v(uname, keyword) - ON (r.rolname = v.uname) - ORDER BY 1; -$$ LANGUAGE SQL; - -CREATE OR REPLACE 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_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; -$$ LANGUAGE SQL; - -CREATE OR REPLACE 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; -$$ 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_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_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 CURRENT_ROLE 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_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 CURRENT_ROLE 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_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_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 CURRENT_ROLE SET application_name to 'BAZ'; -- error -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_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_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 CURRENT_USER SET application_name to 'BAZ'; -- error -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 SESSION_USER; -CREATE SCHEMA newschema4 AUTHORIZATION regress_testrolx; -CREATE SCHEMA newschema5 AUTHORIZATION "Public"; - -CREATE SCHEMA newschema6 AUTHORIZATION USER; -- error -CREATE SCHEMA newschema6 AUTHORIZATION CURRENT_ROLE; -- error -CREATE SCHEMA newschema6 AUTHORIZATION PUBLIC; -- error -CREATE SCHEMA newschema6 AUTHORIZATION "public"; -- error -CREATE SCHEMA newschema6 AUTHORIZATION NONE; -- error -CREATE SCHEMA newschema6 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 SESSION_USER; -CREATE SCHEMA IF NOT EXISTS newschema4 AUTHORIZATION regress_testrolx; -CREATE SCHEMA IF NOT EXISTS newschema5 AUTHORIZATION "Public"; - -CREATE SCHEMA IF NOT EXISTS newschema6 AUTHORIZATION USER; -- error -CREATE SCHEMA IF NOT EXISTS newschema6 AUTHORIZATION CURRENT_ROLE; -- error -CREATE SCHEMA IF NOT EXISTS newschema6 AUTHORIZATION PUBLIC; -- error -CREATE SCHEMA IF NOT EXISTS newschema6 AUTHORIZATION "public"; -- error -CREATE SCHEMA IF NOT EXISTS newschema6 AUTHORIZATION NONE; -- error -CREATE SCHEMA IF NOT EXISTS newschema6 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); - -\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 SESSION_USER; -ALTER TABLE testtab4 OWNER TO regress_testrolx; -ALTER TABLE testtab5 OWNER TO "Public"; - -ALTER TABLE testtab6 OWNER TO CURRENT_ROLE; -- error -ALTER TABLE testtab6 OWNER TO USER; --error -ALTER TABLE testtab6 OWNER TO PUBLIC; -- error -ALTER TABLE testtab6 OWNER TO "public"; -- error -ALTER TABLE testtab6 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 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); - -\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 SESSION_USER; -ALTER AGGREGATE testagg4(int2) OWNER TO regress_testrolx; -ALTER AGGREGATE testagg5(int2) OWNER TO "Public"; - -ALTER AGGREGATE testagg5(int2) OWNER TO CURRENT_ROLE; -- error -ALTER AGGREGATE testagg5(int2) OWNER TO USER; -- error -ALTER AGGREGATE testagg5(int2) OWNER TO PUBLIC; -- error -ALTER AGGREGATE testagg5(int2) OWNER TO "public"; -- error -ALTER AGGREGATE testagg5(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 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 USER SERVER sv3 OPTIONS (user 'USER'); -CREATE USER MAPPING FOR "user" SERVER sv4 OPTIONS (user '"USER"'); -CREATE USER MAPPING FOR SESSION_USER SERVER sv5 OPTIONS (user 'SESSION_USER'); -CREATE USER MAPPING FOR PUBLIC SERVER sv6 OPTIONS (user 'PUBLIC'); -CREATE USER MAPPING FOR "Public" SERVER sv7 OPTIONS (user '"Public"'); -CREATE USER MAPPING FOR regress_testrolx SERVER sv8 OPTIONS (user 'regress_testrolx'); - -CREATE USER MAPPING FOR CURRENT_ROLE SERVER sv9 - OPTIONS (user 'CURRENT_ROLE'); -- error -CREATE USER MAPPING FOR nonexistent SERVER sv9 - 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 USER SERVER sv3 - OPTIONS (SET user 'USER_alt'); -ALTER USER MAPPING FOR "user" SERVER sv4 - OPTIONS (SET user '"user"_alt'); -ALTER USER MAPPING FOR SESSION_USER SERVER sv5 - OPTIONS (SET user 'SESSION_USER_alt'); -ALTER USER MAPPING FOR PUBLIC SERVER sv6 - OPTIONS (SET user 'public_alt'); -ALTER USER MAPPING FOR "Public" SERVER sv7 - OPTIONS (SET user '"Public"_alt'); -ALTER USER MAPPING FOR regress_testrolx SERVER sv8 - OPTIONS (SET user 'regress_testrolx_alt'); - -ALTER USER MAPPING FOR CURRENT_ROLE SERVER sv9 - OPTIONS (SET user 'CURRENT_ROLE_alt'); -ALTER USER MAPPING FOR nonexistent SERVER sv9 - 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 USER SERVER sv3; -DROP USER MAPPING FOR "user" SERVER sv4; -DROP USER MAPPING FOR SESSION_USER SERVER sv5; -DROP USER MAPPING FOR PUBLIC SERVER sv6; -DROP USER MAPPING FOR "Public" SERVER sv7; -DROP USER MAPPING FOR regress_testrolx SERVER sv8; - -DROP USER MAPPING FOR CURRENT_ROLE SERVER sv9; -- error -DROP USER MAPPING FOR nonexistent SERVER sv; -- 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 USER SERVER sv3 OPTIONS (user 'USER'); -CREATE USER MAPPING FOR "user" SERVER sv4 OPTIONS (user '"USER"'); -CREATE USER MAPPING FOR SESSION_USER SERVER sv5 OPTIONS (user 'SESSION_USER'); -CREATE USER MAPPING FOR PUBLIC SERVER sv6 OPTIONS (user 'PUBLIC'); -CREATE USER MAPPING FOR "Public" SERVER sv7 OPTIONS (user '"Public"'); -CREATE USER MAPPING FOR regress_testrolx SERVER sv8 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 USER SERVER sv3; -SELECT * FROM chkumapping(); -DROP USER MAPPING IF EXISTS FOR "user" SERVER sv4; -SELECT * FROM chkumapping(); -DROP USER MAPPING IF EXISTS FOR SESSION_USER SERVER sv5; -SELECT * FROM chkumapping(); -DROP USER MAPPING IF EXISTS FOR PUBLIC SERVER sv6; -SELECT * FROM chkumapping(); -DROP USER MAPPING IF EXISTS FOR "Public" SERVER sv7; -SELECT * FROM chkumapping(); -DROP USER MAPPING IF EXISTS FOR regress_testrolx SERVER sv8; -SELECT * FROM chkumapping(); - -DROP USER MAPPING IF EXISTS FOR CURRENT_ROLE SERVER sv9; --error -DROP USER MAPPING IF EXISTS FOR nonexistent SERVER sv9; -- 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 SESSION_USER; -GRANT ALL PRIVILEGES ON FUNCTION testagg5(int2) TO "Public"; -GRANT ALL PRIVILEGES ON FUNCTION testagg6(int2) TO regress_testrolx; -GRANT ALL PRIVILEGES ON FUNCTION testagg7(int2) TO "public"; -GRANT ALL PRIVILEGES ON FUNCTION testagg8(int2) - TO current_user, public, regress_testrolx; - -SELECT proname, proacl FROM pg_proc WHERE proname LIKE 'testagg_'; - -GRANT ALL PRIVILEGES ON FUNCTION testagg9(int2) TO CURRENT_ROLE; --error -GRANT ALL PRIVILEGES ON FUNCTION testagg9(int2) TO USER; --error -GRANT ALL PRIVILEGES ON FUNCTION testagg9(int2) TO NONE; --error -GRANT ALL PRIVILEGES ON FUNCTION testagg9(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 SESSION_USER; -REVOKE ALL PRIVILEGES ON FUNCTION testagg5(int2) FROM "Public"; -REVOKE ALL PRIVILEGES ON FUNCTION testagg6(int2) FROM regress_testrolx; -REVOKE ALL PRIVILEGES ON FUNCTION testagg7(int2) FROM "public"; -REVOKE ALL PRIVILEGES ON FUNCTION testagg8(int2) - FROM current_user, public, regress_testrolx; - -SELECT proname, proacl FROM pg_proc WHERE proname LIKE 'testagg_'; - -REVOKE ALL PRIVILEGES ON FUNCTION testagg9(int2) FROM CURRENT_ROLE; --error -REVOKE ALL PRIVILEGES ON FUNCTION testagg9(int2) FROM USER; --error -REVOKE ALL PRIVILEGES ON FUNCTION testagg9(int2) FROM NONE; --error -REVOKE ALL PRIVILEGES ON FUNCTION testagg9(int2) FROM "none"; --error - -SELECT proname, proacl FROM pg_proc WHERE proname LIKE 'testagg_'; - --- DEFAULT MONITORING ROLES -CREATE ROLE regress_role_haspriv; -CREATE ROLE regress_role_nopriv; - --- pg_read_all_stats -GRANT pg_read_all_stats TO regress_role_haspriv; -SET SESSION AUTHORIZATION regress_role_haspriv; --- returns true with role member of pg_read_all_stats -SELECT COUNT(*) = 0 AS haspriv FROM pg_stat_activity - WHERE query = ''; -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 = ''; -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_user", regress_testrol1, regress_testrol2, regress_testrolx CASCADE; -DROP ROLE regress_testrol0, regress_testrol1, regress_testrol2, regress_testrolx; -DROP ROLE "Public", "None", "current_user", "session_user", "user"; -DROP ROLE regress_role_haspriv, regress_role_nopriv;