如何禁止修改表行除了触发器在postgresql?

Question

我有一个表accounts_balances，由触发器专门管理:

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

是否有办法添加策略来防止有人手动更新这个表?

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

但是我仍然可以这样做:

update accounts_balances set balance = 0 where account_id = 10;

Answer 1

你不能阻止超级用户更新Table-Contents，但是应用程序不应该连接到超级用户，所以没有问题。

只要删除表中所有角色的Update-Privilege，就可以了。

由于您的目标似乎是防止手动修改，您还应该撤销角色的INSERT和DELETE。

关于权限的PostgreSQL文档

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

Answer 2

根据USING中指定的表达式检查已存在的表行，而通过INSERT或UPDATE创建的新行则根据WITH CHECK中指定的表达式检查。

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

没有WITH CHECK表达式