sql - 仅允许从触发器内插入

Question

我是 SQL 编程的新手，我在网上找不到这个问题的答案。

我正在使用 pl/pgsql，我希望获得以下结果:

我有一个具有某些属性的表 A。我应该随时更新此表 - 因此每当进行可能影响 A 值的更改时(在与 A 相关的其他表 B 或 C 中) - 触发更新值的触发器(在此过程中 -可以将新值插入 A，也可以删除旧值)。同时，我想防止有人向 A 中插入值。

我想做的是创建一个触发器来防止插入 A(通过返回 NULL)- 但我不希望在我进行插入时调用此触发器从另一个触发器 - 所以最终 - 只允许从特定触发器内插入 A。

正如我之前所说，我是 SQL 的新手，我不知道这是否可行。

Answer 1

是的，完全有可能。

1。通常不允许 UPDATE 到 A

我会以特权操作:

REVOKE ALL ON TABLE A FROM public;  -- and from anybody else who might have it

这使得诸如 postgres 之类的 super 用户忽略了这些低级限制。使用 pg_has_role() 在 A 上捕获触发函数内的那些:

IF pg_has_role('postgres', 'member') THEN
   RETURN NULL;
END IF;

postgres 是一个真正的 super 用户。注意:这也会捕获其他 super 用户，因为他们是每个角色的成员，甚至是其他 super 用户。

您可以用类似的方式捕获非 super 用户(替代 REVOKE 方法)。

2。允许 UPDATE 用于守护进程

创建一个非登录角色，允许更新A:

CREATE ROLE a_update NOLOGIN;
-- GRANT USAGE ON SCHEMA xyz TO a_update;  -- may be needed, too
GRANT UPDATE ON TABLE A TO a_update;

在表 B 和 C 上创建触发器函数，由此守护进程角色拥有并使用 SECURITY DEFINER。详情:

• Is there a way to disable updates/deletes but still allow triggers to perform them?

在A上添加触发函数:

IF pg_has_role('postgres', 'member') THEN
   RETURN NULL;
ELSIF pg_has_role('a_update', 'member') THEN
   RETURN NEW;
END IF;

对于简单的 1:1 依赖关系，您还可以使用 foreign key constraints (additionally) using ON UPDATE CASCADE .