PostgreSQL中ON DELETE SET DEFAULT执行角色与触发器权限问题

问题描述 #

我在PostgreSQL中创建了child表，通过外键引用parent表并设置ON DELETE SET DEFAULT约束。当删除parent表中被引用的记录时，child表的外键字段会以系统角色（如postgres）更新为默认值。

我在child表上设置了触发器，当is_locked字段为True时阻止编辑。原本期望系统角色postgres执行更新时不受该触发器限制，但发现仅BEFORE UPDATE触发器能识别postgres角色，AFTER UPDATE触发器会切换回发起删除操作的原角色（如user）。

请问这是否符合预期？若符合，如何让postgres角色在记录更新后仍能绕过编辑限制？

测试代码如下：

BEGIN;

CREATE TABLE parent (id INT PRIMARY KEY, name VARCHAR(100));

CREATE TABLE child (
    id INT PRIMARY KEY,
    parent_id INT DEFAULT 0,
    edited_by text,
    CONSTRAINT fk_parent FOREIGN KEY (parent_id) REFERENCES parent (id) ON DELETE SET DEFAULT
);

CREATE TABLE logs ("text" text);

CREATE OR REPLACE FUNCTION react_on_child_update () RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO logs ("text") VALUES (CURRENT_role::text);
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_before_react_on_child_update BEFORE
UPDATE ON child FOR EACH ROW
EXECUTE FUNCTION react_on_child_update();

CREATE TRIGGER trigger_after_react_on_child_update
AFTER UPDATE ON child FOR EACH ROW
EXECUTE FUNCTION react_on_child_update();

INSERT INTO parent (id, name) VALUES (0, 'ghost');
INSERT INTO parent (id, name) VALUES (1, 'Parent 1');
INSERT INTO parent (id, name) VALUES (2, 'Parent 2');
INSERT INTO child (id, parent_id) VALUES (1, 1);
INSERT INTO child (id, parent_id) VALUES (2, 2);

CREATE ROLE "user";
GRANT ALL ON TABLE parent,logs,child TO "user";
SET ROLE TO "user";

DELETE FROM parent
WHERE id = 1;

SELECT * FROM parent;
SELECT * FROM child;

-- See that first role was the admin one, the second the caller ("user")
SELECT * FROM logs;

问题解答 #

1. 该现象是否符合预期？ #

这是PostgreSQL的预期行为，原因如下：

• 当外键约束的ON DELETE SET DEFAULT触发child表的更新时，BEFORE触发器会在约束动作的权限上下文下执行，此时角色为拥有表权限的系统角色（如postgres），确保约束动作能顺利完成。
• AFTER触发器会在约束动作执行完毕后，切换回发起原始删除操作的用户角色（如示例中的user）执行，这是PostgreSQL为了保证后续触发动作在原始请求的权限上下文下运行的设计。

2. 如何让postgres角色在记录更新后仍能绕过编辑限制？ #

最直接的解决方案是使用SECURITY DEFINER属性创建触发器函数，这样无论是BEFORE还是AFTER触发器，都会以函数创建者的角色（postgres）执行，从而在触发器逻辑中识别到系统角色并绕过锁定限制。

修改后的触发器函数示例：

CREATE OR REPLACE FUNCTION react_on_child_update () RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO logs ("text") VALUES (CURRENT_role::text);
    -- 添加锁定判断逻辑：仅当非postgres角色且记录锁定时阻止编辑
    IF NEW.is_locked AND CURRENT_ROLE != 'postgres' THEN
        RAISE EXCEPTION '记录已锁定，无法编辑';
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER
-- 设置安全的搜索路径，避免SQL注入风险
SET search_path = public;

-- 限制函数权限，避免普通用户修改
REVOKE ALL ON FUNCTION react_on_child_update() FROM PUBLIC;
GRANT EXECUTE ON FUNCTION react_on_child_update() TO "user";

关键说明：

• SECURITY DEFINER：让函数以创建者的角色执行，而非调用者角色，这里创建者应为postgres或其他高权限角色。
• SET search_path = public：指定函数执行的搜索路径，防止恶意用户通过修改搜索路径执行未授权操作。
• 权限限制：收回PUBLIC对函数的所有权限，仅授予必要用户执行权限，降低安全风险。

如果不想让整个函数以高权限角色执行，也可以在触发器函数中通过SET ROLE临时切换到postgres角色执行锁定判断逻辑，但这种方式需要额外的权限配置，不如SECURITY DEFINER简洁可靠。

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