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 CREATE FOREIGN DATA WRAPPER postgresql VALIDATOR postgresql_fdw_validator;
26 -- At this point we should have 2 built-in wrappers and no servers.
27 SELECT fdwname, fdwvalidator::regproc, fdwoptions FROM pg_foreign_data_wrapper ORDER BY 1, 2, 3;
28 SELECT srvname, srvoptions FROM pg_foreign_server;
29 SELECT * FROM pg_user_mapping;
31 -- CREATE FOREIGN DATA WRAPPER
32 CREATE FOREIGN DATA WRAPPER foo VALIDATOR bar; -- ERROR
33 CREATE FOREIGN DATA WRAPPER foo;
36 CREATE FOREIGN DATA WRAPPER foo; -- duplicate
37 DROP FOREIGN DATA WRAPPER foo;
38 CREATE FOREIGN DATA WRAPPER foo OPTIONS (testing '1');
41 DROP FOREIGN DATA WRAPPER foo;
42 CREATE FOREIGN DATA WRAPPER foo OPTIONS (testing '1', testing '2'); -- ERROR
43 CREATE FOREIGN DATA WRAPPER foo OPTIONS (testing '1', another '2');
46 DROP FOREIGN DATA WRAPPER foo;
47 SET ROLE regress_test_role;
48 CREATE FOREIGN DATA WRAPPER foo; -- ERROR
50 CREATE FOREIGN DATA WRAPPER foo VALIDATOR postgresql_fdw_validator;
53 -- ALTER FOREIGN DATA WRAPPER
54 ALTER FOREIGN DATA WRAPPER foo; -- ERROR
55 ALTER FOREIGN DATA WRAPPER foo VALIDATOR bar; -- ERROR
56 ALTER FOREIGN DATA WRAPPER foo NO VALIDATOR;
59 ALTER FOREIGN DATA WRAPPER foo OPTIONS (a '1', b '2');
60 ALTER FOREIGN DATA WRAPPER foo OPTIONS (SET c '4'); -- ERROR
61 ALTER FOREIGN DATA WRAPPER foo OPTIONS (DROP c); -- ERROR
62 ALTER FOREIGN DATA WRAPPER foo OPTIONS (ADD x '1', DROP x);
65 ALTER FOREIGN DATA WRAPPER foo OPTIONS (DROP a, SET b '3', ADD c '4');
68 ALTER FOREIGN DATA WRAPPER foo OPTIONS (a '2');
69 ALTER FOREIGN DATA WRAPPER foo OPTIONS (b '4'); -- ERROR
72 SET ROLE regress_test_role;
73 ALTER FOREIGN DATA WRAPPER foo OPTIONS (ADD d '5'); -- ERROR
74 SET ROLE regress_test_role_super;
75 ALTER FOREIGN DATA WRAPPER foo OPTIONS (ADD d '5');
78 ALTER FOREIGN DATA WRAPPER foo OWNER TO regress_test_role; -- ERROR
79 ALTER FOREIGN DATA WRAPPER foo OWNER TO regress_test_role_super;
80 ALTER ROLE regress_test_role_super NOSUPERUSER;
81 SET ROLE regress_test_role_super;
82 ALTER FOREIGN DATA WRAPPER foo OPTIONS (ADD e '6'); -- ERROR
86 -- DROP FOREIGN DATA WRAPPER
87 DROP FOREIGN DATA WRAPPER nonexistent; -- ERROR
88 DROP FOREIGN DATA WRAPPER IF EXISTS nonexistent;
91 DROP ROLE regress_test_role_super; -- ERROR
92 SET ROLE regress_test_role_super;
93 DROP FOREIGN DATA WRAPPER foo; -- ERROR
95 ALTER ROLE regress_test_role_super SUPERUSER;
96 DROP FOREIGN DATA WRAPPER foo;
97 DROP ROLE regress_test_role_super;
100 CREATE FOREIGN DATA WRAPPER foo;
101 CREATE SERVER s1 FOREIGN DATA WRAPPER foo;
102 CREATE USER MAPPING FOR current_user SERVER s1;
106 DROP FOREIGN DATA WRAPPER foo; -- ERROR
107 SET ROLE regress_test_role;
108 DROP FOREIGN DATA WRAPPER foo CASCADE; -- ERROR
110 DROP FOREIGN DATA WRAPPER foo CASCADE;
115 -- exercise CREATE SERVER
116 CREATE SERVER s1 FOREIGN DATA WRAPPER foo; -- ERROR
117 CREATE FOREIGN DATA WRAPPER foo OPTIONS (test_wrapper 'true');
118 CREATE SERVER s1 FOREIGN DATA WRAPPER foo;
119 CREATE SERVER s1 FOREIGN DATA WRAPPER foo; -- ERROR
120 CREATE SERVER s2 FOREIGN DATA WRAPPER foo OPTIONS (host 'a', dbname 'b');
121 CREATE SERVER s3 TYPE 'oracle' FOREIGN DATA WRAPPER foo;
122 CREATE SERVER s4 TYPE 'oracle' FOREIGN DATA WRAPPER foo OPTIONS (host 'a', dbname 'b');
123 CREATE SERVER s5 VERSION '15.0' FOREIGN DATA WRAPPER foo;
124 CREATE SERVER s6 VERSION '16.0' FOREIGN DATA WRAPPER foo OPTIONS (host 'a', dbname 'b');
125 CREATE SERVER s7 TYPE 'oracle' VERSION '17.0' FOREIGN DATA WRAPPER foo OPTIONS (host 'a', dbname 'b');
126 CREATE SERVER s8 FOREIGN DATA WRAPPER postgresql OPTIONS (foo '1'); -- ERROR
127 CREATE SERVER s8 FOREIGN DATA WRAPPER postgresql OPTIONS (host 'localhost', dbname 's8db');
129 SET ROLE regress_test_role;
130 CREATE SERVER t1 FOREIGN DATA WRAPPER foo; -- ERROR: no usage on FDW
132 GRANT USAGE ON FOREIGN DATA WRAPPER foo TO regress_test_role;
133 SET ROLE regress_test_role;
134 CREATE SERVER t1 FOREIGN DATA WRAPPER foo;
138 REVOKE USAGE ON FOREIGN DATA WRAPPER foo FROM regress_test_role;
139 GRANT USAGE ON FOREIGN DATA WRAPPER foo TO regress_test_indirect;
140 SET ROLE regress_test_role;
141 CREATE SERVER t2 FOREIGN DATA WRAPPER foo; -- ERROR
143 GRANT regress_test_indirect TO regress_test_role;
144 SET ROLE regress_test_role;
145 CREATE SERVER t2 FOREIGN DATA WRAPPER foo;
148 REVOKE regress_test_indirect FROM regress_test_role;
151 ALTER SERVER s0; -- ERROR
152 ALTER SERVER s0 OPTIONS (a '1'); -- ERROR
153 ALTER SERVER s1 VERSION '1.0' OPTIONS (servername 's1');
154 ALTER SERVER s2 VERSION '1.1';
155 ALTER SERVER s3 OPTIONS (tnsname 'orcl', port '1521');
156 GRANT USAGE ON FOREIGN SERVER s1 TO regress_test_role;
157 GRANT USAGE ON FOREIGN SERVER s6 TO regress_test_role2 WITH GRANT OPTION;
159 SET ROLE regress_test_role;
160 ALTER SERVER s1 VERSION '1.1'; -- ERROR
161 ALTER SERVER s1 OWNER TO regress_test_role; -- ERROR
163 ALTER SERVER s1 OWNER TO regress_test_role;
164 GRANT regress_test_role2 TO regress_test_role;
165 SET ROLE regress_test_role;
166 ALTER SERVER s1 VERSION '1.1';
167 ALTER SERVER s1 OWNER TO regress_test_role2; -- ERROR
169 ALTER SERVER s8 OPTIONS (foo '1'); -- ERROR option validation
170 ALTER SERVER s8 OPTIONS (connect_timeout '30', SET dbname 'db1', DROP host);
171 SET ROLE regress_test_role;
172 ALTER SERVER s1 OWNER TO regress_test_indirect; -- ERROR
174 GRANT regress_test_indirect TO regress_test_role;
175 SET ROLE regress_test_role;
176 ALTER SERVER s1 OWNER TO regress_test_indirect;
178 GRANT USAGE ON FOREIGN DATA WRAPPER foo TO regress_test_indirect;
179 SET ROLE regress_test_role;
180 ALTER SERVER s1 OWNER TO regress_test_indirect;
182 DROP ROLE regress_test_indirect; -- ERROR
186 DROP SERVER nonexistent; -- ERROR
187 DROP SERVER IF EXISTS nonexistent;
189 SET ROLE regress_test_role;
190 DROP SERVER s2; -- ERROR
194 ALTER SERVER s2 OWNER TO regress_test_role;
195 SET ROLE regress_test_role;
199 CREATE USER MAPPING FOR current_user SERVER s3;
201 DROP SERVER s3; -- ERROR
202 DROP SERVER s3 CASCADE;
206 -- CREATE USER MAPPING
207 CREATE USER MAPPING FOR baz SERVER s1; -- ERROR
208 CREATE USER MAPPING FOR current_user SERVER s1; -- ERROR
209 CREATE USER MAPPING FOR current_user SERVER s4;
210 CREATE USER MAPPING FOR user SERVER s4; -- ERROR duplicate
211 CREATE USER MAPPING FOR public SERVER s4 OPTIONS (mapping 'is public');
212 CREATE USER MAPPING FOR user SERVER s8 OPTIONS (username 'test', password 'secret'); -- ERROR
213 CREATE USER MAPPING FOR user SERVER s8 OPTIONS (user 'test', password 'secret');
214 ALTER SERVER s5 OWNER TO regress_test_role;
215 ALTER SERVER s6 OWNER TO regress_test_indirect;
216 SET ROLE regress_test_role;
217 CREATE USER MAPPING FOR current_user SERVER s5;
218 CREATE USER MAPPING FOR current_user SERVER s6 OPTIONS (username 'test');
219 CREATE USER MAPPING FOR current_user SERVER s7; -- ERROR
220 CREATE USER MAPPING FOR public SERVER s8; -- ERROR
223 ALTER SERVER t1 OWNER TO regress_test_indirect;
224 SET ROLE regress_test_role;
225 CREATE USER MAPPING FOR current_user SERVER t1 OPTIONS (username 'bob', password 'boo');
226 CREATE USER MAPPING FOR public SERVER t1;
230 -- ALTER USER MAPPING
231 ALTER USER MAPPING FOR bob SERVER s4 OPTIONS (gotcha 'true'); -- ERROR
232 ALTER USER MAPPING FOR user SERVER ss4 OPTIONS (gotcha 'true'); -- ERROR
233 ALTER USER MAPPING FOR public SERVER s5 OPTIONS (gotcha 'true'); -- ERROR
234 ALTER USER MAPPING FOR current_user SERVER s8 OPTIONS (username 'test'); -- ERROR
235 ALTER USER MAPPING FOR current_user SERVER s8 OPTIONS (DROP user, SET password 'public');
236 SET ROLE regress_test_role;
237 ALTER USER MAPPING FOR current_user SERVER s5 OPTIONS (ADD modified '1');
238 ALTER USER MAPPING FOR public SERVER s4 OPTIONS (ADD modified '1'); -- ERROR
239 ALTER USER MAPPING FOR public SERVER t1 OPTIONS (ADD modified '1');
244 DROP USER MAPPING FOR bob SERVER s4; -- ERROR
245 DROP USER MAPPING FOR user SERVER ss4;
246 DROP USER MAPPING FOR public SERVER s7; -- ERROR
247 DROP USER MAPPING IF EXISTS FOR bob SERVER s4;
248 DROP USER MAPPING IF EXISTS FOR user SERVER ss4;
249 DROP USER MAPPING IF EXISTS FOR public SERVER s7;
250 CREATE USER MAPPING FOR public SERVER s8;
251 SET ROLE regress_test_role;
252 DROP USER MAPPING FOR public SERVER s8; -- ERROR
257 -- Information schema
259 SELECT * FROM information_schema.foreign_data_wrappers ORDER BY 1, 2;
260 SELECT * FROM information_schema.foreign_data_wrapper_options ORDER BY 1, 2, 3;
261 SELECT * FROM information_schema.foreign_servers ORDER BY 1, 2;
262 SELECT * FROM information_schema.foreign_server_options ORDER BY 1, 2, 3;
263 SELECT * FROM information_schema.user_mappings ORDER BY lower(authorization_identifier), 2, 3;
264 SELECT * FROM information_schema.user_mapping_options ORDER BY lower(authorization_identifier), 2, 3, 4;
265 SELECT * FROM information_schema.usage_privileges WHERE object_type LIKE 'FOREIGN%' ORDER BY 1, 2, 3, 4, 5;
266 SELECT * FROM information_schema.role_usage_grants WHERE object_type LIKE 'FOREIGN%' ORDER BY 1, 2, 3, 4, 5;
267 SET ROLE regress_test_role;
268 SELECT * FROM information_schema.user_mapping_options ORDER BY 1, 2, 3, 4;
269 SELECT * FROM information_schema.usage_privileges WHERE object_type LIKE 'FOREIGN%' ORDER BY 1, 2, 3, 4, 5;
270 SELECT * FROM information_schema.role_usage_grants WHERE object_type LIKE 'FOREIGN%' ORDER BY 1, 2, 3, 4, 5;
271 DROP USER MAPPING FOR current_user SERVER t1;
272 SET ROLE regress_test_role2;
273 SELECT * FROM information_schema.user_mapping_options ORDER BY 1, 2, 3, 4;
277 -- has_foreign_data_wrapper_privilege
278 SELECT has_foreign_data_wrapper_privilege('regress_test_role',
279 (SELECT oid FROM pg_foreign_data_wrapper WHERE fdwname='foo'), 'USAGE');
280 SELECT has_foreign_data_wrapper_privilege('regress_test_role', 'foo', 'USAGE');
281 SELECT has_foreign_data_wrapper_privilege(
282 (SELECT oid FROM pg_roles WHERE rolname='regress_test_role'),
283 (SELECT oid FROM pg_foreign_data_wrapper WHERE fdwname='foo'), 'USAGE');
284 SELECT has_foreign_data_wrapper_privilege(
285 (SELECT oid FROM pg_foreign_data_wrapper WHERE fdwname='foo'), 'USAGE');
286 SELECT has_foreign_data_wrapper_privilege(
287 (SELECT oid FROM pg_roles WHERE rolname='regress_test_role'), 'foo', 'USAGE');
288 SELECT has_foreign_data_wrapper_privilege('foo', 'USAGE');
289 GRANT USAGE ON FOREIGN DATA WRAPPER foo TO regress_test_role;
290 SELECT has_foreign_data_wrapper_privilege('regress_test_role', 'foo', 'USAGE');
292 -- has_server_privilege
293 SELECT has_server_privilege('regress_test_role',
294 (SELECT oid FROM pg_foreign_server WHERE srvname='s8'), 'USAGE');
295 SELECT has_server_privilege('regress_test_role', 's8', 'USAGE');
296 SELECT has_server_privilege(
297 (SELECT oid FROM pg_roles WHERE rolname='regress_test_role'),
298 (SELECT oid FROM pg_foreign_server WHERE srvname='s8'), 'USAGE');
299 SELECT has_server_privilege(
300 (SELECT oid FROM pg_foreign_server WHERE srvname='s8'), 'USAGE');
301 SELECT has_server_privilege(
302 (SELECT oid FROM pg_roles WHERE rolname='regress_test_role'), 's8', 'USAGE');
303 SELECT has_server_privilege('s8', 'USAGE');
304 GRANT USAGE ON FOREIGN SERVER s8 TO regress_test_role;
305 SELECT has_server_privilege('regress_test_role', 's8', 'USAGE');
306 REVOKE USAGE ON FOREIGN SERVER s8 FROM regress_test_role;
308 GRANT USAGE ON FOREIGN SERVER s4 TO regress_test_role;
309 DROP USER MAPPING FOR public SERVER s4;
310 ALTER SERVER s6 OPTIONS (DROP host, DROP dbname);
311 ALTER USER MAPPING FOR regress_test_role SERVER s6 OPTIONS (DROP username);
312 ALTER FOREIGN DATA WRAPPER foo VALIDATOR postgresql_fdw_validator;
315 SET ROLE unprivileged_role;
316 CREATE FOREIGN DATA WRAPPER foobar; -- ERROR
317 ALTER FOREIGN DATA WRAPPER foo OPTIONS (gotcha 'true'); -- ERROR
318 ALTER FOREIGN DATA WRAPPER foo OWNER TO unprivileged_role; -- ERROR
319 DROP FOREIGN DATA WRAPPER foo; -- ERROR
320 GRANT USAGE ON FOREIGN DATA WRAPPER foo TO regress_test_role; -- ERROR
321 CREATE SERVER s9 FOREIGN DATA WRAPPER foo; -- ERROR
322 ALTER SERVER s4 VERSION '0.5'; -- ERROR
323 ALTER SERVER s4 OWNER TO unprivileged_role; -- ERROR
324 DROP SERVER s4; -- ERROR
325 GRANT USAGE ON FOREIGN SERVER s4 TO regress_test_role; -- ERROR
326 CREATE USER MAPPING FOR public SERVER s4; -- ERROR
327 ALTER USER MAPPING FOR regress_test_role SERVER s6 OPTIONS (gotcha 'true'); -- ERROR
328 DROP USER MAPPING FOR regress_test_role SERVER s6; -- ERROR
331 GRANT USAGE ON FOREIGN DATA WRAPPER postgresql TO unprivileged_role;
332 GRANT USAGE ON FOREIGN DATA WRAPPER foo TO unprivileged_role WITH GRANT OPTION;
333 SET ROLE unprivileged_role;
334 CREATE FOREIGN DATA WRAPPER foobar; -- ERROR
335 ALTER FOREIGN DATA WRAPPER foo OPTIONS (gotcha 'true'); -- ERROR
336 DROP FOREIGN DATA WRAPPER foo; -- ERROR
337 GRANT USAGE ON FOREIGN DATA WRAPPER postgresql TO regress_test_role; -- WARNING
338 GRANT USAGE ON FOREIGN DATA WRAPPER foo TO regress_test_role;
339 CREATE SERVER s9 FOREIGN DATA WRAPPER postgresql;
340 ALTER SERVER s6 VERSION '0.5'; -- ERROR
341 DROP SERVER s6; -- ERROR
342 GRANT USAGE ON FOREIGN SERVER s6 TO regress_test_role; -- ERROR
343 GRANT USAGE ON FOREIGN SERVER s9 TO regress_test_role;
344 CREATE USER MAPPING FOR public SERVER s6; -- ERROR
345 CREATE USER MAPPING FOR public SERVER s9;
346 ALTER USER MAPPING FOR regress_test_role SERVER s6 OPTIONS (gotcha 'true'); -- ERROR
347 DROP USER MAPPING FOR regress_test_role SERVER s6; -- ERROR
350 REVOKE USAGE ON FOREIGN DATA WRAPPER foo FROM unprivileged_role; -- ERROR
351 REVOKE USAGE ON FOREIGN DATA WRAPPER foo FROM unprivileged_role CASCADE;
352 SET ROLE unprivileged_role;
353 GRANT USAGE ON FOREIGN DATA WRAPPER foo TO regress_test_role; -- ERROR
354 CREATE SERVER s10 FOREIGN DATA WRAPPER foo; -- ERROR
355 ALTER SERVER s9 VERSION '1.1';
356 GRANT USAGE ON FOREIGN SERVER s9 TO regress_test_role;
357 CREATE USER MAPPING FOR current_user SERVER s9;
358 DROP SERVER s9 CASCADE;
360 CREATE SERVER s9 FOREIGN DATA WRAPPER foo;
361 GRANT USAGE ON FOREIGN SERVER s9 TO unprivileged_role;
362 SET ROLE unprivileged_role;
363 ALTER SERVER s9 VERSION '1.2'; -- ERROR
364 GRANT USAGE ON FOREIGN SERVER s9 TO regress_test_role; -- WARNING
365 CREATE USER MAPPING FOR current_user SERVER s9;
366 DROP SERVER s9 CASCADE; -- ERROR
370 DROP ROLE regress_test_role; -- ERROR
371 DROP SERVER s5 CASCADE;
372 DROP SERVER t1 CASCADE;
374 DROP USER MAPPING FOR regress_test_role SERVER s6;
375 DROP FOREIGN DATA WRAPPER foo CASCADE;
376 DROP SERVER s8 CASCADE;
377 DROP ROLE regress_test_indirect;
378 DROP ROLE regress_test_role;
379 DROP ROLE unprivileged_role; -- ERROR
380 REVOKE ALL ON FOREIGN DATA WRAPPER postgresql FROM unprivileged_role;
381 DROP ROLE unprivileged_role;
382 DROP ROLE regress_test_role2;
383 DROP FOREIGN DATA WRAPPER postgresql CASCADE;
384 DROP FOREIGN DATA WRAPPER dummy CASCADE;
386 DROP ROLE foreign_data_user;
388 -- At this point we should have no wrappers, no servers, and no mappings.
389 SELECT fdwname, fdwvalidator, fdwoptions FROM pg_foreign_data_wrapper;
390 SELECT srvname, srvoptions FROM pg_foreign_server;
391 SELECT * FROM pg_user_mapping;