CODEWITHSUNDEEP
postgresql
Tomas
Tomas

Reputation: 61

Postgres: Row Level Security doesn't work with views

When accessing a Row Level Security protected table via a view, the table is accessed with the owner of the view, making RLS useless.

What I want is to have RLS for user connected with the user "test_user". This includes tables and views accessing the table. The policy uses a parameter that is set for the transaction. I have seen that when test_user is not the owner of the view, RLS doesn't work. If I change the owner of the view to test_user, it works. I think I understand that when using a view, the table is accessed with the owner of the view and not the user accessing the view.

I want the user test_admin to be owner of all tables and views. And I would like to avoid to force row level security, because for convenience I would like test_admin to be able to "by-pass" RLS (when running scripts etc).

I tried to force row level security and then add another permissive policy, saying that test_admin is allowed to access the row, but this means that the test_user will also bypass RLS when accessing views.

Is it fact in Postgres that RLS is bypassed when using views, because the user will change to the owner of the view?

--The setup. Connected as the role/user "test_admin".

    create table test_rls1
    (
    id INTEGER NOT NULL, 
    name TEXT NOT NULL,
    department_id INTEGER NOT NULL,
    PRIMARY KEY (id)
    );

    INSERT INTO test_rls1 VALUES (1, 'Peter', 1);
    INSERT INTO test_rls1 VALUES (2, 'Lisa', 2);

    CREATE POLICY test_rls1_policy ON test_rls1
     AS PERMISSIVE
     FOR SELECT
     TO PUBLIC
     USING (
             department_id = (COALESCE(nullif(current_setting('my.depid'::text, true),''),'-1'))::integer
     );

ALTER TABLE test_rls1 ENABLE ROW LEVEL SECURITY;  

CREATE VIEW test_rls1_view AS
SELECT id, name, department_id FROM test_rls1;

-- test_admin is the owner of the table and the view.
-- I have not executed "force row level security", so RLS will
-- not have effect on the test_admin user.

-- Connected as another role/user: "test_user":
-- select from the table

begin;
SELECT set_config('my.depid', '1', true);
select * from toed_rls1;
rollback;

-- This returns one row: Peter, department_id = 1

-- select from the view

    begin;
    SELECT set_config('my.depid', '1', true);
    select * from toed_rls1_view;
    rollback;

-- This returns all rows!! No RLS in effect.

Upvotes: 6

Views: 1809

Answers (2)

ethicnology
ethicnology

Reputation: 606

What you are searching for is the SECURITY INVOKER that check for the user permissions instead of the view owner, it was introduced in PostgreSQL v15.

"This makes views and row-level security interact nicely and is the appropriate setting in most use cases."

ALTER VIEW your_view SET (security_invoker = on);

Upvotes: 1

Laurenz Albe
Laurenz Albe

Reputation: 247625

You have the choice: force row level security or use a different owner for table and view.

Upvotes: 1

Related Questions