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:
charles@kofdb.archivedb.5432=# \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))
kofadmin@kofdb.archivedb.5432=> \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
charles@kofdb.archivedb.5432=# SELECT CURRENT_USER; SELECT * FROM
public.test_fdw_rls;
charles@kofdb.archivedb.5432=# 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)
charles@kofdb.archivedb.5432=# set role fdwsync ;
SET
charles@kofdb.archivedb.5432=> 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:
kofadmin@kofdb.t-archivedb.5432=> \deu+
List of user mappings
Server | User name | FDW options
------------+-----------+---------------------------------------------
kofdb_prod | kofadmin | (password 'mysecret', "user" 'fdwsync')
kofadmin@kofdb.t-archivedb.5432=> 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
