diff options
Diffstat (limited to 'contrib/postgres_fdw/sql/postgres_fdw.sql')
| -rw-r--r-- | contrib/postgres_fdw/sql/postgres_fdw.sql | 23 |
1 files changed, 23 insertions, 0 deletions
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; |