CODEWITHSUNDEEP
postgresql
stellard
stellard

Reputation: 5314

Postgresql, Restrict update on specific columns (Read Only)

Is it possible to have postresql restrict/prevent an update on a specific record if the update includes changes to specific columns?

How would this be implemented. A trigger/constraint? What would be the most efficient way to implement this?

I am using version 9.1

Upvotes: 25

Views: 20657

Answers (3)

tanius
tanius

Reputation: 16759

Several solutions are available:

  • You can use a BEFORE UPDATE trigger, as already proposed in other answers. Another 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.

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

Inspired by this list. See also a more detailed look at these solutions in another answer of mine, in the context of the Supabase system.

Upvotes: 2

GoT
GoT

Reputation: 156

No, but it should be pretty trivial to write. Just set up a BEFORE UPDATE trigger that compares old field against new field and does a RAISE ERROR if they're different. The pgSQL docs have a few examples of how to write a trigger function.

Upvotes: 7

Ihor Romanchenko
Ihor Romanchenko

Reputation: 28521

The easiest way is to create BEFORE UPDATE trigger that will compare OLD and NEW row and RAISE EXCEPTION if the change to the row is forbidden.

Upvotes: 32

Related Questions