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; 16 17CREATE OR REPLACE FUNCTION chksetconfig() 18 RETURNS TABLE (db name, "role" name, rolkeyword text, setconfig text[]) 19 AS $$ 20SELECT COALESCE(d.datname, 'ALL'), COALESCE(r.rolname, 'ALL'), 21 COALESCE(v.keyword, '-'), s.setconfig 22 FROM pg_db_role_setting s 23 LEFT JOIN pg_roles r ON (r.oid = s.setrole) 24 LEFT JOIN pg_database d ON (d.oid = s.setdatabase) 25 LEFT JOIN (VALUES(CURRENT_USER, 'current_user'), 26 (SESSION_USER, 'session_user')) 27 AS v(uname, keyword) 28 ON (r.rolname = v.uname) 29 WHERE (r.rolname) IN ('Public', 'current_user', 'regress_testrol1', 'regress_testrol2') 30ORDER BY 1, 2; 31$$ LANGUAGE SQL; 32 33CREATE OR REPLACE FUNCTION chkumapping() 34 RETURNS TABLE (umname name, umserver name, umoptions text[]) 35 AS $$ 36SELECT r.rolname, s.srvname, m.umoptions 37 FROM pg_user_mapping m 38 LEFT JOIN pg_roles r ON (r.oid = m.umuser) 39 JOIN pg_foreign_server s ON (s.oid = m.umserver) 40 ORDER BY 2; 41$$ LANGUAGE SQL; 42 43CREATE ROLE "Public"; 44CREATE ROLE "None"; 45CREATE ROLE "current_user"; 46CREATE ROLE "session_user"; 47CREATE ROLE "user"; 48 49CREATE ROLE current_user; -- error 50CREATE ROLE current_role; -- error 51CREATE ROLE session_user; -- error 52CREATE ROLE user; -- error 53CREATE ROLE all; -- error 54 55CREATE ROLE public; -- error 56CREATE ROLE "public"; -- error 57CREATE ROLE none; -- error 58CREATE ROLE "none"; -- error 59 60CREATE ROLE pg_abc; -- error 61CREATE ROLE "pg_abc"; -- error 62CREATE ROLE pg_abcdef; -- error 63CREATE ROLE "pg_abcdef"; -- error 64 65CREATE ROLE regress_testrol0 SUPERUSER LOGIN; 66CREATE ROLE regress_testrolx SUPERUSER LOGIN; 67CREATE ROLE regress_testrol2 SUPERUSER; 68CREATE ROLE regress_testrol1 SUPERUSER LOGIN IN ROLE regress_testrol2; 69 70\c - 71SET SESSION AUTHORIZATION regress_testrol1; 72SET ROLE regress_testrol2; 73 74-- ALTER ROLE 75BEGIN; 76SELECT * FROM chkrolattr(); 77ALTER ROLE CURRENT_USER WITH REPLICATION; 78SELECT * FROM chkrolattr(); 79ALTER ROLE "current_user" WITH REPLICATION; 80SELECT * FROM chkrolattr(); 81ALTER ROLE SESSION_USER WITH REPLICATION; 82SELECT * FROM chkrolattr(); 83ALTER ROLE "session_user" WITH REPLICATION; 84SELECT * FROM chkrolattr(); 85ALTER USER "Public" WITH REPLICATION; 86ALTER USER "None" WITH REPLICATION; 87SELECT * FROM chkrolattr(); 88ALTER USER regress_testrol1 WITH NOREPLICATION; 89ALTER USER regress_testrol2 WITH NOREPLICATION; 90SELECT * FROM chkrolattr(); 91ROLLBACK; 92 93ALTER ROLE USER WITH LOGIN; -- error 94ALTER ROLE CURRENT_ROLE WITH LOGIN; --error 95ALTER ROLE ALL WITH REPLICATION; -- error 96ALTER ROLE SESSION_ROLE WITH NOREPLICATION; -- error 97ALTER ROLE PUBLIC WITH NOREPLICATION; -- error 98ALTER ROLE "public" WITH NOREPLICATION; -- error 99ALTER ROLE NONE WITH NOREPLICATION; -- error 100ALTER ROLE "none" WITH NOREPLICATION; -- error 101ALTER ROLE nonexistent WITH NOREPLICATION; -- error 102 103-- ALTER USER 104BEGIN; 105SELECT * FROM chkrolattr(); 106ALTER USER CURRENT_USER WITH REPLICATION; 107SELECT * FROM chkrolattr(); 108ALTER USER "current_user" WITH REPLICATION; 109SELECT * FROM chkrolattr(); 110ALTER USER SESSION_USER WITH REPLICATION; 111SELECT * FROM chkrolattr(); 112ALTER USER "session_user" WITH REPLICATION; 113SELECT * FROM chkrolattr(); 114ALTER USER "Public" WITH REPLICATION; 115ALTER USER "None" WITH REPLICATION; 116SELECT * FROM chkrolattr(); 117ALTER USER regress_testrol1 WITH NOREPLICATION; 118ALTER USER regress_testrol2 WITH NOREPLICATION; 119SELECT * FROM chkrolattr(); 120ROLLBACK; 121 122ALTER USER USER WITH LOGIN; -- error 123ALTER USER CURRENT_ROLE WITH LOGIN; -- error 124ALTER USER ALL WITH REPLICATION; -- error 125ALTER USER SESSION_ROLE WITH NOREPLICATION; -- error 126ALTER USER PUBLIC WITH NOREPLICATION; -- error 127ALTER USER "public" WITH NOREPLICATION; -- error 128ALTER USER NONE WITH NOREPLICATION; -- error 129ALTER USER "none" WITH NOREPLICATION; -- error 130ALTER USER nonexistent WITH NOREPLICATION; -- error 131 132-- ALTER ROLE SET/RESET 133SELECT * FROM chksetconfig(); 134ALTER ROLE CURRENT_USER SET application_name to 'FOO'; 135ALTER ROLE SESSION_USER SET application_name to 'BAR'; 136ALTER ROLE "current_user" SET application_name to 'FOOFOO'; 137ALTER ROLE "Public" SET application_name to 'BARBAR'; 138ALTER ROLE ALL SET application_name to 'SLAP'; 139SELECT * FROM chksetconfig(); 140ALTER ROLE regress_testrol1 SET application_name to 'SLAM'; 141SELECT * FROM chksetconfig(); 142ALTER ROLE CURRENT_USER RESET application_name; 143ALTER ROLE SESSION_USER RESET application_name; 144ALTER ROLE "current_user" RESET application_name; 145ALTER ROLE "Public" RESET application_name; 146ALTER ROLE ALL RESET application_name; 147SELECT * FROM chksetconfig(); 148 149 150ALTER ROLE CURRENT_ROLE SET application_name to 'BAZ'; -- error 151ALTER ROLE USER SET application_name to 'BOOM'; -- error 152ALTER ROLE PUBLIC SET application_name to 'BOMB'; -- error 153ALTER ROLE nonexistent SET application_name to 'BOMB'; -- error 154 155-- ALTER USER SET/RESET 156SELECT * FROM chksetconfig(); 157ALTER USER CURRENT_USER SET application_name to 'FOO'; 158ALTER USER SESSION_USER SET application_name to 'BAR'; 159ALTER USER "current_user" SET application_name to 'FOOFOO'; 160ALTER USER "Public" SET application_name to 'BARBAR'; 161ALTER USER ALL SET application_name to 'SLAP'; 162SELECT * FROM chksetconfig(); 163ALTER USER regress_testrol1 SET application_name to 'SLAM'; 164SELECT * FROM chksetconfig(); 165ALTER USER CURRENT_USER RESET application_name; 166ALTER USER SESSION_USER RESET application_name; 167ALTER USER "current_user" RESET application_name; 168ALTER USER "Public" RESET application_name; 169ALTER USER ALL RESET application_name; 170SELECT * FROM chksetconfig(); 171 172 173ALTER USER CURRENT_USER SET application_name to 'BAZ'; -- error 174ALTER USER USER SET application_name to 'BOOM'; -- error 175ALTER USER PUBLIC SET application_name to 'BOMB'; -- error 176ALTER USER NONE SET application_name to 'BOMB'; -- error 177ALTER USER nonexistent SET application_name to 'BOMB'; -- error 178 179-- CREATE SCHEMA 180CREATE SCHEMA newschema1 AUTHORIZATION CURRENT_USER; 181CREATE SCHEMA newschema2 AUTHORIZATION "current_user"; 182CREATE SCHEMA newschema3 AUTHORIZATION SESSION_USER; 183CREATE SCHEMA newschema4 AUTHORIZATION regress_testrolx; 184CREATE SCHEMA newschema5 AUTHORIZATION "Public"; 185 186CREATE SCHEMA newschema6 AUTHORIZATION USER; -- error 187CREATE SCHEMA newschema6 AUTHORIZATION CURRENT_ROLE; -- error 188CREATE SCHEMA newschema6 AUTHORIZATION PUBLIC; -- error 189CREATE SCHEMA newschema6 AUTHORIZATION "public"; -- error 190CREATE SCHEMA newschema6 AUTHORIZATION NONE; -- error 191CREATE SCHEMA newschema6 AUTHORIZATION nonexistent; -- error 192 193SELECT n.nspname, r.rolname FROM pg_namespace n 194 JOIN pg_roles r ON (r.oid = n.nspowner) 195 WHERE n.nspname LIKE 'newschema_' ORDER BY 1; 196 197CREATE SCHEMA IF NOT EXISTS newschema1 AUTHORIZATION CURRENT_USER; 198CREATE SCHEMA IF NOT EXISTS newschema2 AUTHORIZATION "current_user"; 199CREATE SCHEMA IF NOT EXISTS newschema3 AUTHORIZATION SESSION_USER; 200CREATE SCHEMA IF NOT EXISTS newschema4 AUTHORIZATION regress_testrolx; 201CREATE SCHEMA IF NOT EXISTS newschema5 AUTHORIZATION "Public"; 202 203CREATE SCHEMA IF NOT EXISTS newschema6 AUTHORIZATION USER; -- error 204CREATE SCHEMA IF NOT EXISTS newschema6 AUTHORIZATION CURRENT_ROLE; -- error 205CREATE SCHEMA IF NOT EXISTS newschema6 AUTHORIZATION PUBLIC; -- error 206CREATE SCHEMA IF NOT EXISTS newschema6 AUTHORIZATION "public"; -- error 207CREATE SCHEMA IF NOT EXISTS newschema6 AUTHORIZATION NONE; -- error 208CREATE SCHEMA IF NOT EXISTS newschema6 AUTHORIZATION nonexistent; -- error 209 210SELECT n.nspname, r.rolname FROM pg_namespace n 211 JOIN pg_roles r ON (r.oid = n.nspowner) 212 WHERE n.nspname LIKE 'newschema_' ORDER BY 1; 213 214-- ALTER TABLE OWNER TO 215\c - 216SET SESSION AUTHORIZATION regress_testrol0; 217CREATE TABLE testtab1 (a int); 218CREATE TABLE testtab2 (a int); 219CREATE TABLE testtab3 (a int); 220CREATE TABLE testtab4 (a int); 221CREATE TABLE testtab5 (a int); 222CREATE TABLE testtab6 (a int); 223 224\c - 225SET SESSION AUTHORIZATION regress_testrol1; 226SET ROLE regress_testrol2; 227 228ALTER TABLE testtab1 OWNER TO CURRENT_USER; 229ALTER TABLE testtab2 OWNER TO "current_user"; 230ALTER TABLE testtab3 OWNER TO SESSION_USER; 231ALTER TABLE testtab4 OWNER TO regress_testrolx; 232ALTER TABLE testtab5 OWNER TO "Public"; 233 234ALTER TABLE testtab6 OWNER TO CURRENT_ROLE; -- error 235ALTER TABLE testtab6 OWNER TO USER; --error 236ALTER TABLE testtab6 OWNER TO PUBLIC; -- error 237ALTER TABLE testtab6 OWNER TO "public"; -- error 238ALTER TABLE testtab6 OWNER TO nonexistent; -- error 239 240SELECT c.relname, r.rolname 241 FROM pg_class c JOIN pg_roles r ON (r.oid = c.relowner) 242 WHERE relname LIKE 'testtab_' 243 ORDER BY 1; 244 245-- ALTER TABLE, VIEW, MATERIALIZED VIEW, FOREIGN TABLE, SEQUENCE are 246-- changed their owner in the same way. 247 248-- ALTER AGGREGATE 249\c - 250SET SESSION AUTHORIZATION regress_testrol0; 251CREATE AGGREGATE testagg1(int2) (SFUNC = int2_sum, STYPE = int8); 252CREATE AGGREGATE testagg2(int2) (SFUNC = int2_sum, STYPE = int8); 253CREATE AGGREGATE testagg3(int2) (SFUNC = int2_sum, STYPE = int8); 254CREATE AGGREGATE testagg4(int2) (SFUNC = int2_sum, STYPE = int8); 255CREATE AGGREGATE testagg5(int2) (SFUNC = int2_sum, STYPE = int8); 256CREATE AGGREGATE testagg5(int2) (SFUNC = int2_sum, STYPE = int8); 257CREATE AGGREGATE testagg6(int2) (SFUNC = int2_sum, STYPE = int8); 258CREATE AGGREGATE testagg7(int2) (SFUNC = int2_sum, STYPE = int8); 259CREATE AGGREGATE testagg8(int2) (SFUNC = int2_sum, STYPE = int8); 260CREATE AGGREGATE testagg9(int2) (SFUNC = int2_sum, STYPE = int8); 261 262\c - 263SET SESSION AUTHORIZATION regress_testrol1; 264SET ROLE regress_testrol2; 265 266ALTER AGGREGATE testagg1(int2) OWNER TO CURRENT_USER; 267ALTER AGGREGATE testagg2(int2) OWNER TO "current_user"; 268ALTER AGGREGATE testagg3(int2) OWNER TO SESSION_USER; 269ALTER AGGREGATE testagg4(int2) OWNER TO regress_testrolx; 270ALTER AGGREGATE testagg5(int2) OWNER TO "Public"; 271 272ALTER AGGREGATE testagg5(int2) OWNER TO CURRENT_ROLE; -- error 273ALTER AGGREGATE testagg5(int2) OWNER TO USER; -- error 274ALTER AGGREGATE testagg5(int2) OWNER TO PUBLIC; -- error 275ALTER AGGREGATE testagg5(int2) OWNER TO "public"; -- error 276ALTER AGGREGATE testagg5(int2) OWNER TO nonexistent; -- error 277 278SELECT p.proname, r.rolname 279 FROM pg_proc p JOIN pg_roles r ON (r.oid = p.proowner) 280 WHERE proname LIKE 'testagg_' 281 ORDER BY 1; 282 283-- CREATE USER MAPPING 284CREATE FOREIGN DATA WRAPPER test_wrapper; 285CREATE SERVER sv1 FOREIGN DATA WRAPPER test_wrapper; 286CREATE SERVER sv2 FOREIGN DATA WRAPPER test_wrapper; 287CREATE SERVER sv3 FOREIGN DATA WRAPPER test_wrapper; 288CREATE SERVER sv4 FOREIGN DATA WRAPPER test_wrapper; 289CREATE SERVER sv5 FOREIGN DATA WRAPPER test_wrapper; 290CREATE SERVER sv6 FOREIGN DATA WRAPPER test_wrapper; 291CREATE SERVER sv7 FOREIGN DATA WRAPPER test_wrapper; 292CREATE SERVER sv8 FOREIGN DATA WRAPPER test_wrapper; 293CREATE SERVER sv9 FOREIGN DATA WRAPPER test_wrapper; 294 295CREATE USER MAPPING FOR CURRENT_USER SERVER sv1 OPTIONS (user 'CURRENT_USER'); 296CREATE USER MAPPING FOR "current_user" SERVER sv2 OPTIONS (user '"current_user"'); 297CREATE USER MAPPING FOR USER SERVER sv3 OPTIONS (user 'USER'); 298CREATE USER MAPPING FOR "user" SERVER sv4 OPTIONS (user '"USER"'); 299CREATE USER MAPPING FOR SESSION_USER SERVER sv5 OPTIONS (user 'SESSION_USER'); 300CREATE USER MAPPING FOR PUBLIC SERVER sv6 OPTIONS (user 'PUBLIC'); 301CREATE USER MAPPING FOR "Public" SERVER sv7 OPTIONS (user '"Public"'); 302CREATE USER MAPPING FOR regress_testrolx SERVER sv8 OPTIONS (user 'regress_testrolx'); 303 304CREATE USER MAPPING FOR CURRENT_ROLE SERVER sv9 305 OPTIONS (user 'CURRENT_ROLE'); -- error 306CREATE USER MAPPING FOR nonexistent SERVER sv9 307 OPTIONS (user 'nonexistent'); -- error; 308 309SELECT * FROM chkumapping(); 310 311-- ALTER USER MAPPING 312ALTER USER MAPPING FOR CURRENT_USER SERVER sv1 313 OPTIONS (SET user 'CURRENT_USER_alt'); 314ALTER USER MAPPING FOR "current_user" SERVER sv2 315 OPTIONS (SET user '"current_user"_alt'); 316ALTER USER MAPPING FOR USER SERVER sv3 317 OPTIONS (SET user 'USER_alt'); 318ALTER USER MAPPING FOR "user" SERVER sv4 319 OPTIONS (SET user '"user"_alt'); 320ALTER USER MAPPING FOR SESSION_USER SERVER sv5 321 OPTIONS (SET user 'SESSION_USER_alt'); 322ALTER USER MAPPING FOR PUBLIC SERVER sv6 323 OPTIONS (SET user 'public_alt'); 324ALTER USER MAPPING FOR "Public" SERVER sv7 325 OPTIONS (SET user '"Public"_alt'); 326ALTER USER MAPPING FOR regress_testrolx SERVER sv8 327 OPTIONS (SET user 'regress_testrolx_alt'); 328 329ALTER USER MAPPING FOR CURRENT_ROLE SERVER sv9 330 OPTIONS (SET user 'CURRENT_ROLE_alt'); 331ALTER USER MAPPING FOR nonexistent SERVER sv9 332 OPTIONS (SET user 'nonexistent_alt'); -- error 333 334SELECT * FROM chkumapping(); 335 336-- DROP USER MAPPING 337DROP USER MAPPING FOR CURRENT_USER SERVER sv1; 338DROP USER MAPPING FOR "current_user" SERVER sv2; 339DROP USER MAPPING FOR USER SERVER sv3; 340DROP USER MAPPING FOR "user" SERVER sv4; 341DROP USER MAPPING FOR SESSION_USER SERVER sv5; 342DROP USER MAPPING FOR PUBLIC SERVER sv6; 343DROP USER MAPPING FOR "Public" SERVER sv7; 344DROP USER MAPPING FOR regress_testrolx SERVER sv8; 345 346DROP USER MAPPING FOR CURRENT_ROLE SERVER sv9; -- error 347DROP USER MAPPING FOR nonexistent SERVER sv; -- error 348SELECT * FROM chkumapping(); 349 350CREATE USER MAPPING FOR CURRENT_USER SERVER sv1 OPTIONS (user 'CURRENT_USER'); 351CREATE USER MAPPING FOR "current_user" SERVER sv2 OPTIONS (user '"current_user"'); 352CREATE USER MAPPING FOR USER SERVER sv3 OPTIONS (user 'USER'); 353CREATE USER MAPPING FOR "user" SERVER sv4 OPTIONS (user '"USER"'); 354CREATE USER MAPPING FOR SESSION_USER SERVER sv5 OPTIONS (user 'SESSION_USER'); 355CREATE USER MAPPING FOR PUBLIC SERVER sv6 OPTIONS (user 'PUBLIC'); 356CREATE USER MAPPING FOR "Public" SERVER sv7 OPTIONS (user '"Public"'); 357CREATE USER MAPPING FOR regress_testrolx SERVER sv8 OPTIONS (user 'regress_testrolx'); 358SELECT * FROM chkumapping(); 359 360-- DROP USER MAPPING IF EXISTS 361DROP USER MAPPING IF EXISTS FOR CURRENT_USER SERVER sv1; 362SELECT * FROM chkumapping(); 363DROP USER MAPPING IF EXISTS FOR "current_user" SERVER sv2; 364SELECT * FROM chkumapping(); 365DROP USER MAPPING IF EXISTS FOR USER SERVER sv3; 366SELECT * FROM chkumapping(); 367DROP USER MAPPING IF EXISTS FOR "user" SERVER sv4; 368SELECT * FROM chkumapping(); 369DROP USER MAPPING IF EXISTS FOR SESSION_USER SERVER sv5; 370SELECT * FROM chkumapping(); 371DROP USER MAPPING IF EXISTS FOR PUBLIC SERVER sv6; 372SELECT * FROM chkumapping(); 373DROP USER MAPPING IF EXISTS FOR "Public" SERVER sv7; 374SELECT * FROM chkumapping(); 375DROP USER MAPPING IF EXISTS FOR regress_testrolx SERVER sv8; 376SELECT * FROM chkumapping(); 377 378DROP USER MAPPING IF EXISTS FOR CURRENT_ROLE SERVER sv9; --error 379DROP USER MAPPING IF EXISTS FOR nonexistent SERVER sv9; -- error 380 381-- GRANT/REVOKE 382GRANT regress_testrol0 TO pg_signal_backend; -- success 383 384SET ROLE pg_signal_backend; --success 385RESET ROLE; 386CREATE SCHEMA test_roles_schema AUTHORIZATION pg_signal_backend; --success 387SET ROLE regress_testrol2; 388 389UPDATE pg_proc SET proacl = null WHERE proname LIKE 'testagg_'; 390SELECT proname, proacl FROM pg_proc WHERE proname LIKE 'testagg_'; 391 392REVOKE ALL PRIVILEGES ON FUNCTION testagg1(int2) FROM PUBLIC; 393REVOKE ALL PRIVILEGES ON FUNCTION testagg2(int2) FROM PUBLIC; 394REVOKE ALL PRIVILEGES ON FUNCTION testagg3(int2) FROM PUBLIC; 395REVOKE ALL PRIVILEGES ON FUNCTION testagg4(int2) FROM PUBLIC; 396REVOKE ALL PRIVILEGES ON FUNCTION testagg5(int2) FROM PUBLIC; 397REVOKE ALL PRIVILEGES ON FUNCTION testagg6(int2) FROM PUBLIC; 398REVOKE ALL PRIVILEGES ON FUNCTION testagg7(int2) FROM PUBLIC; 399REVOKE ALL PRIVILEGES ON FUNCTION testagg8(int2) FROM PUBLIC; 400 401GRANT ALL PRIVILEGES ON FUNCTION testagg1(int2) TO PUBLIC; 402GRANT ALL PRIVILEGES ON FUNCTION testagg2(int2) TO CURRENT_USER; 403GRANT ALL PRIVILEGES ON FUNCTION testagg3(int2) TO "current_user"; 404GRANT ALL PRIVILEGES ON FUNCTION testagg4(int2) TO SESSION_USER; 405GRANT ALL PRIVILEGES ON FUNCTION testagg5(int2) TO "Public"; 406GRANT ALL PRIVILEGES ON FUNCTION testagg6(int2) TO regress_testrolx; 407GRANT ALL PRIVILEGES ON FUNCTION testagg7(int2) TO "public"; 408GRANT ALL PRIVILEGES ON FUNCTION testagg8(int2) 409 TO current_user, public, regress_testrolx; 410 411SELECT proname, proacl FROM pg_proc WHERE proname LIKE 'testagg_'; 412 413GRANT ALL PRIVILEGES ON FUNCTION testagg9(int2) TO CURRENT_ROLE; --error 414GRANT ALL PRIVILEGES ON FUNCTION testagg9(int2) TO USER; --error 415GRANT ALL PRIVILEGES ON FUNCTION testagg9(int2) TO NONE; --error 416GRANT ALL PRIVILEGES ON FUNCTION testagg9(int2) TO "none"; --error 417 418SELECT proname, proacl FROM pg_proc WHERE proname LIKE 'testagg_'; 419 420REVOKE ALL PRIVILEGES ON FUNCTION testagg1(int2) FROM PUBLIC; 421REVOKE ALL PRIVILEGES ON FUNCTION testagg2(int2) FROM CURRENT_USER; 422REVOKE ALL PRIVILEGES ON FUNCTION testagg3(int2) FROM "current_user"; 423REVOKE ALL PRIVILEGES ON FUNCTION testagg4(int2) FROM SESSION_USER; 424REVOKE ALL PRIVILEGES ON FUNCTION testagg5(int2) FROM "Public"; 425REVOKE ALL PRIVILEGES ON FUNCTION testagg6(int2) FROM regress_testrolx; 426REVOKE ALL PRIVILEGES ON FUNCTION testagg7(int2) FROM "public"; 427REVOKE ALL PRIVILEGES ON FUNCTION testagg8(int2) 428 FROM current_user, public, regress_testrolx; 429 430SELECT proname, proacl FROM pg_proc WHERE proname LIKE 'testagg_'; 431 432REVOKE ALL PRIVILEGES ON FUNCTION testagg9(int2) FROM CURRENT_ROLE; --error 433REVOKE ALL PRIVILEGES ON FUNCTION testagg9(int2) FROM USER; --error 434REVOKE ALL PRIVILEGES ON FUNCTION testagg9(int2) FROM NONE; --error 435REVOKE ALL PRIVILEGES ON FUNCTION testagg9(int2) FROM "none"; --error 436 437SELECT proname, proacl FROM pg_proc WHERE proname LIKE 'testagg_'; 438 439-- DEFAULT MONITORING ROLES 440CREATE ROLE regress_role_haspriv; 441CREATE ROLE regress_role_nopriv; 442 443-- pg_read_all_stats 444GRANT pg_read_all_stats TO regress_role_haspriv; 445SET SESSION AUTHORIZATION regress_role_haspriv; 446-- returns true with role member of pg_read_all_stats 447SELECT COUNT(*) = 0 AS haspriv FROM pg_stat_activity 448 WHERE query = '<insufficient privilege>'; 449SET SESSION AUTHORIZATION regress_role_nopriv; 450-- returns false with role not member of pg_read_all_stats 451SELECT COUNT(*) = 0 AS haspriv FROM pg_stat_activity 452 WHERE query = '<insufficient privilege>'; 453RESET SESSION AUTHORIZATION; 454REVOKE pg_read_all_stats FROM regress_role_haspriv; 455 456-- pg_read_all_settings 457GRANT pg_read_all_settings TO regress_role_haspriv; 458BEGIN; 459-- A GUC using GUC_SUPERUSER_ONLY is useful for negative tests. 460SET LOCAL session_preload_libraries TO 'path-to-preload-libraries'; 461SET SESSION AUTHORIZATION regress_role_haspriv; 462-- passes with role member of pg_read_all_settings 463SHOW session_preload_libraries; 464SET SESSION AUTHORIZATION regress_role_nopriv; 465-- fails with role not member of pg_read_all_settings 466SHOW session_preload_libraries; 467RESET SESSION AUTHORIZATION; 468ROLLBACK; 469REVOKE pg_read_all_settings FROM regress_role_haspriv; 470 471-- clean up 472\c 473 474DROP SCHEMA test_roles_schema; 475DROP OWNED BY regress_testrol0, "Public", "current_user", regress_testrol1, regress_testrol2, regress_testrolx CASCADE; 476DROP ROLE regress_testrol0, regress_testrol1, regress_testrol2, regress_testrolx; 477DROP ROLE "Public", "None", "current_user", "session_user", "user"; 478DROP ROLE regress_role_haspriv, regress_role_nopriv; 479