1-- 2-- Test foreign-data wrapper and server management. 3-- 4 5-- Clean up in case a prior regression run failed 6 7-- Suppress NOTICE messages when roles don't exist 8SET client_min_messages TO 'warning'; 9 10DROP ROLE IF EXISTS regress_foreign_data_user, regress_test_role, regress_test_role2, regress_test_role_super, regress_test_indirect, regress_unprivileged_role; 11 12RESET client_min_messages; 13 14CREATE ROLE regress_foreign_data_user LOGIN SUPERUSER; 15SET SESSION AUTHORIZATION 'regress_foreign_data_user'; 16 17CREATE ROLE regress_test_role; 18CREATE ROLE regress_test_role2; 19CREATE ROLE regress_test_role_super SUPERUSER; 20CREATE ROLE regress_test_indirect; 21CREATE ROLE regress_unprivileged_role; 22 23CREATE FOREIGN DATA WRAPPER dummy; 24COMMENT ON FOREIGN DATA WRAPPER dummy IS 'useless'; 25CREATE FOREIGN DATA WRAPPER postgresql VALIDATOR postgresql_fdw_validator; 26 27-- At this point we should have 2 built-in wrappers and no servers. 28SELECT fdwname, fdwhandler::regproc, fdwvalidator::regproc, fdwoptions FROM pg_foreign_data_wrapper ORDER BY 1, 2, 3; 29SELECT srvname, srvoptions FROM pg_foreign_server; 30SELECT * FROM pg_user_mapping; 31 32-- CREATE FOREIGN DATA WRAPPER 33CREATE FOREIGN DATA WRAPPER foo VALIDATOR bar; -- ERROR 34CREATE FOREIGN DATA WRAPPER foo; 35\dew 36 37CREATE FOREIGN DATA WRAPPER foo; -- duplicate 38DROP FOREIGN DATA WRAPPER foo; 39CREATE FOREIGN DATA WRAPPER foo OPTIONS (testing '1'); 40\dew+ 41 42DROP FOREIGN DATA WRAPPER foo; 43CREATE FOREIGN DATA WRAPPER foo OPTIONS (testing '1', testing '2'); -- ERROR 44CREATE FOREIGN DATA WRAPPER foo OPTIONS (testing '1', another '2'); 45\dew+ 46 47DROP FOREIGN DATA WRAPPER foo; 48SET ROLE regress_test_role; 49CREATE FOREIGN DATA WRAPPER foo; -- ERROR 50RESET ROLE; 51CREATE FOREIGN DATA WRAPPER foo VALIDATOR postgresql_fdw_validator; 52\dew+ 53 54-- ALTER FOREIGN DATA WRAPPER 55ALTER FOREIGN DATA WRAPPER foo; -- ERROR 56ALTER FOREIGN DATA WRAPPER foo VALIDATOR bar; -- ERROR 57ALTER FOREIGN DATA WRAPPER foo NO VALIDATOR; 58\dew+ 59 60ALTER FOREIGN DATA WRAPPER foo OPTIONS (a '1', b '2'); 61ALTER FOREIGN DATA WRAPPER foo OPTIONS (SET c '4'); -- ERROR 62ALTER FOREIGN DATA WRAPPER foo OPTIONS (DROP c); -- ERROR 63ALTER FOREIGN DATA WRAPPER foo OPTIONS (ADD x '1', DROP x); 64\dew+ 65 66ALTER FOREIGN DATA WRAPPER foo OPTIONS (DROP a, SET b '3', ADD c '4'); 67\dew+ 68 69ALTER FOREIGN DATA WRAPPER foo OPTIONS (a '2'); 70ALTER FOREIGN DATA WRAPPER foo OPTIONS (b '4'); -- ERROR 71\dew+ 72 73SET ROLE regress_test_role; 74ALTER FOREIGN DATA WRAPPER foo OPTIONS (ADD d '5'); -- ERROR 75SET ROLE regress_test_role_super; 76ALTER FOREIGN DATA WRAPPER foo OPTIONS (ADD d '5'); 77\dew+ 78 79ALTER FOREIGN DATA WRAPPER foo OWNER TO regress_test_role; -- ERROR 80ALTER FOREIGN DATA WRAPPER foo OWNER TO regress_test_role_super; 81ALTER ROLE regress_test_role_super NOSUPERUSER; 82SET ROLE regress_test_role_super; 83ALTER FOREIGN DATA WRAPPER foo OPTIONS (ADD e '6'); -- ERROR 84RESET ROLE; 85\dew+ 86 87ALTER FOREIGN DATA WRAPPER foo RENAME TO foo1; 88\dew+ 89ALTER FOREIGN DATA WRAPPER foo1 RENAME TO foo; 90 91-- DROP FOREIGN DATA WRAPPER 92DROP FOREIGN DATA WRAPPER nonexistent; -- ERROR 93DROP FOREIGN DATA WRAPPER IF EXISTS nonexistent; 94\dew+ 95 96DROP ROLE regress_test_role_super; -- ERROR 97SET ROLE regress_test_role_super; 98DROP FOREIGN DATA WRAPPER foo; 99RESET ROLE; 100DROP ROLE regress_test_role_super; 101\dew+ 102 103CREATE FOREIGN DATA WRAPPER foo; 104CREATE SERVER s1 FOREIGN DATA WRAPPER foo; 105COMMENT ON SERVER s1 IS 'foreign server'; 106CREATE USER MAPPING FOR current_user SERVER s1; 107\dew+ 108\des+ 109\deu+ 110DROP FOREIGN DATA WRAPPER foo; -- ERROR 111SET ROLE regress_test_role; 112DROP FOREIGN DATA WRAPPER foo CASCADE; -- ERROR 113RESET ROLE; 114DROP FOREIGN DATA WRAPPER foo CASCADE; 115\dew+ 116\des+ 117\deu+ 118 119-- exercise CREATE SERVER 120CREATE SERVER s1 FOREIGN DATA WRAPPER foo; -- ERROR 121CREATE FOREIGN DATA WRAPPER foo OPTIONS ("test wrapper" 'true'); 122CREATE SERVER s1 FOREIGN DATA WRAPPER foo; 123CREATE SERVER s1 FOREIGN DATA WRAPPER foo; -- ERROR 124CREATE SERVER s2 FOREIGN DATA WRAPPER foo OPTIONS (host 'a', dbname 'b'); 125CREATE SERVER s3 TYPE 'oracle' FOREIGN DATA WRAPPER foo; 126CREATE SERVER s4 TYPE 'oracle' FOREIGN DATA WRAPPER foo OPTIONS (host 'a', dbname 'b'); 127CREATE SERVER s5 VERSION '15.0' FOREIGN DATA WRAPPER foo; 128CREATE SERVER s6 VERSION '16.0' FOREIGN DATA WRAPPER foo OPTIONS (host 'a', dbname 'b'); 129CREATE SERVER s7 TYPE 'oracle' VERSION '17.0' FOREIGN DATA WRAPPER foo OPTIONS (host 'a', dbname 'b'); 130CREATE SERVER s8 FOREIGN DATA WRAPPER postgresql OPTIONS (foo '1'); -- ERROR 131CREATE SERVER s8 FOREIGN DATA WRAPPER postgresql OPTIONS (host 'localhost', dbname 's8db'); 132\des+ 133SET ROLE regress_test_role; 134CREATE SERVER t1 FOREIGN DATA WRAPPER foo; -- ERROR: no usage on FDW 135RESET ROLE; 136GRANT USAGE ON FOREIGN DATA WRAPPER foo TO regress_test_role; 137SET ROLE regress_test_role; 138CREATE SERVER t1 FOREIGN DATA WRAPPER foo; 139RESET ROLE; 140\des+ 141 142REVOKE USAGE ON FOREIGN DATA WRAPPER foo FROM regress_test_role; 143GRANT USAGE ON FOREIGN DATA WRAPPER foo TO regress_test_indirect; 144SET ROLE regress_test_role; 145CREATE SERVER t2 FOREIGN DATA WRAPPER foo; -- ERROR 146RESET ROLE; 147GRANT regress_test_indirect TO regress_test_role; 148SET ROLE regress_test_role; 149CREATE SERVER t2 FOREIGN DATA WRAPPER foo; 150\des+ 151RESET ROLE; 152REVOKE regress_test_indirect FROM regress_test_role; 153 154-- ALTER SERVER 155ALTER SERVER s0; -- ERROR 156ALTER SERVER s0 OPTIONS (a '1'); -- ERROR 157ALTER SERVER s1 VERSION '1.0' OPTIONS (servername 's1'); 158ALTER SERVER s2 VERSION '1.1'; 159ALTER SERVER s3 OPTIONS ("tns name" 'orcl', port '1521'); 160GRANT USAGE ON FOREIGN SERVER s1 TO regress_test_role; 161GRANT USAGE ON FOREIGN SERVER s6 TO regress_test_role2 WITH GRANT OPTION; 162\des+ 163SET ROLE regress_test_role; 164ALTER SERVER s1 VERSION '1.1'; -- ERROR 165ALTER SERVER s1 OWNER TO regress_test_role; -- ERROR 166RESET ROLE; 167ALTER SERVER s1 OWNER TO regress_test_role; 168GRANT regress_test_role2 TO regress_test_role; 169SET ROLE regress_test_role; 170ALTER SERVER s1 VERSION '1.1'; 171ALTER SERVER s1 OWNER TO regress_test_role2; -- ERROR 172RESET ROLE; 173ALTER SERVER s8 OPTIONS (foo '1'); -- ERROR option validation 174ALTER SERVER s8 OPTIONS (connect_timeout '30', SET dbname 'db1', DROP host); 175SET ROLE regress_test_role; 176ALTER SERVER s1 OWNER TO regress_test_indirect; -- ERROR 177RESET ROLE; 178GRANT regress_test_indirect TO regress_test_role; 179SET ROLE regress_test_role; 180ALTER SERVER s1 OWNER TO regress_test_indirect; 181RESET ROLE; 182GRANT USAGE ON FOREIGN DATA WRAPPER foo TO regress_test_indirect; 183SET ROLE regress_test_role; 184ALTER SERVER s1 OWNER TO regress_test_indirect; 185RESET ROLE; 186DROP ROLE regress_test_indirect; -- ERROR 187\des+ 188 189ALTER SERVER s8 RENAME to s8new; 190\des+ 191ALTER SERVER s8new RENAME to s8; 192 193-- DROP SERVER 194DROP SERVER nonexistent; -- ERROR 195DROP SERVER IF EXISTS nonexistent; 196\des 197SET ROLE regress_test_role; 198DROP SERVER s2; -- ERROR 199DROP SERVER s1; 200RESET ROLE; 201\des 202ALTER SERVER s2 OWNER TO regress_test_role; 203SET ROLE regress_test_role; 204DROP SERVER s2; 205RESET ROLE; 206\des 207CREATE USER MAPPING FOR current_user SERVER s3; 208\deu 209DROP SERVER s3; -- ERROR 210DROP SERVER s3 CASCADE; 211\des 212\deu 213 214-- CREATE USER MAPPING 215CREATE USER MAPPING FOR regress_test_missing_role SERVER s1; -- ERROR 216CREATE USER MAPPING FOR current_user SERVER s1; -- ERROR 217CREATE USER MAPPING FOR current_user SERVER s4; 218CREATE USER MAPPING FOR user SERVER s4; -- ERROR duplicate 219CREATE USER MAPPING FOR public SERVER s4 OPTIONS ("this mapping" 'is public'); 220CREATE USER MAPPING FOR user SERVER s8 OPTIONS (username 'test', password 'secret'); -- ERROR 221CREATE USER MAPPING FOR user SERVER s8 OPTIONS (user 'test', password 'secret'); 222ALTER SERVER s5 OWNER TO regress_test_role; 223ALTER SERVER s6 OWNER TO regress_test_indirect; 224SET ROLE regress_test_role; 225CREATE USER MAPPING FOR current_user SERVER s5; 226CREATE USER MAPPING FOR current_user SERVER s6 OPTIONS (username 'test'); 227CREATE USER MAPPING FOR current_user SERVER s7; -- ERROR 228CREATE USER MAPPING FOR public SERVER s8; -- ERROR 229RESET ROLE; 230 231ALTER SERVER t1 OWNER TO regress_test_indirect; 232SET ROLE regress_test_role; 233CREATE USER MAPPING FOR current_user SERVER t1 OPTIONS (username 'bob', password 'boo'); 234CREATE USER MAPPING FOR public SERVER t1; 235RESET ROLE; 236\deu 237 238-- ALTER USER MAPPING 239ALTER USER MAPPING FOR regress_test_missing_role SERVER s4 OPTIONS (gotcha 'true'); -- ERROR 240ALTER USER MAPPING FOR user SERVER ss4 OPTIONS (gotcha 'true'); -- ERROR 241ALTER USER MAPPING FOR public SERVER s5 OPTIONS (gotcha 'true'); -- ERROR 242ALTER USER MAPPING FOR current_user SERVER s8 OPTIONS (username 'test'); -- ERROR 243ALTER USER MAPPING FOR current_user SERVER s8 OPTIONS (DROP user, SET password 'public'); 244SET ROLE regress_test_role; 245ALTER USER MAPPING FOR current_user SERVER s5 OPTIONS (ADD modified '1'); 246ALTER USER MAPPING FOR public SERVER s4 OPTIONS (ADD modified '1'); -- ERROR 247ALTER USER MAPPING FOR public SERVER t1 OPTIONS (ADD modified '1'); 248RESET ROLE; 249\deu+ 250 251-- DROP USER MAPPING 252DROP USER MAPPING FOR regress_test_missing_role SERVER s4; -- ERROR 253DROP USER MAPPING FOR user SERVER ss4; 254DROP USER MAPPING FOR public SERVER s7; -- ERROR 255DROP USER MAPPING IF EXISTS FOR regress_test_missing_role SERVER s4; 256DROP USER MAPPING IF EXISTS FOR user SERVER ss4; 257DROP USER MAPPING IF EXISTS FOR public SERVER s7; 258CREATE USER MAPPING FOR public SERVER s8; 259SET ROLE regress_test_role; 260DROP USER MAPPING FOR public SERVER s8; -- ERROR 261RESET ROLE; 262DROP SERVER s7; 263\deu 264 265-- CREATE FOREIGN TABLE 266CREATE SCHEMA foreign_schema; 267CREATE SERVER s0 FOREIGN DATA WRAPPER dummy; 268CREATE FOREIGN TABLE ft1 (); -- ERROR 269CREATE FOREIGN TABLE ft1 () SERVER no_server; -- ERROR 270CREATE FOREIGN TABLE ft1 () SERVER s0 WITH OIDS; -- ERROR 271CREATE FOREIGN TABLE ft1 ( 272 c1 integer OPTIONS ("param 1" 'val1') PRIMARY KEY, 273 c2 text OPTIONS (param2 'val2', param3 'val3'), 274 c3 date 275) SERVER s0 OPTIONS (delimiter ',', quote '"', "be quoted" 'value'); -- ERROR 276CREATE TABLE ref_table (id integer PRIMARY KEY); 277CREATE FOREIGN TABLE ft1 ( 278 c1 integer OPTIONS ("param 1" 'val1') REFERENCES ref_table (id), 279 c2 text OPTIONS (param2 'val2', param3 'val3'), 280 c3 date 281) SERVER s0 OPTIONS (delimiter ',', quote '"', "be quoted" 'value'); -- ERROR 282DROP TABLE ref_table; 283CREATE FOREIGN TABLE ft1 ( 284 c1 integer OPTIONS ("param 1" 'val1') NOT NULL, 285 c2 text OPTIONS (param2 'val2', param3 'val3'), 286 c3 date, 287 UNIQUE (c3) 288) SERVER s0 OPTIONS (delimiter ',', quote '"', "be quoted" 'value'); -- ERROR 289CREATE FOREIGN TABLE ft1 ( 290 c1 integer OPTIONS ("param 1" 'val1') NOT NULL, 291 c2 text OPTIONS (param2 'val2', param3 'val3') CHECK (c2 <> ''), 292 c3 date, 293 CHECK (c3 BETWEEN '1994-01-01'::date AND '1994-01-31'::date) 294) SERVER s0 OPTIONS (delimiter ',', quote '"', "be quoted" 'value'); 295COMMENT ON FOREIGN TABLE ft1 IS 'ft1'; 296COMMENT ON COLUMN ft1.c1 IS 'ft1.c1'; 297\d+ ft1 298\det+ 299CREATE INDEX id_ft1_c2 ON ft1 (c2); -- ERROR 300SELECT * FROM ft1; -- ERROR 301EXPLAIN SELECT * FROM ft1; -- ERROR 302 303-- ALTER FOREIGN TABLE 304COMMENT ON FOREIGN TABLE ft1 IS 'foreign table'; 305COMMENT ON FOREIGN TABLE ft1 IS NULL; 306COMMENT ON COLUMN ft1.c1 IS 'foreign column'; 307COMMENT ON COLUMN ft1.c1 IS NULL; 308 309ALTER FOREIGN TABLE ft1 ADD COLUMN c4 integer; 310ALTER FOREIGN TABLE ft1 ADD COLUMN c5 integer DEFAULT 0; 311ALTER FOREIGN TABLE ft1 ADD COLUMN c6 integer; 312ALTER FOREIGN TABLE ft1 ADD COLUMN c7 integer NOT NULL; 313ALTER FOREIGN TABLE ft1 ADD COLUMN c8 integer; 314ALTER FOREIGN TABLE ft1 ADD COLUMN c9 integer; 315ALTER FOREIGN TABLE ft1 ADD COLUMN c10 integer OPTIONS (p1 'v1'); 316 317ALTER FOREIGN TABLE ft1 ALTER COLUMN c4 SET DEFAULT 0; 318ALTER FOREIGN TABLE ft1 ALTER COLUMN c5 DROP DEFAULT; 319ALTER FOREIGN TABLE ft1 ALTER COLUMN c6 SET NOT NULL; 320ALTER FOREIGN TABLE ft1 ALTER COLUMN c7 DROP NOT NULL; 321ALTER FOREIGN TABLE ft1 ALTER COLUMN c8 TYPE char(10) USING '0'; -- ERROR 322ALTER FOREIGN TABLE ft1 ALTER COLUMN c8 TYPE char(10); 323ALTER FOREIGN TABLE ft1 ALTER COLUMN c8 SET DATA TYPE text; 324ALTER FOREIGN TABLE ft1 ALTER COLUMN xmin OPTIONS (ADD p1 'v1'); -- ERROR 325ALTER FOREIGN TABLE ft1 ALTER COLUMN c7 OPTIONS (ADD p1 'v1', ADD p2 'v2'), 326 ALTER COLUMN c8 OPTIONS (ADD p1 'v1', ADD p2 'v2'); 327ALTER FOREIGN TABLE ft1 ALTER COLUMN c8 OPTIONS (SET p2 'V2', DROP p1); 328ALTER FOREIGN TABLE ft1 ALTER COLUMN c1 SET STATISTICS 10000; 329ALTER FOREIGN TABLE ft1 ALTER COLUMN c1 SET (n_distinct = 100); 330ALTER FOREIGN TABLE ft1 ALTER COLUMN c8 SET STATISTICS -1; 331ALTER FOREIGN TABLE ft1 ALTER COLUMN c8 SET STORAGE PLAIN; 332\d+ ft1 333-- can't change the column type if it's used elsewhere 334CREATE TABLE use_ft1_column_type (x ft1); 335ALTER FOREIGN TABLE ft1 ALTER COLUMN c8 SET DATA TYPE integer; -- ERROR 336DROP TABLE use_ft1_column_type; 337ALTER FOREIGN TABLE ft1 ADD PRIMARY KEY (c7); -- ERROR 338ALTER FOREIGN TABLE ft1 ADD CONSTRAINT ft1_c9_check CHECK (c9 < 0) NOT VALID; 339ALTER FOREIGN TABLE ft1 ALTER CONSTRAINT ft1_c9_check DEFERRABLE; -- ERROR 340ALTER FOREIGN TABLE ft1 DROP CONSTRAINT ft1_c9_check; 341ALTER FOREIGN TABLE ft1 DROP CONSTRAINT no_const; -- ERROR 342ALTER FOREIGN TABLE ft1 DROP CONSTRAINT IF EXISTS no_const; 343ALTER FOREIGN TABLE ft1 SET WITH OIDS; 344ALTER FOREIGN TABLE ft1 OWNER TO regress_test_role; 345ALTER FOREIGN TABLE ft1 OPTIONS (DROP delimiter, SET quote '~', ADD escape '@'); 346ALTER FOREIGN TABLE ft1 DROP COLUMN no_column; -- ERROR 347ALTER FOREIGN TABLE ft1 DROP COLUMN IF EXISTS no_column; 348ALTER FOREIGN TABLE ft1 DROP COLUMN c9; 349ALTER FOREIGN TABLE ft1 SET SCHEMA foreign_schema; 350ALTER FOREIGN TABLE ft1 SET TABLESPACE ts; -- ERROR 351ALTER FOREIGN TABLE foreign_schema.ft1 RENAME c1 TO foreign_column_1; 352ALTER FOREIGN TABLE foreign_schema.ft1 RENAME TO foreign_table_1; 353\d foreign_schema.foreign_table_1 354 355-- alter noexisting table 356ALTER FOREIGN TABLE IF EXISTS doesnt_exist_ft1 ADD COLUMN c4 integer; 357ALTER FOREIGN TABLE IF EXISTS doesnt_exist_ft1 ADD COLUMN c6 integer; 358ALTER FOREIGN TABLE IF EXISTS doesnt_exist_ft1 ADD COLUMN c7 integer NOT NULL; 359ALTER FOREIGN TABLE IF EXISTS doesnt_exist_ft1 ADD COLUMN c8 integer; 360ALTER FOREIGN TABLE IF EXISTS doesnt_exist_ft1 ADD COLUMN c9 integer; 361ALTER FOREIGN TABLE IF EXISTS doesnt_exist_ft1 ADD COLUMN c10 integer OPTIONS (p1 'v1'); 362 363ALTER FOREIGN TABLE IF EXISTS doesnt_exist_ft1 ALTER COLUMN c6 SET NOT NULL; 364ALTER FOREIGN TABLE IF EXISTS doesnt_exist_ft1 ALTER COLUMN c7 DROP NOT NULL; 365ALTER FOREIGN TABLE IF EXISTS doesnt_exist_ft1 ALTER COLUMN c8 TYPE char(10); 366ALTER FOREIGN TABLE IF EXISTS doesnt_exist_ft1 ALTER COLUMN c8 SET DATA TYPE text; 367ALTER FOREIGN TABLE IF EXISTS doesnt_exist_ft1 ALTER COLUMN c7 OPTIONS (ADD p1 'v1', ADD p2 'v2'), 368 ALTER COLUMN c8 OPTIONS (ADD p1 'v1', ADD p2 'v2'); 369ALTER FOREIGN TABLE IF EXISTS doesnt_exist_ft1 ALTER COLUMN c8 OPTIONS (SET p2 'V2', DROP p1); 370 371ALTER FOREIGN TABLE IF EXISTS doesnt_exist_ft1 DROP CONSTRAINT IF EXISTS no_const; 372ALTER FOREIGN TABLE IF EXISTS doesnt_exist_ft1 DROP CONSTRAINT ft1_c1_check; 373ALTER FOREIGN TABLE IF EXISTS doesnt_exist_ft1 OWNER TO regress_test_role; 374ALTER FOREIGN TABLE IF EXISTS doesnt_exist_ft1 OPTIONS (DROP delimiter, SET quote '~', ADD escape '@'); 375ALTER FOREIGN TABLE IF EXISTS doesnt_exist_ft1 DROP COLUMN IF EXISTS no_column; 376ALTER FOREIGN TABLE IF EXISTS doesnt_exist_ft1 DROP COLUMN c9; 377ALTER FOREIGN TABLE IF EXISTS doesnt_exist_ft1 SET SCHEMA foreign_schema; 378ALTER FOREIGN TABLE IF EXISTS doesnt_exist_ft1 RENAME c1 TO foreign_column_1; 379ALTER FOREIGN TABLE IF EXISTS doesnt_exist_ft1 RENAME TO foreign_table_1; 380 381-- Information schema 382 383SELECT * FROM information_schema.foreign_data_wrappers ORDER BY 1, 2; 384SELECT * FROM information_schema.foreign_data_wrapper_options ORDER BY 1, 2, 3; 385SELECT * FROM information_schema.foreign_servers ORDER BY 1, 2; 386SELECT * FROM information_schema.foreign_server_options ORDER BY 1, 2, 3; 387SELECT * FROM information_schema.user_mappings ORDER BY lower(authorization_identifier), 2, 3; 388SELECT * FROM information_schema.user_mapping_options ORDER BY lower(authorization_identifier), 2, 3, 4; 389SELECT * FROM information_schema.usage_privileges WHERE object_type LIKE 'FOREIGN%' AND object_name IN ('s6', 'foo') ORDER BY 1, 2, 3, 4, 5; 390SELECT * FROM information_schema.role_usage_grants WHERE object_type LIKE 'FOREIGN%' AND object_name IN ('s6', 'foo') ORDER BY 1, 2, 3, 4, 5; 391SELECT * FROM information_schema.foreign_tables ORDER BY 1, 2, 3; 392SELECT * FROM information_schema.foreign_table_options ORDER BY 1, 2, 3, 4; 393SET ROLE regress_test_role; 394SELECT * FROM information_schema.user_mapping_options ORDER BY 1, 2, 3, 4; 395SELECT * FROM information_schema.usage_privileges WHERE object_type LIKE 'FOREIGN%' AND object_name IN ('s6', 'foo') ORDER BY 1, 2, 3, 4, 5; 396SELECT * FROM information_schema.role_usage_grants WHERE object_type LIKE 'FOREIGN%' AND object_name IN ('s6', 'foo') ORDER BY 1, 2, 3, 4, 5; 397DROP USER MAPPING FOR current_user SERVER t1; 398SET ROLE regress_test_role2; 399SELECT * FROM information_schema.user_mapping_options ORDER BY 1, 2, 3, 4; 400RESET ROLE; 401 402 403-- has_foreign_data_wrapper_privilege 404SELECT has_foreign_data_wrapper_privilege('regress_test_role', 405 (SELECT oid FROM pg_foreign_data_wrapper WHERE fdwname='foo'), 'USAGE'); 406SELECT has_foreign_data_wrapper_privilege('regress_test_role', 'foo', 'USAGE'); 407SELECT has_foreign_data_wrapper_privilege( 408 (SELECT oid FROM pg_roles WHERE rolname='regress_test_role'), 409 (SELECT oid FROM pg_foreign_data_wrapper WHERE fdwname='foo'), 'USAGE'); 410SELECT has_foreign_data_wrapper_privilege( 411 (SELECT oid FROM pg_foreign_data_wrapper WHERE fdwname='foo'), 'USAGE'); 412SELECT has_foreign_data_wrapper_privilege( 413 (SELECT oid FROM pg_roles WHERE rolname='regress_test_role'), 'foo', 'USAGE'); 414SELECT has_foreign_data_wrapper_privilege('foo', 'USAGE'); 415GRANT USAGE ON FOREIGN DATA WRAPPER foo TO regress_test_role; 416SELECT has_foreign_data_wrapper_privilege('regress_test_role', 'foo', 'USAGE'); 417 418-- has_server_privilege 419SELECT has_server_privilege('regress_test_role', 420 (SELECT oid FROM pg_foreign_server WHERE srvname='s8'), 'USAGE'); 421SELECT has_server_privilege('regress_test_role', 's8', 'USAGE'); 422SELECT has_server_privilege( 423 (SELECT oid FROM pg_roles WHERE rolname='regress_test_role'), 424 (SELECT oid FROM pg_foreign_server WHERE srvname='s8'), 'USAGE'); 425SELECT has_server_privilege( 426 (SELECT oid FROM pg_foreign_server WHERE srvname='s8'), 'USAGE'); 427SELECT has_server_privilege( 428 (SELECT oid FROM pg_roles WHERE rolname='regress_test_role'), 's8', 'USAGE'); 429SELECT has_server_privilege('s8', 'USAGE'); 430GRANT USAGE ON FOREIGN SERVER s8 TO regress_test_role; 431SELECT has_server_privilege('regress_test_role', 's8', 'USAGE'); 432REVOKE USAGE ON FOREIGN SERVER s8 FROM regress_test_role; 433 434GRANT USAGE ON FOREIGN SERVER s4 TO regress_test_role; 435DROP USER MAPPING FOR public SERVER s4; 436ALTER SERVER s6 OPTIONS (DROP host, DROP dbname); 437ALTER USER MAPPING FOR regress_test_role SERVER s6 OPTIONS (DROP username); 438ALTER FOREIGN DATA WRAPPER foo VALIDATOR postgresql_fdw_validator; 439 440-- Privileges 441SET ROLE regress_unprivileged_role; 442CREATE FOREIGN DATA WRAPPER foobar; -- ERROR 443ALTER FOREIGN DATA WRAPPER foo OPTIONS (gotcha 'true'); -- ERROR 444ALTER FOREIGN DATA WRAPPER foo OWNER TO regress_unprivileged_role; -- ERROR 445DROP FOREIGN DATA WRAPPER foo; -- ERROR 446GRANT USAGE ON FOREIGN DATA WRAPPER foo TO regress_test_role; -- ERROR 447CREATE SERVER s9 FOREIGN DATA WRAPPER foo; -- ERROR 448ALTER SERVER s4 VERSION '0.5'; -- ERROR 449ALTER SERVER s4 OWNER TO regress_unprivileged_role; -- ERROR 450DROP SERVER s4; -- ERROR 451GRANT USAGE ON FOREIGN SERVER s4 TO regress_test_role; -- ERROR 452CREATE USER MAPPING FOR public SERVER s4; -- ERROR 453ALTER USER MAPPING FOR regress_test_role SERVER s6 OPTIONS (gotcha 'true'); -- ERROR 454DROP USER MAPPING FOR regress_test_role SERVER s6; -- ERROR 455RESET ROLE; 456 457GRANT USAGE ON FOREIGN DATA WRAPPER postgresql TO regress_unprivileged_role; 458GRANT USAGE ON FOREIGN DATA WRAPPER foo TO regress_unprivileged_role WITH GRANT OPTION; 459SET ROLE regress_unprivileged_role; 460CREATE FOREIGN DATA WRAPPER foobar; -- ERROR 461ALTER FOREIGN DATA WRAPPER foo OPTIONS (gotcha 'true'); -- ERROR 462DROP FOREIGN DATA WRAPPER foo; -- ERROR 463GRANT USAGE ON FOREIGN DATA WRAPPER postgresql TO regress_test_role; -- WARNING 464GRANT USAGE ON FOREIGN DATA WRAPPER foo TO regress_test_role; 465CREATE SERVER s9 FOREIGN DATA WRAPPER postgresql; 466ALTER SERVER s6 VERSION '0.5'; -- ERROR 467DROP SERVER s6; -- ERROR 468GRANT USAGE ON FOREIGN SERVER s6 TO regress_test_role; -- ERROR 469GRANT USAGE ON FOREIGN SERVER s9 TO regress_test_role; 470CREATE USER MAPPING FOR public SERVER s6; -- ERROR 471CREATE USER MAPPING FOR public SERVER s9; 472ALTER USER MAPPING FOR regress_test_role SERVER s6 OPTIONS (gotcha 'true'); -- ERROR 473DROP USER MAPPING FOR regress_test_role SERVER s6; -- ERROR 474RESET ROLE; 475 476REVOKE USAGE ON FOREIGN DATA WRAPPER foo FROM regress_unprivileged_role; -- ERROR 477REVOKE USAGE ON FOREIGN DATA WRAPPER foo FROM regress_unprivileged_role CASCADE; 478SET ROLE regress_unprivileged_role; 479GRANT USAGE ON FOREIGN DATA WRAPPER foo TO regress_test_role; -- ERROR 480CREATE SERVER s10 FOREIGN DATA WRAPPER foo; -- ERROR 481ALTER SERVER s9 VERSION '1.1'; 482GRANT USAGE ON FOREIGN SERVER s9 TO regress_test_role; 483CREATE USER MAPPING FOR current_user SERVER s9; 484-- We use terse mode to avoid ordering issues in cascade detail output. 485\set VERBOSITY terse 486DROP SERVER s9 CASCADE; 487\set VERBOSITY default 488RESET ROLE; 489CREATE SERVER s9 FOREIGN DATA WRAPPER foo; 490GRANT USAGE ON FOREIGN SERVER s9 TO regress_unprivileged_role; 491SET ROLE regress_unprivileged_role; 492ALTER SERVER s9 VERSION '1.2'; -- ERROR 493GRANT USAGE ON FOREIGN SERVER s9 TO regress_test_role; -- WARNING 494CREATE USER MAPPING FOR current_user SERVER s9; 495DROP SERVER s9 CASCADE; -- ERROR 496 497-- Check visibility of user mapping data 498SET ROLE regress_test_role; 499CREATE SERVER s10 FOREIGN DATA WRAPPER foo; 500CREATE USER MAPPING FOR public SERVER s10 OPTIONS (user 'secret'); 501CREATE USER MAPPING FOR regress_unprivileged_role SERVER s10 OPTIONS (user 'secret'); 502-- owner of server can see some option fields 503\deu+ 504RESET ROLE; 505-- superuser can see all option fields 506\deu+ 507-- unprivileged user cannot see any option field 508SET ROLE regress_unprivileged_role; 509\deu+ 510RESET ROLE; 511\set VERBOSITY terse 512DROP SERVER s10 CASCADE; 513\set VERBOSITY default 514 515-- Triggers 516CREATE FUNCTION dummy_trigger() RETURNS TRIGGER AS $$ 517 BEGIN 518 RETURN NULL; 519 END 520$$ language plpgsql; 521 522CREATE TRIGGER trigtest_before_stmt BEFORE INSERT OR UPDATE OR DELETE 523ON foreign_schema.foreign_table_1 524FOR EACH STATEMENT 525EXECUTE PROCEDURE dummy_trigger(); 526 527CREATE TRIGGER trigtest_after_stmt AFTER INSERT OR UPDATE OR DELETE 528ON foreign_schema.foreign_table_1 529FOR EACH STATEMENT 530EXECUTE PROCEDURE dummy_trigger(); 531 532CREATE TRIGGER trigtest_before_row BEFORE INSERT OR UPDATE OR DELETE 533ON foreign_schema.foreign_table_1 534FOR EACH ROW 535EXECUTE PROCEDURE dummy_trigger(); 536 537CREATE TRIGGER trigtest_after_row AFTER INSERT OR UPDATE OR DELETE 538ON foreign_schema.foreign_table_1 539FOR EACH ROW 540EXECUTE PROCEDURE dummy_trigger(); 541 542CREATE CONSTRAINT TRIGGER trigtest_constraint AFTER INSERT OR UPDATE OR DELETE 543ON foreign_schema.foreign_table_1 544FOR EACH ROW 545EXECUTE PROCEDURE dummy_trigger(); 546 547ALTER FOREIGN TABLE foreign_schema.foreign_table_1 548 DISABLE TRIGGER trigtest_before_stmt; 549ALTER FOREIGN TABLE foreign_schema.foreign_table_1 550 ENABLE TRIGGER trigtest_before_stmt; 551 552DROP TRIGGER trigtest_before_stmt ON foreign_schema.foreign_table_1; 553DROP TRIGGER trigtest_before_row ON foreign_schema.foreign_table_1; 554DROP TRIGGER trigtest_after_stmt ON foreign_schema.foreign_table_1; 555DROP TRIGGER trigtest_after_row ON foreign_schema.foreign_table_1; 556 557DROP FUNCTION dummy_trigger(); 558 559-- Table inheritance 560CREATE TABLE pt1 ( 561 c1 integer NOT NULL, 562 c2 text, 563 c3 date 564); 565CREATE FOREIGN TABLE ft2 () INHERITS (pt1) 566 SERVER s0 OPTIONS (delimiter ',', quote '"', "be quoted" 'value'); 567\d+ pt1 568\d+ ft2 569DROP FOREIGN TABLE ft2; 570\d+ pt1 571CREATE FOREIGN TABLE ft2 ( 572 c1 integer NOT NULL, 573 c2 text, 574 c3 date 575) SERVER s0 OPTIONS (delimiter ',', quote '"', "be quoted" 'value'); 576\d+ ft2 577ALTER FOREIGN TABLE ft2 INHERIT pt1; 578\d+ pt1 579\d+ ft2 580CREATE TABLE ct3() INHERITS(ft2); 581CREATE FOREIGN TABLE ft3 ( 582 c1 integer NOT NULL, 583 c2 text, 584 c3 date 585) INHERITS(ft2) 586 SERVER s0; 587\d+ ft2 588\d+ ct3 589\d+ ft3 590 591-- add attributes recursively 592ALTER TABLE pt1 ADD COLUMN c4 integer; 593ALTER TABLE pt1 ADD COLUMN c5 integer DEFAULT 0; 594ALTER TABLE pt1 ADD COLUMN c6 integer; 595ALTER TABLE pt1 ADD COLUMN c7 integer NOT NULL; 596ALTER TABLE pt1 ADD COLUMN c8 integer; 597\d+ pt1 598\d+ ft2 599\d+ ct3 600\d+ ft3 601 602-- alter attributes recursively 603ALTER TABLE pt1 ALTER COLUMN c4 SET DEFAULT 0; 604ALTER TABLE pt1 ALTER COLUMN c5 DROP DEFAULT; 605ALTER TABLE pt1 ALTER COLUMN c6 SET NOT NULL; 606ALTER TABLE pt1 ALTER COLUMN c7 DROP NOT NULL; 607ALTER TABLE pt1 ALTER COLUMN c8 TYPE char(10) USING '0'; -- ERROR 608ALTER TABLE pt1 ALTER COLUMN c8 TYPE char(10); 609ALTER TABLE pt1 ALTER COLUMN c8 SET DATA TYPE text; 610ALTER TABLE pt1 ALTER COLUMN c1 SET STATISTICS 10000; 611ALTER TABLE pt1 ALTER COLUMN c1 SET (n_distinct = 100); 612ALTER TABLE pt1 ALTER COLUMN c8 SET STATISTICS -1; 613ALTER TABLE pt1 ALTER COLUMN c8 SET STORAGE EXTERNAL; 614\d+ pt1 615\d+ ft2 616 617-- drop attributes recursively 618ALTER TABLE pt1 DROP COLUMN c4; 619ALTER TABLE pt1 DROP COLUMN c5; 620ALTER TABLE pt1 DROP COLUMN c6; 621ALTER TABLE pt1 DROP COLUMN c7; 622ALTER TABLE pt1 DROP COLUMN c8; 623\d+ pt1 624\d+ ft2 625 626-- add constraints recursively 627ALTER TABLE pt1 ADD CONSTRAINT pt1chk1 CHECK (c1 > 0) NO INHERIT; 628ALTER TABLE pt1 ADD CONSTRAINT pt1chk2 CHECK (c2 <> ''); 629-- connoinherit should be true for NO INHERIT constraint 630SELECT relname, conname, contype, conislocal, coninhcount, connoinherit 631 FROM pg_class AS pc JOIN pg_constraint AS pgc ON (conrelid = pc.oid) 632 WHERE pc.relname = 'pt1' 633 ORDER BY 1,2; 634-- child does not inherit NO INHERIT constraints 635\d+ pt1 636\d+ ft2 637\set VERBOSITY terse 638DROP FOREIGN TABLE ft2; -- ERROR 639DROP FOREIGN TABLE ft2 CASCADE; 640\set VERBOSITY default 641CREATE FOREIGN TABLE ft2 ( 642 c1 integer NOT NULL, 643 c2 text, 644 c3 date 645) SERVER s0 OPTIONS (delimiter ',', quote '"', "be quoted" 'value'); 646-- child must have parent's INHERIT constraints 647ALTER FOREIGN TABLE ft2 INHERIT pt1; -- ERROR 648ALTER FOREIGN TABLE ft2 ADD CONSTRAINT pt1chk2 CHECK (c2 <> ''); 649ALTER FOREIGN TABLE ft2 INHERIT pt1; 650-- child does not inherit NO INHERIT constraints 651\d+ pt1 652\d+ ft2 653 654-- drop constraints recursively 655ALTER TABLE pt1 DROP CONSTRAINT pt1chk1 CASCADE; 656ALTER TABLE pt1 DROP CONSTRAINT pt1chk2 CASCADE; 657 658-- NOT VALID case 659INSERT INTO pt1 VALUES (1, 'pt1'::text, '1994-01-01'::date); 660ALTER TABLE pt1 ADD CONSTRAINT pt1chk3 CHECK (c2 <> '') NOT VALID; 661\d+ pt1 662\d+ ft2 663-- VALIDATE CONSTRAINT need do nothing on foreign tables 664ALTER TABLE pt1 VALIDATE CONSTRAINT pt1chk3; 665\d+ pt1 666\d+ ft2 667 668-- OID system column 669ALTER TABLE pt1 SET WITH OIDS; 670\d+ pt1 671\d+ ft2 672ALTER TABLE ft2 SET WITHOUT OIDS; -- ERROR 673ALTER TABLE pt1 SET WITHOUT OIDS; 674\d+ pt1 675\d+ ft2 676 677-- changes name of an attribute recursively 678ALTER TABLE pt1 RENAME COLUMN c1 TO f1; 679ALTER TABLE pt1 RENAME COLUMN c2 TO f2; 680ALTER TABLE pt1 RENAME COLUMN c3 TO f3; 681-- changes name of a constraint recursively 682ALTER TABLE pt1 RENAME CONSTRAINT pt1chk3 TO f2_check; 683\d+ pt1 684\d+ ft2 685 686-- TRUNCATE doesn't work on foreign tables, either directly or recursively 687TRUNCATE ft2; -- ERROR 688TRUNCATE pt1; -- ERROR 689 690DROP TABLE pt1 CASCADE; 691 692-- IMPORT FOREIGN SCHEMA 693IMPORT FOREIGN SCHEMA s1 FROM SERVER s9 INTO public; -- ERROR 694IMPORT FOREIGN SCHEMA s1 LIMIT TO (t1) FROM SERVER s9 INTO public; --ERROR 695IMPORT FOREIGN SCHEMA s1 EXCEPT (t1) FROM SERVER s9 INTO public; -- ERROR 696IMPORT FOREIGN SCHEMA s1 EXCEPT (t1, t2) FROM SERVER s9 INTO public 697OPTIONS (option1 'value1', option2 'value2'); -- ERROR 698 699-- DROP FOREIGN TABLE 700DROP FOREIGN TABLE no_table; -- ERROR 701DROP FOREIGN TABLE IF EXISTS no_table; 702DROP FOREIGN TABLE foreign_schema.foreign_table_1; 703 704-- REASSIGN OWNED/DROP OWNED of foreign objects 705REASSIGN OWNED BY regress_test_role TO regress_test_role2; 706DROP OWNED BY regress_test_role2; 707DROP OWNED BY regress_test_role2 CASCADE; 708 709-- Cleanup 710DROP SCHEMA foreign_schema CASCADE; 711DROP ROLE regress_test_role; -- ERROR 712DROP SERVER t1 CASCADE; 713DROP USER MAPPING FOR regress_test_role SERVER s6; 714\set VERBOSITY terse 715DROP FOREIGN DATA WRAPPER foo CASCADE; 716DROP SERVER s8 CASCADE; 717\set VERBOSITY default 718DROP ROLE regress_test_indirect; 719DROP ROLE regress_test_role; 720DROP ROLE regress_unprivileged_role; -- ERROR 721REVOKE ALL ON FOREIGN DATA WRAPPER postgresql FROM regress_unprivileged_role; 722DROP ROLE regress_unprivileged_role; 723DROP ROLE regress_test_role2; 724DROP FOREIGN DATA WRAPPER postgresql CASCADE; 725DROP FOREIGN DATA WRAPPER dummy CASCADE; 726\c 727DROP ROLE regress_foreign_data_user; 728 729-- At this point we should have no wrappers, no servers, and no mappings. 730SELECT fdwname, fdwhandler, fdwvalidator, fdwoptions FROM pg_foreign_data_wrapper; 731SELECT srvname, srvoptions FROM pg_foreign_server; 732SELECT * FROM pg_user_mapping; 733