postgresql - Policy to prevent a field from being updated

Question

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

Answer 1

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