只允许从触发器内插入 [英] Allow insertion only from within a trigger

问题描述

我是SQL编程的新手，我无法在线找到该问题的答案。

I'm new to SQL programming, and I couldn't find an answer to this question online.

我正在使用pl / pgsql，但我希望以达到以下结果：

I'm working with pl/pgsql and I wish to achieve the following result:

我有一个带有某些属性的表A。
我应该随时对该表进行更新-因此，只要进行可能影响A值的更改（在其他与A相关的表B或C中），就会触发一个触发器来更新值（在此过程中-可以将新值插入A，也可以删除旧值）。
同时，我想防止有人将值插入A。

I have a table A with certain attributes. I am supposed to keep this table updated at any time - thus whenever a change was made that can affect A's values (in other tables B or C which are related to A) - a trigger is fired which updates the values (in the process - new values can be inserted into A, as well as old values can be deleted). At the same time, I want to prevent from someone insert values into A.

我要做的是创建一个触发器，以防止插入A（通过返回NULL）-但我不希望在我从另一个触发器插入时调用此触发器-因此，最终-仅允许在特定范围内插入A

What I want to do is to create a trigger which will prevent insertion into A (by returning NULL) - but I don't want this trigger to be called when I'm doing the insertion from another Trigger - so eventually - insertion to A will only be allowed from within a specific trigger.

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

As I said before, I'm new to SQL, and I don't know if this is even possible.

推荐答案

是的，完全有可能。

特权：

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

这会留下诸如之类的超级用户postgres 忽略了这些限制。使用A 上的触发函数中捕获那些INFO-ACCESS-TABLE rel = nofollow noreferrer> pg_has_role（） ：

That leaves superusers such as postgres who ignore these lowly restrictions. Catch those inside your trigger-function on A with pg_has_role():

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

其中 postgres 是实际的超级用户。注意：这也会捕获其他超级用户，因为它们是每个角色的成员，甚至其他超级用户也是如此。

Where postgres is an actual superuser. Note: this catches other superusers as well, since they are member of every role, even other superusers.

您可以以类似的方式捕获非超级用户（ 撤消方法）。

You could catch non-superusers in a similar fashion (alternative to the REVOKE approach).

创建一个非登录角色，该角色可以更新 A ：

Create a non-login role, which is allowed to 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 。详细信息：

Create trigger functions on tables B and C, owned by this daemon role and with SECURITY DEFINER. Details:

• 是否可以禁用更新/删除，但仍允许触发器执行更新？

• 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依赖关系，您还可以使用外键约束（附加）使用 ON UPDATE CASCADE 。

For simple 1:1 dependencies, you can also work with foreign key constraints (additionally) using ON UPDATE CASCADE.

这篇关于只允许从触发器内插入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！