如何在postgresql中禁止除触发器以外的表行修改?

watbbzwu  于 2023-04-11  发布在  PostgreSQL
关注(0)|答案(2)|浏览(236)

我有一个表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;
j9per5c4

j9per5c41#

您不能阻止超级用户更新Table-Contents,但应用程序不应该与超级用户连接,因此没有问题。
只需删除所有角色的表的Update-Privilege,您就可以开始了。
由于您的目标似乎是防止手动修改,因此还应该撤销角色的INSERT和DELETE。
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;
j7dteeu8

j7dteeu82#

根据USING中指定的表达式检查现有表行,而根据WITH CHECK中指定的表达式检查通过INSERT或UPDATE创建的新行。
https://www.postgresql.org/docs/current/sql-createpolicy.html
您没有WITH CHECK表达式。

相关问题