Обсуждение: Row level security insert policy does not validate update new values/content?
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 |
+------+------------+--------+
| 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.
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 <postgres.rocks@gmail.com> 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.