Hello
On 2020-05-25 15:50, Laurenz Albe wrote:
On Fri, 2020-05-22 at 08:02 -0500, Ted Toth wrote:
Will RLS be applied to data being retrieved via a FDW?
ALTER FOREIGN TABLE rp_2019 ENABLE ROW LEVEL SECURITY;
ERROR: "rp_2019" is not a table
Doesn't look good.
Yours,
Laurenz Albe
Actually it does work if you set the policy on the source table and
access it using the user defined in the user mappings on the foreign
table on the remote server.
Server 1:
[email protected]=# \d public.test_fdw_rls
Table "public.test_fdw_rls"
Column | Type | Collation | Nullable | Default
----------+---------+-----------+----------+---------
id | integer | | |
content | text | | |
username | text | | |
Policies:
POLICY "kofadmin_select" FOR SELECT
TO kofadmin
USING ((username = ("current_user"())::text))
[email protected]=> \dp public.test_fdw_rls
Access privileges
Schema | Name | Type | Access privileges | Column
privileges | Policies
--------+--------------+-------+-------------------------+-------------------+----------------------------------------------
public | test_fdw_rls | table | charles=arwdDxt/charles+|
| kofadmin_select (r): +
| | | kofadmin=arwd/charles |
| (u): (username = ("current_user"())::text)+
| | | |
| to: kofadmin
[email protected]=# SELECT CURRENT_USER; SELECT * FROM
public.test_fdw_rls;
[email protected]=# SELECT CURRENT_USER; SELECT * FROM
public.test_fdw_rls;
current_user
--------------
charles
(1 row)
id | content | username
----+----------------------------------+----------
1 | Text for charles | charles
1 | Access from fdw via user fdwsync | fdwsync
(2 rows)
[email protected]=# set role fdwsync ;
SET
[email protected]=> SELECT CURRENT_USER; SELECT * FROM
public.test_fdw_rls;
current_user
--------------
fdwsync
(1 row)
id | content | username
----+----------------------------------+----------
1 | Access from fdw via user fdwsync | fdwsync
(1 row)
On the server accessing the table via FDW:
[email protected]=> \deu+
List of user mappings
Server | User name | FDW options
------------+-----------+---------------------------------------------
kofdb_prod | kofadmin | (password 'mysecret', "user" 'fdwsync')
[email protected]=> SELECT CURRENT_USER; SELECT * FROM
public.test_fdw_rls ;
current_user
--------------
kofadmin
(1 row)
id | content | username
----+----------------------------------+----------
1 | Access from fdw via user fdwsync | fdwsync
(1 row)
Regards
Charles
