| blob | 8f4f0eb526efec9d26f3abcd3ce2545d5c348975 |
1 --
2 -- Test foreign-data wrapper and server management.
3 --
4 -- Clean up in case a prior regression run failed
5 -- Suppress NOTICE messages when roles don't exist
6 SET client_min_messages TO 'error';
7 DROP ROLE IF EXISTS foreign_data_user, regress_test_role, regress_test_role2, regress_test_role_super, regress_test_indirect, unpriviled_role;
8 RESET client_min_messages;
9 CREATE ROLE foreign_data_user LOGIN SUPERUSER;
10 SET SESSION AUTHORIZATION 'foreign_data_user';
11 CREATE ROLE regress_test_role;
12 CREATE ROLE regress_test_role2;
13 CREATE ROLE regress_test_role_super SUPERUSER;
14 CREATE ROLE regress_test_indirect;
15 CREATE ROLE unprivileged_role;
16 CREATE FOREIGN DATA WRAPPER dummy;
17 CREATE FOREIGN DATA WRAPPER postgresql VALIDATOR postgresql_fdw_validator;
18 -- At this point we should have 2 built-in wrappers and no servers.
19 SELECT fdwname, fdwvalidator::regproc, fdwoptions FROM pg_foreign_data_wrapper ORDER BY 1, 2, 3;
20 fdwname | fdwvalidator | fdwoptions
21 ------------+--------------------------+------------
22 dummy | - |
23 postgresql | postgresql_fdw_validator |
24 (2 rows)
26 SELECT srvname, srvoptions FROM pg_foreign_server;
27 srvname | srvoptions
28 ---------+------------
29 (0 rows)
31 SELECT * FROM pg_user_mapping;
32 umuser | umserver | umoptions
33 --------+----------+-----------
34 (0 rows)
36 -- CREATE FOREIGN DATA WRAPPER
37 CREATE FOREIGN DATA WRAPPER foo VALIDATOR bar; -- ERROR
38 ERROR: function bar(text[], oid) does not exist
39 CREATE FOREIGN DATA WRAPPER foo;
40 \dew
41 List of foreign-data wrappers
42 Name | Owner | Validator
43 ------------+-------------------+--------------------------
44 dummy | foreign_data_user | -
45 foo | foreign_data_user | -
46 postgresql | foreign_data_user | postgresql_fdw_validator
47 (3 rows)
49 CREATE FOREIGN DATA WRAPPER foo; -- duplicate
50 ERROR: foreign-data wrapper "foo" already exists
51 DROP FOREIGN DATA WRAPPER foo;
52 CREATE FOREIGN DATA WRAPPER foo OPTIONS (testing '1');
53 \dew+
54 List of foreign-data wrappers
55 Name | Owner | Validator | Access privileges | Options
56 ------------+-------------------+--------------------------+-------------------+-------------
57 dummy | foreign_data_user | - | |
58 foo | foreign_data_user | - | | {testing=1}
59 postgresql | foreign_data_user | postgresql_fdw_validator | |
60 (3 rows)
62 DROP FOREIGN DATA WRAPPER foo;
63 CREATE FOREIGN DATA WRAPPER foo OPTIONS (testing '1', testing '2'); -- ERROR
64 ERROR: option "testing" provided more than once
65 CREATE FOREIGN DATA WRAPPER foo OPTIONS (testing '1', another '2');
66 \dew+
67 List of foreign-data wrappers
68 Name | Owner | Validator | Access privileges | Options
69 ------------+-------------------+--------------------------+-------------------+-----------------------
70 dummy | foreign_data_user | - | |
71 foo | foreign_data_user | - | | {testing=1,another=2}
72 postgresql | foreign_data_user | postgresql_fdw_validator | |
73 (3 rows)
75 DROP FOREIGN DATA WRAPPER foo;
76 SET ROLE regress_test_role;
77 CREATE FOREIGN DATA WRAPPER foo; -- ERROR
78 ERROR: permission denied to create foreign-data wrapper "foo"
79 HINT: Must be superuser to create a foreign-data wrapper.
80 RESET ROLE;
81 CREATE FOREIGN DATA WRAPPER foo VALIDATOR postgresql_fdw_validator;
82 \dew+
83 List of foreign-data wrappers
84 Name | Owner | Validator | Access privileges | Options
85 ------------+-------------------+--------------------------+-------------------+---------
86 dummy | foreign_data_user | - | |
87 foo | foreign_data_user | postgresql_fdw_validator | |
88 postgresql | foreign_data_user | postgresql_fdw_validator | |
89 (3 rows)
91 -- ALTER FOREIGN DATA WRAPPER
92 ALTER FOREIGN DATA WRAPPER foo; -- ERROR
93 ERROR: syntax error at or near ";"
94 LINE 1: ALTER FOREIGN DATA WRAPPER foo;
95 ^
96 ALTER FOREIGN DATA WRAPPER foo VALIDATOR bar; -- ERROR
97 ERROR: function bar(text[], oid) does not exist
98 ALTER FOREIGN DATA WRAPPER foo NO VALIDATOR;
99 \dew+
100 List of foreign-data wrappers
101 Name | Owner | Validator | Access privileges | Options
102 ------------+-------------------+--------------------------+-------------------+---------
103 dummy | foreign_data_user | - | |
104 foo | foreign_data_user | - | |
105 postgresql | foreign_data_user | postgresql_fdw_validator | |
106 (3 rows)
108 ALTER FOREIGN DATA WRAPPER foo OPTIONS (a '1', b '2');
109 ALTER FOREIGN DATA WRAPPER foo OPTIONS (SET c '4'); -- ERROR
110 ERROR: option "c" not found
111 ALTER FOREIGN DATA WRAPPER foo OPTIONS (DROP c); -- ERROR
112 ERROR: option "c" not found
113 ALTER FOREIGN DATA WRAPPER foo OPTIONS (ADD x '1', DROP x);
114 \dew+
115 List of foreign-data wrappers
116 Name | Owner | Validator | Access privileges | Options
117 ------------+-------------------+--------------------------+-------------------+-----------
118 dummy | foreign_data_user | - | |
119 foo | foreign_data_user | - | | {a=1,b=2}
120 postgresql | foreign_data_user | postgresql_fdw_validator | |
121 (3 rows)
123 ALTER FOREIGN DATA WRAPPER foo OPTIONS (DROP a, SET b '3', ADD c '4');
124 \dew+
125 List of foreign-data wrappers
126 Name | Owner | Validator | Access privileges | Options
127 ------------+-------------------+--------------------------+-------------------+-----------
128 dummy | foreign_data_user | - | |
129 foo | foreign_data_user | - | | {b=3,c=4}
130 postgresql | foreign_data_user | postgresql_fdw_validator | |
131 (3 rows)
133 ALTER FOREIGN DATA WRAPPER foo OPTIONS (a '2');
134 ALTER FOREIGN DATA WRAPPER foo OPTIONS (b '4'); -- ERROR
135 ERROR: option "b" provided more than once
136 \dew+
137 List of foreign-data wrappers
138 Name | Owner | Validator | Access privileges | Options
139 ------------+-------------------+--------------------------+-------------------+---------------
140 dummy | foreign_data_user | - | |
141 foo | foreign_data_user | - | | {b=3,c=4,a=2}
142 postgresql | foreign_data_user | postgresql_fdw_validator | |
143 (3 rows)
145 SET ROLE regress_test_role;
146 ALTER FOREIGN DATA WRAPPER foo OPTIONS (ADD d '5'); -- ERROR
147 ERROR: permission denied to alter foreign-data wrapper "foo"
148 HINT: Must be superuser to alter a foreign-data wrapper.
149 SET ROLE regress_test_role_super;
150 ALTER FOREIGN DATA WRAPPER foo OPTIONS (ADD d '5');
151 \dew+
152 List of foreign-data wrappers
153 Name | Owner | Validator | Access privileges | Options
154 ------------+-------------------+--------------------------+-------------------+-------------------
155 dummy | foreign_data_user | - | |
156 foo | foreign_data_user | - | | {b=3,c=4,a=2,d=5}
157 postgresql | foreign_data_user | postgresql_fdw_validator | |
158 (3 rows)
160 ALTER FOREIGN DATA WRAPPER foo OWNER TO regress_test_role; -- ERROR
161 ERROR: permission denied to change owner of foreign-data wrapper "foo"
162 HINT: The owner of a foreign-data wrapper must be a superuser.
163 ALTER FOREIGN DATA WRAPPER foo OWNER TO regress_test_role_super;
164 ALTER ROLE regress_test_role_super NOSUPERUSER;
165 SET ROLE regress_test_role_super;
166 ALTER FOREIGN DATA WRAPPER foo OPTIONS (ADD e '6'); -- ERROR
167 ERROR: permission denied to alter foreign-data wrapper "foo"
168 HINT: Must be superuser to alter a foreign-data wrapper.
169 RESET ROLE;
170 \dew+
171 List of foreign-data wrappers
172 Name | Owner | Validator | Access privileges | Options
173 ------------+-------------------------+--------------------------+-------------------+-------------------
174 dummy | foreign_data_user | - | |
175 foo | regress_test_role_super | - | | {b=3,c=4,a=2,d=5}
176 postgresql | foreign_data_user | postgresql_fdw_validator | |
177 (3 rows)
179 -- DROP FOREIGN DATA WRAPPER
180 DROP FOREIGN DATA WRAPPER nonexistent; -- ERROR
181 ERROR: foreign-data wrapper "nonexistent" does not exist
182 DROP FOREIGN DATA WRAPPER IF EXISTS nonexistent;
183 NOTICE: foreign-data wrapper "nonexistent" does not exist, skipping
184 \dew+
185 List of foreign-data wrappers
186 Name | Owner | Validator | Access privileges | Options
187 ------------+-------------------------+--------------------------+-------------------+-------------------
188 dummy | foreign_data_user | - | |
189 foo | regress_test_role_super | - | | {b=3,c=4,a=2,d=5}
190 postgresql | foreign_data_user | postgresql_fdw_validator | |
191 (3 rows)
193 DROP ROLE regress_test_role_super; -- ERROR
194 ERROR: role "regress_test_role_super" cannot be dropped because some objects depend on it
195 DETAIL: owner of foreign-data wrapper foo
196 SET ROLE regress_test_role_super;
197 DROP FOREIGN DATA WRAPPER foo; -- ERROR
198 ERROR: permission denied to drop foreign-data wrapper "foo"
199 HINT: Must be superuser to drop a foreign-data wrapper.
200 RESET ROLE;
201 ALTER ROLE regress_test_role_super SUPERUSER;
202 DROP FOREIGN DATA WRAPPER foo;
203 DROP ROLE regress_test_role_super;
204 \dew+
205 List of foreign-data wrappers
206 Name | Owner | Validator | Access privileges | Options
207 ------------+-------------------+--------------------------+-------------------+---------
208 dummy | foreign_data_user | - | |
209 postgresql | foreign_data_user | postgresql_fdw_validator | |
210 (2 rows)
212 CREATE FOREIGN DATA WRAPPER foo;
213 CREATE SERVER s1 FOREIGN DATA WRAPPER foo;
214 CREATE USER MAPPING FOR current_user SERVER s1;
215 \dew+
216 List of foreign-data wrappers
217 Name | Owner | Validator | Access privileges | Options
218 ------------+-------------------+--------------------------+-------------------+---------
219 dummy | foreign_data_user | - | |
220 foo | foreign_data_user | - | |
221 postgresql | foreign_data_user | postgresql_fdw_validator | |
222 (3 rows)
224 \des+
225 List of foreign servers
226 Name | Owner | Foreign-data wrapper | Access privileges | Type | Version | Options
227 ------+-------------------+----------------------+-------------------+------+---------+---------
228 s1 | foreign_data_user | foo | | | |
229 (1 row)
231 \deu+
232 List of user mappings
233 Server | Username | Options
234 --------+-------------------+---------
235 s1 | foreign_data_user |
236 (1 row)
238 DROP FOREIGN DATA WRAPPER foo; -- ERROR
239 ERROR: cannot drop foreign-data wrapper foo because other objects depend on it
240 DETAIL: server s1 depends on foreign-data wrapper foo
241 user mapping for foreign_data_user depends on server s1
242 HINT: Use DROP ... CASCADE to drop the dependent objects too.
243 SET ROLE regress_test_role;
244 DROP FOREIGN DATA WRAPPER foo CASCADE; -- ERROR
245 ERROR: permission denied to drop foreign-data wrapper "foo"
246 HINT: Must be superuser to drop a foreign-data wrapper.
247 RESET ROLE;
248 DROP FOREIGN DATA WRAPPER foo CASCADE;
249 NOTICE: drop cascades to 2 other objects
250 DETAIL: drop cascades to server s1
251 drop cascades to user mapping for foreign_data_user
252 \dew+
253 List of foreign-data wrappers
254 Name | Owner | Validator | Access privileges | Options
255 ------------+-------------------+--------------------------+-------------------+---------
256 dummy | foreign_data_user | - | |
257 postgresql | foreign_data_user | postgresql_fdw_validator | |
258 (2 rows)
260 \des+
261 List of foreign servers
262 Name | Owner | Foreign-data wrapper | Access privileges | Type | Version | Options
263 ------+-------+----------------------+-------------------+------+---------+---------
264 (0 rows)
266 \deu+
267 List of user mappings
268 Server | Username | Options
269 --------+----------+---------
270 (0 rows)
272 -- exercise CREATE SERVER
273 CREATE SERVER s1 FOREIGN DATA WRAPPER foo; -- ERROR
274 ERROR: foreign-data wrapper "foo" does not exist
275 CREATE FOREIGN DATA WRAPPER foo OPTIONS (test_wrapper 'true');
276 CREATE SERVER s1 FOREIGN DATA WRAPPER foo;
277 CREATE SERVER s1 FOREIGN DATA WRAPPER foo; -- ERROR
278 ERROR: server "s1" already exists
279 CREATE SERVER s2 FOREIGN DATA WRAPPER foo OPTIONS (host 'a', dbname 'b');
280 CREATE SERVER s3 TYPE 'oracle' FOREIGN DATA WRAPPER foo;
281 CREATE SERVER s4 TYPE 'oracle' FOREIGN DATA WRAPPER foo OPTIONS (host 'a', dbname 'b');
282 CREATE SERVER s5 VERSION '15.0' FOREIGN DATA WRAPPER foo;
283 CREATE SERVER s6 VERSION '16.0' FOREIGN DATA WRAPPER foo OPTIONS (host 'a', dbname 'b');
284 CREATE SERVER s7 TYPE 'oracle' VERSION '17.0' FOREIGN DATA WRAPPER foo OPTIONS (host 'a', dbname 'b');
285 CREATE SERVER s8 FOREIGN DATA WRAPPER postgresql OPTIONS (foo '1'); -- ERROR
286 ERROR: invalid option "foo"
287 HINT: Valid options in this context are: authtype, service, user, password, connect_timeout, dbname, host, hostaddr, port, tty, options, requiressl, sslmode, gsslib
288 CREATE SERVER s8 FOREIGN DATA WRAPPER postgresql OPTIONS (host 'localhost', dbname 's8db');
289 \des+
290 List of foreign servers
291 Name | Owner | Foreign-data wrapper | Access privileges | Type | Version | Options
292 ------+-------------------+----------------------+-------------------+--------+---------+------------------------------
293 s1 | foreign_data_user | foo | | | |
294 s2 | foreign_data_user | foo | | | | {host=a,dbname=b}
295 s3 | foreign_data_user | foo | | oracle | |
296 s4 | foreign_data_user | foo | | oracle | | {host=a,dbname=b}
297 s5 | foreign_data_user | foo | | | 15.0 |
298 s6 | foreign_data_user | foo | | | 16.0 | {host=a,dbname=b}
299 s7 | foreign_data_user | foo | | oracle | 17.0 | {host=a,dbname=b}
300 s8 | foreign_data_user | postgresql | | | | {host=localhost,dbname=s8db}
301 (8 rows)
303 SET ROLE regress_test_role;
304 CREATE SERVER t1 FOREIGN DATA WRAPPER foo; -- ERROR: no usage on FDW
305 ERROR: permission denied for foreign-data wrapper foo
306 RESET ROLE;
307 GRANT USAGE ON FOREIGN DATA WRAPPER foo TO regress_test_role;
308 SET ROLE regress_test_role;
309 CREATE SERVER t1 FOREIGN DATA WRAPPER foo;
310 RESET ROLE;
311 \des+
312 List of foreign servers
313 Name | Owner | Foreign-data wrapper | Access privileges | Type | Version | Options
314 ------+-------------------+----------------------+-------------------+--------+---------+------------------------------
315 s1 | foreign_data_user | foo | | | |
316 s2 | foreign_data_user | foo | | | | {host=a,dbname=b}
317 s3 | foreign_data_user | foo | | oracle | |
318 s4 | foreign_data_user | foo | | oracle | | {host=a,dbname=b}
319 s5 | foreign_data_user | foo | | | 15.0 |
320 s6 | foreign_data_user | foo | | | 16.0 | {host=a,dbname=b}
321 s7 | foreign_data_user | foo | | oracle | 17.0 | {host=a,dbname=b}
322 s8 | foreign_data_user | postgresql | | | | {host=localhost,dbname=s8db}
323 t1 | regress_test_role | foo | | | |
324 (9 rows)
326 REVOKE USAGE ON FOREIGN DATA WRAPPER foo FROM regress_test_role;
327 GRANT USAGE ON FOREIGN DATA WRAPPER foo TO regress_test_indirect;
328 SET ROLE regress_test_role;
329 CREATE SERVER t2 FOREIGN DATA WRAPPER foo; -- ERROR
330 ERROR: permission denied for foreign-data wrapper foo
331 RESET ROLE;
332 GRANT regress_test_indirect TO regress_test_role;
333 SET ROLE regress_test_role;
334 CREATE SERVER t2 FOREIGN DATA WRAPPER foo;
335 \des+
336 List of foreign servers
337 Name | Owner | Foreign-data wrapper | Access privileges | Type | Version | Options
338 ------+-------------------+----------------------+-------------------+--------+---------+------------------------------
339 s1 | foreign_data_user | foo | | | |
340 s2 | foreign_data_user | foo | | | | {host=a,dbname=b}
341 s3 | foreign_data_user | foo | | oracle | |
342 s4 | foreign_data_user | foo | | oracle | | {host=a,dbname=b}
343 s5 | foreign_data_user | foo | | | 15.0 |
344 s6 | foreign_data_user | foo | | | 16.0 | {host=a,dbname=b}
345 s7 | foreign_data_user | foo | | oracle | 17.0 | {host=a,dbname=b}
346 s8 | foreign_data_user | postgresql | | | | {host=localhost,dbname=s8db}
347 t1 | regress_test_role | foo | | | |
348 t2 | regress_test_role | foo | | | |
349 (10 rows)
351 RESET ROLE;
352 REVOKE regress_test_indirect FROM regress_test_role;
353 -- ALTER SERVER
354 ALTER SERVER s0; -- ERROR
355 ERROR: syntax error at or near ";"
356 LINE 1: ALTER SERVER s0;
357 ^
358 ALTER SERVER s0 OPTIONS (a '1'); -- ERROR
359 ERROR: server "s0" does not exist
360 ALTER SERVER s1 VERSION '1.0' OPTIONS (servername 's1');
361 ALTER SERVER s2 VERSION '1.1';
362 ALTER SERVER s3 OPTIONS (tnsname 'orcl', port '1521');
363 GRANT USAGE ON FOREIGN SERVER s1 TO regress_test_role;
364 GRANT USAGE ON FOREIGN SERVER s6 TO regress_test_role2 WITH GRANT OPTION;
365 \des+
366 List of foreign servers
367 Name | Owner | Foreign-data wrapper | Access privileges | Type | Version | Options
368 ------+-------------------+----------------------+-----------------------------------------+--------+---------+------------------------------
369 s1 | foreign_data_user | foo | foreign_data_user=U/foreign_data_user | | 1.0 | {servername=s1}
370 : regress_test_role=U/foreign_data_user
371 s2 | foreign_data_user | foo | | | 1.1 | {host=a,dbname=b}
372 s3 | foreign_data_user | foo | | oracle | | {tnsname=orcl,port=1521}
373 s4 | foreign_data_user | foo | | oracle | | {host=a,dbname=b}
374 s5 | foreign_data_user | foo | | | 15.0 |
375 s6 | foreign_data_user | foo | foreign_data_user=U/foreign_data_user | | 16.0 | {host=a,dbname=b}
376 : regress_test_role2=U*/foreign_data_user
377 s7 | foreign_data_user | foo | | oracle | 17.0 | {host=a,dbname=b}
378 s8 | foreign_data_user | postgresql | | | | {host=localhost,dbname=s8db}
379 t1 | regress_test_role | foo | | | |
380 t2 | regress_test_role | foo | | | |
381 (10 rows)
383 SET ROLE regress_test_role;
384 ALTER SERVER s1 VERSION '1.1'; -- ERROR
385 ERROR: must be owner of foreign server s1
386 ALTER SERVER s1 OWNER TO regress_test_role; -- ERROR
387 ERROR: must be owner of foreign server s1
388 RESET ROLE;
389 ALTER SERVER s1 OWNER TO regress_test_role;
390 GRANT regress_test_role2 TO regress_test_role;
391 SET ROLE regress_test_role;
392 ALTER SERVER s1 VERSION '1.1';
393 ALTER SERVER s1 OWNER TO regress_test_role2; -- ERROR
394 ERROR: permission denied for foreign-data wrapper foo
395 RESET ROLE;
396 ALTER SERVER s8 OPTIONS (foo '1'); -- ERROR option validation
397 ERROR: invalid option "foo"
398 HINT: Valid options in this context are: authtype, service, user, password, connect_timeout, dbname, host, hostaddr, port, tty, options, requiressl, sslmode, gsslib
399 ALTER SERVER s8 OPTIONS (connect_timeout '30', SET dbname 'db1', DROP host);
400 SET ROLE regress_test_role;
401 ALTER SERVER s1 OWNER TO regress_test_indirect; -- ERROR
402 ERROR: must be member of role "regress_test_indirect"
403 RESET ROLE;
404 GRANT regress_test_indirect TO regress_test_role;
405 SET ROLE regress_test_role;
406 ALTER SERVER s1 OWNER TO regress_test_indirect;
407 RESET ROLE;
408 GRANT USAGE ON FOREIGN DATA WRAPPER foo TO regress_test_indirect;
409 SET ROLE regress_test_role;
410 ALTER SERVER s1 OWNER TO regress_test_indirect;
411 RESET ROLE;
412 DROP ROLE regress_test_indirect; -- ERROR
413 ERROR: role "regress_test_indirect" cannot be dropped because some objects depend on it
414 DETAIL: owner of server s1
415 access to foreign-data wrapper foo
416 \des+
417 List of foreign servers
418 Name | Owner | Foreign-data wrapper | Access privileges | Type | Version | Options
419 ------+-----------------------+----------------------+-----------------------------------------+--------+---------+---------------------------------
420 s1 | regress_test_indirect | foo | foreign_data_user=U/foreign_data_user | | 1.1 | {servername=s1}
421 : regress_test_role=U/foreign_data_user
422 s2 | foreign_data_user | foo | | | 1.1 | {host=a,dbname=b}
423 s3 | foreign_data_user | foo | | oracle | | {tnsname=orcl,port=1521}
424 s4 | foreign_data_user | foo | | oracle | | {host=a,dbname=b}
425 s5 | foreign_data_user | foo | | | 15.0 |
426 s6 | foreign_data_user | foo | foreign_data_user=U/foreign_data_user | | 16.0 | {host=a,dbname=b}
427 : regress_test_role2=U*/foreign_data_user
428 s7 | foreign_data_user | foo | | oracle | 17.0 | {host=a,dbname=b}
429 s8 | foreign_data_user | postgresql | | | | {dbname=db1,connect_timeout=30}
430 t1 | regress_test_role | foo | | | |
431 t2 | regress_test_role | foo | | | |
432 (10 rows)
434 -- DROP SERVER
435 DROP SERVER nonexistent; -- ERROR
436 ERROR: server "nonexistent" does not exist
437 DROP SERVER IF EXISTS nonexistent;
438 NOTICE: server "nonexistent" does not exist, skipping
439 \des
440 List of foreign servers
441 Name | Owner | Foreign-data wrapper
442 ------+-----------------------+----------------------
443 s1 | regress_test_indirect | foo
444 s2 | foreign_data_user | foo
445 s3 | foreign_data_user | foo
446 s4 | foreign_data_user | foo
447 s5 | foreign_data_user | foo
448 s6 | foreign_data_user | foo
449 s7 | foreign_data_user | foo
450 s8 | foreign_data_user | postgresql
451 t1 | regress_test_role | foo
452 t2 | regress_test_role | foo
453 (10 rows)
455 SET ROLE regress_test_role;
456 DROP SERVER s2; -- ERROR
457 ERROR: must be owner of foreign server s2
458 DROP SERVER s1;
459 RESET ROLE;
460 \des
461 List of foreign servers
462 Name | Owner | Foreign-data wrapper
463 ------+-------------------+----------------------
464 s2 | foreign_data_user | foo
465 s3 | foreign_data_user | foo
466 s4 | foreign_data_user | foo
467 s5 | foreign_data_user | foo
468 s6 | foreign_data_user | foo
469 s7 | foreign_data_user | foo
470 s8 | foreign_data_user | postgresql
471 t1 | regress_test_role | foo
472 t2 | regress_test_role | foo
473 (9 rows)
475 ALTER SERVER s2 OWNER TO regress_test_role;
476 SET ROLE regress_test_role;
477 DROP SERVER s2;
478 RESET ROLE;
479 \des
480 List of foreign servers
481 Name | Owner | Foreign-data wrapper
482 ------+-------------------+----------------------
483 s3 | foreign_data_user | foo
484 s4 | foreign_data_user | foo
485 s5 | foreign_data_user | foo
486 s6 | foreign_data_user | foo
487 s7 | foreign_data_user | foo
488 s8 | foreign_data_user | postgresql
489 t1 | regress_test_role | foo
490 t2 | regress_test_role | foo
491 (8 rows)
493 CREATE USER MAPPING FOR current_user SERVER s3;
494 \deu
495 List of user mappings
496 Server | Username
497 --------+-------------------
498 s3 | foreign_data_user
499 (1 row)
501 DROP SERVER s3; -- ERROR
502 ERROR: cannot drop server s3 because other objects depend on it
503 DETAIL: user mapping for foreign_data_user depends on server s3
504 HINT: Use DROP ... CASCADE to drop the dependent objects too.
505 DROP SERVER s3 CASCADE;
506 NOTICE: drop cascades to user mapping for foreign_data_user
507 \des
508 List of foreign servers
509 Name | Owner | Foreign-data wrapper
510 ------+-------------------+----------------------
511 s4 | foreign_data_user | foo
512 s5 | foreign_data_user | foo
513 s6 | foreign_data_user | foo
514 s7 | foreign_data_user | foo
515 s8 | foreign_data_user | postgresql
516 t1 | regress_test_role | foo
517 t2 | regress_test_role | foo
518 (7 rows)
520 \deu
521 List of user mappings
522 Server | Username
523 --------+----------
524 (0 rows)
526 -- CREATE USER MAPPING
527 CREATE USER MAPPING FOR baz SERVER s1; -- ERROR
528 ERROR: role "baz" does not exist
529 CREATE USER MAPPING FOR current_user SERVER s1; -- ERROR
530 ERROR: server "s1" does not exist
531 CREATE USER MAPPING FOR current_user SERVER s4;
532 CREATE USER MAPPING FOR user SERVER s4; -- ERROR duplicate
533 ERROR: user mapping "foreign_data_user" already exists for server s4
534 CREATE USER MAPPING FOR public SERVER s4 OPTIONS (mapping 'is public');
535 CREATE USER MAPPING FOR user SERVER s8 OPTIONS (username 'test', password 'secret'); -- ERROR
536 ERROR: invalid option "username"
537 HINT: Valid options in this context are: authtype, service, user, password, connect_timeout, dbname, host, hostaddr, port, tty, options, requiressl, sslmode, gsslib
538 CREATE USER MAPPING FOR user SERVER s8 OPTIONS (user 'test', password 'secret');
539 ALTER SERVER s5 OWNER TO regress_test_role;
540 ALTER SERVER s6 OWNER TO regress_test_indirect;
541 SET ROLE regress_test_role;
542 CREATE USER MAPPING FOR current_user SERVER s5;
543 CREATE USER MAPPING FOR current_user SERVER s6 OPTIONS (username 'test');
544 CREATE USER MAPPING FOR current_user SERVER s7; -- ERROR
545 ERROR: permission denied for foreign server s7
546 CREATE USER MAPPING FOR public SERVER s8; -- ERROR
547 ERROR: must be owner of foreign server s8
548 RESET ROLE;
549 ALTER SERVER t1 OWNER TO regress_test_indirect;
550 SET ROLE regress_test_role;
551 CREATE USER MAPPING FOR current_user SERVER t1 OPTIONS (username 'bob', password 'boo');
552 CREATE USER MAPPING FOR public SERVER t1;
553 RESET ROLE;
554 \deu
555 List of user mappings
556 Server | Username
557 --------+-------------------
558 s4 | foreign_data_user
559 s4 | public
560 s5 | regress_test_role
561 s6 | regress_test_role
562 s8 | foreign_data_user
563 t1 | public
564 t1 | regress_test_role
565 (7 rows)
567 -- ALTER USER MAPPING
568 ALTER USER MAPPING FOR bob SERVER s4 OPTIONS (gotcha 'true'); -- ERROR
569 ERROR: role "bob" does not exist
570 ALTER USER MAPPING FOR user SERVER ss4 OPTIONS (gotcha 'true'); -- ERROR
571 ERROR: server "ss4" does not exist
572 ALTER USER MAPPING FOR public SERVER s5 OPTIONS (gotcha 'true'); -- ERROR
573 ERROR: user mapping "public" does not exist for the server
574 ALTER USER MAPPING FOR current_user SERVER s8 OPTIONS (username 'test'); -- ERROR
575 ERROR: invalid option "username"
576 HINT: Valid options in this context are: authtype, service, user, password, connect_timeout, dbname, host, hostaddr, port, tty, options, requiressl, sslmode, gsslib
577 ALTER USER MAPPING FOR current_user SERVER s8 OPTIONS (DROP user, SET password 'public');
578 SET ROLE regress_test_role;
579 ALTER USER MAPPING FOR current_user SERVER s5 OPTIONS (ADD modified '1');
580 ALTER USER MAPPING FOR public SERVER s4 OPTIONS (ADD modified '1'); -- ERROR
581 ERROR: must be owner of foreign server s4
582 ALTER USER MAPPING FOR public SERVER t1 OPTIONS (ADD modified '1');
583 RESET ROLE;
584 \deu+
585 List of user mappings
586 Server | Username | Options
587 --------+-------------------+-----------------------------
588 s4 | foreign_data_user |
589 s4 | public | {"mapping=is public"}
590 s5 | regress_test_role | {modified=1}
591 s6 | regress_test_role | {username=test}
592 s8 | foreign_data_user | {password=public}
593 t1 | public | {modified=1}
594 t1 | regress_test_role | {username=bob,password=boo}
595 (7 rows)
597 -- DROP USER MAPPING
598 DROP USER MAPPING FOR bob SERVER s4; -- ERROR
599 ERROR: role "bob" does not exist
600 DROP USER MAPPING FOR user SERVER ss4;
601 ERROR: server "ss4" does not exist
602 DROP USER MAPPING FOR public SERVER s7; -- ERROR
603 ERROR: user mapping "public" does not exist for the server
604 DROP USER MAPPING IF EXISTS FOR bob SERVER s4;
605 NOTICE: role "bob" does not exist, skipping
606 DROP USER MAPPING IF EXISTS FOR user SERVER ss4;
607 NOTICE: server does not exist, skipping
608 DROP USER MAPPING IF EXISTS FOR public SERVER s7;
609 NOTICE: user mapping "public" does not exist for the server, skipping
610 CREATE USER MAPPING FOR public SERVER s8;
611 SET ROLE regress_test_role;
612 DROP USER MAPPING FOR public SERVER s8; -- ERROR
613 ERROR: must be owner of foreign server s8
614 RESET ROLE;
615 DROP SERVER s7;
616 \deu
617 List of user mappings
618 Server | Username
619 --------+-------------------
620 s4 | foreign_data_user
621 s4 | public
622 s5 | regress_test_role
623 s6 | regress_test_role
624 s8 | foreign_data_user
625 s8 | public
626 t1 | public
627 t1 | regress_test_role
628 (8 rows)
630 -- Information schema
631 SELECT * FROM information_schema.foreign_data_wrappers ORDER BY 1, 2;
632 foreign_data_wrapper_catalog | foreign_data_wrapper_name | authorization_identifier | library_name | foreign_data_wrapper_language
633 ------------------------------+---------------------------+--------------------------+--------------+-------------------------------
634 regression | dummy | foreign_data_user | | c
635 regression | foo | foreign_data_user | | c
636 regression | postgresql | foreign_data_user | | c
637 (3 rows)
639 SELECT * FROM information_schema.foreign_data_wrapper_options ORDER BY 1, 2, 3;
640 foreign_data_wrapper_catalog | foreign_data_wrapper_name | option_name | option_value
641 ------------------------------+---------------------------+--------------+--------------
642 regression | foo | test_wrapper | true
643 (1 row)
645 SELECT * FROM information_schema.foreign_servers ORDER BY 1, 2;
646 foreign_server_catalog | foreign_server_name | foreign_data_wrapper_catalog | foreign_data_wrapper_name | foreign_server_type | foreign_server_version | authorization_identifier
647 ------------------------+---------------------+------------------------------+---------------------------+---------------------+------------------------+--------------------------
648 regression | s4 | regression | foo | oracle | | foreign_data_user
649 regression | s5 | regression | foo | | 15.0 | regress_test_role
650 regression | s6 | regression | foo | | 16.0 | regress_test_indirect
651 regression | s8 | regression | postgresql | | | foreign_data_user
652 regression | t1 | regression | foo | | | regress_test_indirect
653 regression | t2 | regression | foo | | | regress_test_role
654 (6 rows)
656 SELECT * FROM information_schema.foreign_server_options ORDER BY 1, 2, 3;
657 foreign_server_catalog | foreign_server_name | option_name | option_value
658 ------------------------+---------------------+-----------------+--------------
659 regression | s4 | dbname | b
660 regression | s4 | host | a
661 regression | s6 | dbname | b
662 regression | s6 | host | a
663 regression | s8 | connect_timeout | 30
664 regression | s8 | dbname | db1
665 (6 rows)
667 SELECT * FROM information_schema.user_mappings ORDER BY lower(authorization_identifier), 2, 3;
668 authorization_identifier | foreign_server_catalog | foreign_server_name
669 --------------------------+------------------------+---------------------
670 foreign_data_user | regression | s4
671 foreign_data_user | regression | s8
672 PUBLIC | regression | s4
673 PUBLIC | regression | s8
674 PUBLIC | regression | t1
675 regress_test_role | regression | s5
676 regress_test_role | regression | s6
677 regress_test_role | regression | t1
678 (8 rows)
680 SELECT * FROM information_schema.user_mapping_options ORDER BY lower(authorization_identifier), 2, 3, 4;
681 authorization_identifier | foreign_server_catalog | foreign_server_name | option_name | option_value
682 --------------------------+------------------------+---------------------+-------------+--------------
683 foreign_data_user | regression | s8 | password | public
684 PUBLIC | regression | s4 | mapping | is public
685 PUBLIC | regression | t1 | modified | 1
686 regress_test_role | regression | s5 | modified | 1
687 regress_test_role | regression | s6 | username | test
688 regress_test_role | regression | t1 | password | boo
689 regress_test_role | regression | t1 | username | bob
690 (7 rows)
692 SELECT * FROM information_schema.usage_privileges WHERE object_type LIKE 'FOREIGN%' ORDER BY 1, 2, 3, 4, 5;
693 grantor | grantee | object_catalog | object_schema | object_name | object_type | privilege_type | is_grantable
694 -------------------+-----------------------+----------------+---------------+-------------+----------------------+----------------+--------------
695 foreign_data_user | foreign_data_user | regression | | foo | FOREIGN DATA WRAPPER | USAGE | YES
696 foreign_data_user | foreign_data_user | regression | | s6 | FOREIGN SERVER | USAGE | YES
697 foreign_data_user | regress_test_indirect | regression | | foo | FOREIGN DATA WRAPPER | USAGE | NO
698 foreign_data_user | regress_test_role2 | regression | | s6 | FOREIGN SERVER | USAGE | YES
699 (4 rows)
701 SELECT * FROM information_schema.role_usage_grants WHERE object_type LIKE 'FOREIGN%' ORDER BY 1, 2, 3, 4, 5;
702 grantor | grantee | object_catalog | object_schema | object_name | object_type | privilege_type | is_grantable
703 -------------------+-----------------------+----------------+---------------+-------------+----------------------+----------------+--------------
704 foreign_data_user | foreign_data_user | regression | | foo | FOREIGN DATA WRAPPER | USAGE | YES
705 foreign_data_user | foreign_data_user | regression | | s6 | FOREIGN SERVER | USAGE | YES
706 foreign_data_user | regress_test_indirect | regression | | foo | FOREIGN DATA WRAPPER | USAGE | NO
707 foreign_data_user | regress_test_role2 | regression | | s6 | FOREIGN SERVER | USAGE | YES
708 (4 rows)
710 SET ROLE regress_test_role;
711 SELECT * FROM information_schema.user_mapping_options ORDER BY 1, 2, 3, 4;
712 authorization_identifier | foreign_server_catalog | foreign_server_name | option_name | option_value
713 --------------------------+------------------------+---------------------+-------------+--------------
714 PUBLIC | regression | t1 | modified | 1
715 regress_test_role | regression | s5 | modified | 1
716 regress_test_role | regression | s6 | username | test
717 regress_test_role | regression | t1 | password | boo
718 regress_test_role | regression | t1 | username | bob
719 (5 rows)
721 SELECT * FROM information_schema.usage_privileges WHERE object_type LIKE 'FOREIGN%' ORDER BY 1, 2, 3, 4, 5;
722 grantor | grantee | object_catalog | object_schema | object_name | object_type | privilege_type | is_grantable
723 -------------------+-----------------------+----------------+---------------+-------------+----------------------+----------------+--------------
724 foreign_data_user | regress_test_indirect | regression | | foo | FOREIGN DATA WRAPPER | USAGE | NO
725 foreign_data_user | regress_test_role2 | regression | | s6 | FOREIGN SERVER | USAGE | YES
726 (2 rows)
728 SELECT * FROM information_schema.role_usage_grants WHERE object_type LIKE 'FOREIGN%' ORDER BY 1, 2, 3, 4, 5;
729 grantor | grantee | object_catalog | object_schema | object_name | object_type | privilege_type | is_grantable
730 -------------------+-----------------------+----------------+---------------+-------------+----------------------+----------------+--------------
731 foreign_data_user | regress_test_indirect | regression | | foo | FOREIGN DATA WRAPPER | USAGE | NO
732 foreign_data_user | regress_test_role2 | regression | | s6 | FOREIGN SERVER | USAGE | YES
733 (2 rows)
735 DROP USER MAPPING FOR current_user SERVER t1;
736 SET ROLE regress_test_role2;
737 SELECT * FROM information_schema.user_mapping_options ORDER BY 1, 2, 3, 4;
738 authorization_identifier | foreign_server_catalog | foreign_server_name | option_name | option_value
739 --------------------------+------------------------+---------------------+-------------+--------------
740 regress_test_role | regression | s6 | username |
741 (1 row)
743 RESET ROLE;
744 -- has_foreign_data_wrapper_privilege
745 SELECT has_foreign_data_wrapper_privilege('regress_test_role',
746 (SELECT oid FROM pg_foreign_data_wrapper WHERE fdwname='foo'), 'USAGE');
747 has_foreign_data_wrapper_privilege
748 ------------------------------------
749 t
750 (1 row)
752 SELECT has_foreign_data_wrapper_privilege('regress_test_role', 'foo', 'USAGE');
753 has_foreign_data_wrapper_privilege
754 ------------------------------------
755 t
756 (1 row)
758 SELECT has_foreign_data_wrapper_privilege(
759 (SELECT oid FROM pg_roles WHERE rolname='regress_test_role'),
760 (SELECT oid FROM pg_foreign_data_wrapper WHERE fdwname='foo'), 'USAGE');
761 has_foreign_data_wrapper_privilege
762 ------------------------------------
763 t
764 (1 row)
766 SELECT has_foreign_data_wrapper_privilege(
767 (SELECT oid FROM pg_foreign_data_wrapper WHERE fdwname='foo'), 'USAGE');
768 has_foreign_data_wrapper_privilege
769 ------------------------------------
770 t
771 (1 row)
773 SELECT has_foreign_data_wrapper_privilege(
774 (SELECT oid FROM pg_roles WHERE rolname='regress_test_role'), 'foo', 'USAGE');
775 has_foreign_data_wrapper_privilege
776 ------------------------------------
777 t
778 (1 row)
780 SELECT has_foreign_data_wrapper_privilege('foo', 'USAGE');
781 has_foreign_data_wrapper_privilege
782 ------------------------------------
783 t
784 (1 row)
786 GRANT USAGE ON FOREIGN DATA WRAPPER foo TO regress_test_role;
787 SELECT has_foreign_data_wrapper_privilege('regress_test_role', 'foo', 'USAGE');
788 has_foreign_data_wrapper_privilege
789 ------------------------------------
790 t
791 (1 row)
793 -- has_server_privilege
794 SELECT has_server_privilege('regress_test_role',
795 (SELECT oid FROM pg_foreign_server WHERE srvname='s8'), 'USAGE');
796 has_server_privilege
797 ----------------------
798 f
799 (1 row)
801 SELECT has_server_privilege('regress_test_role', 's8', 'USAGE');
802 has_server_privilege
803 ----------------------
804 f
805 (1 row)
807 SELECT has_server_privilege(
808 (SELECT oid FROM pg_roles WHERE rolname='regress_test_role'),
809 (SELECT oid FROM pg_foreign_server WHERE srvname='s8'), 'USAGE');
810 has_server_privilege
811 ----------------------
812 f
813 (1 row)
815 SELECT has_server_privilege(
816 (SELECT oid FROM pg_foreign_server WHERE srvname='s8'), 'USAGE');
817 has_server_privilege
818 ----------------------
819 t
820 (1 row)
822 SELECT has_server_privilege(
823 (SELECT oid FROM pg_roles WHERE rolname='regress_test_role'), 's8', 'USAGE');
824 has_server_privilege
825 ----------------------
826 f
827 (1 row)
829 SELECT has_server_privilege('s8', 'USAGE');
830 has_server_privilege
831 ----------------------
832 t
833 (1 row)
835 GRANT USAGE ON FOREIGN SERVER s8 TO regress_test_role;
836 SELECT has_server_privilege('regress_test_role', 's8', 'USAGE');
837 has_server_privilege
838 ----------------------
839 t
840 (1 row)
842 REVOKE USAGE ON FOREIGN SERVER s8 FROM regress_test_role;
843 GRANT USAGE ON FOREIGN SERVER s4 TO regress_test_role;
844 DROP USER MAPPING FOR public SERVER s4;
845 ALTER SERVER s6 OPTIONS (DROP host, DROP dbname);
846 ALTER USER MAPPING FOR regress_test_role SERVER s6 OPTIONS (DROP username);
847 ALTER FOREIGN DATA WRAPPER foo VALIDATOR postgresql_fdw_validator;
848 WARNING: changing the foreign-data wrapper validator can cause the options for dependent objects to become invalid
849 -- Privileges
850 SET ROLE unprivileged_role;
851 CREATE FOREIGN DATA WRAPPER foobar; -- ERROR
852 ERROR: permission denied to create foreign-data wrapper "foobar"
853 HINT: Must be superuser to create a foreign-data wrapper.
854 ALTER FOREIGN DATA WRAPPER foo OPTIONS (gotcha 'true'); -- ERROR
855 ERROR: permission denied to alter foreign-data wrapper "foo"
856 HINT: Must be superuser to alter a foreign-data wrapper.
857 ALTER FOREIGN DATA WRAPPER foo OWNER TO unprivileged_role; -- ERROR
858 ERROR: permission denied to change owner of foreign-data wrapper "foo"
859 HINT: Must be superuser to change owner of a foreign-data wrapper.
860 DROP FOREIGN DATA WRAPPER foo; -- ERROR
861 ERROR: permission denied to drop foreign-data wrapper "foo"
862 HINT: Must be superuser to drop a foreign-data wrapper.
863 GRANT USAGE ON FOREIGN DATA WRAPPER foo TO regress_test_role; -- ERROR
864 ERROR: permission denied for foreign-data wrapper foo
865 CREATE SERVER s9 FOREIGN DATA WRAPPER foo; -- ERROR
866 ERROR: permission denied for foreign-data wrapper foo
867 ALTER SERVER s4 VERSION '0.5'; -- ERROR
868 ERROR: must be owner of foreign server s4
869 ALTER SERVER s4 OWNER TO unprivileged_role; -- ERROR
870 ERROR: must be owner of foreign server s4
871 DROP SERVER s4; -- ERROR
872 ERROR: must be owner of foreign server s4
873 GRANT USAGE ON FOREIGN SERVER s4 TO regress_test_role; -- ERROR
874 ERROR: permission denied for foreign server s4
875 CREATE USER MAPPING FOR public SERVER s4; -- ERROR
876 ERROR: must be owner of foreign server s4
877 ALTER USER MAPPING FOR regress_test_role SERVER s6 OPTIONS (gotcha 'true'); -- ERROR
878 ERROR: must be owner of foreign server s6
879 DROP USER MAPPING FOR regress_test_role SERVER s6; -- ERROR
880 ERROR: must be owner of foreign server s6
881 RESET ROLE;
882 GRANT USAGE ON FOREIGN DATA WRAPPER postgresql TO unprivileged_role;
883 GRANT USAGE ON FOREIGN DATA WRAPPER foo TO unprivileged_role WITH GRANT OPTION;
884 SET ROLE unprivileged_role;
885 CREATE FOREIGN DATA WRAPPER foobar; -- ERROR
886 ERROR: permission denied to create foreign-data wrapper "foobar"
887 HINT: Must be superuser to create a foreign-data wrapper.
888 ALTER FOREIGN DATA WRAPPER foo OPTIONS (gotcha 'true'); -- ERROR
889 ERROR: permission denied to alter foreign-data wrapper "foo"
890 HINT: Must be superuser to alter a foreign-data wrapper.
891 DROP FOREIGN DATA WRAPPER foo; -- ERROR
892 ERROR: permission denied to drop foreign-data wrapper "foo"
893 HINT: Must be superuser to drop a foreign-data wrapper.
894 GRANT USAGE ON FOREIGN DATA WRAPPER postgresql TO regress_test_role; -- WARNING
895 WARNING: no privileges were granted for "postgresql"
896 GRANT USAGE ON FOREIGN DATA WRAPPER foo TO regress_test_role;
897 CREATE SERVER s9 FOREIGN DATA WRAPPER postgresql;
898 ALTER SERVER s6 VERSION '0.5'; -- ERROR
899 ERROR: must be owner of foreign server s6
900 DROP SERVER s6; -- ERROR
901 ERROR: must be owner of foreign server s6
902 GRANT USAGE ON FOREIGN SERVER s6 TO regress_test_role; -- ERROR
903 ERROR: permission denied for foreign server s6
904 GRANT USAGE ON FOREIGN SERVER s9 TO regress_test_role;
905 CREATE USER MAPPING FOR public SERVER s6; -- ERROR
906 ERROR: must be owner of foreign server s6
907 CREATE USER MAPPING FOR public SERVER s9;
908 ALTER USER MAPPING FOR regress_test_role SERVER s6 OPTIONS (gotcha 'true'); -- ERROR
909 ERROR: must be owner of foreign server s6
910 DROP USER MAPPING FOR regress_test_role SERVER s6; -- ERROR
911 ERROR: must be owner of foreign server s6
912 RESET ROLE;
913 REVOKE USAGE ON FOREIGN DATA WRAPPER foo FROM unprivileged_role; -- ERROR
914 ERROR: dependent privileges exist
915 HINT: Use CASCADE to revoke them too.
916 REVOKE USAGE ON FOREIGN DATA WRAPPER foo FROM unprivileged_role CASCADE;
917 SET ROLE unprivileged_role;
918 GRANT USAGE ON FOREIGN DATA WRAPPER foo TO regress_test_role; -- ERROR
919 ERROR: permission denied for foreign-data wrapper foo
920 CREATE SERVER s10 FOREIGN DATA WRAPPER foo; -- ERROR
921 ERROR: permission denied for foreign-data wrapper foo
922 ALTER SERVER s9 VERSION '1.1';
923 GRANT USAGE ON FOREIGN SERVER s9 TO regress_test_role;
924 CREATE USER MAPPING FOR current_user SERVER s9;
925 DROP SERVER s9 CASCADE;
926 NOTICE: drop cascades to 2 other objects
927 DETAIL: drop cascades to user mapping for public
928 drop cascades to user mapping for unprivileged_role
929 RESET ROLE;
930 CREATE SERVER s9 FOREIGN DATA WRAPPER foo;
931 GRANT USAGE ON FOREIGN SERVER s9 TO unprivileged_role;
932 SET ROLE unprivileged_role;
933 ALTER SERVER s9 VERSION '1.2'; -- ERROR
934 ERROR: must be owner of foreign server s9
935 GRANT USAGE ON FOREIGN SERVER s9 TO regress_test_role; -- WARNING
936 WARNING: no privileges were granted for "s9"
937 CREATE USER MAPPING FOR current_user SERVER s9;
938 DROP SERVER s9 CASCADE; -- ERROR
939 ERROR: must be owner of foreign server s9
940 RESET ROLE;
941 -- Cleanup
942 DROP ROLE regress_test_role; -- ERROR
943 ERROR: role "regress_test_role" cannot be dropped because some objects depend on it
944 DETAIL: access to server s4
945 access to foreign-data wrapper foo
946 owner of user mapping for regress_test_role
947 owner of user mapping for regress_test_role
948 owner of server s5
949 owner of server t2
950 DROP SERVER s5 CASCADE;
951 NOTICE: drop cascades to user mapping for regress_test_role
952 DROP SERVER t1 CASCADE;
953 NOTICE: drop cascades to user mapping for public
954 DROP SERVER t2;
955 DROP USER MAPPING FOR regress_test_role SERVER s6;
956 DROP FOREIGN DATA WRAPPER foo CASCADE;
957 NOTICE: drop cascades to 5 other objects
958 DETAIL: drop cascades to server s4
959 drop cascades to user mapping for foreign_data_user
960 drop cascades to server s6
961 drop cascades to server s9
962 drop cascades to user mapping for unprivileged_role
963 DROP SERVER s8 CASCADE;
964 NOTICE: drop cascades to 2 other objects
965 DETAIL: drop cascades to user mapping for foreign_data_user
966 drop cascades to user mapping for public
967 DROP ROLE regress_test_indirect;
968 DROP ROLE regress_test_role;
969 DROP ROLE unprivileged_role; -- ERROR
970 ERROR: role "unprivileged_role" cannot be dropped because some objects depend on it
971 DETAIL: access to foreign-data wrapper postgresql
972 REVOKE ALL ON FOREIGN DATA WRAPPER postgresql FROM unprivileged_role;
973 DROP ROLE unprivileged_role;
974 DROP ROLE regress_test_role2;
975 DROP FOREIGN DATA WRAPPER postgresql CASCADE;
976 DROP FOREIGN DATA WRAPPER dummy CASCADE;
977 \c
978 DROP ROLE foreign_data_user;
979 -- At this point we should have no wrappers, no servers, and no mappings.
980 SELECT fdwname, fdwvalidator, fdwoptions FROM pg_foreign_data_wrapper;
981 fdwname | fdwvalidator | fdwoptions
982 ---------+--------------+------------
983 (0 rows)
985 SELECT srvname, srvoptions FROM pg_foreign_server;
986 srvname | srvoptions
987 ---------+------------
988 (0 rows)
990 SELECT * FROM pg_user_mapping;
991 umuser | umserver | umoptions
992 --------+----------+-----------
993 (0 rows)