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

On Tue, May 17, 2022 at 4:57 AM alias <postgres(dot)rocks(at)gmail(dot)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.