2 -- Test foreign-data wrapper and server management.
5 -- Clean up in case a prior regression run failed
7 -- Suppress NOTICE messages when roles don't exist
8 SET client_min_messages TO 'error';
10 DROP ROLE IF EXISTS foreign_data_user, regress_test_role, regress_test_role2, regress_test_role_super, regress_test_indirect, unpriviled_role;
12 RESET client_min_messages;
14 CREATE ROLE foreign_data_user LOGIN SUPERUSER;
15 SET SESSION AUTHORIZATION 'foreign_data_user';
17 CREATE ROLE regress_test_role;
18 CREATE ROLE regress_test_role2;
19 CREATE ROLE regress_test_role_super SUPERUSER;
20 CREATE ROLE regress_test_indirect;
21 CREATE ROLE unprivileged_role;
23 CREATE FOREIGN DATA WRAPPER dummy;
24 COMMENT ON FOREIGN DATA WRAPPER dummy IS 'useless';
25 CREATE FOREIGN DATA WRAPPER postgresql VALIDATOR postgresql_fdw_validator;
27 -- At this point we should have 2 built-in wrappers and no servers.
28 SELECT fdwname, fdwhandler::regproc, fdwvalidator::regproc, fdwoptions FROM pg_foreign_data_wrapper ORDER BY 1, 2, 3;
29 SELECT srvname, srvoptions FROM pg_foreign_server;
30 SELECT * FROM pg_user_mapping;
32 -- CREATE FOREIGN DATA WRAPPER
33 CREATE FOREIGN DATA WRAPPER foo VALIDATOR bar; -- ERROR
34 CREATE FOREIGN DATA WRAPPER foo;
37 CREATE FOREIGN DATA WRAPPER foo; -- duplicate
38 DROP FOREIGN DATA WRAPPER foo;
39 CREATE FOREIGN DATA WRAPPER foo OPTIONS (testing '1');
42 DROP FOREIGN DATA WRAPPER foo;
43 CREATE FOREIGN DATA WRAPPER foo OPTIONS (testing '1', testing '2'); -- ERROR
44 CREATE FOREIGN DATA WRAPPER foo OPTIONS (testing '1', another '2');
47 DROP FOREIGN DATA WRAPPER foo;
48 SET ROLE regress_test_role;
49 CREATE FOREIGN DATA WRAPPER foo; -- ERROR
51 CREATE FOREIGN DATA WRAPPER foo VALIDATOR postgresql_fdw_validator;
54 -- ALTER FOREIGN DATA WRAPPER
55 ALTER FOREIGN DATA WRAPPER foo; -- ERROR
56 ALTER FOREIGN DATA WRAPPER foo VALIDATOR bar; -- ERROR
57 ALTER FOREIGN DATA WRAPPER foo NO VALIDATOR;
60 ALTER FOREIGN DATA WRAPPER foo OPTIONS (a '1', b '2');
61 ALTER FOREIGN DATA WRAPPER foo OPTIONS (SET c '4'); -- ERROR
62 ALTER FOREIGN DATA WRAPPER foo OPTIONS (DROP c); -- ERROR
63 ALTER FOREIGN DATA WRAPPER foo OPTIONS (ADD x '1', DROP x);
66 ALTER FOREIGN DATA WRAPPER foo OPTIONS (DROP a, SET b '3', ADD c '4');
69 ALTER FOREIGN DATA WRAPPER foo OPTIONS (a '2');
70 ALTER FOREIGN DATA WRAPPER foo OPTIONS (b '4'); -- ERROR
73 SET ROLE regress_test_role;
74 ALTER FOREIGN DATA WRAPPER foo OPTIONS (ADD d '5'); -- ERROR
75 SET ROLE regress_test_role_super;
76 ALTER FOREIGN DATA WRAPPER foo OPTIONS (ADD d '5');
79 ALTER FOREIGN DATA WRAPPER foo OWNER TO regress_test_role; -- ERROR
80 ALTER FOREIGN DATA WRAPPER foo OWNER TO regress_test_role_super;
81 ALTER ROLE regress_test_role_super NOSUPERUSER;
82 SET ROLE regress_test_role_super;
83 ALTER FOREIGN DATA WRAPPER foo OPTIONS (ADD e '6'); -- ERROR
87 -- DROP FOREIGN DATA WRAPPER
88 DROP FOREIGN DATA WRAPPER nonexistent; -- ERROR
89 DROP FOREIGN DATA WRAPPER IF EXISTS nonexistent;
92 DROP ROLE regress_test_role_super; -- ERROR
93 SET ROLE regress_test_role_super;
94 DROP FOREIGN DATA WRAPPER foo; -- ERROR
96 ALTER ROLE regress_test_role_super SUPERUSER;
97 DROP FOREIGN DATA WRAPPER foo;
98 DROP ROLE regress_test_role_super;
101 CREATE FOREIGN DATA WRAPPER foo;
102 CREATE SERVER s1 FOREIGN DATA WRAPPER foo;
103 COMMENT ON SERVER s1 IS 'foreign server';
104 CREATE USER MAPPING FOR current_user SERVER s1;
108 DROP FOREIGN DATA WRAPPER foo; -- ERROR
109 SET ROLE regress_test_role;
110 DROP FOREIGN DATA WRAPPER foo CASCADE; -- ERROR
112 DROP FOREIGN DATA WRAPPER foo CASCADE;
117 -- exercise CREATE SERVER
118 CREATE SERVER s1 FOREIGN DATA WRAPPER foo; -- ERROR
119 CREATE FOREIGN DATA WRAPPER foo OPTIONS (test_wrapper 'true');
120 CREATE SERVER s1 FOREIGN DATA WRAPPER foo;
121 CREATE SERVER s1 FOREIGN DATA WRAPPER foo; -- ERROR
122 CREATE SERVER s2 FOREIGN DATA WRAPPER foo OPTIONS (host 'a', dbname 'b');
123 CREATE SERVER s3 TYPE 'oracle' FOREIGN DATA WRAPPER foo;
124 CREATE SERVER s4 TYPE 'oracle' FOREIGN DATA WRAPPER foo OPTIONS (host 'a', dbname 'b');
125 CREATE SERVER s5 VERSION '15.0' FOREIGN DATA WRAPPER foo;
126 CREATE SERVER s6 VERSION '16.0' FOREIGN DATA WRAPPER foo OPTIONS (host 'a', dbname 'b');
127 CREATE SERVER s7 TYPE 'oracle' VERSION '17.0' FOREIGN DATA WRAPPER foo OPTIONS (host 'a', dbname 'b');
128 CREATE SERVER s8 FOREIGN DATA WRAPPER postgresql OPTIONS (foo '1'); -- ERROR
129 CREATE SERVER s8 FOREIGN DATA WRAPPER postgresql OPTIONS (host 'localhost', dbname 's8db');
131 SET ROLE regress_test_role;
132 CREATE SERVER t1 FOREIGN DATA WRAPPER foo; -- ERROR: no usage on FDW
134 GRANT USAGE ON FOREIGN DATA WRAPPER foo TO regress_test_role;
135 SET ROLE regress_test_role;
136 CREATE SERVER t1 FOREIGN DATA WRAPPER foo;
140 REVOKE USAGE ON FOREIGN DATA WRAPPER foo FROM regress_test_role;
141 GRANT USAGE ON FOREIGN DATA WRAPPER foo TO regress_test_indirect;
142 SET ROLE regress_test_role;
143 CREATE SERVER t2 FOREIGN DATA WRAPPER foo; -- ERROR
145 GRANT regress_test_indirect TO regress_test_role;
146 SET ROLE regress_test_role;
147 CREATE SERVER t2 FOREIGN DATA WRAPPER foo;
150 REVOKE regress_test_indirect FROM regress_test_role;
153 ALTER SERVER s0; -- ERROR
154 ALTER SERVER s0 OPTIONS (a '1'); -- ERROR
155 ALTER SERVER s1 VERSION '1.0' OPTIONS (servername 's1');
156 ALTER SERVER s2 VERSION '1.1';
157 ALTER SERVER s3 OPTIONS (tnsname 'orcl', port '1521');
158 GRANT USAGE ON FOREIGN SERVER s1 TO regress_test_role;
159 GRANT USAGE ON FOREIGN SERVER s6 TO regress_test_role2 WITH GRANT OPTION;
161 SET ROLE regress_test_role;
162 ALTER SERVER s1 VERSION '1.1'; -- ERROR
163 ALTER SERVER s1 OWNER TO regress_test_role; -- ERROR
165 ALTER SERVER s1 OWNER TO regress_test_role;
166 GRANT regress_test_role2 TO regress_test_role;
167 SET ROLE regress_test_role;
168 ALTER SERVER s1 VERSION '1.1';
169 ALTER SERVER s1 OWNER TO regress_test_role2; -- ERROR
171 ALTER SERVER s8 OPTIONS (foo '1'); -- ERROR option validation
172 ALTER SERVER s8 OPTIONS (connect_timeout '30', SET dbname 'db1', DROP host);
173 SET ROLE regress_test_role;
174 ALTER SERVER s1 OWNER TO regress_test_indirect; -- ERROR
176 GRANT regress_test_indirect TO regress_test_role;
177 SET ROLE regress_test_role;
178 ALTER SERVER s1 OWNER TO regress_test_indirect;
180 GRANT USAGE ON FOREIGN DATA WRAPPER foo TO regress_test_indirect;
181 SET ROLE regress_test_role;
182 ALTER SERVER s1 OWNER TO regress_test_indirect;
184 DROP ROLE regress_test_indirect; -- ERROR
188 DROP SERVER nonexistent; -- ERROR
189 DROP SERVER IF EXISTS nonexistent;
191 SET ROLE regress_test_role;
192 DROP SERVER s2; -- ERROR
196 ALTER SERVER s2 OWNER TO regress_test_role;
197 SET ROLE regress_test_role;
201 CREATE USER MAPPING FOR current_user SERVER s3;
203 DROP SERVER s3; -- ERROR
204 DROP SERVER s3 CASCADE;
208 -- CREATE USER MAPPING
209 CREATE USER MAPPING FOR regress_test_missing_role SERVER s1; -- ERROR
210 CREATE USER MAPPING FOR current_user SERVER s1; -- ERROR
211 CREATE USER MAPPING FOR current_user SERVER s4;
212 CREATE USER MAPPING FOR user SERVER s4; -- ERROR duplicate
213 CREATE USER MAPPING FOR public SERVER s4 OPTIONS (mapping 'is public');
214 CREATE USER MAPPING FOR user SERVER s8 OPTIONS (username 'test', password 'secret'); -- ERROR
215 CREATE USER MAPPING FOR user SERVER s8 OPTIONS (user 'test', password 'secret');
216 ALTER SERVER s5 OWNER TO regress_test_role;
217 ALTER SERVER s6 OWNER TO regress_test_indirect;
218 SET ROLE regress_test_role;
219 CREATE USER MAPPING FOR current_user SERVER s5;
220 CREATE USER MAPPING FOR current_user SERVER s6 OPTIONS (username 'test');
221 CREATE USER MAPPING FOR current_user SERVER s7; -- ERROR
222 CREATE USER MAPPING FOR public SERVER s8; -- ERROR
225 ALTER SERVER t1 OWNER TO regress_test_indirect;
226 SET ROLE regress_test_role;
227 CREATE USER MAPPING FOR current_user SERVER t1 OPTIONS (username 'bob', password 'boo');
228 CREATE USER MAPPING FOR public SERVER t1;
232 -- ALTER USER MAPPING
233 ALTER USER MAPPING FOR regress_test_missing_role SERVER s4 OPTIONS (gotcha 'true'); -- ERROR
234 ALTER USER MAPPING FOR user SERVER ss4 OPTIONS (gotcha 'true'); -- ERROR
235 ALTER USER MAPPING FOR public SERVER s5 OPTIONS (gotcha 'true'); -- ERROR
236 ALTER USER MAPPING FOR current_user SERVER s8 OPTIONS (username 'test'); -- ERROR
237 ALTER USER MAPPING FOR current_user SERVER s8 OPTIONS (DROP user, SET password 'public');
238 SET ROLE regress_test_role;
239 ALTER USER MAPPING FOR current_user SERVER s5 OPTIONS (ADD modified '1');
240 ALTER USER MAPPING FOR public SERVER s4 OPTIONS (ADD modified '1'); -- ERROR
241 ALTER USER MAPPING FOR public SERVER t1 OPTIONS (ADD modified '1');
246 DROP USER MAPPING FOR regress_test_missing_role SERVER s4; -- ERROR
247 DROP USER MAPPING FOR user SERVER ss4;
248 DROP USER MAPPING FOR public SERVER s7; -- ERROR
249 DROP USER MAPPING IF EXISTS FOR regress_test_missing_role SERVER s4;
250 DROP USER MAPPING IF EXISTS FOR user SERVER ss4;
251 DROP USER MAPPING IF EXISTS FOR public SERVER s7;
252 CREATE USER MAPPING FOR public SERVER s8;
253 SET ROLE regress_test_role;
254 DROP USER MAPPING FOR public SERVER s8; -- ERROR
259 -- CREATE FOREIGN TABLE
260 CREATE SCHEMA foreign_schema;
261 CREATE SERVER sc FOREIGN DATA WRAPPER dummy;
262 CREATE FOREIGN TABLE ft1 (); -- ERROR
263 CREATE FOREIGN TABLE ft1 () SERVER no_server; -- ERROR
264 CREATE FOREIGN TABLE ft1 (c1 serial) SERVER sc; -- ERROR
265 CREATE FOREIGN TABLE ft1 () SERVER sc WITH OIDS; -- ERROR
266 CREATE FOREIGN TABLE ft1 (
270 ) SERVER sc OPTIONS (delimiter ',', quote '"');
271 COMMENT ON FOREIGN TABLE ft1 IS 'ft1';
272 COMMENT ON COLUMN ft1.c1 IS 'ft1.c1';
275 CREATE INDEX id_ft1_c2 ON ft1 (c2); -- ERROR
276 SELECT * FROM ft1; -- ERROR
277 EXPLAIN SELECT * FROM ft1; -- ERROR
279 -- ALTER FOREIGN TABLE
280 COMMENT ON FOREIGN TABLE ft1 IS 'foreign table';
281 COMMENT ON FOREIGN TABLE ft1 IS NULL;
282 COMMENT ON COLUMN ft1.c1 IS 'foreign column';
283 COMMENT ON COLUMN ft1.c1 IS NULL;
285 ALTER FOREIGN TABLE ft1 ADD COLUMN c4 integer;
286 ALTER FOREIGN TABLE ft1 ADD COLUMN c5 integer DEFAULT 0; -- ERROR
287 ALTER FOREIGN TABLE ft1 ADD COLUMN c6 integer;
288 ALTER FOREIGN TABLE ft1 ADD COLUMN c7 integer NOT NULL;
289 ALTER FOREIGN TABLE ft1 ADD COLUMN c8 integer;
290 ALTER FOREIGN TABLE ft1 ADD COLUMN c9 integer;
291 ALTER FOREIGN TABLE ft1 ADD COLUMN c10 integer;
293 ALTER FOREIGN TABLE ft1 ALTER COLUMN c4 SET DEFAULT 0; -- ERROR
294 ALTER FOREIGN TABLE ft1 ALTER COLUMN c5 DROP DEFAULT; -- ERROR
295 ALTER FOREIGN TABLE ft1 ALTER COLUMN c6 SET NOT NULL;
296 ALTER FOREIGN TABLE ft1 ALTER COLUMN c7 DROP NOT NULL;
297 ALTER FOREIGN TABLE ft1 ALTER COLUMN c8 TYPE char(10) using '0'; -- ERROR
298 ALTER FOREIGN TABLE ft1 ALTER COLUMN c8 TYPE char(10);
299 ALTER FOREIGN TABLE ft1 ALTER COLUMN c8 SET DATA TYPE text;
300 -- can't change the column type if it's used elsewhere
301 CREATE TABLE use_ft1_column_type (x ft1);
302 ALTER FOREIGN TABLE ft1 ALTER COLUMN c8 SET DATA TYPE integer; -- ERROR
303 DROP TABLE use_ft1_column_type;
304 ALTER FOREIGN TABLE ft1 ADD CONSTRAINT ft1_c9_check CHECK (c9 < 0); -- ERROR
305 ALTER FOREIGN TABLE ft1 DROP CONSTRAINT no_const; -- ERROR
306 ALTER FOREIGN TABLE ft1 DROP CONSTRAINT IF EXISTS no_const;
307 ALTER FOREIGN TABLE ft1 DROP CONSTRAINT ft1_c1_check;
308 ALTER FOREIGN TABLE ft1 SET WITH OIDS; -- ERROR
309 ALTER FOREIGN TABLE ft1 OWNER TO regress_test_role;
310 ALTER FOREIGN TABLE ft1 OPTIONS (DROP delimiter, SET quote '~', ADD escape '@');
311 ALTER FOREIGN TABLE ft1 DROP COLUMN no_column; -- ERROR
312 ALTER FOREIGN TABLE ft1 DROP COLUMN IF EXISTS no_column;
313 ALTER FOREIGN TABLE ft1 DROP COLUMN c9;
314 ALTER FOREIGN TABLE ft1 SET SCHEMA foreign_schema;
315 ALTER FOREIGN TABLE ft1 SET TABLESPACE ts; -- ERROR
316 ALTER FOREIGN TABLE foreign_schema.ft1 RENAME c1 TO foreign_column_1;
317 ALTER FOREIGN TABLE foreign_schema.ft1 RENAME TO foreign_table_1;
318 \d foreign_schema.foreign_table_1
320 -- Information schema
322 SELECT * FROM information_schema.foreign_data_wrappers ORDER BY 1, 2;
323 SELECT * FROM information_schema.foreign_data_wrapper_options ORDER BY 1, 2, 3;
324 SELECT * FROM information_schema.foreign_servers ORDER BY 1, 2;
325 SELECT * FROM information_schema.foreign_server_options ORDER BY 1, 2, 3;
326 SELECT * FROM information_schema.user_mappings ORDER BY lower(authorization_identifier), 2, 3;
327 SELECT * FROM information_schema.user_mapping_options ORDER BY lower(authorization_identifier), 2, 3, 4;
328 SELECT * FROM information_schema.usage_privileges WHERE object_type LIKE 'FOREIGN%' ORDER BY 1, 2, 3, 4, 5;
329 SELECT * FROM information_schema.role_usage_grants WHERE object_type LIKE 'FOREIGN%' ORDER BY 1, 2, 3, 4, 5;
330 SELECT * FROM information_schema.foreign_tables ORDER BY 1, 2, 3;
331 SELECT * FROM information_schema.foreign_table_options ORDER BY 1, 2, 3, 4;
332 SET ROLE regress_test_role;
333 SELECT * FROM information_schema.user_mapping_options ORDER BY 1, 2, 3, 4;
334 SELECT * FROM information_schema.usage_privileges WHERE object_type LIKE 'FOREIGN%' ORDER BY 1, 2, 3, 4, 5;
335 SELECT * FROM information_schema.role_usage_grants WHERE object_type LIKE 'FOREIGN%' ORDER BY 1, 2, 3, 4, 5;
336 DROP USER MAPPING FOR current_user SERVER t1;
337 SET ROLE regress_test_role2;
338 SELECT * FROM information_schema.user_mapping_options ORDER BY 1, 2, 3, 4;
342 -- has_foreign_data_wrapper_privilege
343 SELECT has_foreign_data_wrapper_privilege('regress_test_role',
344 (SELECT oid FROM pg_foreign_data_wrapper WHERE fdwname='foo'), 'USAGE');
345 SELECT has_foreign_data_wrapper_privilege('regress_test_role', 'foo', 'USAGE');
346 SELECT has_foreign_data_wrapper_privilege(
347 (SELECT oid FROM pg_roles WHERE rolname='regress_test_role'),
348 (SELECT oid FROM pg_foreign_data_wrapper WHERE fdwname='foo'), 'USAGE');
349 SELECT has_foreign_data_wrapper_privilege(
350 (SELECT oid FROM pg_foreign_data_wrapper WHERE fdwname='foo'), 'USAGE');
351 SELECT has_foreign_data_wrapper_privilege(
352 (SELECT oid FROM pg_roles WHERE rolname='regress_test_role'), 'foo', 'USAGE');
353 SELECT has_foreign_data_wrapper_privilege('foo', 'USAGE');
354 GRANT USAGE ON FOREIGN DATA WRAPPER foo TO regress_test_role;
355 SELECT has_foreign_data_wrapper_privilege('regress_test_role', 'foo', 'USAGE');
357 -- has_server_privilege
358 SELECT has_server_privilege('regress_test_role',
359 (SELECT oid FROM pg_foreign_server WHERE srvname='s8'), 'USAGE');
360 SELECT has_server_privilege('regress_test_role', 's8', 'USAGE');
361 SELECT has_server_privilege(
362 (SELECT oid FROM pg_roles WHERE rolname='regress_test_role'),
363 (SELECT oid FROM pg_foreign_server WHERE srvname='s8'), 'USAGE');
364 SELECT has_server_privilege(
365 (SELECT oid FROM pg_foreign_server WHERE srvname='s8'), 'USAGE');
366 SELECT has_server_privilege(
367 (SELECT oid FROM pg_roles WHERE rolname='regress_test_role'), 's8', 'USAGE');
368 SELECT has_server_privilege('s8', 'USAGE');
369 GRANT USAGE ON FOREIGN SERVER s8 TO regress_test_role;
370 SELECT has_server_privilege('regress_test_role', 's8', 'USAGE');
371 REVOKE USAGE ON FOREIGN SERVER s8 FROM regress_test_role;
373 GRANT USAGE ON FOREIGN SERVER s4 TO regress_test_role;
374 DROP USER MAPPING FOR public SERVER s4;
375 ALTER SERVER s6 OPTIONS (DROP host, DROP dbname);
376 ALTER USER MAPPING FOR regress_test_role SERVER s6 OPTIONS (DROP username);
377 ALTER FOREIGN DATA WRAPPER foo VALIDATOR postgresql_fdw_validator;
380 SET ROLE unprivileged_role;
381 CREATE FOREIGN DATA WRAPPER foobar; -- ERROR
382 ALTER FOREIGN DATA WRAPPER foo OPTIONS (gotcha 'true'); -- ERROR
383 ALTER FOREIGN DATA WRAPPER foo OWNER TO unprivileged_role; -- ERROR
384 DROP FOREIGN DATA WRAPPER foo; -- ERROR
385 GRANT USAGE ON FOREIGN DATA WRAPPER foo TO regress_test_role; -- ERROR
386 CREATE SERVER s9 FOREIGN DATA WRAPPER foo; -- ERROR
387 ALTER SERVER s4 VERSION '0.5'; -- ERROR
388 ALTER SERVER s4 OWNER TO unprivileged_role; -- ERROR
389 DROP SERVER s4; -- ERROR
390 GRANT USAGE ON FOREIGN SERVER s4 TO regress_test_role; -- ERROR
391 CREATE USER MAPPING FOR public SERVER s4; -- ERROR
392 ALTER USER MAPPING FOR regress_test_role SERVER s6 OPTIONS (gotcha 'true'); -- ERROR
393 DROP USER MAPPING FOR regress_test_role SERVER s6; -- ERROR
396 GRANT USAGE ON FOREIGN DATA WRAPPER postgresql TO unprivileged_role;
397 GRANT USAGE ON FOREIGN DATA WRAPPER foo TO unprivileged_role WITH GRANT OPTION;
398 SET ROLE unprivileged_role;
399 CREATE FOREIGN DATA WRAPPER foobar; -- ERROR
400 ALTER FOREIGN DATA WRAPPER foo OPTIONS (gotcha 'true'); -- ERROR
401 DROP FOREIGN DATA WRAPPER foo; -- ERROR
402 GRANT USAGE ON FOREIGN DATA WRAPPER postgresql TO regress_test_role; -- WARNING
403 GRANT USAGE ON FOREIGN DATA WRAPPER foo TO regress_test_role;
404 CREATE SERVER s9 FOREIGN DATA WRAPPER postgresql;
405 ALTER SERVER s6 VERSION '0.5'; -- ERROR
406 DROP SERVER s6; -- ERROR
407 GRANT USAGE ON FOREIGN SERVER s6 TO regress_test_role; -- ERROR
408 GRANT USAGE ON FOREIGN SERVER s9 TO regress_test_role;
409 CREATE USER MAPPING FOR public SERVER s6; -- ERROR
410 CREATE USER MAPPING FOR public SERVER s9;
411 ALTER USER MAPPING FOR regress_test_role SERVER s6 OPTIONS (gotcha 'true'); -- ERROR
412 DROP USER MAPPING FOR regress_test_role SERVER s6; -- ERROR
415 REVOKE USAGE ON FOREIGN DATA WRAPPER foo FROM unprivileged_role; -- ERROR
416 REVOKE USAGE ON FOREIGN DATA WRAPPER foo FROM unprivileged_role CASCADE;
417 SET ROLE unprivileged_role;
418 GRANT USAGE ON FOREIGN DATA WRAPPER foo TO regress_test_role; -- ERROR
419 CREATE SERVER s10 FOREIGN DATA WRAPPER foo; -- ERROR
420 ALTER SERVER s9 VERSION '1.1';
421 GRANT USAGE ON FOREIGN SERVER s9 TO regress_test_role;
422 CREATE USER MAPPING FOR current_user SERVER s9;
423 DROP SERVER s9 CASCADE;
425 CREATE SERVER s9 FOREIGN DATA WRAPPER foo;
426 GRANT USAGE ON FOREIGN SERVER s9 TO unprivileged_role;
427 SET ROLE unprivileged_role;
428 ALTER SERVER s9 VERSION '1.2'; -- ERROR
429 GRANT USAGE ON FOREIGN SERVER s9 TO regress_test_role; -- WARNING
430 CREATE USER MAPPING FOR current_user SERVER s9;
431 DROP SERVER s9 CASCADE; -- ERROR
434 -- DROP FOREIGN TABLE
435 DROP FOREIGN TABLE no_table; -- ERROR
436 DROP FOREIGN TABLE IF EXISTS no_table;
437 DROP FOREIGN TABLE foreign_schema.foreign_table_1;
440 DROP SCHEMA foreign_schema CASCADE;
441 DROP ROLE regress_test_role; -- ERROR
442 DROP SERVER s5 CASCADE;
443 DROP SERVER t1 CASCADE;
445 DROP USER MAPPING FOR regress_test_role SERVER s6;
446 -- This test causes some order dependent cascade detail output,
447 -- so switch to terse mode for it.
449 DROP FOREIGN DATA WRAPPER foo CASCADE;
450 \set VERBOSITY default
451 DROP SERVER s8 CASCADE;
452 DROP ROLE regress_test_indirect;
453 DROP ROLE regress_test_role;
454 DROP ROLE unprivileged_role; -- ERROR
455 REVOKE ALL ON FOREIGN DATA WRAPPER postgresql FROM unprivileged_role;
456 DROP ROLE unprivileged_role;
457 DROP ROLE regress_test_role2;
458 DROP FOREIGN DATA WRAPPER postgresql CASCADE;
459 DROP FOREIGN DATA WRAPPER dummy CASCADE;
461 DROP ROLE foreign_data_user;
463 -- At this point we should have no wrappers, no servers, and no mappings.
464 SELECT fdwname, fdwhandler, fdwvalidator, fdwoptions FROM pg_foreign_data_wrapper;
465 SELECT srvname, srvoptions FROM pg_foreign_server;
466 SELECT * FROM pg_user_mapping;