postgresqldatabase-permissions

Immutability in Postgres


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

Solution

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