How to forbid modification of table rows except for triggers in postgresql?

April 2, 2023

I have a table accounts_balances which is managed exclusively by triggers :

create table accounts_balances (
    account_id integer primary key references accounts(id) on delete cascade,
    balance integer not null
);

create or replace function new_account() returns trigger as $$
begin
    insert into accounts_balances (account_id, balance) values (new.id, 0);
    return new;
end;
$$ language plpgsql;

create trigger new_account
after insert on accounts
for each row
execute procedure new_account();

create or replace function add_operation() returns trigger as $$
begin
    update accounts_balances
    set balance = balance + new.amount
    where account_id = new.creditor_id;
    update accounts_balances
    set balance = balance - new.amount
    where account_id = new.debitor_id;
    return new;
end;
$$ language plpgsql;

create trigger add_operation
after insert on operations
for each row
execute procedure add_operation();

-- etc ...

Is there a way to add policies to prevent someone to update this table manually ?

I tried :

alter table accounts_balances enable row level security;

drop policy if exists forbid_update on accounts_balances;
create policy forbid_update ON accounts_balances
for all
using (false);

but I can still do this :

update accounts_balances set balance = 0 where account_id = 10;

>Solution :

You can’t prevent SuperUsers from updating the Table-Contents, but an application shouldn’t be connecting with a superuser, so no problem there.

Simply remove the Update-Privilege for the table for all roles and you should be good to go.

As your goal seems to prevent manual modifications you should also revoke INSERT and DELETE for the roles.

PostgreSQL Documentation about privileges

REVOKE UPDATE ON accounts_balances FROM public;
REVOKE UPDATE ON accounts_balances FROM role1;
REVOKE UPDATE, INSERT, DELETE ON accounts_balances FROM all;