postgresql - Postgres Trigger 副作用与行级安全选择策略无序发生

Question

上下文

我正在使用行级安全性和触发器来实现纯 SQL RBAC 实现。这样做时，我遇到了 INSERT 触发器和 SELECT 行级安全策略之间的奇怪行为。

为简单起见，本问题的其余部分将使用以下简化表格讨论该问题:

CREATE TABLE a (id TEXT);
ALTER TABLE a ENABLE ROW LEVEL SECURITY;
ALTER TABLE a FORCE ROW LEVEL SECURITY;

CREATE TABLE b (id TEXT);

问题

考虑以下策略和触发器:

CREATE POLICY aSelect ON a FOR SELECT
USING (EXISTS(
    select * from b where a.id = b.id
));

CREATE POLICY aInsert ON a FOR INSERT
WITH CHECK (true);

CREATE FUNCTION reproHandler() RETURNS TRIGGER AS $$
BEGIN
    RAISE NOTICE USING MESSAGE = 'inside trigger handler';
    INSERT INTO b (id) VALUES (NEW.id);
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER reproTrigger BEFORE INSERT ON a
FOR EACH ROW EXECUTE PROCEDURE reproHandler();

现在考虑以下语句:

INSERT INTO a VALUES ('fails') returning id;

我基于阅读 policies applied by command type table 的期望和一般的 SQL 理解是以下事情应该按顺序发生:

1. 为INSERT准备了一个新行('fails')
2. BEFORE 触发器触发，NEW 设置为新行
3. ('fails') 行被插入到 b 中，并从触发器过程中原封不动地返回
4. INSERT 的WITH CHECK 策略 true 被评估为 true
5. SELECT 的USING 策略select * from b where a.id = b.id 被评估。 由于第 3 步，这应该返回 true
6. 通过所有策略后，('fails') 行被插入到表中
7. 返回插入行的id(失败)

不幸的是(正如您可能已经猜到的那样)，我们看到的不是上述步骤发生的情况:

test=> INSERT INTO a VALUES ('fails') returning id;
NOTICE:  inside trigger handler
ERROR:  new row violates row-level security policy for table "a"

此问题的目的是找出预期行为未发生的原因。

请注意，以下语句按预期正确运行:

test=> INSERT INTO a VALUES ('works');
NOTICE:  inside trigger handler
INSERT 0 1
test=> select * from a; select * from b;
  id   
-------
 works
(1 row)

  id   
-------
 works
(1 row)

我尝试了什么？

• 在触发器定义中对 BEFORE 与 AFTER 进行了实验
  • AFTER 导致触发器根本不执行
• 尝试定义适用于 ALL 命令的单一策略(使用相同的 using/with 检查表达式)
  • 导致相同的行为

附录

• Postgres 版本
  • x86_64-pc-linux-musl 上的 PostgreSQL 10.3，由 gcc (Alpine 6.4.0) 6.4.0，64 位编译
• 如果您尝试重现问题，请确保您没有使用 SUPER 权限运行，因为那样会忽略行安全性

Answer 1

在与一般邮件列表中的其他 PostgreSQL 用户/开发人员来回交流之后，确定此特定问题是由单个语句中的突变可见性引起的。 You can review the entire discussion here .特别感谢 Dean Rasheed 解释问题并提出解决方案。为了 Stack Overflow 社区的利益，我在这里总结了他的回答。

总而言之，由于整个语句在一个单个 PostgreSQL 快照。

解决此问题的一种方法是确保使用新快照运行 EXISTS 子句。为此，EXISTS 子句可以使用标记为 VOLATILE 的 PostgreSQL 函数。此函数属性将使函数能够观察同一语句中所做的更改。有关详细信息，请参阅 the documentation .相关段落摘录在此供引用:

For functions written in SQL or in any of the standard procedural languages, there is a second important property determined by the volatility category, namely the visibility of any data changes that have been made by the SQL command that is calling the function. A VOLATILE function will see such changes, a STABLE or IMMUTABLE function will not. This behavior is implemented using the snapshotting behavior of MVCC (see Chapter 13): STABLE and IMMUTABLE functions use a snapshot established as of the start of the calling query, whereas VOLATILE functions obtain a fresh snapshot at the start of each query they execute.

因此，解决此问题的一种方法是将 RLS 选择策略实现为 VOLATILE 函数。对政策的修改示例如下:

CREATE OR REPLACE FUNCTION rlsCheck(_id text) RETURNS TABLE (id text) AS $$
    select * from b where b.id = _id
$$ LANGUAGE sql VOLATILE;

CREATE POLICY reproPolicySelect ON a FOR SELECT
USING (
    EXISTS(select * from rlsCheck(a.id))
);

在此解决方案中，从表 a 投影的每一行都要求函数 rlsCheck 至少返回一行。此函数将使用每个投影行的新快照运行。每次调用 rlsCheck 生成的新快照将允许它看到原始示例中的 INSERT 触发器对表 b 的修改。

如果您进行上述修改并运行测试，您将看到以下行为:

test=> select * from a;
id 
----
(0 rows)

test=> select * from b;
id 
----
(0 rows)

test=> insert into a values ('hi') returning id;
NOTICE:  inside trigger handler
id 
----
hi
(1 row)

INSERT 0 1

此行为符合我的预期，因此我接受此作为问题的答案。不幸的是，该函数会在查询执行期间导致 Not Acceptable 优化围栏，因此我不会在我的 RBAC 实现中使用它。我认为不可能有一个可优化的解决方案来解决我的问题，因为 SELECT 策略中的 EXISTS 表达式不能同时内联和 VOLATILE。