/
githubmirror
/
postgres
Обзор
Документация
Войти
/
githubmirror
/
postgres
Код
Запросы
0
Пакеты
0
Релизы
0
Аналитика
Безопасность
master
src/test/modules/unsafe_tests/expected/rolenames.out
1 096 строк
46 KB
Andrew Dunstan
Disallow CR and LF in database, role, and tablespace names
23 фев 2026, 19:19
23 фев 2026, 19:19
b380a56
Код
Авторство
О чём код?
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 "invalid rolename"; -- error ERROR: role name "invalid rolename" contains a newline or carriage return character 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: permission denied to examine "session_preload_libraries" DETAIL: Only roles with privileges of the "pg_read_all_settings" role may examine this parameter. 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;