Hi,
original query
BEGIN;
RESET session AUTHORIZATION;
SET search_path = test;
SET session AUTHORIZATION alice;
CREATE TABLE emp (
name text,
paydate date,
income numeric
);
GRANT ALL ON TABLE emp TO public;
INSERT INTO emp
VALUES ('John', '12-01-2009', 50000), ('Jake', '12-01-2009', 70000), ('Jill', '12-21-2009', 85000.75), ('Jonn', '12-27-2009', 120000.5), ('June', '01-01-2010', 100000.25), ('Joey', '01-01-2010', NULL), ('Jack', '01-01-2010', 120000), ('Jane', '01-01-2010', 110000.75), ('Jean', '01-01-2010', NULL), ('Joon', '01-01-2010', NULL);
ALTER TABLE emp ENABLE ROW LEVEL SECURITY;
CREATE POLICY test_special_date ON emp
FOR INSERT TO public
WITH CHECK ((income > 100000 AND paydate = '2010-01-01'::date)
OR (income IS NULL AND paydate = '2010-01-01'::date)
OR (paydate != '2010-01-01'::date));
CREATE POLICY emp_sel ON emp
FOR SELECT TO public
USING (TRUE);
CREATE POLICY emp_del ON emp
FOR DELETE TO public
USING (TRUE);
-- create policy emp_upd on emp for update to public using (true);
CREATE POLICY test_special_date_upd ON emp
FOR UPDATE TO public
WITH CHECK ((income > 100000 AND paydate = '2010-01-01'::date)
OR (income IS NULL AND paydate = '2010-01-01'::date)
OR (paydate != '2010-01-01'::date));
COMMIT;
then
use user sessions that cannot bypass row level security to execute the following command return 0 rows. (that's what i intended).
update emp set income = 11 where name = 'Jane' returning *;
However:
comment out policy test_special_date_upd, uncomment out policy emp_upd , the following query will return 1 row.
update emp set income = 11 where name = 'Jane' returning *;
/*
+------+------------+--------+
| name | paydate | income |
+------+------------+--------+
| Jane | 2010-01-01 | 11 |
+------+------------+--------+
*/
My thought process:
update = delete + insert.
so create policy emp_upd on emp for update to public using (true); should be ok for updating every row, let insert policy handle new row.
since there is only one check_expression, also no need to worry about permissive/restrictive.
but it seems, I need to create the same policy as insert operation to update operation to validate the new content/row of update operation.