*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.