1CREATE OR REPLACE FUNCTION chkrolattr() 2 RETURNS TABLE ("role" name, rolekeyword text, canlogin bool, replication bool) 3 AS $$ 4SELECT r.rolname, v.keyword, r.rolcanlogin, r.rolreplication 5 FROM pg_roles r 6 JOIN (VALUES(CURRENT_USER, 'current_user'), 7 (SESSION_USER, 'session_user'), 8 ('current_user', '-'), 9 ('session_user', '-'), 10 ('Public', '-'), 11 ('None', '-')) 12 AS v(uname, keyword) 13 ON (r.rolname = v.uname) 14 ORDER BY 1; 15$$ LANGUAGE SQL; 16CREATE OR REPLACE FUNCTION chksetconfig() 17 RETURNS TABLE (db name, "role" name, rolkeyword text, setconfig text[]) 18 AS $$ 19SELECT COALESCE(d.datname, 'ALL'), COALESCE(r.rolname, 'ALL'), 20 COALESCE(v.keyword, '-'), s.setconfig 21 FROM pg_db_role_setting s 22 LEFT JOIN pg_roles r ON (r.oid = s.setrole) 23 LEFT JOIN pg_database d ON (d.oid = s.setdatabase) 24 LEFT JOIN (VALUES(CURRENT_USER, 'current_user'), 25 (SESSION_USER, 'session_user')) 26 AS v(uname, keyword) 27 ON (r.rolname = v.uname) 28 WHERE (r.rolname) IN ('Public', 'current_user', 'regress_testrol1', 'regress_testrol2') 29ORDER BY 1, 2; 30$$ LANGUAGE SQL; 31CREATE OR REPLACE FUNCTION chkumapping() 32 RETURNS TABLE (umname name, umserver name, umoptions text[]) 33 AS $$ 34SELECT r.rolname, s.srvname, m.umoptions 35 FROM pg_user_mapping m 36 LEFT JOIN pg_roles r ON (r.oid = m.umuser) 37 JOIN pg_foreign_server s ON (s.oid = m.umserver) 38 ORDER BY 2; 39$$ LANGUAGE SQL; 40CREATE ROLE "Public"; 41CREATE ROLE "None"; 42CREATE ROLE "current_user"; 43CREATE ROLE "session_user"; 44CREATE ROLE "user"; 45CREATE ROLE current_user; -- error 46ERROR: CURRENT_USER cannot be used as a role name here 47LINE 1: CREATE ROLE current_user; 48 ^ 49CREATE ROLE current_role; -- error 50ERROR: syntax error at or near "current_role" 51LINE 1: CREATE ROLE current_role; 52 ^ 53CREATE ROLE session_user; -- error 54ERROR: SESSION_USER cannot be used as a role name here 55LINE 1: CREATE ROLE session_user; 56 ^ 57CREATE ROLE user; -- error 58ERROR: syntax error at or near "user" 59LINE 1: CREATE ROLE user; 60 ^ 61CREATE ROLE all; -- error 62ERROR: syntax error at or near "all" 63LINE 1: CREATE ROLE all; 64 ^ 65CREATE ROLE public; -- error 66ERROR: role name "public" is reserved 67LINE 1: CREATE ROLE public; 68 ^ 69CREATE ROLE "public"; -- error 70ERROR: role name "public" is reserved 71LINE 1: CREATE ROLE "public"; 72 ^ 73CREATE ROLE none; -- error 74ERROR: role name "none" is reserved 75LINE 1: CREATE ROLE none; 76 ^ 77CREATE ROLE "none"; -- error 78ERROR: role name "none" is reserved 79LINE 1: CREATE ROLE "none"; 80 ^ 81CREATE ROLE pg_abc; -- error 82ERROR: role name "pg_abc" is reserved 83DETAIL: Role names starting with "pg_" are reserved. 84CREATE ROLE "pg_abc"; -- error 85ERROR: role name "pg_abc" is reserved 86DETAIL: Role names starting with "pg_" are reserved. 87CREATE ROLE pg_abcdef; -- error 88ERROR: role name "pg_abcdef" is reserved 89DETAIL: Role names starting with "pg_" are reserved. 90CREATE ROLE "pg_abcdef"; -- error 91ERROR: role name "pg_abcdef" is reserved 92DETAIL: Role names starting with "pg_" are reserved. 93CREATE ROLE regress_testrol0 SUPERUSER LOGIN; 94CREATE ROLE regress_testrolx SUPERUSER LOGIN; 95CREATE ROLE regress_testrol2 SUPERUSER; 96CREATE ROLE regress_testrol1 SUPERUSER LOGIN IN ROLE regress_testrol2; 97\c - 98SET SESSION AUTHORIZATION regress_testrol1; 99SET ROLE regress_testrol2; 100-- ALTER ROLE 101BEGIN; 102SELECT * FROM chkrolattr(); 103 role | rolekeyword | canlogin | replication 104------------------+--------------+----------+------------- 105 None | - | f | f 106 Public | - | f | f 107 current_user | - | f | f 108 regress_testrol1 | session_user | t | f 109 regress_testrol2 | current_user | f | f 110 session_user | - | f | f 111(6 rows) 112 113ALTER ROLE CURRENT_USER WITH REPLICATION; 114SELECT * FROM chkrolattr(); 115 role | rolekeyword | canlogin | replication 116------------------+--------------+----------+------------- 117 None | - | f | f 118 Public | - | f | f 119 current_user | - | f | f 120 regress_testrol1 | session_user | t | f 121 regress_testrol2 | current_user | f | t 122 session_user | - | f | f 123(6 rows) 124 125ALTER ROLE "current_user" WITH REPLICATION; 126SELECT * FROM chkrolattr(); 127 role | rolekeyword | canlogin | replication 128------------------+--------------+----------+------------- 129 None | - | f | f 130 Public | - | f | f 131 current_user | - | f | t 132 regress_testrol1 | session_user | t | f 133 regress_testrol2 | current_user | f | t 134 session_user | - | f | f 135(6 rows) 136 137ALTER ROLE SESSION_USER WITH REPLICATION; 138SELECT * FROM chkrolattr(); 139 role | rolekeyword | canlogin | replication 140------------------+--------------+----------+------------- 141 None | - | f | f 142 Public | - | f | f 143 current_user | - | f | t 144 regress_testrol1 | session_user | t | t 145 regress_testrol2 | current_user | f | t 146 session_user | - | f | f 147(6 rows) 148 149ALTER ROLE "session_user" WITH REPLICATION; 150SELECT * FROM chkrolattr(); 151 role | rolekeyword | canlogin | replication 152------------------+--------------+----------+------------- 153 None | - | f | f 154 Public | - | f | f 155 current_user | - | f | t 156 regress_testrol1 | session_user | t | t 157 regress_testrol2 | current_user | f | t 158 session_user | - | f | t 159(6 rows) 160 161ALTER USER "Public" WITH REPLICATION; 162ALTER USER "None" WITH REPLICATION; 163SELECT * FROM chkrolattr(); 164 role | rolekeyword | canlogin | replication 165------------------+--------------+----------+------------- 166 None | - | f | t 167 Public | - | f | t 168 current_user | - | f | t 169 regress_testrol1 | session_user | t | t 170 regress_testrol2 | current_user | f | t 171 session_user | - | f | t 172(6 rows) 173 174ALTER USER regress_testrol1 WITH NOREPLICATION; 175ALTER USER regress_testrol2 WITH NOREPLICATION; 176SELECT * FROM chkrolattr(); 177 role | rolekeyword | canlogin | replication 178------------------+--------------+----------+------------- 179 None | - | f | t 180 Public | - | f | t 181 current_user | - | f | t 182 regress_testrol1 | session_user | t | f 183 regress_testrol2 | current_user | f | f 184 session_user | - | f | t 185(6 rows) 186 187ROLLBACK; 188ALTER ROLE USER WITH LOGIN; -- error 189ERROR: syntax error at or near "USER" 190LINE 1: ALTER ROLE USER WITH LOGIN; 191 ^ 192ALTER ROLE CURRENT_ROLE WITH LOGIN; --error 193ERROR: syntax error at or near "CURRENT_ROLE" 194LINE 1: ALTER ROLE CURRENT_ROLE WITH LOGIN; 195 ^ 196ALTER ROLE ALL WITH REPLICATION; -- error 197ERROR: syntax error at or near "WITH" 198LINE 1: ALTER ROLE ALL WITH REPLICATION; 199 ^ 200ALTER ROLE SESSION_ROLE WITH NOREPLICATION; -- error 201ERROR: role "session_role" does not exist 202ALTER ROLE PUBLIC WITH NOREPLICATION; -- error 203ERROR: role "public" does not exist 204ALTER ROLE "public" WITH NOREPLICATION; -- error 205ERROR: role "public" does not exist 206ALTER ROLE NONE WITH NOREPLICATION; -- error 207ERROR: role name "none" is reserved 208LINE 1: ALTER ROLE NONE WITH NOREPLICATION; 209 ^ 210ALTER ROLE "none" WITH NOREPLICATION; -- error 211ERROR: role name "none" is reserved 212LINE 1: ALTER ROLE "none" WITH NOREPLICATION; 213 ^ 214ALTER ROLE nonexistent WITH NOREPLICATION; -- error 215ERROR: role "nonexistent" does not exist 216-- ALTER USER 217BEGIN; 218SELECT * FROM chkrolattr(); 219 role | rolekeyword | canlogin | replication 220------------------+--------------+----------+------------- 221 None | - | f | f 222 Public | - | f | f 223 current_user | - | f | f 224 regress_testrol1 | session_user | t | f 225 regress_testrol2 | current_user | f | f 226 session_user | - | f | f 227(6 rows) 228 229ALTER USER CURRENT_USER WITH REPLICATION; 230SELECT * FROM chkrolattr(); 231 role | rolekeyword | canlogin | replication 232------------------+--------------+----------+------------- 233 None | - | f | f 234 Public | - | f | f 235 current_user | - | f | f 236 regress_testrol1 | session_user | t | f 237 regress_testrol2 | current_user | f | t 238 session_user | - | f | f 239(6 rows) 240 241ALTER USER "current_user" WITH REPLICATION; 242SELECT * FROM chkrolattr(); 243 role | rolekeyword | canlogin | replication 244------------------+--------------+----------+------------- 245 None | - | f | f 246 Public | - | f | f 247 current_user | - | f | t 248 regress_testrol1 | session_user | t | f 249 regress_testrol2 | current_user | f | t 250 session_user | - | f | f 251(6 rows) 252 253ALTER USER SESSION_USER WITH REPLICATION; 254SELECT * FROM chkrolattr(); 255 role | rolekeyword | canlogin | replication 256------------------+--------------+----------+------------- 257 None | - | f | f 258 Public | - | f | f 259 current_user | - | f | t 260 regress_testrol1 | session_user | t | t 261 regress_testrol2 | current_user | f | t 262 session_user | - | f | f 263(6 rows) 264 265ALTER USER "session_user" WITH REPLICATION; 266SELECT * FROM chkrolattr(); 267 role | rolekeyword | canlogin | replication 268------------------+--------------+----------+------------- 269 None | - | f | f 270 Public | - | f | f 271 current_user | - | f | t 272 regress_testrol1 | session_user | t | t 273 regress_testrol2 | current_user | f | t 274 session_user | - | f | t 275(6 rows) 276 277ALTER USER "Public" WITH REPLICATION; 278ALTER USER "None" WITH REPLICATION; 279SELECT * FROM chkrolattr(); 280 role | rolekeyword | canlogin | replication 281------------------+--------------+----------+------------- 282 None | - | f | t 283 Public | - | f | t 284 current_user | - | f | t 285 regress_testrol1 | session_user | t | t 286 regress_testrol2 | current_user | f | t 287 session_user | - | f | t 288(6 rows) 289 290ALTER USER regress_testrol1 WITH NOREPLICATION; 291ALTER USER regress_testrol2 WITH NOREPLICATION; 292SELECT * FROM chkrolattr(); 293 role | rolekeyword | canlogin | replication 294------------------+--------------+----------+------------- 295 None | - | f | t 296 Public | - | f | t 297 current_user | - | f | t 298 regress_testrol1 | session_user | t | f 299 regress_testrol2 | current_user | f | f 300 session_user | - | f | t 301(6 rows) 302 303ROLLBACK; 304ALTER USER USER WITH LOGIN; -- error 305ERROR: syntax error at or near "USER" 306LINE 1: ALTER USER USER WITH LOGIN; 307 ^ 308ALTER USER CURRENT_ROLE WITH LOGIN; -- error 309ERROR: syntax error at or near "CURRENT_ROLE" 310LINE 1: ALTER USER CURRENT_ROLE WITH LOGIN; 311 ^ 312ALTER USER ALL WITH REPLICATION; -- error 313ERROR: syntax error at or near "WITH" 314LINE 1: ALTER USER ALL WITH REPLICATION; 315 ^ 316ALTER USER SESSION_ROLE WITH NOREPLICATION; -- error 317ERROR: role "session_role" does not exist 318ALTER USER PUBLIC WITH NOREPLICATION; -- error 319ERROR: role "public" does not exist 320ALTER USER "public" WITH NOREPLICATION; -- error 321ERROR: role "public" does not exist 322ALTER USER NONE WITH NOREPLICATION; -- error 323ERROR: role name "none" is reserved 324LINE 1: ALTER USER NONE WITH NOREPLICATION; 325 ^ 326ALTER USER "none" WITH NOREPLICATION; -- error 327ERROR: role name "none" is reserved 328LINE 1: ALTER USER "none" WITH NOREPLICATION; 329 ^ 330ALTER USER nonexistent WITH NOREPLICATION; -- error 331ERROR: role "nonexistent" does not exist 332-- ALTER ROLE SET/RESET 333SELECT * FROM chksetconfig(); 334 db | role | rolkeyword | setconfig 335----+------+------------+----------- 336(0 rows) 337 338ALTER ROLE CURRENT_USER SET application_name to 'FOO'; 339ALTER ROLE SESSION_USER SET application_name to 'BAR'; 340ALTER ROLE "current_user" SET application_name to 'FOOFOO'; 341ALTER ROLE "Public" SET application_name to 'BARBAR'; 342ALTER ROLE ALL SET application_name to 'SLAP'; 343SELECT * FROM chksetconfig(); 344 db | role | rolkeyword | setconfig 345-----+------------------+--------------+--------------------------- 346 ALL | Public | - | {application_name=BARBAR} 347 ALL | current_user | - | {application_name=FOOFOO} 348 ALL | regress_testrol1 | session_user | {application_name=BAR} 349 ALL | regress_testrol2 | current_user | {application_name=FOO} 350(4 rows) 351 352ALTER ROLE regress_testrol1 SET application_name to 'SLAM'; 353SELECT * FROM chksetconfig(); 354 db | role | rolkeyword | setconfig 355-----+------------------+--------------+--------------------------- 356 ALL | Public | - | {application_name=BARBAR} 357 ALL | current_user | - | {application_name=FOOFOO} 358 ALL | regress_testrol1 | session_user | {application_name=SLAM} 359 ALL | regress_testrol2 | current_user | {application_name=FOO} 360(4 rows) 361 362ALTER ROLE CURRENT_USER RESET application_name; 363ALTER ROLE SESSION_USER RESET application_name; 364ALTER ROLE "current_user" RESET application_name; 365ALTER ROLE "Public" RESET application_name; 366ALTER ROLE ALL RESET application_name; 367SELECT * FROM chksetconfig(); 368 db | role | rolkeyword | setconfig 369----+------+------------+----------- 370(0 rows) 371 372ALTER ROLE CURRENT_ROLE SET application_name to 'BAZ'; -- error 373ERROR: syntax error at or near "CURRENT_ROLE" 374LINE 1: ALTER ROLE CURRENT_ROLE SET application_name to 'BAZ'; 375 ^ 376ALTER ROLE USER SET application_name to 'BOOM'; -- error 377ERROR: syntax error at or near "USER" 378LINE 1: ALTER ROLE USER SET application_name to 'BOOM'; 379 ^ 380ALTER ROLE PUBLIC SET application_name to 'BOMB'; -- error 381ERROR: role "public" does not exist 382ALTER ROLE nonexistent SET application_name to 'BOMB'; -- error 383ERROR: role "nonexistent" does not exist 384-- ALTER USER SET/RESET 385SELECT * FROM chksetconfig(); 386 db | role | rolkeyword | setconfig 387----+------+------------+----------- 388(0 rows) 389 390ALTER USER CURRENT_USER SET application_name to 'FOO'; 391ALTER USER SESSION_USER SET application_name to 'BAR'; 392ALTER USER "current_user" SET application_name to 'FOOFOO'; 393ALTER USER "Public" SET application_name to 'BARBAR'; 394ALTER USER ALL SET application_name to 'SLAP'; 395SELECT * FROM chksetconfig(); 396 db | role | rolkeyword | setconfig 397-----+------------------+--------------+--------------------------- 398 ALL | Public | - | {application_name=BARBAR} 399 ALL | current_user | - | {application_name=FOOFOO} 400 ALL | regress_testrol1 | session_user | {application_name=BAR} 401 ALL | regress_testrol2 | current_user | {application_name=FOO} 402(4 rows) 403 404ALTER USER regress_testrol1 SET application_name to 'SLAM'; 405SELECT * FROM chksetconfig(); 406 db | role | rolkeyword | setconfig 407-----+------------------+--------------+--------------------------- 408 ALL | Public | - | {application_name=BARBAR} 409 ALL | current_user | - | {application_name=FOOFOO} 410 ALL | regress_testrol1 | session_user | {application_name=SLAM} 411 ALL | regress_testrol2 | current_user | {application_name=FOO} 412(4 rows) 413 414ALTER USER CURRENT_USER RESET application_name; 415ALTER USER SESSION_USER RESET application_name; 416ALTER USER "current_user" RESET application_name; 417ALTER USER "Public" RESET application_name; 418ALTER USER ALL RESET application_name; 419SELECT * FROM chksetconfig(); 420 db | role | rolkeyword | setconfig 421----+------+------------+----------- 422(0 rows) 423 424ALTER USER CURRENT_USER SET application_name to 'BAZ'; -- error 425ALTER USER USER SET application_name to 'BOOM'; -- error 426ERROR: syntax error at or near "USER" 427LINE 1: ALTER USER USER SET application_name to 'BOOM'; 428 ^ 429ALTER USER PUBLIC SET application_name to 'BOMB'; -- error 430ERROR: role "public" does not exist 431ALTER USER NONE SET application_name to 'BOMB'; -- error 432ERROR: role name "none" is reserved 433LINE 1: ALTER USER NONE SET application_name to 'BOMB'; 434 ^ 435ALTER USER nonexistent SET application_name to 'BOMB'; -- error 436ERROR: role "nonexistent" does not exist 437-- CREATE SCHEMA 438set client_min_messages to error; 439CREATE SCHEMA newschema1 AUTHORIZATION CURRENT_USER; 440CREATE SCHEMA newschema2 AUTHORIZATION "current_user"; 441CREATE SCHEMA newschema3 AUTHORIZATION SESSION_USER; 442CREATE SCHEMA newschema4 AUTHORIZATION regress_testrolx; 443CREATE SCHEMA newschema5 AUTHORIZATION "Public"; 444CREATE SCHEMA newschema6 AUTHORIZATION USER; -- error 445ERROR: syntax error at or near "USER" 446LINE 1: CREATE SCHEMA newschema6 AUTHORIZATION USER; 447 ^ 448CREATE SCHEMA newschema6 AUTHORIZATION CURRENT_ROLE; -- error 449ERROR: syntax error at or near "CURRENT_ROLE" 450LINE 1: CREATE SCHEMA newschema6 AUTHORIZATION CURRENT_ROLE; 451 ^ 452CREATE SCHEMA newschema6 AUTHORIZATION PUBLIC; -- error 453ERROR: role "public" does not exist 454CREATE SCHEMA newschema6 AUTHORIZATION "public"; -- error 455ERROR: role "public" does not exist 456CREATE SCHEMA newschema6 AUTHORIZATION NONE; -- error 457ERROR: role name "none" is reserved 458LINE 1: CREATE SCHEMA newschema6 AUTHORIZATION NONE; 459 ^ 460CREATE SCHEMA newschema6 AUTHORIZATION nonexistent; -- error 461ERROR: role "nonexistent" does not exist 462SELECT n.nspname, r.rolname FROM pg_namespace n 463 JOIN pg_roles r ON (r.oid = n.nspowner) 464 WHERE n.nspname LIKE 'newschema_' ORDER BY 1; 465 nspname | rolname 466------------+------------------ 467 newschema1 | regress_testrol2 468 newschema2 | current_user 469 newschema3 | regress_testrol1 470 newschema4 | regress_testrolx 471 newschema5 | Public 472(5 rows) 473 474CREATE SCHEMA IF NOT EXISTS newschema1 AUTHORIZATION CURRENT_USER; 475CREATE SCHEMA IF NOT EXISTS newschema2 AUTHORIZATION "current_user"; 476CREATE SCHEMA IF NOT EXISTS newschema3 AUTHORIZATION SESSION_USER; 477CREATE SCHEMA IF NOT EXISTS newschema4 AUTHORIZATION regress_testrolx; 478CREATE SCHEMA IF NOT EXISTS newschema5 AUTHORIZATION "Public"; 479CREATE SCHEMA IF NOT EXISTS newschema6 AUTHORIZATION USER; -- error 480ERROR: syntax error at or near "USER" 481LINE 1: CREATE SCHEMA IF NOT EXISTS newschema6 AUTHORIZATION USER; 482 ^ 483CREATE SCHEMA IF NOT EXISTS newschema6 AUTHORIZATION CURRENT_ROLE; -- error 484ERROR: syntax error at or near "CURRENT_ROLE" 485LINE 1: ...ATE SCHEMA IF NOT EXISTS newschema6 AUTHORIZATION CURRENT_RO... 486 ^ 487CREATE SCHEMA IF NOT EXISTS newschema6 AUTHORIZATION PUBLIC; -- error 488ERROR: role "public" does not exist 489CREATE SCHEMA IF NOT EXISTS newschema6 AUTHORIZATION "public"; -- error 490ERROR: role "public" does not exist 491CREATE SCHEMA IF NOT EXISTS newschema6 AUTHORIZATION NONE; -- error 492ERROR: role name "none" is reserved 493LINE 1: CREATE SCHEMA IF NOT EXISTS newschema6 AUTHORIZATION NONE; 494 ^ 495CREATE SCHEMA IF NOT EXISTS newschema6 AUTHORIZATION nonexistent; -- error 496ERROR: role "nonexistent" does not exist 497SELECT n.nspname, r.rolname FROM pg_namespace n 498 JOIN pg_roles r ON (r.oid = n.nspowner) 499 WHERE n.nspname LIKE 'newschema_' ORDER BY 1; 500 nspname | rolname 501------------+------------------ 502 newschema1 | regress_testrol2 503 newschema2 | current_user 504 newschema3 | regress_testrol1 505 newschema4 | regress_testrolx 506 newschema5 | Public 507(5 rows) 508 509-- ALTER TABLE OWNER TO 510\c - 511SET SESSION AUTHORIZATION regress_testrol0; 512set client_min_messages to error; 513CREATE TABLE testtab1 (a int); 514CREATE TABLE testtab2 (a int); 515CREATE TABLE testtab3 (a int); 516CREATE TABLE testtab4 (a int); 517CREATE TABLE testtab5 (a int); 518CREATE TABLE testtab6 (a int); 519\c - 520SET SESSION AUTHORIZATION regress_testrol1; 521SET ROLE regress_testrol2; 522ALTER TABLE testtab1 OWNER TO CURRENT_USER; 523ALTER TABLE testtab2 OWNER TO "current_user"; 524ALTER TABLE testtab3 OWNER TO SESSION_USER; 525ALTER TABLE testtab4 OWNER TO regress_testrolx; 526ALTER TABLE testtab5 OWNER TO "Public"; 527ALTER TABLE testtab6 OWNER TO CURRENT_ROLE; -- error 528ERROR: syntax error at or near "CURRENT_ROLE" 529LINE 1: ALTER TABLE testtab6 OWNER TO CURRENT_ROLE; 530 ^ 531ALTER TABLE testtab6 OWNER TO USER; --error 532ERROR: syntax error at or near "USER" 533LINE 1: ALTER TABLE testtab6 OWNER TO USER; 534 ^ 535ALTER TABLE testtab6 OWNER TO PUBLIC; -- error 536ERROR: role "public" does not exist 537ALTER TABLE testtab6 OWNER TO "public"; -- error 538ERROR: role "public" does not exist 539ALTER TABLE testtab6 OWNER TO nonexistent; -- error 540ERROR: role "nonexistent" does not exist 541SELECT c.relname, r.rolname 542 FROM pg_class c JOIN pg_roles r ON (r.oid = c.relowner) 543 WHERE relname LIKE 'testtab_' 544 ORDER BY 1; 545 relname | rolname 546----------+------------------ 547 testtab1 | regress_testrol2 548 testtab2 | current_user 549 testtab3 | regress_testrol1 550 testtab4 | regress_testrolx 551 testtab5 | Public 552 testtab6 | regress_testrol0 553(6 rows) 554 555-- ALTER TABLE, VIEW, MATERIALIZED VIEW, FOREIGN TABLE, SEQUENCE are 556-- changed their owner in the same way. 557-- ALTER AGGREGATE 558\c - 559SET SESSION AUTHORIZATION regress_testrol0; 560CREATE AGGREGATE testagg1(int2) (SFUNC = int2_sum, STYPE = int8); 561CREATE AGGREGATE testagg2(int2) (SFUNC = int2_sum, STYPE = int8); 562CREATE AGGREGATE testagg3(int2) (SFUNC = int2_sum, STYPE = int8); 563CREATE AGGREGATE testagg4(int2) (SFUNC = int2_sum, STYPE = int8); 564CREATE AGGREGATE testagg5(int2) (SFUNC = int2_sum, STYPE = int8); 565CREATE AGGREGATE testagg5(int2) (SFUNC = int2_sum, STYPE = int8); 566ERROR: function "testagg5" already exists with same argument types 567CREATE AGGREGATE testagg6(int2) (SFUNC = int2_sum, STYPE = int8); 568CREATE AGGREGATE testagg7(int2) (SFUNC = int2_sum, STYPE = int8); 569CREATE AGGREGATE testagg8(int2) (SFUNC = int2_sum, STYPE = int8); 570CREATE AGGREGATE testagg9(int2) (SFUNC = int2_sum, STYPE = int8); 571\c - 572SET SESSION AUTHORIZATION regress_testrol1; 573SET ROLE regress_testrol2; 574ALTER AGGREGATE testagg1(int2) OWNER TO CURRENT_USER; 575ALTER AGGREGATE testagg2(int2) OWNER TO "current_user"; 576ALTER AGGREGATE testagg3(int2) OWNER TO SESSION_USER; 577ALTER AGGREGATE testagg4(int2) OWNER TO regress_testrolx; 578ALTER AGGREGATE testagg5(int2) OWNER TO "Public"; 579ALTER AGGREGATE testagg5(int2) OWNER TO CURRENT_ROLE; -- error 580ERROR: syntax error at or near "CURRENT_ROLE" 581LINE 1: ALTER AGGREGATE testagg5(int2) OWNER TO CURRENT_ROLE; 582 ^ 583ALTER AGGREGATE testagg5(int2) OWNER TO USER; -- error 584ERROR: syntax error at or near "USER" 585LINE 1: ALTER AGGREGATE testagg5(int2) OWNER TO USER; 586 ^ 587ALTER AGGREGATE testagg5(int2) OWNER TO PUBLIC; -- error 588ERROR: role "public" does not exist 589ALTER AGGREGATE testagg5(int2) OWNER TO "public"; -- error 590ERROR: role "public" does not exist 591ALTER AGGREGATE testagg5(int2) OWNER TO nonexistent; -- error 592ERROR: role "nonexistent" does not exist 593SELECT p.proname, r.rolname 594 FROM pg_proc p JOIN pg_roles r ON (r.oid = p.proowner) 595 WHERE proname LIKE 'testagg_' 596 ORDER BY 1; 597 proname | rolname 598----------+------------------ 599 testagg1 | regress_testrol2 600 testagg2 | current_user 601 testagg3 | regress_testrol1 602 testagg4 | regress_testrolx 603 testagg5 | Public 604 testagg6 | regress_testrol0 605 testagg7 | regress_testrol0 606 testagg8 | regress_testrol0 607 testagg9 | regress_testrol0 608(9 rows) 609 610-- CREATE USER MAPPING 611CREATE FOREIGN DATA WRAPPER test_wrapper; 612CREATE SERVER sv1 FOREIGN DATA WRAPPER test_wrapper; 613CREATE SERVER sv2 FOREIGN DATA WRAPPER test_wrapper; 614CREATE SERVER sv3 FOREIGN DATA WRAPPER test_wrapper; 615CREATE SERVER sv4 FOREIGN DATA WRAPPER test_wrapper; 616CREATE SERVER sv5 FOREIGN DATA WRAPPER test_wrapper; 617CREATE SERVER sv6 FOREIGN DATA WRAPPER test_wrapper; 618CREATE SERVER sv7 FOREIGN DATA WRAPPER test_wrapper; 619CREATE SERVER sv8 FOREIGN DATA WRAPPER test_wrapper; 620CREATE SERVER sv9 FOREIGN DATA WRAPPER test_wrapper; 621CREATE USER MAPPING FOR CURRENT_USER SERVER sv1 OPTIONS (user 'CURRENT_USER'); 622CREATE USER MAPPING FOR "current_user" SERVER sv2 OPTIONS (user '"current_user"'); 623CREATE USER MAPPING FOR USER SERVER sv3 OPTIONS (user 'USER'); 624CREATE USER MAPPING FOR "user" SERVER sv4 OPTIONS (user '"USER"'); 625CREATE USER MAPPING FOR SESSION_USER SERVER sv5 OPTIONS (user 'SESSION_USER'); 626CREATE USER MAPPING FOR PUBLIC SERVER sv6 OPTIONS (user 'PUBLIC'); 627CREATE USER MAPPING FOR "Public" SERVER sv7 OPTIONS (user '"Public"'); 628CREATE USER MAPPING FOR regress_testrolx SERVER sv8 OPTIONS (user 'regress_testrolx'); 629CREATE USER MAPPING FOR CURRENT_ROLE SERVER sv9 630 OPTIONS (user 'CURRENT_ROLE'); -- error 631ERROR: syntax error at or near "CURRENT_ROLE" 632LINE 1: CREATE USER MAPPING FOR CURRENT_ROLE SERVER sv9 633 ^ 634CREATE USER MAPPING FOR nonexistent SERVER sv9 635 OPTIONS (user 'nonexistent'); -- error; 636ERROR: role "nonexistent" does not exist 637SELECT * FROM chkumapping(); 638 umname | umserver | umoptions 639------------------+----------+--------------------------- 640 regress_testrol2 | sv1 | {user=CURRENT_USER} 641 current_user | sv2 | {"user=\"current_user\""} 642 regress_testrol2 | sv3 | {user=USER} 643 user | sv4 | {"user=\"USER\""} 644 regress_testrol1 | sv5 | {user=SESSION_USER} 645 | sv6 | {user=PUBLIC} 646 Public | sv7 | {"user=\"Public\""} 647 regress_testrolx | sv8 | {user=regress_testrolx} 648(8 rows) 649 650-- ALTER USER MAPPING 651ALTER USER MAPPING FOR CURRENT_USER SERVER sv1 652 OPTIONS (SET user 'CURRENT_USER_alt'); 653ALTER USER MAPPING FOR "current_user" SERVER sv2 654 OPTIONS (SET user '"current_user"_alt'); 655ALTER USER MAPPING FOR USER SERVER sv3 656 OPTIONS (SET user 'USER_alt'); 657ALTER USER MAPPING FOR "user" SERVER sv4 658 OPTIONS (SET user '"user"_alt'); 659ALTER USER MAPPING FOR SESSION_USER SERVER sv5 660 OPTIONS (SET user 'SESSION_USER_alt'); 661ALTER USER MAPPING FOR PUBLIC SERVER sv6 662 OPTIONS (SET user 'public_alt'); 663ALTER USER MAPPING FOR "Public" SERVER sv7 664 OPTIONS (SET user '"Public"_alt'); 665ALTER USER MAPPING FOR regress_testrolx SERVER sv8 666 OPTIONS (SET user 'regress_testrolx_alt'); 667ALTER USER MAPPING FOR CURRENT_ROLE SERVER sv9 668 OPTIONS (SET user 'CURRENT_ROLE_alt'); 669ERROR: syntax error at or near "CURRENT_ROLE" 670LINE 1: ALTER USER MAPPING FOR CURRENT_ROLE SERVER sv9 671 ^ 672ALTER USER MAPPING FOR nonexistent SERVER sv9 673 OPTIONS (SET user 'nonexistent_alt'); -- error 674ERROR: role "nonexistent" does not exist 675SELECT * FROM chkumapping(); 676 umname | umserver | umoptions 677------------------+----------+------------------------------- 678 regress_testrol2 | sv1 | {user=CURRENT_USER_alt} 679 current_user | sv2 | {"user=\"current_user\"_alt"} 680 regress_testrol2 | sv3 | {user=USER_alt} 681 user | sv4 | {"user=\"user\"_alt"} 682 regress_testrol1 | sv5 | {user=SESSION_USER_alt} 683 | sv6 | {user=public_alt} 684 Public | sv7 | {"user=\"Public\"_alt"} 685 regress_testrolx | sv8 | {user=regress_testrolx_alt} 686(8 rows) 687 688-- DROP USER MAPPING 689DROP USER MAPPING FOR CURRENT_USER SERVER sv1; 690DROP USER MAPPING FOR "current_user" SERVER sv2; 691DROP USER MAPPING FOR USER SERVER sv3; 692DROP USER MAPPING FOR "user" SERVER sv4; 693DROP USER MAPPING FOR SESSION_USER SERVER sv5; 694DROP USER MAPPING FOR PUBLIC SERVER sv6; 695DROP USER MAPPING FOR "Public" SERVER sv7; 696DROP USER MAPPING FOR regress_testrolx SERVER sv8; 697DROP USER MAPPING FOR CURRENT_ROLE SERVER sv9; -- error 698ERROR: syntax error at or near "CURRENT_ROLE" 699LINE 1: DROP USER MAPPING FOR CURRENT_ROLE SERVER sv9; 700 ^ 701DROP USER MAPPING FOR nonexistent SERVER sv; -- error 702ERROR: role "nonexistent" does not exist 703SELECT * FROM chkumapping(); 704 umname | umserver | umoptions 705--------+----------+----------- 706(0 rows) 707 708CREATE USER MAPPING FOR CURRENT_USER SERVER sv1 OPTIONS (user 'CURRENT_USER'); 709CREATE USER MAPPING FOR "current_user" SERVER sv2 OPTIONS (user '"current_user"'); 710CREATE USER MAPPING FOR USER SERVER sv3 OPTIONS (user 'USER'); 711CREATE USER MAPPING FOR "user" SERVER sv4 OPTIONS (user '"USER"'); 712CREATE USER MAPPING FOR SESSION_USER SERVER sv5 OPTIONS (user 'SESSION_USER'); 713CREATE USER MAPPING FOR PUBLIC SERVER sv6 OPTIONS (user 'PUBLIC'); 714CREATE USER MAPPING FOR "Public" SERVER sv7 OPTIONS (user '"Public"'); 715CREATE USER MAPPING FOR regress_testrolx SERVER sv8 OPTIONS (user 'regress_testrolx'); 716SELECT * FROM chkumapping(); 717 umname | umserver | umoptions 718------------------+----------+--------------------------- 719 regress_testrol2 | sv1 | {user=CURRENT_USER} 720 current_user | sv2 | {"user=\"current_user\""} 721 regress_testrol2 | sv3 | {user=USER} 722 user | sv4 | {"user=\"USER\""} 723 regress_testrol1 | sv5 | {user=SESSION_USER} 724 | sv6 | {user=PUBLIC} 725 Public | sv7 | {"user=\"Public\""} 726 regress_testrolx | sv8 | {user=regress_testrolx} 727(8 rows) 728 729-- DROP USER MAPPING IF EXISTS 730DROP USER MAPPING IF EXISTS FOR CURRENT_USER SERVER sv1; 731SELECT * FROM chkumapping(); 732 umname | umserver | umoptions 733------------------+----------+--------------------------- 734 current_user | sv2 | {"user=\"current_user\""} 735 regress_testrol2 | sv3 | {user=USER} 736 user | sv4 | {"user=\"USER\""} 737 regress_testrol1 | sv5 | {user=SESSION_USER} 738 | sv6 | {user=PUBLIC} 739 Public | sv7 | {"user=\"Public\""} 740 regress_testrolx | sv8 | {user=regress_testrolx} 741(7 rows) 742 743DROP USER MAPPING IF EXISTS FOR "current_user" SERVER sv2; 744SELECT * FROM chkumapping(); 745 umname | umserver | umoptions 746------------------+----------+------------------------- 747 regress_testrol2 | sv3 | {user=USER} 748 user | sv4 | {"user=\"USER\""} 749 regress_testrol1 | sv5 | {user=SESSION_USER} 750 | sv6 | {user=PUBLIC} 751 Public | sv7 | {"user=\"Public\""} 752 regress_testrolx | sv8 | {user=regress_testrolx} 753(6 rows) 754 755DROP USER MAPPING IF EXISTS FOR USER SERVER sv3; 756SELECT * FROM chkumapping(); 757 umname | umserver | umoptions 758------------------+----------+------------------------- 759 user | sv4 | {"user=\"USER\""} 760 regress_testrol1 | sv5 | {user=SESSION_USER} 761 | sv6 | {user=PUBLIC} 762 Public | sv7 | {"user=\"Public\""} 763 regress_testrolx | sv8 | {user=regress_testrolx} 764(5 rows) 765 766DROP USER MAPPING IF EXISTS FOR "user" SERVER sv4; 767SELECT * FROM chkumapping(); 768 umname | umserver | umoptions 769------------------+----------+------------------------- 770 regress_testrol1 | sv5 | {user=SESSION_USER} 771 | sv6 | {user=PUBLIC} 772 Public | sv7 | {"user=\"Public\""} 773 regress_testrolx | sv8 | {user=regress_testrolx} 774(4 rows) 775 776DROP USER MAPPING IF EXISTS FOR SESSION_USER SERVER sv5; 777SELECT * FROM chkumapping(); 778 umname | umserver | umoptions 779------------------+----------+------------------------- 780 | sv6 | {user=PUBLIC} 781 Public | sv7 | {"user=\"Public\""} 782 regress_testrolx | sv8 | {user=regress_testrolx} 783(3 rows) 784 785DROP USER MAPPING IF EXISTS FOR PUBLIC SERVER sv6; 786SELECT * FROM chkumapping(); 787 umname | umserver | umoptions 788------------------+----------+------------------------- 789 Public | sv7 | {"user=\"Public\""} 790 regress_testrolx | sv8 | {user=regress_testrolx} 791(2 rows) 792 793DROP USER MAPPING IF EXISTS FOR "Public" SERVER sv7; 794SELECT * FROM chkumapping(); 795 umname | umserver | umoptions 796------------------+----------+------------------------- 797 regress_testrolx | sv8 | {user=regress_testrolx} 798(1 row) 799 800DROP USER MAPPING IF EXISTS FOR regress_testrolx SERVER sv8; 801SELECT * FROM chkumapping(); 802 umname | umserver | umoptions 803--------+----------+----------- 804(0 rows) 805 806DROP USER MAPPING IF EXISTS FOR CURRENT_ROLE SERVER sv9; --error 807ERROR: syntax error at or near "CURRENT_ROLE" 808LINE 1: DROP USER MAPPING IF EXISTS FOR CURRENT_ROLE SERVER sv9; 809 ^ 810DROP USER MAPPING IF EXISTS FOR nonexistent SERVER sv9; -- error 811NOTICE: role "nonexistent" does not exist, skipping 812-- GRANT/REVOKE 813GRANT regress_testrol0 TO pg_signal_backend; -- success 814SET ROLE pg_signal_backend; --success 815RESET ROLE; 816CREATE SCHEMA test_schema AUTHORIZATION pg_signal_backend; --success 817SET ROLE regress_testrol2; 818UPDATE pg_proc SET proacl = null WHERE proname LIKE 'testagg_'; 819SELECT proname, proacl FROM pg_proc WHERE proname LIKE 'testagg_'; 820 proname | proacl 821----------+-------- 822 testagg1 | 823 testagg2 | 824 testagg3 | 825 testagg4 | 826 testagg5 | 827 testagg6 | 828 testagg7 | 829 testagg8 | 830 testagg9 | 831(9 rows) 832 833REVOKE ALL PRIVILEGES ON FUNCTION testagg1(int2) FROM PUBLIC; 834REVOKE ALL PRIVILEGES ON FUNCTION testagg2(int2) FROM PUBLIC; 835REVOKE ALL PRIVILEGES ON FUNCTION testagg3(int2) FROM PUBLIC; 836REVOKE ALL PRIVILEGES ON FUNCTION testagg4(int2) FROM PUBLIC; 837REVOKE ALL PRIVILEGES ON FUNCTION testagg5(int2) FROM PUBLIC; 838REVOKE ALL PRIVILEGES ON FUNCTION testagg6(int2) FROM PUBLIC; 839REVOKE ALL PRIVILEGES ON FUNCTION testagg7(int2) FROM PUBLIC; 840REVOKE ALL PRIVILEGES ON FUNCTION testagg8(int2) FROM PUBLIC; 841GRANT ALL PRIVILEGES ON FUNCTION testagg1(int2) TO PUBLIC; 842GRANT ALL PRIVILEGES ON FUNCTION testagg2(int2) TO CURRENT_USER; 843GRANT ALL PRIVILEGES ON FUNCTION testagg3(int2) TO "current_user"; 844GRANT ALL PRIVILEGES ON FUNCTION testagg4(int2) TO SESSION_USER; 845GRANT ALL PRIVILEGES ON FUNCTION testagg5(int2) TO "Public"; 846GRANT ALL PRIVILEGES ON FUNCTION testagg6(int2) TO regress_testrolx; 847GRANT ALL PRIVILEGES ON FUNCTION testagg7(int2) TO "public"; 848GRANT ALL PRIVILEGES ON FUNCTION testagg8(int2) 849 TO current_user, public, regress_testrolx; 850SELECT proname, proacl FROM pg_proc WHERE proname LIKE 'testagg_'; 851 proname | proacl 852----------+----------------------------------------------------------------------------------------------------------------------------------- 853 testagg1 | {regress_testrol2=X/regress_testrol2,=X/regress_testrol2} 854 testagg2 | {current_user=X/current_user,regress_testrol2=X/current_user} 855 testagg3 | {regress_testrol1=X/regress_testrol1,current_user=X/regress_testrol1} 856 testagg4 | {regress_testrolx=X/regress_testrolx,regress_testrol1=X/regress_testrolx} 857 testagg5 | {Public=X/Public} 858 testagg6 | {regress_testrol0=X/regress_testrol0,regress_testrolx=X/regress_testrol0} 859 testagg7 | {regress_testrol0=X/regress_testrol0,=X/regress_testrol0} 860 testagg8 | {regress_testrol0=X/regress_testrol0,regress_testrol2=X/regress_testrol0,=X/regress_testrol0,regress_testrolx=X/regress_testrol0} 861 testagg9 | 862(9 rows) 863 864GRANT ALL PRIVILEGES ON FUNCTION testagg9(int2) TO CURRENT_ROLE; --error 865ERROR: syntax error at or near "CURRENT_ROLE" 866LINE 1: ...RANT ALL PRIVILEGES ON FUNCTION testagg9(int2) TO CURRENT_RO... 867 ^ 868GRANT ALL PRIVILEGES ON FUNCTION testagg9(int2) TO USER; --error 869ERROR: syntax error at or near "USER" 870LINE 1: GRANT ALL PRIVILEGES ON FUNCTION testagg9(int2) TO USER; 871 ^ 872GRANT ALL PRIVILEGES ON FUNCTION testagg9(int2) TO NONE; --error 873ERROR: role name "none" is reserved 874LINE 1: GRANT ALL PRIVILEGES ON FUNCTION testagg9(int2) TO NONE; 875 ^ 876GRANT ALL PRIVILEGES ON FUNCTION testagg9(int2) TO "none"; --error 877ERROR: role name "none" is reserved 878LINE 1: GRANT ALL PRIVILEGES ON FUNCTION testagg9(int2) TO "none"; 879 ^ 880SELECT proname, proacl FROM pg_proc WHERE proname LIKE 'testagg_'; 881 proname | proacl 882----------+----------------------------------------------------------------------------------------------------------------------------------- 883 testagg1 | {regress_testrol2=X/regress_testrol2,=X/regress_testrol2} 884 testagg2 | {current_user=X/current_user,regress_testrol2=X/current_user} 885 testagg3 | {regress_testrol1=X/regress_testrol1,current_user=X/regress_testrol1} 886 testagg4 | {regress_testrolx=X/regress_testrolx,regress_testrol1=X/regress_testrolx} 887 testagg5 | {Public=X/Public} 888 testagg6 | {regress_testrol0=X/regress_testrol0,regress_testrolx=X/regress_testrol0} 889 testagg7 | {regress_testrol0=X/regress_testrol0,=X/regress_testrol0} 890 testagg8 | {regress_testrol0=X/regress_testrol0,regress_testrol2=X/regress_testrol0,=X/regress_testrol0,regress_testrolx=X/regress_testrol0} 891 testagg9 | 892(9 rows) 893 894REVOKE ALL PRIVILEGES ON FUNCTION testagg1(int2) FROM PUBLIC; 895REVOKE ALL PRIVILEGES ON FUNCTION testagg2(int2) FROM CURRENT_USER; 896REVOKE ALL PRIVILEGES ON FUNCTION testagg3(int2) FROM "current_user"; 897REVOKE ALL PRIVILEGES ON FUNCTION testagg4(int2) FROM SESSION_USER; 898REVOKE ALL PRIVILEGES ON FUNCTION testagg5(int2) FROM "Public"; 899REVOKE ALL PRIVILEGES ON FUNCTION testagg6(int2) FROM regress_testrolx; 900REVOKE ALL PRIVILEGES ON FUNCTION testagg7(int2) FROM "public"; 901REVOKE ALL PRIVILEGES ON FUNCTION testagg8(int2) 902 FROM current_user, public, regress_testrolx; 903SELECT proname, proacl FROM pg_proc WHERE proname LIKE 'testagg_'; 904 proname | proacl 905----------+--------------------------------------- 906 testagg1 | {regress_testrol2=X/regress_testrol2} 907 testagg2 | {current_user=X/current_user} 908 testagg3 | {regress_testrol1=X/regress_testrol1} 909 testagg4 | {regress_testrolx=X/regress_testrolx} 910 testagg5 | {} 911 testagg6 | {regress_testrol0=X/regress_testrol0} 912 testagg7 | {regress_testrol0=X/regress_testrol0} 913 testagg8 | {regress_testrol0=X/regress_testrol0} 914 testagg9 | 915(9 rows) 916 917REVOKE ALL PRIVILEGES ON FUNCTION testagg9(int2) FROM CURRENT_ROLE; --error 918ERROR: syntax error at or near "CURRENT_ROLE" 919LINE 1: ...KE ALL PRIVILEGES ON FUNCTION testagg9(int2) FROM CURRENT_RO... 920 ^ 921REVOKE ALL PRIVILEGES ON FUNCTION testagg9(int2) FROM USER; --error 922ERROR: syntax error at or near "USER" 923LINE 1: REVOKE ALL PRIVILEGES ON FUNCTION testagg9(int2) FROM USER; 924 ^ 925REVOKE ALL PRIVILEGES ON FUNCTION testagg9(int2) FROM NONE; --error 926ERROR: role name "none" is reserved 927LINE 1: REVOKE ALL PRIVILEGES ON FUNCTION testagg9(int2) FROM NONE; 928 ^ 929REVOKE ALL PRIVILEGES ON FUNCTION testagg9(int2) FROM "none"; --error 930ERROR: role name "none" is reserved 931LINE 1: ...EVOKE ALL PRIVILEGES ON FUNCTION testagg9(int2) FROM "none"; 932 ^ 933SELECT proname, proacl FROM pg_proc WHERE proname LIKE 'testagg_'; 934 proname | proacl 935----------+--------------------------------------- 936 testagg1 | {regress_testrol2=X/regress_testrol2} 937 testagg2 | {current_user=X/current_user} 938 testagg3 | {regress_testrol1=X/regress_testrol1} 939 testagg4 | {regress_testrolx=X/regress_testrolx} 940 testagg5 | {} 941 testagg6 | {regress_testrol0=X/regress_testrol0} 942 testagg7 | {regress_testrol0=X/regress_testrol0} 943 testagg8 | {regress_testrol0=X/regress_testrol0} 944 testagg9 | 945(9 rows) 946 947-- clean up 948\c 949DROP SCHEMA test_schema; 950DROP OWNED BY regress_testrol0, "Public", "current_user", regress_testrol1, regress_testrol2, regress_testrolx CASCADE; 951DROP ROLE regress_testrol0, regress_testrol1, regress_testrol2, regress_testrolx; 952DROP ROLE "Public", "None", "current_user", "session_user", "user"; 953