Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit5d4171d

Browse files
committed
Don't require a user mapping for FDWs to work.
Commitfbe5a3f accidentally changedthis behavior; put things back the way they were, and add someregression tests.Report by Andres Freund; patch by Ashutosh Bapat, with a bit ofkibitzing by me.
1 parent868628e commit5d4171d

File tree

8 files changed

+125
-22
lines changed

8 files changed

+125
-22
lines changed

‎contrib/file_fdw/input/file_fdw.source

Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -173,6 +173,9 @@ SET ROLE file_fdw_user;
173173
\t on
174174
EXPLAIN (VERBOSE, COSTS FALSE) SELECT * FROM agg_text WHERE a > 0;
175175
\t off
176+
-- file FDW allows foreign tables to be accessed without user mapping
177+
DROP USER MAPPING FOR file_fdw_user SERVER file_server;
178+
SELECT * FROM agg_text ORDER BY a;
176179

177180
-- privilege tests for object
178181
SET ROLE file_fdw_superuser;

‎contrib/file_fdw/output/file_fdw.source

Lines changed: 12 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -322,6 +322,17 @@ EXPLAIN (VERBOSE, COSTS FALSE) SELECT * FROM agg_text WHERE a > 0;
322322
Foreign File: @abs_srcdir@/data/agg.data
323323

324324
\t off
325+
-- file FDW allows foreign tables to be accessed without user mapping
326+
DROP USER MAPPING FOR file_fdw_user SERVER file_server;
327+
SELECT * FROM agg_text ORDER BY a;
328+
a | b
329+
-----+---------
330+
0 | 0.09561
331+
42 | 324.78
332+
56 | 7.8
333+
100 | 99.097
334+
(4 rows)
335+
325336
-- privilege tests for object
326337
SET ROLE file_fdw_superuser;
327338
ALTER FOREIGN TABLE agg_text OWNER TO file_fdw_user;
@@ -333,9 +344,8 @@ SET ROLE file_fdw_superuser;
333344
-- cleanup
334345
RESET ROLE;
335346
DROP EXTENSION file_fdw CASCADE;
336-
NOTICE: drop cascades to8 other objects
347+
NOTICE: drop cascades to7 other objects
337348
DETAIL: drop cascades to server file_server
338-
drop cascades to user mapping for file_fdw_user on server file_server
339349
drop cascades to user mapping for file_fdw_superuser on server file_server
340350
drop cascades to user mapping for no_priv_user on server file_server
341351
drop cascades to foreign table agg_text

‎contrib/postgres_fdw/expected/postgres_fdw.out

Lines changed: 56 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -1958,13 +1958,30 @@ EXECUTE join_stmt;
19581958

19591959
-- change the session user to view_owner and execute the statement. Because of
19601960
-- change in session user, the plan should get invalidated and created again.
1961-
--While creating the plan, it should throw errorsincethere is no user mapping
1962-
--available for view_owner.
1961+
--The join will not be pushed downsincethe joining relations do not have a
1962+
--valid user mapping.
19631963
SET SESSION ROLE view_owner;
19641964
EXPLAIN (COSTS OFF, VERBOSE) EXECUTE join_stmt;
1965-
ERROR: user mapping not found for "view_owner"
1966-
EXECUTE join_stmt;
1967-
ERROR: user mapping not found for "view_owner"
1965+
QUERY PLAN
1966+
------------------------------------------------------------------
1967+
Limit
1968+
Output: t1.c1, t2.c1
1969+
-> Sort
1970+
Output: t1.c1, t2.c1
1971+
Sort Key: t1.c1, t2.c1
1972+
-> Hash Left Join
1973+
Output: t1.c1, t2.c1
1974+
Hash Cond: (t1.c1 = t2.c1)
1975+
-> Foreign Scan on public.ft4 t1
1976+
Output: t1.c1, t1.c2, t1.c3
1977+
Remote SQL: SELECT c1 FROM "S 1"."T 3"
1978+
-> Hash
1979+
Output: t2.c1
1980+
-> Foreign Scan on public.ft5 t2
1981+
Output: t2.c1
1982+
Remote SQL: SELECT c1 FROM "S 1"."T 4"
1983+
(16 rows)
1984+
19681985
RESET ROLE;
19691986
DEALLOCATE join_stmt;
19701987
CREATE VIEW v_ft5 AS SELECT * FROM ft5;
@@ -2021,6 +2038,40 @@ EXECUTE join_stmt;
20212038
----+----
20222039
(0 rows)
20232040

