FDW and RLS

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

FDW and RLS

Ted Toth
Will RLS be applied to data being retrieved via a FDW?

Ted
Reply | Threaded
Open this post in threaded view
|

Re: FDW and RLS

Laurenz Albe
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



Reply | Threaded
Open this post in threaded view
|

Re: FDW and RLS

Charles Clavadetscher
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:

[hidden email].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))

[hidden email].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

[hidden email].5432=# SELECT CURRENT_USER; SELECT * FROM
public.test_fdw_rls;

[hidden email].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)

[hidden email].5432=# set role fdwsync ;
SET
[hidden email].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:

[hidden email]-archivedb.5432=> \deu+
                         List of user mappings
    Server   | User name |                 FDW options
------------+-----------+---------------------------------------------
  kofdb_prod | kofadmin  | (password 'mysecret', "user" 'fdwsync')

[hidden email]-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