Immutability in Postgres

Question

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

Laurenz Albe · Answer 1 · 2023-01-13T08:24:02.467

9

The solution is to give the user that accesses the database only the INSERT and SELECT privilege on the tables involved.

A lock is not a tool to deny somebody access, but a short-time barrier to prevent conflicting data modifications to happen at the same time.

Here is an example:

CREATE TABLE sensitive (
   id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
   available text,
   restricted text
);

Now I want to allow someuser to insert data and read and update all columns except restricted, and I want to keep myself from deleting data in that table:

/* the CREATE TABLE above was run by user "laurenz" */
REVOKE DELETE ON sensitive FROM laurenz;

GRANT INSERT ON sensitive TO someuser;
GRANT SELECT (id, available), UPDATE (id, available) ON sensitive TO someuser;

edited Jan 13 '23 at 08:24

answered Mar 18 '19 at 20:18

Laurenz Albe

209,280
17
206
263

Nope, it didn't work for me. – danissimo Jan 13 '23 at 01:13
"It didn't work" doesn't give me a handle to figure out where you went wrong. Look at `\dp table_name` in `psql`. – Laurenz Albe Jan 13 '23 at 06:32
I went wrong nowhere. Take a look at a couple of the first lines: https://stackoverflow.com/a/75104506/2634207 – danissimo Jan 13 '23 at 08:05
You must have gone wrong. The best I can do is add some sample code to my answer to show how it is done. – Laurenz Albe Jan 13 '23 at 08:24
Would you please? I'd like to learn – danissimo Jan 13 '23 at 08:33
I have added an example. – Laurenz Albe Jan 13 '23 at 08:38

danissimo · Accepted Answer · 2023-01-13T08:00:02.360

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.

Immutability in Postgres

2 Answers2