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