2041+
-- If a sub-join can't be pushed down, upper level join shouldn't be either.
2042+
EXPLAIN (COSTS false, VERBOSE)
2043+
SELECT t1.c1, t2.c1 FROM (ft5 t1 JOIN v_ft5 t2 ON (t1.c1 = t2.c1)) left join (ft5 t3 JOIN v_ft5 t4 ON (t3.c1 = t4.c1)) ON (t1.c1 = t3.c1);
2044+
QUERY PLAN
2045+
------------------------------------------------------------------
2046+
Hash Join
2047+
Output: t1.c1, ft5.c1
2048+
Hash Cond: (t1.c1 = ft5.c1)
2049+
-> Hash Right Join
2050+
Output: t1.c1
2051+
Hash Cond: (t3.c1 = t1.c1)
2052+
-> Hash Join
2053+
Output: t3.c1
2054+
Hash Cond: (t3.c1 = ft5_1.c1)
2055+
-> Foreign Scan on public.ft5 t3
2056+
Output: t3.c1, t3.c2, t3.c3
2057+
Remote SQL: SELECT c1 FROM "S 1"."T 4"
2058+
-> Hash
2059+
Output: ft5_1.c1
2060+
-> Foreign Scan on public.ft5 ft5_1
2061+
Output: ft5_1.c1
2062+
Remote SQL: SELECT c1 FROM "S 1"."T 4"
2063+
-> Hash
2064+
Output: t1.c1
2065+
-> Foreign Scan on public.ft5 t1
2066+
Output: t1.c1
2067+
Remote SQL: SELECT c1 FROM "S 1"."T 4"
2068+
-> Hash
2069+
Output: ft5.c1
2070+
-> Foreign Scan on public.ft5
2071+
Output: ft5.c1
2072+
Remote SQL: SELECT c1 FROM "S 1"."T 4"
2073+
(27 rows)
2074+
20242075
-- recreate the dropped user mapping for further tests
20252076
CREATE USER MAPPING FOR CURRENT_USER SERVER loopback;
20262077
DROP USER MAPPING FOR PUBLIC SERVER loopback;

‎contrib/postgres_fdw/postgres_fdw.c

