How do I make a table read-only in PostgreSQL

Question

I need to prevent accidental changes in a core table of the application. I don't want anyone to change the rows during the operation of the application, though, I will make changes during the deployment of a new version of the application. For example:

create table procedure (
  id int primary key not null,
  name varchar(50) not null
);

insert into procedure (id, name) values (1, 'Send Exam Request');
insert into procedure (id, name) values (2, 'Confirm Exam Data');
insert into procedure (id, name) values (3, 'Close Visit');

-- Now, make the table read-only.

Great, the first version of the application is now in production running well.

Next month when I deploy the next version of the application:

-- Make the table read-write.

insert into procedure (id, name) values (4, 'Request Visit'); -- new proc
delete from procedure where id = 2; -- remove old unsupported proc

-- Now, make the table read-only again.

Ideally, it would be great if an error message was shown if anyone tried to change it during normal operation of the application. Something like:

insert into procedure (id, name) values (5, 'New bad procedure');
ERROR: Cannot modify table procedure. 
Contact the system administrator if you want to make this change.

Any idea what's the best approach to make this happen?

Answer 1

Create a trigger that throws an error:

create function do_not_change()
  returns trigger
as
$$
begin
  raise exception 'Cannot modify table procedure. 
Contact the system administrator if you want to make this change.';
end;
$$
language plpgsql;

create trigger no_change_trigger
  before insert or update or delete on "procedure"
  execute procedure do_not_change();

Note that procedure is a keyword. You should avoid a table with that name.

---

A better option is to separate the DB-user that creates tables and the user that modifies table. The creator should be the owner and the "modification" user is only granted the necessary privileges: select,insert,update,delete for all regular tables and only select for the procedure table. That will however require some changes to your deployment scripts, but is definitely the more secure option (especially if only a few people know the "creator" password)

Answer 2

This is what access control is for. Limit insert privileges on the table in question (or better yet, on a view of the table) to certain users (perhaps just yourself). Other users will get an "insufficient privileges" error message if they try inserting into the table. It works for updating and deleting, too. And querying, for that matter.