postgresql - Postgres 政策的范围是什么？

Question

我正在努力研究 Postgres 中的行级安全性。不幸的是，文档在这个问题上不是很详细。我的问题如下:

我有两个表:locations 和 locations_owners。在 INSERT 上为位置设置了一个 TRIGGER，它会自动向 locations_owners 表添加一个新行，包括 request.jwt .claim.sub variable .

这一切都很好，但是当我想像这样为 DELETE 创建策略时:

CREATE POLICY location_delete ON eventzimmer.locations FOR DELETE TO organizer USING(
    (SELECT EXISTS (SELECT name FROM protected.locations_owners AS owners WHERE owners.name = name AND owners.sub = (SELECT current_setting('request.jwt.claim.sub', true))))
);

无论实际内容如何，​​它的计算结果始终为真。我知道我可以在此处使用 SELECT 调用自定义过程，但我最终遇到了以下问题:

• 政策的范围是什么？我可以访问表格吗？我可以访问程序吗？文档说 “任何 SQL 条件表达式” 所以 SELECT EXISTS 应该没问题
• 行的列名如何映射到策略。这些示例只是神奇地使用了列名(我通过使用 name 变量采用了它)，但是我还没有找到关于它实际做什么的任何文档
• 什么是神奇的 user_name 变量。它从何而来？我相信是当前的角色正在执行查询，但我怎么知道呢？
• 为什么没有可用于DELETE 的WITH CHECK 表达式？如果我理解正确，WITH CHECK 将失败任何具有无效约束的行，这是我更喜欢的行为(因为否则 PostgREST 将始终返回 204)

我对 PostgreSQL 的(否则)非常好的文档中惊人地缺失的大量信息感到有点困惑。这些信息在哪里？我怎样才能找到它？

为了完整起见，我还附上了下面的列定义:

CREATE TABLE eventzimmer.locations (
  name varchar PRIMARY KEY NOT NULL,
  latitude float NOT NULL,
  longitude float NOT NULL
);

CREATE TABLE IF NOT EXISTS protected.locations_owners (
    name varchar NOT NULL REFERENCES eventzimmer.locations(name) ON DELETE CASCADE,
    sub varchar NOT NULL
);

Answer 1

一旦您了解了行级安全性的实现方式，许多问题就会变得清晰:策略中的条件将自动添加到查询中，就像您添加了另一个 WHERE 条件一样。

使用 EXPLAIN 查看查询计划，您将在其中看到策略的条件。

• 因此您可以使用定义策略的表中的任何列。

  基本上，您可以在策略定义中使用您可以在 WHERE 条件中使用的任何内容:函数调用、子查询等。

  如果需要消除歧义，您还可以使用表名限定列名。这可以在您示例的策略中使用:未限定的 name 被解释为 owners.name，因此测试总是成功的。要修正政策，请使用 locations.name 而不是 name。

• 没有神奇的 user_name 变量，我不知道您是从哪里得到的。但是，current_user 函数始终可用，当然也可以在策略定义中使用。

• WITH CHECK 是 INSERT 或 UPDATE 添加的新行必须满足的条件。由于 DELETE 不添加任何数据，因此 WITH CHECK 不适用于它。