I want to create an immutable Postgres database, where the user can insert & select (write & read) data, but cannot update or delete the data.
I am aware of the FOR UPDATE
lock, but I don't understand how to use it.
Let's say for example I have the table below, how can I make it immutable (or, if I understood correctly, how can I use the FOR UPDATE
lock permanently)
CREATE TABLE account(
user_id serial PRIMARY KEY,
username VARCHAR (50) UNIQUE NOT NULL,
password VARCHAR (50) NOT NULL,
email VARCHAR (355) UNIQUE NOT NULL,
created_on TIMESTAMP NOT NULL,
last_login TIMESTAMP
);
Nope, that 👆🏼 solution doesn't work. I found this one. I make a before trigger on the table on update for each row:
create or replace function table_update_guard() returns trigger
language plpgsql immutable parallel safe cost 1 as $body$
begin
raise exception
'trigger %: updating is prohibited for %.%',
tg_name, tg_table_schema, tg_table_name
using errcode = 'restrict_violation';
return null;
end;
$body$;
create or replace trigger account_update_guard
before update on account for each row
execute function table_update_guard();
See my original research.