Lines changed: 10 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -3910,6 +3910,16 @@ foreign_join_ok(PlannerInfo *root, RelOptInfo *joinrel, JoinType jointype,
39103910
List*joinclauses;
39113911
List*otherclauses;
39123912

3913+
/*
3914+
* Core code may call GetForeignJoinPaths hook even when the join
3915+
* relation doesn't have a valid user mapping associated with it. See
3916+
* build_join_rel() for details. We can't push down such join, since
3917+
* there doesn't exist a user mapping which can be used to connect to the
3918+
* foreign server.
3919+
*/
3920+
if (!OidIsValid(joinrel->umid))
3921+
return false;
3922+
39133923
/*
39143924
* We support pushing down INNER, LEFT, RIGHT and FULL OUTER joins.
39153925
* Constructing queries representing SEMI and ANTI joins is hard, hence

‎contrib/postgres_fdw/sql/postgres_fdw.sql

Lines changed: 6 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -478,11 +478,10 @@ EXPLAIN (COSTS OFF, VERBOSE) EXECUTE join_stmt;
478478
EXECUTE join_stmt;
479479
-- change the session user to view_owner and execute the statement. Because of
480480
-- change in session user, the plan should get invalidated and created again.
481-
--While creating the plan, it should throw errorsincethere is no user mapping
482-
--available for view_owner.
481+
--The join will not be pushed downsincethe joining relations do not have a
482+
--valid user mapping.
483483
SET SESSION ROLE view_owner;
484484
EXPLAIN (COSTS OFF, VERBOSE) EXECUTE join_stmt;
485-
EXECUTE join_stmt;
486485
RESET ROLE;
487486
DEALLOCATE join_stmt;
488487

@@ -506,6 +505,10 @@ CREATE USER MAPPING FOR view_owner SERVER loopback;
506505
EXPLAIN (COSTS false, VERBOSE) EXECUTE join_stmt;
507506
EXECUTE join_stmt;
508507

508+
-- If a sub-join can't be pushed down, upper level join shouldn't be either.
509+
EXPLAIN (COSTS false, VERBOSE)
510+
SELECTt1.c1,t2.c1FROM (ft5 t1JOIN v_ft5 t2ON (t1.c1=t2.c1))left join (ft5 t3JOIN v_ft5 t4ON (t3.c1=t4.c1))ON (t1.c1=t3.c1);
511+
509512
-- recreate the dropped user mapping for further tests
510513
CREATEUSERMAPPING FORCURRENT_USER SERVER loopback;
511514
DROPUSER MAPPING FOR PUBLIC SERVER loopback;

‎src/backend/foreign/foreign.c

Lines changed: 27 additions & 9 deletions
Original file line numberDiff line numberDiff line change
@@ -31,7 +31,7 @@
3131
externDatumpg_options_to_table(PG_FUNCTION_ARGS);
3232
externDatumpostgresql_fdw_validator(PG_FUNCTION_ARGS);
3333

34-
staticHeapTuplefind_user_mapping(Oiduserid,Oidserverid);
34+
staticHeapTuplefind_user_mapping(Oiduserid,Oidserverid,boolmissing_ok);
3535

3636
/*
3737
* GetForeignDataWrapper -look up the foreign-data wrapper by OID.
@@ -223,7 +223,7 @@ GetUserMapping(Oid userid, Oid serverid)
223223
boolisnull;
224224
UserMapping*um;
225225

226-
tp=find_user_mapping(userid,serverid);
226+
tp=find_user_mapping(userid,serverid, false);
227227

228228
um= (UserMapping*)palloc(sizeof(UserMapping));
229229
um->umid=HeapTupleGetOid(tp);
@@ -250,14 +250,23 @@ GetUserMapping(Oid userid, Oid serverid)
250250
*
251251
* If no mapping is found for the supplied user, we also look for
252252
* PUBLIC mappings (userid == InvalidOid).
253+
*
254+
* If missing_ok is true, the function returns InvalidOid when it does not find
255+
* required user mapping. Otherwise, find_user_mapping() throws error if it
256+
* does not find required user mapping.
253257
*/
254258
Oid
255-
GetUserMappingId(Oiduserid,Oidserverid)
259+
GetUserMappingId(Oiduserid,Oidserverid,boolmissing_ok)
256260
{
257261
HeapTupletp;
258262
Oidumid;
259263

260-
tp=find_user_mapping(userid,serverid);
264+
tp=find_user_mapping(userid,serverid,missing_ok);
265+
266+
Assert(missing_ok||tp);
267+
268+
if (!tp&&missing_ok)
269+
returnInvalidOid;
261270

262271
/* Extract the Oid */
263272
umid=HeapTupleGetOid(tp);
@@ -273,9 +282,13 @@ GetUserMappingId(Oid userid, Oid serverid)
273282
*
274283
* If no mapping is found for the supplied user, we also look for
275284
* PUBLIC mappings (userid == InvalidOid).
285+
*
286+
* If missing_ok is true, the function returns NULL, if it does not find
287+
* the required user mapping. Otherwise, it throws error if it does not
288+
* find the required user mapping.
276289
*/
277290
staticHeapTuple
278-
find_user_mapping(Oiduserid,Oidserverid)
291+
find_user_mapping(Oiduserid,Oidserverid,boolmissing_ok)
279292
{
280293
HeapTupletp;
281294

@@ -292,10 +305,15 @@ find_user_mapping(Oid userid, Oid serverid)
292305
ObjectIdGetDatum(serverid));
293306

294307
if (!HeapTupleIsValid(tp))
295-
ereport(ERROR,
296-
(errcode(ERRCODE_UNDEFINED_OBJECT),
297-
errmsg("user mapping not found for \"%s\"",
298-
MappingUserName(userid))));
308+
{
309+
if (missing_ok)
310+
returnNULL;
311+
else
312+
ereport(ERROR,
313+
(errcode(ERRCODE_UNDEFINED_OBJECT),
314+
errmsg("user mapping not found for \"%s\"",
315+
MappingUserName(userid))));
316+
}
299317

300318
returntp;
301319
}

‎src/backend/optimizer/util/relnode.c

Lines changed: 10 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -180,11 +180,15 @@ build_simple_rel(PlannerInfo *root, int relid, RelOptKind reloptkind)
180180
* ensure that it gets invalidated in the case of a user OID change.
181181
* See RevalidateCachedQuery and more generally the hasForeignJoin
182182
* flags in PlannerGlobal and PlannedStmt.
183+
*
184+
* It's possible, and not necessarily an error, for rel->umid to be
185+
* InvalidOid even though rel->serverid is set. That just means there
186+
* is a server with no user mapping.
183187
*/
184188
Oiduserid;
185189

186190
userid=OidIsValid(rte->checkAsUser) ?rte->checkAsUser :GetUserId();
187-
rel->umid=GetUserMappingId(userid,rel->serverid);
191+
rel->umid=GetUserMappingId(userid,rel->serverid, true);
188192
}
189193
else
190194
rel->umid=InvalidOid;
@@ -435,12 +439,16 @@ build_join_rel(PlannerInfo *root,
435439
*
436440
* Otherwise those fields are left invalid, so FDW API will not be called
437441
* for the join relation.
442+
*
443+
* For FDWs like file_fdw, which ignore user mapping, the user mapping id
444+
* associated with the joining relation may be invalid. A valid serverid
445+
* distinguishes between a pushed down join with no user mapping and
446+
* a join which can not be pushed down because of user mapping mismatch.
438447
*/
439448
if (OidIsValid(outer_rel->serverid)&&
440449
inner_rel->serverid==outer_rel->serverid&&
441450
inner_rel->umid==outer_rel->umid)
442451
{
443-
Assert(OidIsValid(outer_rel->umid));
444452
joinrel->serverid=outer_rel->serverid;
445453
joinrel->umid=outer_rel->umid;
446454
joinrel->fdwroutine=outer_rel->fdwroutine;

‎src/include/foreign/foreign.h

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -72,7 +72,7 @@ typedef struct ForeignTable
7272
externForeignServer*GetForeignServer(Oidserverid);
7373
externForeignServer*GetForeignServerByName(constchar*name,boolmissing_ok);
7474
externUserMapping*GetUserMapping(Oiduserid,Oidserverid);
75-
externOidGetUserMappingId(Oiduserid,Oidserverid);
75+
externOidGetUserMappingId(Oiduserid,Oidserverid,boolmissing_ok);
7676
externUserMapping*GetUserMappingById(Oidumid);
7777
externForeignDataWrapper*GetForeignDataWrapper(Oidfdwid);
7878
externForeignDataWrapper*GetForeignDataWrapperByName(constchar*name,

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp