FDW and RLS

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
--
Cybertec | https://www.cybertec-postgresql.com

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