如何禁止手动修改PostgreSQL表行，仅允许触发器操作？

场景背景 #

我维护着一张完全由触发器管理的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 ...

问题与尝试 #

我希望添加策略禁止手动更新这张表，于是尝试了以下行级安全（RLS）配置：

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;

原因分析 #

你的配置没生效主要有两个核心原因：

• 超级用户默认绕过所有RLS策略，如果你是以超级用户身份执行更新，策略会直接被忽略。
• 表的所有者默认也不受RLS限制，除非你显式开启FORCE ROW LEVEL SECURITY强制生效。

解决方案 #

要彻底禁止手动操作，同时保留触发器的正常运行，可按以下步骤配置：

1. 启用并强制RLS #

确保包括表所有者在内的所有用户都受RLS约束：

ALTER TABLE accounts_balances ENABLE ROW LEVEL SECURITY;
ALTER TABLE accounts_balances FORCE ROW LEVEL SECURITY;

2. 创建仅允许触发器操作的策略 #

利用PostgreSQL的trigger_depth参数区分操作来源：触发器执行时该参数值大于0，手动操作时为0。基于此创建策略：

DROP POLICY IF EXISTS allow_trigger_operations ON accounts_balances;
CREATE POLICY allow_trigger_operations ON accounts_balances
FOR ALL
USING (current_setting('trigger_depth')::integer > 0)
WITH CHECK (current_setting('trigger_depth')::integer > 0);

验证效果 #

配置完成后，手动执行更新、插入或删除操作会被拒绝，而通过accounts或operations表触发的触发器操作仍能正常维护accounts_balances的数据。

另外，你也可以配合权限撤销进一步加固：

-- 撤销所有普通用户的写权限
REVOKE INSERT, UPDATE, DELETE ON accounts_balances FROM PUBLIC;
-- 若有特定业务用户，也单独撤销权限
REVOKE INSERT, UPDATE, DELETE ON accounts_balances FROM your_business_user;

内容的提问来源于stack exchange，提问作者Koala-Gentil