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

Jonathan
  • 153
  • 2
  • 6

1 Answers1

6

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

tanius
  • 271
  • 3
  • 6