3

How can I create a policy to prevent a field from being updated?

I am thinking something like this:

CREATE POLICY "test" ON posts.created_at FOR UPDATE WITH CHECK (exists(created_at));

Obviously this does not work, but I would like a way for this to work as long as there is no created_at coming into the UPDATE fields.

J

3
  • 1
    "as long as there is no created_at coming into the UPDATE fields." - there is no way to detect if a specific column was specified in the UPDATE statement or not
    – user1822
    Feb 7, 2022 at 7:57
  • ok, so it is impossible without a trigger I suppose. Thanks!
    – Jonathan
    Feb 7, 2022 at 13:07
  • 1
    I wrote an article on this after lots of research - code.build/p/XUPzns9ykZj4Abcazg1kt5/…
    – Jonathan
    Feb 15, 2023 at 4:55

1 Answer 1

4

This is indeed possible with a row-level security (RLS) policy by manually retrieving the old row and checking if your field's value would change between the old to the new row. A near-complete solution for this has been posted as a Stack Overflow answer.

Several alternative solutions are also at your disposal:

  • You can use a BEFORE UPDATE trigger, as you already mentioned. A Stack Overflow answer shows an example for such a trigger.

  • You can split your table into two tables, with a 1:1 relationship between them. Then, you can provide SELECT and UPDATE access to one table and only SELECT access to the other.

  • You can provide UPDATE access to only a subset of columns of the table: GRANT UPDATE(col1, col2). (details)

  • You can provide read-only access to the table and create a VIEW with the updateable columns of the table, and grant UPDATE access to that. Such a VIEW cannot have its own independent RLS policies, but if you need RLS, you can work around that in at least two ways:

    • re-using the RLS policies of the underlaying table via security_invoker = on (details), while also using table-level permissions to prevent users from editing that underlaying table

    • creating a user-specific view that shows only the rows the user is allowed to edit (details).

  • You can hide the table behind a FUNCTION with SECURITY DEFINER. The table itself would not provide UPDATE access, instead users can only update the table through the FUNCTION.

(Inspired by this list.)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.