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