如何禁止手动插入update_log表，仅允许指定触发器写入？

我之前也碰到过完全一样的困扰：有两张表data和update_log，通过触发器在data表更新时自动往update_log插入记录，但想阻止用户手动向update_log写数据，试了前置触发器抛异常的方法，结果连触发器本身的插入也被拦截了。后来找到了一个靠谱的解决方案，分享给大家：

核心思路 #

通过PostgreSQL的用户权限分离+security definer函数来实现：用一个高权限用户负责创建表、触发器和函数，另一个普通用户负责日常操作data表，并且只有通过触发器才能间接写入update_log。

具体步骤 #

1. 创建两个用户 #

• 高权限用户（比如叫db_admin）：拥有data、update_log表的所有权，能创建触发器和函数，并且拥有update_log的插入权限。
• 普通用户（比如叫app_user）：只拥有data表的操作权限（更新、查询等），没有直接向update_log插入数据的权限。

2. 修改触发器函数为security definer #

把原来的udf_update_log函数加上security definer属性，这样当普通用户触发触发器时，函数会以创建它的高权限用户身份执行，从而绕过普通用户的权限限制完成插入。

修改后的函数代码：

create or replace function udf_update_log() returns trigger language plpgsql security definer as $$
begin
    insert into update_log(old_id, new_id, update_date) values (old.id, new.id, current_timestamp);
end;
$$;

3. 给普通用户分配必要权限 #

确保普通用户能正常操作data表并触发触发器：

-- 赋予普通用户data表的更新权限
grant update on data to app_user;
-- 按需赋予查询权限
grant select on data to app_user;
-- 允许用户触发data表上的触发器
grant trigger on data to app_user;

原理说明 #

• 普通用户自己执行insert into update_log(...)会因为权限不足被PostgreSQL拒绝；
• 当普通用户更新data表时，触发器会调用带有security definer属性的函数，此时函数会以高权限用户的身份运行，拥有update_log的插入权限，从而成功写入日志记录。

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