Question 89125 in PostgreSQL Question posted in
The official documentation can be found here.

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

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;
Tags:

2

Answers


  1. 0

    Existing table rows are checked against the expression specified in USING, while new rows that would be created via INSERT or UPDATE are checked against the expression specified in WITH CHECK.

    https://www.postgresql.org/docs/current/sql-createpolicy.html

    You don’t have a WITH CHECK expression.

    Login or Signup to reply.
  2. 0

    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;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search