diff options
Diffstat (limited to 'src/test/regress/expected/rolenames.out')
| -rw-r--r-- | src/test/regress/expected/rolenames.out | 940 |
1 files changed, 940 insertions, 0 deletions
diff --git a/src/test/regress/expected/rolenames.out b/src/test/regress/expected/rolenames.out new file mode 100644 index 00000000000..1879337ea0d --- /dev/null +++ b/src/test/regress/expected/rolenames.out @@ -0,0 +1,940 @@ +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', 'testrol1', '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; +CREATE ROLE "Public"; +CREATE ROLE "None"; +CREATE ROLE "current_user"; +CREATE ROLE "session_user"; +CREATE ROLE "user"; +CREATE ROLE current_user; -- error +ERROR: CURRENT_USER cannot be used as a role name +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 +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 testrol0 SUPERUSER LOGIN; +CREATE ROLE testrolx SUPERUSER LOGIN; +CREATE ROLE testrol2 SUPERUSER; +CREATE ROLE testrol1 SUPERUSER LOGIN IN ROLE testrol2; +\c - +SET SESSION AUTHORIZATION testrol1; +SET ROLE testrol2; +-- ALTER ROLE +BEGIN; +SELECT * FROM chkrolattr(); + role | rolekeyword | canlogin | replication +--------------+--------------+----------+------------- + None | - | f | f + Public | - | f | f + current_user | - | f | f + session_user | - | f | f + testrol1 | session_user | t | f + testrol2 | current_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 + session_user | - | f | f + testrol1 | session_user | t | f + testrol2 | current_user | f | t +(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 + session_user | - | f | f + testrol1 | session_user | t | f + testrol2 | current_user | f | t +(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 + session_user | - | f | f + testrol1 | session_user | t | t + testrol2 | current_user | f | t +(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 + session_user | - | f | t + testrol1 | session_user | t | t + testrol2 | current_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 + session_user | - | f | t + testrol1 | session_user | t | t + testrol2 | current_user | f | t +(6 rows) + +ALTER USER testrol1 WITH NOREPLICATION; +ALTER USER testrol2 WITH NOREPLICATION; +SELECT * FROM chkrolattr(); + role | rolekeyword | canlogin | replication +--------------+--------------+----------+------------- + None | - | f | t + Public | - | f | t + current_user | - | f | t + session_user | - | f | t + testrol1 | session_user | t | f + testrol2 | current_user | f | f +(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 + session_user | - | f | f + testrol1 | session_user | t | f + testrol2 | current_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 + session_user | - | f | f + testrol1 | session_user | t | f + testrol2 | current_user | f | t +(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 + session_user | - | f | f + testrol1 | session_user | t | f + testrol2 | current_user | f | t +(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 + session_user | - | f | f + testrol1 | session_user | t | t + testrol2 | current_user | f | t +(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 + session_user | - | f | t + testrol1 | session_user | t | t + testrol2 | current_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 + session_user | - | f | t + testrol1 | session_user | t | t + testrol2 | current_user | f | t +(6 rows) + +ALTER USER testrol1 WITH NOREPLICATION; +ALTER USER testrol2 WITH NOREPLICATION; +SELECT * FROM chkrolattr(); + role | rolekeyword | canlogin | replication +--------------+--------------+----------+------------- + None | - | f | t + Public | - | f | t + current_user | - | f | t + session_user | - | f | t + testrol1 | session_user | t | f + testrol2 | current_user | f | f +(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 "ALL" +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 | testrol1 | session_user | {application_name=BAR} + ALL | testrol2 | current_user | {application_name=FOO} +(4 rows) + +ALTER ROLE 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 | testrol1 | session_user | {application_name=SLAM} + ALL | 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'; +ERROR: syntax error at or near "ALL" +LINE 1: 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 | testrol1 | session_user | {application_name=BAR} + ALL | testrol2 | current_user | {application_name=FOO} +(4 rows) + +ALTER USER 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 | testrol1 | session_user | {application_name=SLAM} + ALL | 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; +ERROR: syntax error at or near "ALL" +LINE 1: 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 +-- CREAETE SCHEMA +set client_min_messages to error; +CREATE SCHEMA newschema1 AUTHORIZATION CURRENT_USER; +CREATE SCHEMA newschema2 AUTHORIZATION "current_user"; +CREATE SCHEMA newschema3 AUTHORIZATION SESSION_USER; +CREATE SCHEMA newschema4 AUTHORIZATION 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 | testrol2 + newschema2 | current_user + newschema3 | testrol1 + newschema4 | testrolx + newschema5 | Public +(5 rows) + +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 testrolx; +CREATE SCHEMA IF NOT EXISTS newschema5 AUTHORIZATION "Public"; +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 | testrol2 + newschema2 | current_user + newschema3 | testrol1 + newschema4 | testrolx + newschema5 | Public +(5 rows) + +-- ALTER TABLE OWNER TO +\c - +SET SESSION AUTHORIZATION testrol0; +set client_min_messages to error; +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 testrol1; +SET ROLE 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 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 | testrol2 + testtab2 | current_user + testtab3 | testrol1 + testtab4 | testrolx + testtab5 | Public + testtab6 | 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 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 testrol1; +SET ROLE 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 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 | testrol2 + testagg2 | current_user + testagg3 | testrol1 + testagg4 | testrolx + testagg5 | Public + testagg6 | testrol0 + testagg7 | testrol0 + testagg8 | testrol0 + testagg9 | 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 testrolx SERVER sv8 OPTIONS (user '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 +--------------+----------+--------------------------- + testrol2 | sv1 | {user=CURRENT_USER} + current_user | sv2 | {"user=\"current_user\""} + testrol2 | sv3 | {user=USER} + user | sv4 | {"user=\"USER\""} + testrol1 | sv5 | {user=SESSION_USER} + | sv6 | {user=PUBLIC} + Public | sv7 | {"user=\"Public\""} + testrolx | sv8 | {user=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 testrolx SERVER sv8 + OPTIONS (SET user '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 +--------------+----------+------------------------------- + testrol2 | sv1 | {user=CURRENT_USER_alt} + current_user | sv2 | {"user=\"current_user\"_alt"} + testrol2 | sv3 | {user=USER_alt} + user | sv4 | {"user=\"user\"_alt"} + testrol1 | sv5 | {user=SESSION_USER_alt} + | sv6 | {user=public_alt} + Public | sv7 | {"user=\"Public\"_alt"} + testrolx | sv8 | {user=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 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 testrolx SERVER sv8 OPTIONS (user 'testrolx'); +SELECT * FROM chkumapping(); + umname | umserver | umoptions +--------------+----------+--------------------------- + testrol2 | sv1 | {user=CURRENT_USER} + current_user | sv2 | {"user=\"current_user\""} + testrol2 | sv3 | {user=USER} + user | sv4 | {"user=\"USER\""} + testrol1 | sv5 | {user=SESSION_USER} + | sv6 | {user=PUBLIC} + Public | sv7 | {"user=\"Public\""} + testrolx | sv8 | {user=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\""} + testrol2 | sv3 | {user=USER} + user | sv4 | {"user=\"USER\""} + testrol1 | sv5 | {user=SESSION_USER} + | sv6 | {user=PUBLIC} + Public | sv7 | {"user=\"Public\""} + testrolx | sv8 | {user=testrolx} +(7 rows) + +DROP USER MAPPING IF EXISTS FOR "current_user" SERVER sv2; +SELECT * FROM chkumapping(); + umname | umserver | umoptions +----------+----------+--------------------- + testrol2 | sv3 | {user=USER} + user | sv4 | {"user=\"USER\""} + testrol1 | sv5 | {user=SESSION_USER} + | sv6 | {user=PUBLIC} + Public | sv7 | {"user=\"Public\""} + testrolx | sv8 | {user=testrolx} +(6 rows) + +DROP USER MAPPING IF EXISTS FOR USER SERVER sv3; +SELECT * FROM chkumapping(); + umname | umserver | umoptions +----------+----------+--------------------- + user | sv4 | {"user=\"USER\""} + testrol1 | sv5 | {user=SESSION_USER} + | sv6 | {user=PUBLIC} + Public | sv7 | {"user=\"Public\""} + testrolx | sv8 | {user=testrolx} +(5 rows) + +DROP USER MAPPING IF EXISTS FOR "user" SERVER sv4; +SELECT * FROM chkumapping(); + umname | umserver | umoptions +----------+----------+--------------------- + testrol1 | sv5 | {user=SESSION_USER} + | sv6 | {user=PUBLIC} + Public | sv7 | {"user=\"Public\""} + testrolx | sv8 | {user=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\""} + testrolx | sv8 | {user=testrolx} +(3 rows) + +DROP USER MAPPING IF EXISTS FOR PUBLIC SERVER sv6; +SELECT * FROM chkumapping(); + umname | umserver | umoptions +----------+----------+--------------------- + Public | sv7 | {"user=\"Public\""} + testrolx | sv8 | {user=testrolx} +(2 rows) + +DROP USER MAPPING IF EXISTS FOR "Public" SERVER sv7; +SELECT * FROM chkumapping(); + umname | umserver | umoptions +----------+----------+----------------- + testrolx | sv8 | {user=testrolx} +(1 row) + +DROP USER MAPPING IF EXISTS FOR 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 +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 testrolx; +GRANT ALL PRIVILEGES ON FUNCTION testagg7(int2) TO "public"; +GRANT ALL PRIVILEGES ON FUNCTION testagg8(int2) + TO current_user, public, testrolx; +SELECT proname, proacl FROM pg_proc WHERE proname LIKE 'testagg_'; + proname | proacl +----------+--------------------------------------------------------------------------- + testagg1 | {testrol2=X/testrol2,=X/testrol2} + testagg2 | {current_user=X/current_user,testrol2=X/current_user} + testagg3 | {testrol1=X/testrol1,current_user=X/testrol1} + testagg4 | {testrolx=X/testrolx,testrol1=X/testrolx} + testagg5 | {Public=X/Public} + testagg6 | {testrol0=X/testrol0,testrolx=X/testrol0} + testagg7 | {testrol0=X/testrol0,=X/testrol0} + testagg8 | {testrol0=X/testrol0,testrol2=X/testrol0,=X/testrol0,testrolx=X/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 | {testrol2=X/testrol2,=X/testrol2} + testagg2 | {current_user=X/current_user,testrol2=X/current_user} + testagg3 | {testrol1=X/testrol1,current_user=X/testrol1} + testagg4 | {testrolx=X/testrolx,testrol1=X/testrolx} + testagg5 | {Public=X/Public} + testagg6 | {testrol0=X/testrol0,testrolx=X/testrol0} + testagg7 | {testrol0=X/testrol0,=X/testrol0} + testagg8 | {testrol0=X/testrol0,testrol2=X/testrol0,=X/testrol0,testrolx=X/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 testrolx; +REVOKE ALL PRIVILEGES ON FUNCTION testagg7(int2) FROM "public"; +REVOKE ALL PRIVILEGES ON FUNCTION testagg8(int2) + FROM current_user, public, testrolx; +SELECT proname, proacl FROM pg_proc WHERE proname LIKE 'testagg_'; + proname | proacl +----------+------------------------------- + testagg1 | {testrol2=X/testrol2} + testagg2 | {current_user=X/current_user} + testagg3 | {testrol1=X/testrol1} + testagg4 | {testrolx=X/testrolx} + testagg5 | {} + testagg6 | {testrol0=X/testrol0} + testagg7 | {testrol0=X/testrol0} + testagg8 | {testrol0=X/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 | {testrol2=X/testrol2} + testagg2 | {current_user=X/current_user} + testagg3 | {testrol1=X/testrol1} + testagg4 | {testrolx=X/testrolx} + testagg5 | {} + testagg6 | {testrol0=X/testrol0} + testagg7 | {testrol0=X/testrol0} + testagg8 | {testrol0=X/testrol0} + testagg9 | +(9 rows) + +-- clean up +\c +DROP OWNED BY testrol0, "Public", "current_user", testrol1, testrol2, testrolx CASCADE; +DROP ROLE testrol0, testrol1, testrol2, testrolx; +DROP ROLE "Public", "None", "current_user", "session_user", "user"; |