I'm trying to set up row level security on some tables. By now I had no issues with INSERT and SELECT, now trying to handle UPDATE.
I tried many things but really got stuck when I defined the policy with WITH CHECK (false) and I have the ability to execute updates on that table with rls user despite the policy defined.
Why is that happening? I expected all updates on that table to fail because WITH CHECK is a boolean validator.
Here is my code:
CREATE TABLE t (
"id" SERIAL PRIMARY KEY NOT NULL,
"name" varchar (50) NOT NULL
);
create role rls_user NOINHERIT;
GRANT USAGE ON SCHEMA public TO rls_user;
GRANT SELECT, UPDATE, INSERT ON ALL TABLES IN SCHEMA public TO rls_user;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO rls_user;
alter table t enable row level security;
CREATE POLICY t_update ON t
FOR UPDATE WITH CHECK (false);
----> CREATE POLICY t_update ON t
----> FOR UPDATE USING (false); ---> also doesn't work
set role rls_user;
UPDATE t
SET name = 'blabla'
WHERE "id" = 1;
---> expected: UPDATE FAILED
---> result:
---> UPDATE 0
---> Query returned successfully in 73 msec.
Your policy makes the table appear empty for the rls_user, because it has no USING clause.
Therefore, the UPDATE will never modify a row (UPDATE 0), and since no row is modified, no row has to pass the CHECK condition and no error is thrown.
Note that you granted the user UPDATE permissions on the table, so it is allowed to run all updates on the table, as long as the modified rows pass the CHECK condition.