diff options
Diffstat (limited to 'src/test/modules/unsafe_tests/expected/rolenames.out')
| -rw-r--r-- | src/test/modules/unsafe_tests/expected/rolenames.out | 1091 |
1 files changed, 1091 insertions, 0 deletions
diff --git a/src/test/modules/unsafe_tests/expected/rolenames.out b/src/test/modules/unsafe_tests/expected/rolenames.out new file mode 100644 index 0000000..88b1ff8 --- /dev/null +++ b/src/test/modules/unsafe_tests/expected/rolenames.out @@ -0,0 +1,1091 @@ +CREATE FUNCTION chkrolattr() + RETURNS TABLE ("role" name, rolekeyword text, canlogin bool, replication bool) + AS $$ +SELECT r.rolname, v.keyword, r.rolcanlogin, r.rolreplication + FROM pg_roles r + JOIN (VALUES(CURRENT_ROLE, 'current_role'), + (CURRENT_USER, 'current_user'), + (SESSION_USER, 'session_user'), + ('current_role', '-'), + ('current_user', '-'), + ('session_user', '-'), + ('Public', '-'), + ('None', '-')) + AS v(uname, keyword) + ON (r.rolname = v.uname) + ORDER BY 1, 2; +$$ LANGUAGE SQL; +CREATE FUNCTION chksetconfig() + RETURNS TABLE (db name, "role" name, rolkeyword text, setconfig text[]) + AS $$ +SELECT COALESCE(d.datname, 'ALL'), COALESCE(r.rolname, 'ALL'), + COALESCE(v.keyword, '-'), s.setconfig + FROM pg_db_role_setting s + LEFT JOIN pg_roles r ON (r.oid = s.setrole) + LEFT JOIN pg_database d ON (d.oid = s.setdatabase) + LEFT JOIN (VALUES(CURRENT_ROLE, 'current_role'), + (CURRENT_USER, 'current_user'), + (SESSION_USER, 'session_user')) + AS v(uname, keyword) + ON (r.rolname = v.uname) + WHERE (r.rolname) IN ('Public', 'current_user', 'regress_testrol1', 'regress_testrol2') +ORDER BY 1, 2, 3; +$$ LANGUAGE SQL; +CREATE FUNCTION chkumapping() + RETURNS TABLE (umname name, umserver name, umoptions text[]) + AS $$ +SELECT r.rolname, s.srvname, m.umoptions + FROM pg_user_mapping m + LEFT JOIN pg_roles r ON (r.oid = m.umuser) + JOIN pg_foreign_server s ON (s.oid = m.umserver) + ORDER BY 2, 1; +$$ LANGUAGE SQL; +-- +-- We test creation and use of these role names to ensure that the server +-- correctly distinguishes role keywords from quoted names that look like +-- those keywords. In a test environment, creation of these roles may +-- provoke warnings, so hide the warnings by raising client_min_messages. +-- +SET client_min_messages = ERROR; +CREATE ROLE "Public"; +CREATE ROLE "None"; +CREATE ROLE "current_role"; +CREATE ROLE "current_user"; +CREATE ROLE "session_user"; +CREATE ROLE "user"; +RESET client_min_messages; +CREATE ROLE current_user; -- error +ERROR: CURRENT_USER cannot be used as a role name here +LINE 1: CREATE ROLE current_user; + ^ +CREATE ROLE current_role; -- error +ERROR: CURRENT_ROLE cannot be used as a role name here +LINE 1: CREATE ROLE current_role; + ^ +CREATE ROLE session_user; -- error +ERROR: SESSION_USER cannot be used as a role name here +LINE 1: CREATE ROLE session_user; + ^ +CREATE ROLE user; -- error +ERROR: syntax error at or near "user" +LINE 1: CREATE ROLE user; + ^ +CREATE ROLE all; -- error +ERROR: syntax error at or near "all" +LINE 1: CREATE ROLE all; + ^ +CREATE ROLE public; -- error +ERROR: role name "public" is reserved +LINE 1: CREATE ROLE public; + ^ +CREATE ROLE "public"; -- error +ERROR: role name "public" is reserved +LINE 1: CREATE ROLE "public"; + ^ +CREATE ROLE none; -- error +ERROR: role name "none" is reserved +LINE 1: CREATE ROLE none; + ^ +CREATE ROLE "none"; -- error +ERROR: role name "none" is reserved +LINE 1: CREATE ROLE "none"; + ^ +CREATE ROLE pg_abc; -- error +ERROR: role name "pg_abc" is reserved +DETAIL: Role names starting with "pg_" are reserved. +CREATE ROLE "pg_abc"; -- error +ERROR: role name "pg_abc" is reserved +DETAIL: Role names starting with "pg_" are reserved. +CREATE ROLE pg_abcdef; -- error +ERROR: role name "pg_abcdef" is reserved +DETAIL: Role names starting with "pg_" are reserved. +CREATE ROLE "pg_abcdef"; -- error +ERROR: role name "pg_abcdef" is reserved +DETAIL: Role names starting with "pg_" are reserved. +CREATE ROLE regress_testrol0 SUPERUSER LOGIN; +CREATE ROLE regress_testrolx SUPERUSER LOGIN; +CREATE ROLE regress_testrol2 SUPERUSER; +CREATE ROLE regress_testrol1 SUPERUSER LOGIN IN ROLE regress_testrol2; +\c - +SET SESSION AUTHORIZATION regress_testrol1; +SET ROLE regress_testrol2; +-- ALTER ROLE +BEGIN; +SELECT * FROM chkrolattr(); + role | rolekeyword | canlogin | replication +------------------+--------------+----------+------------- + None | - | f | f + Public | - | f | f + current_role | - | f | f + current_user | - | f | f + regress_testrol1 | session_user | t | f + regress_testrol2 | current_role | f | f + regress_testrol2 | current_user | f | f + session_user | - | f | f +(8 rows) + +ALTER ROLE CURRENT_ROLE WITH REPLICATION; +SELECT * FROM chkrolattr(); + role | rolekeyword | canlogin | replication +------------------+--------------+----------+------------- + None | - | f | f + Public | - | f | f + current_role | - | f | f + current_user | - | f | f + regress_testrol1 | session_user | t | f + regress_testrol2 | current_role | f | t + regress_testrol2 | current_user | f | t + session_user | - | f | f +(8 rows) + +ALTER ROLE "current_role" WITH REPLICATION; +SELECT * FROM chkrolattr(); + role | rolekeyword | canlogin | replication +------------------+--------------+----------+------------- + None | - | f | f + Public | - | f | f + current_role | - | f | t + current_user | - | f | f + regress_testrol1 | session_user | t | f + regress_testrol2 | current_role | f | t + regress_testrol2 | current_user | f | t + session_user | - | f | f +(8 rows) + +ALTER ROLE CURRENT_ROLE WITH NOREPLICATION; +ALTER ROLE CURRENT_USER WITH REPLICATION; +SELECT * FROM chkrolattr(); + role | rolekeyword | canlogin | replication +------------------+--------------+----------+------------- + None | - | f | f + Public | - | f | f + current_role | - | f | t + current_user | - | f | f + regress_testrol1 | session_user | t | f + regress_testrol2 | current_role | f | t + regress_testrol2 | current_user | f | t + session_user | - | f | f +(8 rows) + +ALTER ROLE "current_user" WITH REPLICATION; +SELECT * FROM chkrolattr(); + role | rolekeyword | canlogin | replication +------------------+--------------+----------+------------- + None | - | f | f + Public | - | f | f + current_role | - | f | t + current_user | - | f | t + regress_testrol1 | session_user | t | f + regress_testrol2 | current_role | f | t + regress_testrol2 | current_user | f | t + session_user | - | f | f +(8 rows) + +ALTER ROLE SESSION_USER WITH REPLICATION; +SELECT * FROM chkrolattr(); + role | rolekeyword | canlogin | replication +------------------+--------------+----------+------------- + None | - | f | f + Public | - | f | f + current_role | - | f | t + current_user | - | f | t + regress_testrol1 | session_user | t | t + regress_testrol2 | current_role | f | t + regress_testrol2 | current_user | f | t + session_user | - | f | f +(8 rows) + +ALTER ROLE "session_user" WITH REPLICATION; +SELECT * FROM chkrolattr(); + role | rolekeyword | canlogin | replication +------------------+--------------+----------+------------- + None | - | f | f + Public | - | f | f + current_role | - | f | t + current_user | - | f | t + regress_testrol1 | session_user | t | t + regress_testrol2 | current_role | f | t + regress_testrol2 | current_user | f | t + session_user | - | f | t +(8 rows) + +ALTER USER "Public" WITH REPLICATION; +ALTER USER "None" WITH REPLICATION; +SELECT * FROM chkrolattr(); + role | rolekeyword | canlogin | replication +------------------+--------------+----------+------------- + None | - | f | t + Public | - | f | t + current_role | - | f | t + current_user | - | f | t + regress_testrol1 | session_user | t | t + regress_testrol2 | current_role | f | t + regress_testrol2 | current_user | f | t + session_user | - | f | t +(8 rows) + +ALTER USER regress_testrol1 WITH NOREPLICATION; +ALTER USER regress_testrol2 WITH NOREPLICATION; +SELECT * FROM chkrolattr(); + role | rolekeyword | canlogin | replication +------------------+--------------+----------+------------- + None | - | f | t + Public | - | f | t + current_role | - | f | t + current_user | - | f | t + regress_testrol1 | session_user | t | f + regress_testrol2 | current_role | f | f + regress_testrol2 | current_user | f | f + session_user | - | f | t +(8 rows) + +ROLLBACK; +ALTER ROLE USER WITH LOGIN; -- error +ERROR: syntax error at or near "USER" +LINE 1: ALTER ROLE USER WITH LOGIN; + ^ +ALTER ROLE ALL WITH REPLICATION; -- error +ERROR: syntax error at or near "WITH" +LINE 1: ALTER ROLE ALL WITH REPLICATION; + ^ +ALTER ROLE SESSION_ROLE WITH NOREPLICATION; -- error +ERROR: role "session_role" does not exist +ALTER ROLE PUBLIC WITH NOREPLICATION; -- error +ERROR: role "public" does not exist +ALTER ROLE "public" WITH NOREPLICATION; -- error +ERROR: role "public" does not exist +ALTER ROLE NONE WITH NOREPLICATION; -- error +ERROR: role name "none" is reserved +LINE 1: ALTER ROLE NONE WITH NOREPLICATION; + ^ +ALTER ROLE "none" WITH NOREPLICATION; -- error +ERROR: role name "none" is reserved +LINE 1: ALTER ROLE "none" WITH NOREPLICATION; + ^ +ALTER ROLE nonexistent WITH NOREPLICATION; -- error +ERROR: role "nonexistent" does not exist +-- ALTER USER +BEGIN; +SELECT * FROM chkrolattr(); + role | rolekeyword | canlogin | replication +------------------+--------------+----------+------------- + None | - | f | f + Public | - | f | f + current_role | - | f | f + current_user | - | f | f + regress_testrol1 | session_user | t | f + regress_testrol2 | current_role | f | f + regress_testrol2 | current_user | f | f + session_user | - | f | f +(8 rows) + +ALTER USER CURRENT_ROLE WITH REPLICATION; +SELECT * FROM chkrolattr(); + role | rolekeyword | canlogin | replication +------------------+--------------+----------+------------- + None | - | f | f + Public | - | f | f + current_role | - | f | f + current_user | - | f | f + regress_testrol1 | session_user | t | f + regress_testrol2 | current_role | f | t + regress_testrol2 | current_user | f | t + session_user | - | f | f +(8 rows) + +ALTER USER "current_role" WITH REPLICATION; +SELECT * FROM chkrolattr(); + role | rolekeyword | canlogin | replication +------------------+--------------+----------+------------- + None | - | f | f + Public | - | f | f + current_role | - | f | t + current_user | - | f | f + regress_testrol1 | session_user | t | f + regress_testrol2 | current_role | f | t + regress_testrol2 | current_user | f | t + session_user | - | f | f +(8 rows) + +ALTER USER CURRENT_ROLE WITH NOREPLICATION; +ALTER USER CURRENT_USER WITH REPLICATION; +SELECT * FROM chkrolattr(); + role | rolekeyword | canlogin | replication +------------------+--------------+----------+------------- + None | - | f | f + Public | - | f | f + current_role | - | f | t + current_user | - | f | f + regress_testrol1 | session_user | t | f + regress_testrol2 | current_role | f | t + regress_testrol2 | current_user | f | t + session_user | - | f | f +(8 rows) + +ALTER USER "current_user" WITH REPLICATION; +SELECT * FROM chkrolattr(); + role | rolekeyword | canlogin | replication +------------------+--------------+----------+------------- + None | - | f | f + Public | - | f | f + current_role | - | f | t + current_user | - | f | t + regress_testrol1 | session_user | t | f + regress_testrol2 | current_role | f | t + regress_testrol2 | current_user | f | t + session_user | - | f | f +(8 rows) + +ALTER USER SESSION_USER WITH REPLICATION; +SELECT * FROM chkrolattr(); + role | rolekeyword | canlogin | replication +------------------+--------------+----------+------------- + None | - | f | f + Public | - | f | f + current_role | - | f | t + current_user | - | f | t + regress_testrol1 | session_user | t | t + regress_testrol2 | current_role | f | t + regress_testrol2 | current_user | f | t + session_user | - | f | f +(8 rows) + +ALTER USER "session_user" WITH REPLICATION; +SELECT * FROM chkrolattr(); + role | rolekeyword | canlogin | replication +------------------+--------------+----------+------------- + None | - | f | f + Public | - | f | f + current_role | - | f | t + current_user | - | f | t + regress_testrol1 | session_user | t | t + regress_testrol2 | current_role | f | t + regress_testrol2 | current_user | f | t + session_user | - | f | t +(8 rows) + +ALTER USER "Public" WITH REPLICATION; +ALTER USER "None" WITH REPLICATION; +SELECT * FROM chkrolattr(); + role | rolekeyword | canlogin | replication +------------------+--------------+----------+------------- + None | - | f | t + Public | - | f | t + current_role | - | f | t + current_user | - | f | t + regress_testrol1 | session_user | t | t + regress_testrol2 | current_role | f | t + regress_testrol2 | current_user | f | t + session_user | - | f | t +(8 rows) + +ALTER USER regress_testrol1 WITH NOREPLICATION; +ALTER USER regress_testrol2 WITH NOREPLICATION; +SELECT * FROM chkrolattr(); + role | rolekeyword | canlogin | replication +------------------+--------------+----------+------------- + None | - | f | t + Public | - | f | t + current_role | - | f | t + current_user | - | f | t + regress_testrol1 | session_user | t | f + regress_testrol2 | current_role | f | f + regress_testrol2 | current_user | f | f + session_user | - | f | t +(8 rows) + +ROLLBACK; +ALTER USER USER WITH LOGIN; -- error +ERROR: syntax error at or near "USER" +LINE 1: ALTER USER USER WITH LOGIN; + ^ +ALTER USER ALL WITH REPLICATION; -- error +ERROR: syntax error at or near "WITH" +LINE 1: ALTER USER ALL WITH REPLICATION; + ^ +ALTER USER SESSION_ROLE WITH NOREPLICATION; -- error +ERROR: role "session_role" does not exist +ALTER USER PUBLIC WITH NOREPLICATION; -- error +ERROR: role "public" does not exist +ALTER USER "public" WITH NOREPLICATION; -- error +ERROR: role "public" does not exist +ALTER USER NONE WITH NOREPLICATION; -- error +ERROR: role name "none" is reserved +LINE 1: ALTER USER NONE WITH NOREPLICATION; + ^ +ALTER USER "none" WITH NOREPLICATION; -- error +ERROR: role name "none" is reserved +LINE 1: ALTER USER "none" WITH NOREPLICATION; + ^ +ALTER USER nonexistent WITH NOREPLICATION; -- error +ERROR: role "nonexistent" does not exist +-- ALTER ROLE SET/RESET +SELECT * FROM chksetconfig(); + db | role | rolkeyword | setconfig +----+------+------------+----------- +(0 rows) + +ALTER ROLE CURRENT_ROLE SET application_name to 'BAZ'; +ALTER ROLE CURRENT_USER SET application_name to 'FOO'; +ALTER ROLE SESSION_USER SET application_name to 'BAR'; +ALTER ROLE "current_user" SET application_name to 'FOOFOO'; +ALTER ROLE "Public" SET application_name to 'BARBAR'; +ALTER ROLE ALL SET application_name to 'SLAP'; +SELECT * FROM chksetconfig(); + db | role | rolkeyword | setconfig +-----+------------------+--------------+--------------------------- + ALL | Public | - | {application_name=BARBAR} + ALL | current_user | - | {application_name=FOOFOO} + ALL | regress_testrol1 | session_user | {application_name=BAR} + ALL | regress_testrol2 | current_role | {application_name=FOO} + ALL | regress_testrol2 | current_user | {application_name=FOO} +(5 rows) + +ALTER ROLE regress_testrol1 SET application_name to 'SLAM'; +SELECT * FROM chksetconfig(); + db | role | rolkeyword | setconfig +-----+------------------+--------------+--------------------------- + ALL | Public | - | {application_name=BARBAR} + ALL | current_user | - | {application_name=FOOFOO} + ALL | regress_testrol1 | session_user | {application_name=SLAM} + ALL | regress_testrol2 | current_role | {application_name=FOO} + ALL | regress_testrol2 | current_user | {application_name=FOO} +(5 rows) + +ALTER ROLE CURRENT_ROLE RESET application_name; +ALTER ROLE CURRENT_USER RESET application_name; +ALTER ROLE SESSION_USER RESET application_name; +ALTER ROLE "current_user" RESET application_name; +ALTER ROLE "Public" RESET application_name; +ALTER ROLE ALL RESET application_name; +SELECT * FROM chksetconfig(); + db | role | rolkeyword | setconfig +----+------+------------+----------- +(0 rows) + +ALTER ROLE USER SET application_name to 'BOOM'; -- error +ERROR: syntax error at or near "USER" +LINE 1: ALTER ROLE USER SET application_name to 'BOOM'; + ^ +ALTER ROLE PUBLIC SET application_name to 'BOMB'; -- error +ERROR: role "public" does not exist +ALTER ROLE nonexistent SET application_name to 'BOMB'; -- error +ERROR: role "nonexistent" does not exist +-- ALTER USER SET/RESET +SELECT * FROM chksetconfig(); + db | role | rolkeyword | setconfig +----+------+------------+----------- +(0 rows) + +ALTER USER CURRENT_ROLE SET application_name to 'BAZ'; +ALTER USER CURRENT_USER SET application_name to 'FOO'; +ALTER USER SESSION_USER SET application_name to 'BAR'; +ALTER USER "current_user" SET application_name to 'FOOFOO'; +ALTER USER "Public" SET application_name to 'BARBAR'; +ALTER USER ALL SET application_name to 'SLAP'; +SELECT * FROM chksetconfig(); + db | role | rolkeyword | setconfig +-----+------------------+--------------+--------------------------- + ALL | Public | - | {application_name=BARBAR} + ALL | current_user | - | {application_name=FOOFOO} + ALL | regress_testrol1 | session_user | {application_name=BAR} + ALL | regress_testrol2 | current_role | {application_name=FOO} + ALL | regress_testrol2 | current_user | {application_name=FOO} +(5 rows) + +ALTER USER regress_testrol1 SET application_name to 'SLAM'; +SELECT * FROM chksetconfig(); + db | role | rolkeyword | setconfig +-----+------------------+--------------+--------------------------- + ALL | Public | - | {application_name=BARBAR} + ALL | current_user | - | {application_name=FOOFOO} + ALL | regress_testrol1 | session_user | {application_name=SLAM} + ALL | regress_testrol2 | current_role | {application_name=FOO} + ALL | regress_testrol2 | current_user | {application_name=FOO} +(5 rows) + +ALTER USER CURRENT_ROLE RESET application_name; +ALTER USER CURRENT_USER RESET application_name; +ALTER USER SESSION_USER RESET application_name; +ALTER USER "current_user" RESET application_name; +ALTER USER "Public" RESET application_name; +ALTER USER ALL RESET application_name; +SELECT * FROM chksetconfig(); + db | role | rolkeyword | setconfig +----+------+------------+----------- +(0 rows) + +ALTER USER USER SET application_name to 'BOOM'; -- error +ERROR: syntax error at or near "USER" +LINE 1: ALTER USER USER SET application_name to 'BOOM'; + ^ +ALTER USER PUBLIC SET application_name to 'BOMB'; -- error +ERROR: role "public" does not exist +ALTER USER NONE SET application_name to 'BOMB'; -- error +ERROR: role name "none" is reserved +LINE 1: ALTER USER NONE SET application_name to 'BOMB'; + ^ +ALTER USER nonexistent SET application_name to 'BOMB'; -- error +ERROR: role "nonexistent" does not exist +-- CREATE SCHEMA +CREATE SCHEMA newschema1 AUTHORIZATION CURRENT_USER; +CREATE SCHEMA newschema2 AUTHORIZATION "current_user"; +CREATE SCHEMA newschema3 AUTHORIZATION CURRENT_ROLE; +CREATE SCHEMA newschema4 AUTHORIZATION SESSION_USER; +CREATE SCHEMA newschema5 AUTHORIZATION regress_testrolx; +CREATE SCHEMA newschema6 AUTHORIZATION "Public"; +CREATE SCHEMA newschemax AUTHORIZATION USER; -- error +ERROR: syntax error at or near "USER" +LINE 1: CREATE SCHEMA newschemax AUTHORIZATION USER; + ^ +CREATE SCHEMA newschemax AUTHORIZATION PUBLIC; -- error +ERROR: role "public" does not exist +CREATE SCHEMA newschemax AUTHORIZATION "public"; -- error +ERROR: role "public" does not exist +CREATE SCHEMA newschemax AUTHORIZATION NONE; -- error +ERROR: role name "none" is reserved +LINE 1: CREATE SCHEMA newschemax AUTHORIZATION NONE; + ^ +CREATE SCHEMA newschemax AUTHORIZATION nonexistent; -- error +ERROR: role "nonexistent" does not exist +SELECT n.nspname, r.rolname FROM pg_namespace n + JOIN pg_roles r ON (r.oid = n.nspowner) + WHERE n.nspname LIKE 'newschema_' ORDER BY 1; + nspname | rolname +------------+------------------ + newschema1 | regress_testrol2 + newschema2 | current_user + newschema3 | regress_testrol2 + newschema4 | regress_testrol1 + newschema5 | regress_testrolx + newschema6 | Public +(6 rows) + +CREATE SCHEMA IF NOT EXISTS newschema1 AUTHORIZATION CURRENT_USER; +NOTICE: schema "newschema1" already exists, skipping +CREATE SCHEMA IF NOT EXISTS newschema2 AUTHORIZATION "current_user"; +NOTICE: schema "newschema2" already exists, skipping +CREATE SCHEMA IF NOT EXISTS newschema3 AUTHORIZATION CURRENT_ROLE; +NOTICE: schema "newschema3" already exists, skipping +CREATE SCHEMA IF NOT EXISTS newschema4 AUTHORIZATION SESSION_USER; +NOTICE: schema "newschema4" already exists, skipping +CREATE SCHEMA IF NOT EXISTS newschema5 AUTHORIZATION regress_testrolx; +NOTICE: schema "newschema5" already exists, skipping +CREATE SCHEMA IF NOT EXISTS newschema6 AUTHORIZATION "Public"; +NOTICE: schema "newschema6" already exists, skipping +CREATE SCHEMA IF NOT EXISTS newschemax AUTHORIZATION USER; -- error +ERROR: syntax error at or near "USER" +LINE 1: CREATE SCHEMA IF NOT EXISTS newschemax AUTHORIZATION USER; + ^ +CREATE SCHEMA IF NOT EXISTS newschemax AUTHORIZATION PUBLIC; -- error +ERROR: role "public" does not exist +CREATE SCHEMA IF NOT EXISTS newschemax AUTHORIZATION "public"; -- error +ERROR: role "public" does not exist +CREATE SCHEMA IF NOT EXISTS newschemax AUTHORIZATION NONE; -- error +ERROR: role name "none" is reserved +LINE 1: CREATE SCHEMA IF NOT EXISTS newschemax AUTHORIZATION NONE; + ^ +CREATE SCHEMA IF NOT EXISTS newschemax AUTHORIZATION nonexistent; -- error +ERROR: role "nonexistent" does not exist +SELECT n.nspname, r.rolname FROM pg_namespace n + JOIN pg_roles r ON (r.oid = n.nspowner) + WHERE n.nspname LIKE 'newschema_' ORDER BY 1; + nspname | rolname +------------+------------------ + newschema1 | regress_testrol2 + newschema2 | current_user + newschema3 | regress_testrol2 + newschema4 | regress_testrol1 + newschema5 | regress_testrolx + newschema6 | Public +(6 rows) + +-- ALTER TABLE OWNER TO +\c - +SET SESSION AUTHORIZATION regress_testrol0; +CREATE TABLE testtab1 (a int); +CREATE TABLE testtab2 (a int); +CREATE TABLE testtab3 (a int); +CREATE TABLE testtab4 (a int); +CREATE TABLE testtab5 (a int); +CREATE TABLE testtab6 (a int); +CREATE TABLE testtab7 (a int); +\c - +SET SESSION AUTHORIZATION regress_testrol1; +SET ROLE regress_testrol2; +ALTER TABLE testtab1 OWNER TO CURRENT_USER; +ALTER TABLE testtab2 OWNER TO "current_user"; +ALTER TABLE testtab3 OWNER TO CURRENT_ROLE; +ALTER TABLE testtab4 OWNER TO SESSION_USER; +ALTER TABLE testtab5 OWNER TO regress_testrolx; +ALTER TABLE testtab6 OWNER TO "Public"; +ALTER TABLE testtab7 OWNER TO USER; --error +ERROR: syntax error at or near "USER" +LINE 1: ALTER TABLE testtab7 OWNER TO USER; + ^ +ALTER TABLE testtab7 OWNER TO PUBLIC; -- error +ERROR: role "public" does not exist +ALTER TABLE testtab7 OWNER TO "public"; -- error +ERROR: role "public" does not exist +ALTER TABLE testtab7 OWNER TO nonexistent; -- error +ERROR: role "nonexistent" does not exist +SELECT c.relname, r.rolname + FROM pg_class c JOIN pg_roles r ON (r.oid = c.relowner) + WHERE relname LIKE 'testtab_' + ORDER BY 1; + relname | rolname +----------+------------------ + testtab1 | regress_testrol2 + testtab2 | current_user + testtab3 | regress_testrol2 + testtab4 | regress_testrol1 + testtab5 | regress_testrolx + testtab6 | Public + testtab7 | regress_testrol0 +(7 rows) + +-- ALTER TABLE, VIEW, MATERIALIZED VIEW, FOREIGN TABLE, SEQUENCE are +-- changed their owner in the same way. +-- ALTER AGGREGATE +\c - +SET SESSION AUTHORIZATION regress_testrol0; +CREATE AGGREGATE testagg1(int2) (SFUNC = int2_sum, STYPE = int8); +CREATE AGGREGATE testagg2(int2) (SFUNC = int2_sum, STYPE = int8); +CREATE AGGREGATE testagg3(int2) (SFUNC = int2_sum, STYPE = int8); +CREATE AGGREGATE testagg4(int2) (SFUNC = int2_sum, STYPE = int8); +CREATE AGGREGATE testagg5(int2) (SFUNC = int2_sum, STYPE = int8); +CREATE AGGREGATE testagg6(int2) (SFUNC = int2_sum, STYPE = int8); +CREATE AGGREGATE testagg7(int2) (SFUNC = int2_sum, STYPE = int8); +CREATE AGGREGATE testagg8(int2) (SFUNC = int2_sum, STYPE = int8); +CREATE AGGREGATE testagg9(int2) (SFUNC = int2_sum, STYPE = int8); +CREATE AGGREGATE testagga(int2) (SFUNC = int2_sum, STYPE = int8); +\c - +SET SESSION AUTHORIZATION regress_testrol1; +SET ROLE regress_testrol2; +ALTER AGGREGATE testagg1(int2) OWNER TO CURRENT_USER; +ALTER AGGREGATE testagg2(int2) OWNER TO "current_user"; +ALTER AGGREGATE testagg3(int2) OWNER TO CURRENT_ROLE; +ALTER AGGREGATE testagg4(int2) OWNER TO SESSION_USER; +ALTER AGGREGATE testagg5(int2) OWNER TO regress_testrolx; +ALTER AGGREGATE testagg6(int2) OWNER TO "Public"; +ALTER AGGREGATE testagg6(int2) OWNER TO USER; -- error +ERROR: syntax error at or near "USER" +LINE 1: ALTER AGGREGATE testagg6(int2) OWNER TO USER; + ^ +ALTER AGGREGATE testagg6(int2) OWNER TO PUBLIC; -- error +ERROR: role "public" does not exist +ALTER AGGREGATE testagg6(int2) OWNER TO "public"; -- error +ERROR: role "public" does not exist +ALTER AGGREGATE testagg6(int2) OWNER TO nonexistent; -- error +ERROR: role "nonexistent" does not exist +SELECT p.proname, r.rolname + FROM pg_proc p JOIN pg_roles r ON (r.oid = p.proowner) + WHERE proname LIKE 'testagg_' + ORDER BY 1; + proname | rolname +----------+------------------ + testagg1 | regress_testrol2 + testagg2 | current_user + testagg3 | regress_testrol2 + testagg4 | regress_testrol1 + testagg5 | regress_testrolx + testagg6 | Public + testagg7 | regress_testrol0 + testagg8 | regress_testrol0 + testagg9 | regress_testrol0 + testagga | regress_testrol0 +(10 rows) + +-- CREATE USER MAPPING +CREATE FOREIGN DATA WRAPPER test_wrapper; +CREATE SERVER sv1 FOREIGN DATA WRAPPER test_wrapper; +CREATE SERVER sv2 FOREIGN DATA WRAPPER test_wrapper; +CREATE SERVER sv3 FOREIGN DATA WRAPPER test_wrapper; +CREATE SERVER sv4 FOREIGN DATA WRAPPER test_wrapper; +CREATE SERVER sv5 FOREIGN DATA WRAPPER test_wrapper; +CREATE SERVER sv6 FOREIGN DATA WRAPPER test_wrapper; +CREATE SERVER sv7 FOREIGN DATA WRAPPER test_wrapper; +CREATE SERVER sv8 FOREIGN DATA WRAPPER test_wrapper; +CREATE SERVER sv9 FOREIGN DATA WRAPPER test_wrapper; +CREATE SERVER sv10 FOREIGN DATA WRAPPER test_wrapper; +CREATE USER MAPPING FOR CURRENT_USER SERVER sv1 OPTIONS (user 'CURRENT_USER'); +CREATE USER MAPPING FOR "current_user" SERVER sv2 OPTIONS (user '"current_user"'); +CREATE USER MAPPING FOR CURRENT_ROLE SERVER sv3 OPTIONS (user 'CURRENT_ROLE'); +CREATE USER MAPPING FOR USER SERVER sv4 OPTIONS (user 'USER'); +CREATE USER MAPPING FOR "user" SERVER sv5 OPTIONS (user '"USER"'); +CREATE USER MAPPING FOR SESSION_USER SERVER sv6 OPTIONS (user 'SESSION_USER'); +CREATE USER MAPPING FOR PUBLIC SERVER sv7 OPTIONS (user 'PUBLIC'); +CREATE USER MAPPING FOR "Public" SERVER sv8 OPTIONS (user '"Public"'); +CREATE USER MAPPING FOR regress_testrolx SERVER sv9 OPTIONS (user 'regress_testrolx'); +CREATE USER MAPPING FOR nonexistent SERVER sv10 OPTIONS (user 'nonexistent'); -- error; +ERROR: role "nonexistent" does not exist +SELECT * FROM chkumapping(); + umname | umserver | umoptions +------------------+----------+--------------------------- + regress_testrol2 | sv1 | {user=CURRENT_USER} + current_user | sv2 | {"user=\"current_user\""} + regress_testrol2 | sv3 | {user=CURRENT_ROLE} + regress_testrol2 | sv4 | {user=USER} + user | sv5 | {"user=\"USER\""} + regress_testrol1 | sv6 | {user=SESSION_USER} + | sv7 | {user=PUBLIC} + Public | sv8 | {"user=\"Public\""} + regress_testrolx | sv9 | {user=regress_testrolx} +(9 rows) + +-- ALTER USER MAPPING +ALTER USER MAPPING FOR CURRENT_USER SERVER sv1 + OPTIONS (SET user 'CURRENT_USER_alt'); +ALTER USER MAPPING FOR "current_user" SERVER sv2 + OPTIONS (SET user '"current_user"_alt'); +ALTER USER MAPPING FOR CURRENT_ROLE SERVER sv3 + OPTIONS (SET user 'CURRENT_ROLE_alt'); +ALTER USER MAPPING FOR USER SERVER sv4 + OPTIONS (SET user 'USER_alt'); +ALTER USER MAPPING FOR "user" SERVER sv5 + OPTIONS (SET user '"user"_alt'); +ALTER USER MAPPING FOR SESSION_USER SERVER sv6 + OPTIONS (SET user 'SESSION_USER_alt'); +ALTER USER MAPPING FOR PUBLIC SERVER sv7 + OPTIONS (SET user 'public_alt'); +ALTER USER MAPPING FOR "Public" SERVER sv8 + OPTIONS (SET user '"Public"_alt'); +ALTER USER MAPPING FOR regress_testrolx SERVER sv9 + OPTIONS (SET user 'regress_testrolx_alt'); +ALTER USER MAPPING FOR nonexistent SERVER sv10 + OPTIONS (SET user 'nonexistent_alt'); -- error +ERROR: role "nonexistent" does not exist +SELECT * FROM chkumapping(); + umname | umserver | umoptions +------------------+----------+------------------------------- + regress_testrol2 | sv1 | {user=CURRENT_USER_alt} + current_user | sv2 | {"user=\"current_user\"_alt"} + regress_testrol2 | sv3 | {user=CURRENT_ROLE_alt} + regress_testrol2 | sv4 | {user=USER_alt} + user | sv5 | {"user=\"user\"_alt"} + regress_testrol1 | sv6 | {user=SESSION_USER_alt} + | sv7 | {user=public_alt} + Public | sv8 | {"user=\"Public\"_alt"} + regress_testrolx | sv9 | {user=regress_testrolx_alt} +(9 rows) + +-- DROP USER MAPPING +DROP USER MAPPING FOR CURRENT_USER SERVER sv1; +DROP USER MAPPING FOR "current_user" SERVER sv2; +DROP USER MAPPING FOR CURRENT_ROLE SERVER sv3; +DROP USER MAPPING FOR USER SERVER sv4; +DROP USER MAPPING FOR "user" SERVER sv5; +DROP USER MAPPING FOR SESSION_USER SERVER sv6; +DROP USER MAPPING FOR PUBLIC SERVER sv7; +DROP USER MAPPING FOR "Public" SERVER sv8; +DROP USER MAPPING FOR regress_testrolx SERVER sv9; +DROP USER MAPPING FOR nonexistent SERVER sv10; -- error +ERROR: role "nonexistent" does not exist +SELECT * FROM chkumapping(); + umname | umserver | umoptions +--------+----------+----------- +(0 rows) + +CREATE USER MAPPING FOR CURRENT_USER SERVER sv1 OPTIONS (user 'CURRENT_USER'); +CREATE USER MAPPING FOR "current_user" SERVER sv2 OPTIONS (user '"current_user"'); +CREATE USER MAPPING FOR CURRENT_ROLE SERVER sv3 OPTIONS (user 'CURRENT_ROLE'); +CREATE USER MAPPING FOR USER SERVER sv4 OPTIONS (user 'USER'); +CREATE USER MAPPING FOR "user" SERVER sv5 OPTIONS (user '"USER"'); +CREATE USER MAPPING FOR SESSION_USER SERVER sv6 OPTIONS (user 'SESSION_USER'); +CREATE USER MAPPING FOR PUBLIC SERVER sv7 OPTIONS (user 'PUBLIC'); +CREATE USER MAPPING FOR "Public" SERVER sv8 OPTIONS (user '"Public"'); +CREATE USER MAPPING FOR regress_testrolx SERVER sv9 OPTIONS (user 'regress_testrolx'); +SELECT * FROM chkumapping(); + umname | umserver | umoptions +------------------+----------+--------------------------- + regress_testrol2 | sv1 | {user=CURRENT_USER} + current_user | sv2 | {"user=\"current_user\""} + regress_testrol2 | sv3 | {user=CURRENT_ROLE} + regress_testrol2 | sv4 | {user=USER} + user | sv5 | {"user=\"USER\""} + regress_testrol1 | sv6 | {user=SESSION_USER} + | sv7 | {user=PUBLIC} + Public | sv8 | {"user=\"Public\""} + regress_testrolx | sv9 | {user=regress_testrolx} +(9 rows) + +-- DROP USER MAPPING IF EXISTS +DROP USER MAPPING IF EXISTS FOR CURRENT_USER SERVER sv1; +SELECT * FROM chkumapping(); + umname | umserver | umoptions +------------------+----------+--------------------------- + current_user | sv2 | {"user=\"current_user\""} + regress_testrol2 | sv3 | {user=CURRENT_ROLE} + regress_testrol2 | sv4 | {user=USER} + user | sv5 | {"user=\"USER\""} + regress_testrol1 | sv6 | {user=SESSION_USER} + | sv7 | {user=PUBLIC} + Public | sv8 | {"user=\"Public\""} + regress_testrolx | sv9 | {user=regress_testrolx} +(8 rows) + +DROP USER MAPPING IF EXISTS FOR "current_user" SERVER sv2; +SELECT * FROM chkumapping(); + umname | umserver | umoptions +------------------+----------+------------------------- + regress_testrol2 | sv3 | {user=CURRENT_ROLE} + regress_testrol2 | sv4 | {user=USER} + user | sv5 | {"user=\"USER\""} + regress_testrol1 | sv6 | {user=SESSION_USER} + | sv7 | {user=PUBLIC} + Public | sv8 | {"user=\"Public\""} + regress_testrolx | sv9 | {user=regress_testrolx} +(7 rows) + +DROP USER MAPPING IF EXISTS FOR CURRENT_USER SERVER sv3; +SELECT * FROM chkumapping(); + umname | umserver | umoptions +------------------+----------+------------------------- + regress_testrol2 | sv4 | {user=USER} + user | sv5 | {"user=\"USER\""} + regress_testrol1 | sv6 | {user=SESSION_USER} + | sv7 | {user=PUBLIC} + Public | sv8 | {"user=\"Public\""} + regress_testrolx | sv9 | {user=regress_testrolx} +(6 rows) + +DROP USER MAPPING IF EXISTS FOR USER SERVER sv4; +SELECT * FROM chkumapping(); + umname | umserver | umoptions +------------------+----------+------------------------- + user | sv5 | {"user=\"USER\""} + regress_testrol1 | sv6 | {user=SESSION_USER} + | sv7 | {user=PUBLIC} + Public | sv8 | {"user=\"Public\""} + regress_testrolx | sv9 | {user=regress_testrolx} +(5 rows) + +DROP USER MAPPING IF EXISTS FOR "user" SERVER sv5; +SELECT * FROM chkumapping(); + umname | umserver | umoptions +------------------+----------+------------------------- + regress_testrol1 | sv6 | {user=SESSION_USER} + | sv7 | {user=PUBLIC} + Public | sv8 | {"user=\"Public\""} + regress_testrolx | sv9 | {user=regress_testrolx} +(4 rows) + +DROP USER MAPPING IF EXISTS FOR SESSION_USER SERVER sv6; +SELECT * FROM chkumapping(); + umname | umserver | umoptions +------------------+----------+------------------------- + | sv7 | {user=PUBLIC} + Public | sv8 | {"user=\"Public\""} + regress_testrolx | sv9 | {user=regress_testrolx} +(3 rows) + +DROP USER MAPPING IF EXISTS FOR PUBLIC SERVER sv7; +SELECT * FROM chkumapping(); + umname | umserver | umoptions +------------------+----------+------------------------- + Public | sv8 | {"user=\"Public\""} + regress_testrolx | sv9 | {user=regress_testrolx} +(2 rows) + +DROP USER MAPPING IF EXISTS FOR "Public" SERVER sv8; +SELECT * FROM chkumapping(); + umname | umserver | umoptions +------------------+----------+------------------------- + regress_testrolx | sv9 | {user=regress_testrolx} +(1 row) + +DROP USER MAPPING IF EXISTS FOR regress_testrolx SERVER sv9; +SELECT * FROM chkumapping(); + umname | umserver | umoptions +--------+----------+----------- +(0 rows) + +DROP USER MAPPING IF EXISTS FOR nonexistent SERVER sv10; -- error +NOTICE: role "nonexistent" does not exist, skipping +-- GRANT/REVOKE +GRANT regress_testrol0 TO pg_signal_backend; -- success +SET ROLE pg_signal_backend; --success +RESET ROLE; +CREATE SCHEMA test_roles_schema AUTHORIZATION pg_signal_backend; --success +SET ROLE regress_testrol2; +UPDATE pg_proc SET proacl = null WHERE proname LIKE 'testagg_'; +SELECT proname, proacl FROM pg_proc WHERE proname LIKE 'testagg_'; + proname | proacl +----------+-------- + testagg1 | + testagg2 | + testagg3 | + testagg4 | + testagg5 | + testagg6 | + testagg7 | + testagg8 | + testagg9 | + testagga | +(10 rows) + +REVOKE ALL PRIVILEGES ON FUNCTION testagg1(int2) FROM PUBLIC; +REVOKE ALL PRIVILEGES ON FUNCTION testagg2(int2) FROM PUBLIC; +REVOKE ALL PRIVILEGES ON FUNCTION testagg3(int2) FROM PUBLIC; +REVOKE ALL PRIVILEGES ON FUNCTION testagg4(int2) FROM PUBLIC; +REVOKE ALL PRIVILEGES ON FUNCTION testagg5(int2) FROM PUBLIC; +REVOKE ALL PRIVILEGES ON FUNCTION testagg6(int2) FROM PUBLIC; +REVOKE ALL PRIVILEGES ON FUNCTION testagg7(int2) FROM PUBLIC; +REVOKE ALL PRIVILEGES ON FUNCTION testagg8(int2) FROM PUBLIC; +GRANT ALL PRIVILEGES ON FUNCTION testagg1(int2) TO PUBLIC; +GRANT ALL PRIVILEGES ON FUNCTION testagg2(int2) TO CURRENT_USER; +GRANT ALL PRIVILEGES ON FUNCTION testagg3(int2) TO "current_user"; +GRANT ALL PRIVILEGES ON FUNCTION testagg4(int2) TO CURRENT_ROLE; +GRANT ALL PRIVILEGES ON FUNCTION testagg5(int2) TO SESSION_USER; +GRANT ALL PRIVILEGES ON FUNCTION testagg6(int2) TO "Public"; +GRANT ALL PRIVILEGES ON FUNCTION testagg7(int2) TO regress_testrolx; +GRANT ALL PRIVILEGES ON FUNCTION testagg8(int2) TO "public"; +GRANT ALL PRIVILEGES ON FUNCTION testagg9(int2) + TO current_user, public, regress_testrolx; +SELECT proname, proacl FROM pg_proc WHERE proname LIKE 'testagg_'; + proname | proacl +----------+----------------------------------------------------------------------------------------------------------------------------------- + testagg1 | {regress_testrol2=X/regress_testrol2,=X/regress_testrol2} + testagg2 | {current_user=X/current_user,regress_testrol2=X/current_user} + testagg3 | {regress_testrol2=X/regress_testrol2,current_user=X/regress_testrol2} + testagg4 | {regress_testrol1=X/regress_testrol1,regress_testrol2=X/regress_testrol1} + testagg5 | {regress_testrolx=X/regress_testrolx,regress_testrol1=X/regress_testrolx} + testagg6 | {Public=X/Public} + testagg7 | {regress_testrol0=X/regress_testrol0,regress_testrolx=X/regress_testrol0} + testagg8 | {regress_testrol0=X/regress_testrol0,=X/regress_testrol0} + testagg9 | {=X/regress_testrol0,regress_testrol0=X/regress_testrol0,regress_testrol2=X/regress_testrol0,regress_testrolx=X/regress_testrol0} + testagga | +(10 rows) + +GRANT ALL PRIVILEGES ON FUNCTION testagga(int2) TO USER; --error +ERROR: syntax error at or near "USER" +LINE 1: GRANT ALL PRIVILEGES ON FUNCTION testagga(int2) TO USER; + ^ +GRANT ALL PRIVILEGES ON FUNCTION testagga(int2) TO NONE; --error +ERROR: role name "none" is reserved +LINE 1: GRANT ALL PRIVILEGES ON FUNCTION testagga(int2) TO NONE; + ^ +GRANT ALL PRIVILEGES ON FUNCTION testagga(int2) TO "none"; --error +ERROR: role name "none" is reserved +LINE 1: GRANT ALL PRIVILEGES ON FUNCTION testagga(int2) TO "none"; + ^ +SELECT proname, proacl FROM pg_proc WHERE proname LIKE 'testagg_'; + proname | proacl +----------+----------------------------------------------------------------------------------------------------------------------------------- + testagg1 | {regress_testrol2=X/regress_testrol2,=X/regress_testrol2} + testagg2 | {current_user=X/current_user,regress_testrol2=X/current_user} + testagg3 | {regress_testrol2=X/regress_testrol2,current_user=X/regress_testrol2} + testagg4 | {regress_testrol1=X/regress_testrol1,regress_testrol2=X/regress_testrol1} + testagg5 | {regress_testrolx=X/regress_testrolx,regress_testrol1=X/regress_testrolx} + testagg6 | {Public=X/Public} + testagg7 | {regress_testrol0=X/regress_testrol0,regress_testrolx=X/regress_testrol0} + testagg8 | {regress_testrol0=X/regress_testrol0,=X/regress_testrol0} + testagg9 | {=X/regress_testrol0,regress_testrol0=X/regress_testrol0,regress_testrol2=X/regress_testrol0,regress_testrolx=X/regress_testrol0} + testagga | +(10 rows) + +REVOKE ALL PRIVILEGES ON FUNCTION testagg1(int2) FROM PUBLIC; +REVOKE ALL PRIVILEGES ON FUNCTION testagg2(int2) FROM CURRENT_USER; +REVOKE ALL PRIVILEGES ON FUNCTION testagg3(int2) FROM "current_user"; +REVOKE ALL PRIVILEGES ON FUNCTION testagg4(int2) FROM CURRENT_ROLE; +REVOKE ALL PRIVILEGES ON FUNCTION testagg5(int2) FROM SESSION_USER; +REVOKE ALL PRIVILEGES ON FUNCTION testagg6(int2) FROM "Public"; +REVOKE ALL PRIVILEGES ON FUNCTION testagg7(int2) FROM regress_testrolx; +REVOKE ALL PRIVILEGES ON FUNCTION testagg8(int2) FROM "public"; +REVOKE ALL PRIVILEGES ON FUNCTION testagg9(int2) + FROM current_user, public, regress_testrolx; +SELECT proname, proacl FROM pg_proc WHERE proname LIKE 'testagg_'; + proname | proacl +----------+--------------------------------------- + testagg1 | {regress_testrol2=X/regress_testrol2} + testagg2 | {current_user=X/current_user} + testagg3 | {regress_testrol2=X/regress_testrol2} + testagg4 | {regress_testrol1=X/regress_testrol1} + testagg5 | {regress_testrolx=X/regress_testrolx} + testagg6 | {} + testagg7 | {regress_testrol0=X/regress_testrol0} + testagg8 | {regress_testrol0=X/regress_testrol0} + testagg9 | {regress_testrol0=X/regress_testrol0} + testagga | +(10 rows) + +REVOKE ALL PRIVILEGES ON FUNCTION testagga(int2) FROM USER; --error +ERROR: syntax error at or near "USER" +LINE 1: REVOKE ALL PRIVILEGES ON FUNCTION testagga(int2) FROM USER; + ^ +REVOKE ALL PRIVILEGES ON FUNCTION testagga(int2) FROM NONE; --error +ERROR: role name "none" is reserved +LINE 1: REVOKE ALL PRIVILEGES ON FUNCTION testagga(int2) FROM NONE; + ^ +REVOKE ALL PRIVILEGES ON FUNCTION testagga(int2) FROM "none"; --error +ERROR: role name "none" is reserved +LINE 1: ...EVOKE ALL PRIVILEGES ON FUNCTION testagga(int2) FROM "none"; + ^ +SELECT proname, proacl FROM pg_proc WHERE proname LIKE 'testagg_'; + proname | proacl +----------+--------------------------------------- + testagg1 | {regress_testrol2=X/regress_testrol2} + testagg2 | {current_user=X/current_user} + testagg3 | {regress_testrol2=X/regress_testrol2} + testagg4 | {regress_testrol1=X/regress_testrol1} + testagg5 | {regress_testrolx=X/regress_testrolx} + testagg6 | {} + testagg7 | {regress_testrol0=X/regress_testrol0} + testagg8 | {regress_testrol0=X/regress_testrol0} + testagg9 | {regress_testrol0=X/regress_testrol0} + testagga | +(10 rows) + +-- DEFAULT MONITORING ROLES +CREATE ROLE regress_role_haspriv; +CREATE ROLE regress_role_nopriv; +-- pg_read_all_stats +GRANT pg_read_all_stats TO regress_role_haspriv; +SET SESSION AUTHORIZATION regress_role_haspriv; +-- returns true with role member of pg_read_all_stats +SELECT COUNT(*) = 0 AS haspriv FROM pg_stat_activity + WHERE query = '<insufficient privilege>'; + haspriv +--------- + t +(1 row) + +SET SESSION AUTHORIZATION regress_role_nopriv; +-- returns false with role not member of pg_read_all_stats +SELECT COUNT(*) = 0 AS haspriv FROM pg_stat_activity + WHERE query = '<insufficient privilege>'; + haspriv +--------- + f +(1 row) + +RESET SESSION AUTHORIZATION; +REVOKE pg_read_all_stats FROM regress_role_haspriv; +-- pg_read_all_settings +GRANT pg_read_all_settings TO regress_role_haspriv; +BEGIN; +-- A GUC using GUC_SUPERUSER_ONLY is useful for negative tests. +SET LOCAL session_preload_libraries TO 'path-to-preload-libraries'; +SET SESSION AUTHORIZATION regress_role_haspriv; +-- passes with role member of pg_read_all_settings +SHOW session_preload_libraries; + session_preload_libraries +----------------------------- + "path-to-preload-libraries" +(1 row) + +SET SESSION AUTHORIZATION regress_role_nopriv; +-- fails with role not member of pg_read_all_settings +SHOW session_preload_libraries; +ERROR: must be superuser or have privileges of pg_read_all_settings to examine "session_preload_libraries" +RESET SESSION AUTHORIZATION; +ERROR: current transaction is aborted, commands ignored until end of transaction block +ROLLBACK; +REVOKE pg_read_all_settings FROM regress_role_haspriv; +-- clean up +\c +DROP SCHEMA test_roles_schema; +DROP OWNED BY regress_testrol0, "Public", "current_role", "current_user", regress_testrol1, regress_testrol2, regress_testrolx CASCADE; +DROP ROLE regress_testrol0, regress_testrol1, regress_testrol2, regress_testrolx; +DROP ROLE "Public", "None", "current_role", "current_user", "session_user", "user"; +DROP ROLE regress_role_haspriv, regress_role_nopriv; |