Re: Row level security insert policy does not validate update new values/content?

[David G. Johnston]

On Tue, May 17, 2022 at 4:57 AM alias  wrote:

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

update = delete + insert is not universally true.  Its main point is that
due to MVCC when you update something the old tuple is deleted (but remains
around waiting to be vacuumed) and a new tuple is created.  It is also an
implementation detail - while the usage of INSERT/UPDATE/DELETE in CREATE
POLICY are semantically significant and mean to convey the user writing out
those specific commands (or subcommand in the case of the ON CONFLICT
UPDATE subclause of INSERT).

In this case it gives the policy writer flexibility, at the cost of some
duplication.  One useful thing to do is write a function that accepts
either columns, or the table's data type, as an input argument and put the
logic in there.  Then just call the function in the policy with check
and/or using clauses.

David J.

Row level security insert policy does not validate update new values/content?

[alias]

*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', 5), ('Jake', '12-01-2009', 7),
> ('Jill', '12-21-2009', 85000.75), ('Jonn', '12-27-2009', 12.5),
> ('June', '01-01-2010', 10.25), ('Joey', '01-01-2010', NULL), ('Jack',
> '01-01-2010', 12), ('Jane', '01-01-2010', 11.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 > 10 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 > 10 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.