How to compare between old and new values of a row in postgres policy for update

Question

I have a permission structure so that a specific permission only allows to edit 2 out of 5 fields in my table. I have RLS in my entire system so I need to execute the above inside the policy.

At first I thought of writing a function that checks if the user updated fields they don't have permissions updating, and using it's return value inside the policy. But I couldn't find a way to use the return value inside the policy without having to define a variable, which obviously you can't inside a policy.

Here's the said function:

CREATE OR REPLACE FUNCTION is_user_updating_non_permitted_fields(id uuid, fieldA integer, fieldB text...)
.....
 DECLARE
  old_row MY_TABLE
 BEGIN
  SELECT * FROM MY_TABLE m WHERE id = m.id INTO old_row;
  IF (fieldA != old_row.fieldA OR fieldB != old_row.fieldB)
    THEN RETURN 1;
    ELSE RETURN 0;
  ENDIF;
 END;
....

And the policy will be something like:

CREATE POLICY my_table_update ON MY_TABLE FOR UPDATE
WITH CHECK (
 (SELECT CASE WHEN (
    ''use function here''
   ) = 1 THEN false ELSE true END;
 )
)

As a last resort, I thought of doing a trigger before update and using ROLLBACK TRANSACTION but I really don't wanna go that way.

Monocle

Answer 1

According to the documentation, that is not possible:

Only rows for which the expression evaluates to true will be allowed. An error will be thrown if the expression evaluates to false or null for any of the records inserted or any of the records that result from the update. Note that the check_expression is evaluated against the proposed new contents of the row, not the original contents.

You will have to use a trigger after all.

Glad

Answer 2

As I ran into a similar problem, maybe something like this could help in this situation:

CREATE OR REPLACE FUNCTION is_not_updating_restricted_fields(
    _id UUID,
    _restricted_field_c TYPE,
    _restricted_field_d TYPE,
    _restricted_field_e TYPE,
) RETURNS BOOLEAN AS
$$
WITH original_row AS (
    SELECT restricted_field_c, restricted_field_d, restricted_field_e
    FROM my_table
    WHERE my_table.id = _id
)
SELECT(
    (SELECT restricted_field_c FROM original_row) = _restricted_field_c AND
    (SELECT restricted_field_d FROM original_row) = _restricted_field_d AND
    (SELECT restricted_field_e FROM original_row) = _restricted_field_e
)
$$ LANGUAGE SQL SECURITY DEFINER;


CREATE POLICY my_table_update ON MY_TABLE FOR UPDATE
WITH CHECK (
    is_not_updating_restricted_fields(
        id,
        restricted_field_c, 
        restricted_field_d, 
        restricted_field_e
    )
)

The key point here is that WITH CHECK is evaluated against proposed new contents (as mentioned above @Laurenz Albe), and therefore you have to check those values against the original values, that are acquired via original_row CTE.

It is also possible to combine both, WITH CHECK (that is evaluated against the proposed new contents) and USING together (where USING is evaluated against original contents):

CREATE POLICY my_table_update ON MY_TABLE FOR UPDATE
USING (...)
WITH CHECK (...)

This can also give some flexibility in more complicated cases.

Chancery

As is, this only works for fields restricted_field_* that cannot be NULL. If they were NULL before, and the UPDATE does not change it, your function still prevents the UPDATE due to NULL = NULL being false. Solution: use IS NOT DISTINCT FROM instead of =. (But then also make sure the function returns true only when EXISTS(SELECT COUNT(*) FROM original_row), otherwise circumventing the check by changing id becomes possible.) – Filipino