PostgreSQL中创建强制追加仅写表：权限配置问题及优化方案咨询

一、解决撤销所有角色权限的问题 #

你之前的方案只处理了PUBLIC角色的权限，但部分用户（比如管理员、被直接授权的角色）可能拥有独立于PUBLIC的直接权限，需要先清理这些权限：

1. 查询所有拥有该表直接权限的角色
   执行以下SQL找出除PUBLIC外，对ticket_trail有直接权限的角色：

SELECT grantee, privilege_type
FROM information_schema.table_privileges
WHERE table_name = 'ticket_trail'
AND grantee != 'PUBLIC';

2. 逐个撤销这些角色的全部权限
   对查询结果中的每个角色执行撤销命令：

REVOKE ALL ON TABLE ticket_trail FROM 具体角色名;

3. 清理PUBLIC权限并授予插入权限
   完成上述步骤后，再执行你原来的命令：

REVOKE ALL ON TABLE ticket_trail FROM PUBLIC;
GRANT INSERT ON TABLE ticket_trail TO PUBLIC;

注意：超级用户（如默认的postgres角色）拥有所有权限，无法通过REVOKE命令撤销，若要限制超级用户，需结合下文的其他方案。

二、创建追加仅写表的其他更好方法 #

1. 行级安全策略（RLS） #

这是PostgreSQL中推荐的细粒度权限控制方式，适合大多数场景：

-- 开启表的行级安全
ALTER TABLE ticket_trail ENABLE ROW LEVEL SECURITY;

-- 创建允许所有用户插入的策略
CREATE POLICY allow_insert_only ON ticket_trail
FOR INSERT TO PUBLIC
WITH CHECK (true);

-- 创建拒绝其他所有操作的策略
CREATE POLICY deny_non_insert_ops ON ticket_trail
FOR SELECT, UPDATE, DELETE, TRUNCATE TO PUBLIC
USING (false);

该策略会限制所有非超级用户只能执行INSERT操作，无法查询、修改或删除数据。

2. 触发器强制限制操作 #

通过触发器拦截除INSERT外的所有操作，适合需要自定义错误提示或额外逻辑的场景：

-- 创建触发器函数
CREATE OR REPLACE FUNCTION block_non_insert()
RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP NOT IN ('INSERT') THEN
        RAISE EXCEPTION '该表为追加仅写，仅允许INSERT操作';
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- 绑定触发器到表，拦截UPDATE/DELETE/TRUNCATE操作
CREATE TRIGGER enforce_append_only_trigger
BEFORE UPDATE OR DELETE OR TRUNCATE ON ticket_trail
FOR EACH STATEMENT
EXECUTE FUNCTION block_non_insert();

-- 若要限制超级用户，需设置触发器始终生效
ALTER TABLE ticket_trail ENABLE ALWAYS TRIGGER enforce_append_only_trigger;

3. 不可变表（PostgreSQL 12+） #

PostgreSQL 12及以上版本支持将表标记为不可变，直接禁止修改和删除操作：

ALTER TABLE ticket_trail SET (immutable = true);

该方式操作简单，但会限制表结构的修改（如添加列），适合完全不需要修改数据和结构的场景。

内容的提问来源于stack exchange，提问作者NotSoShabby