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

From: alias <postgres(dot)rocks(at)gmail(dot)com>
To: pgsql-general <pgsql-general(at)lists(dot)postgresql(dot)org>
Subject: Row level security insert policy does not validate update new values/content?
Date: 2022-05-17 11:56:39
Message-ID: CAJA4AWSYNJNHsCn_eMTZ-2RoyTh8yFyV0FdOFWxA5WqXs7Y6QA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message huangning290@yahoo.com 2022-05-17 12:24:18 Parallel not working
Previous Message Bryn Llewellyn 2022-05-17 05:21:36 Re: Restricting user to see schema structure