我有一个表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 ...
是否有方法添加策略以防止有人手动更新此表?
我试过:
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;
2条答案
按热度按时间j9per5c41#
您不能阻止超级用户更新Table-Contents,但应用程序不应该与超级用户连接,因此没有问题。
只需删除所有角色的表的Update-Privilege,您就可以开始了。
由于您的目标似乎是防止手动修改,因此还应该撤销角色的INSERT和DELETE。
PostgreSQL Documentation about privileges
j7dteeu82#
根据USING中指定的表达式检查现有表行,而根据WITH CHECK中指定的表达式检查通过INSERT或UPDATE创建的新行。
https://www.postgresql.org/docs/current/sql-createpolicy.html
您没有WITH CHECK表达式。