diff options
Diffstat (limited to 'contrib/postgres_fdw')
| -rw-r--r-- | contrib/postgres_fdw/expected/postgres_fdw.out | 42 | ||||
| -rw-r--r-- | contrib/postgres_fdw/sql/postgres_fdw.sql | 23 |
2 files changed, 65 insertions, 0 deletions
diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out index c8c4614b547..6513b3fff23 100644 --- a/contrib/postgres_fdw/expected/postgres_fdw.out +++ b/contrib/postgres_fdw/expected/postgres_fdw.out @@ -2689,6 +2689,48 @@ SELECT t1.c1, t2.c2 FROM v4 t1 LEFT JOIN ft5 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c (10 rows) ALTER VIEW v4 OWNER TO regress_view_owner; +-- ==================================================================== +-- Check that userid to use when querying the remote table is correctly +-- propagated into foreign rels present in subqueries under an UNION ALL +-- ==================================================================== +CREATE ROLE regress_view_owner_another; +ALTER VIEW v4 OWNER TO regress_view_owner_another; +GRANT SELECT ON ft4 TO regress_view_owner_another; +ALTER FOREIGN TABLE ft4 OPTIONS (ADD use_remote_estimate 'true'); +-- The following should query the remote backing table of ft4 as user +-- regress_view_owner_another, the view owner, though it fails as expected +-- due to the lack of a user mapping for that user. +EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM v4; +ERROR: user mapping not found for "regress_view_owner_another" +-- Likewise, but with the query under an UNION ALL +EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM (SELECT * FROM v4 UNION ALL SELECT * FROM v4); +ERROR: user mapping not found for "regress_view_owner_another" +-- Should not get that error once a user mapping is created +CREATE USER MAPPING FOR regress_view_owner_another SERVER loopback OPTIONS (password_required 'false'); +EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM v4; + QUERY PLAN +-------------------------------------------------- + Foreign Scan on public.ft4 + Output: ft4.c1, ft4.c2, ft4.c3 + Remote SQL: SELECT c1, c2, c3 FROM "S 1"."T 3" +(3 rows) + +EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM (SELECT * FROM v4 UNION ALL SELECT * FROM v4); + QUERY PLAN +-------------------------------------------------------- + Append + -> Foreign Scan on public.ft4 + Output: ft4.c1, ft4.c2, ft4.c3 + Remote SQL: SELECT c1, c2, c3 FROM "S 1"."T 3" + -> Foreign Scan on public.ft4 ft4_1 + Output: ft4_1.c1, ft4_1.c2, ft4_1.c3 + Remote SQL: SELECT c1, c2, c3 FROM "S 1"."T 3" +(7 rows) + +DROP USER MAPPING FOR regress_view_owner_another SERVER loopback; +DROP OWNED BY regress_view_owner_another; +DROP ROLE regress_view_owner_another; +ALTER FOREIGN TABLE ft4 OPTIONS (SET use_remote_estimate 'false'); -- cleanup DROP OWNED BY regress_view_owner; DROP ROLE regress_view_owner; diff --git a/contrib/postgres_fdw/sql/postgres_fdw.sql b/contrib/postgres_fdw/sql/postgres_fdw.sql index b54903ad8fa..e94abb7da81 100644 --- a/contrib/postgres_fdw/sql/postgres_fdw.sql +++ b/contrib/postgres_fdw/sql/postgres_fdw.sql @@ -714,6 +714,29 @@ SELECT t1.c1, t2.c2 FROM v4 t1 LEFT JOIN ft5 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c SELECT t1.c1, t2.c2 FROM v4 t1 LEFT JOIN ft5 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c1, t2.c1 OFFSET 10 LIMIT 10; ALTER VIEW v4 OWNER TO regress_view_owner; +-- ==================================================================== +-- Check that userid to use when querying the remote table is correctly +-- propagated into foreign rels present in subqueries under an UNION ALL +-- ==================================================================== +CREATE ROLE regress_view_owner_another; +ALTER VIEW v4 OWNER TO regress_view_owner_another; +GRANT SELECT ON ft4 TO regress_view_owner_another; +ALTER FOREIGN TABLE ft4 OPTIONS (ADD use_remote_estimate 'true'); +-- The following should query the remote backing table of ft4 as user +-- regress_view_owner_another, the view owner, though it fails as expected +-- due to the lack of a user mapping for that user. +EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM v4; +-- Likewise, but with the query under an UNION ALL +EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM (SELECT * FROM v4 UNION ALL SELECT * FROM v4); +-- Should not get that error once a user mapping is created +CREATE USER MAPPING FOR regress_view_owner_another SERVER loopback OPTIONS (password_required 'false'); +EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM v4; +EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM (SELECT * FROM v4 UNION ALL SELECT * FROM v4); +DROP USER MAPPING FOR regress_view_owner_another SERVER loopback; +DROP OWNED BY regress_view_owner_another; +DROP ROLE regress_view_owner_another; +ALTER FOREIGN TABLE ft4 OPTIONS (SET use_remote_estimate 'false'); + -- cleanup DROP OWNED BY regress_view_owner; DROP ROLE regress_view_owner; |