Re: FDW and RLS - Mailing list pgsql-general

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