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