Postgresql 中文操作指南

CREATE POLICY

CREATE POLICY — 为表定义新的行级安全策略

Synopsis

CREATE POLICY name ON table_name
    [ AS { PERMISSIVE | RESTRICTIVE } ]
    [ FOR { ALL | SELECT | INSERT | UPDATE | DELETE } ]
    [ TO { role_name | PUBLIC | CURRENT_ROLE | CURRENT_USER | SESSION_USER } [, ...] ]
    [ USING ( using_expression ) ]
    [ WITH CHECK ( check_expression ) ]

Description

CREATE POLICY 命令为表定义新的行级安全策略。请注意，必须针对表启用行级安全（使用 ALTER TABLE …​ ENABLE ROW LEVEL SECURITY ），才能应用已创建的策略。

策略授予选择、插入、更新或删除与相关策略表达式匹配的行的权限。现有表行将根据 USING 中指定的表达式进行检查，而要通过 INSERT 或 UPDATE 创建的新行将根据 WITH CHECK 中指定的表达式进行检查。当 USING 表达式对给定行返回 true 时，该行对用户可见，而如果返回 false 或 null，则该行不可见。当 WITH CHECK 表达式对行返回 true 时，该行将插入或更新，而返回 false 或 null 时，将发生错误。

对于 INSERT 、 UPDATE 和 MERGE 语句，将在触发 WITH CHECK 触发器后、在进行任何实际数据修改之前强制执行 BEFORE 表达式。因此， BEFORE ROW 触发器可以修改要插入的数据，从而影响安全策略检查的结果。 WITH CHECK 表达式在任何其他约束之前强制执行。

策略名称按表分列。因此，一个策略名称可用于多个不同的表，针对每个表都有适合该表的定义。

策略可针对特定命令或特定角色应用。新创建的策略的默认设置是，除非另有规定，它们适用于所有命令和角色。多个策略可能应用于单个命令；请参阅下文以了解更多详细信息。 Table 292 概述了不同类型的策略如何应用于特定命令。

对于既可以包含 USING 表达式又可以包含 WITH CHECK 表达式（ ALL 和 UPDATE ）的策略，如果没有定义 WITH CHECK 表达式，那么 USING 表达式将同时用于确定哪些行可见（ USING 常规情况）和允许添加哪些新行（ WITH CHECK 情况）。

如果为表启用了行级安全，但不存在任何可应用的策略，则认为存在“默认拒绝”策略，因此不会有任何行可见或可更新。

Parameters

  • name

    • 要创建的策略的名称。此名称必须不同于表中任何其他策略的名称。

  • table_name

    • 策略适用的表（可选地限定架构）的名称。

  • PERMISSIVE

    • 指定将策略创建为一个许可策略。适用于给定查询的所有许可策略将使用布尔“OR”运算符合并在一起。通过创建许可策略，管理员可以添加到可以访问的记录集中。策略默认情况下是许可的。

  • RESTRICTIVE

    • 指定将策略创建为限制策略。适用于给定查询的所有限制策略将使用布尔“AND”运算符合并在一起。通过创建限制策略，管理员可以减少可以访问的记录集，因为每个记录都必须通过所有限制策略。

    • 请注意，在限制策略能够有效地用于减少访问之前，需要至少有一个许可策略来授予访问记录的权限。如果仅存在限制策略，则将无法访问任何记录。当存在许可策略和限制策略的混合时，仅当至少一个许可策略通过且所有限制策略都通过时，才能访问一个记录。

  • command

    • 策略应用到的命令。有效选项有 ALL 、 SELECT 、 INSERT 、 UPDATE 和 DELETE 。 ALL 是默认值。有关如何应用这些选项的具体信息，请参见下方。

  • role_name

    • 策略应用到的角色。默认值为 PUBLIC ，它将策略应用到所有角色。

  • using_expression

    • 任何 SQL 条件表达式（返回 boolean ）。条件表达式不能包含任何聚合或窗口函数。如果启用了行级安全性，则将此表达式添加到引用该表的查询中。表达式返回 true 的行将可见。任何表达式返回 false 或 null 的行对于用户都是不可见的（在 SELECT 中），并且不可用于修改（在 UPDATE 或 DELETE 中）。此类行将被静默抑制；不会报告错误。

  • check_expression

    • 任何 SQL 条件表达式（返回 boolean ）。条件表达式不能包含任何聚合或窗口函数。如果启用了行级安全性，此表达式将用于对照表的 INSERT 和 UPDATE 查询。只有表达式计算结果为 true 的行才被允许。如果插入的任何记录或更新产生的任何记录的表达式计算结果为 false 或 null，则会引发错误。请注意， check_expression 是针对行的提议新内容评估的，而不是针对原始内容。

Per-Command Policies

  • ALL #

    • 为策略使用 ALL 意味着它将应用到所有命令，无论命令的类型如何。如果存在 ALL 策略和更具体的策略，则 ALL 策略和更具体的策略（或策略）都将被应用。此外， ALL 策略将被应用到查询的选取端和修改端，如果只定义了 USING 表达式，则在两种情况下都使用 USING 表达式。

    • 例如，如果发出 UPDATE ，则 ALL 策略将同时适用于 UPDATE 将能够选择哪些行作为要更新的行（应用 USING 表达式），以及适用于已更新的行，以检查是否允许将它们添加到表中（应用 WITH CHECK 表达式（如果已定义）和 USING 否则表达式）。如果 INSERT 或 UPDATE 命令尝试将不通过 ALL 策略的 WITH CHECK 表达式的行添加到表中，则整个命令将被中止。

  • SELECT #

    • 为策略使用 SELECT 意味着它将应用到 SELECT 查询和在关系上需要 SELECT 权限的任何时候，该权限是针对策略定义的。结果是，关系中只有那些通过 SELECT 策略的记录会在 SELECT 查询期间被返回，而需要 SELECT 权限（例如 UPDATE ）的查询也会只看到 SELECT 策略允许的那些记录。 SELECT 策略不能有 WITH CHECK 表达式，因为它只在从关系检索记录的情况下才适用。

  • INSERT #

    • 为策略使用 INSERT 意味着它将应用到 INSERT 命令和包含 INSERT 操作的 MERGE 命令。不通过此策略的正在插入的行将导致策略违规错误，并且整个 INSERT 命令将被中止。 INSERT 策略不能有 USING 表达式，因为它只在向关系添加记录的情况下才适用。

    • 请注意， INSERT 与 ON CONFLICT DO UPDATE 的 INSERT 策略的 WITH CHECK 表达式只针对 INSERT 路径添加到关系的行进行检查。

  • UPDATE #

    • 为策略使用 UPDATE 意味着它将应用到 UPDATE 、 SELECT FOR UPDATE 和 SELECT FOR SHARE 命令，以及 INSERT 命令的辅助 ON CONFLICT DO UPDATE 子句。包含 UPDATE 操作的 MERGE 命令也会受到影响。由于 UPDATE 涉及抽出一个现有记录并用一个新的修改记录替换它，因此 UPDATE 策略接受 USING 表达式和 WITH CHECK 表达式。 USING 表达式确定 UPDATE 命令将针对哪些记录进行操作，而 WITH CHECK 表达式定义哪些修改后的行被允许存储回关系中。

    • 任何更新值不通过 WITH CHECK 表达式的行都会导致错误，并且整个命令将被中止。如果只指定了一个 USING 子句，那么该子句将用于 USING 和 WITH CHECK 情况。

    • 通常， UPDATE 命令还需要从正在更新的关系中的列中读取数据（例如，在 WHERE 子句或 RETURNING 子句中，或在 SET 子句的右侧的表达式中）。在这种情况下，更新的关系还需要有 SELECT 权限，并且除了 UPDATE 策略之外，还将应用适当的 SELECT 或 ALL 策略。因此，除了通过 UPDATE 或 ALL 策略授予更新行的权限之外，用户必须通过 SELECT 或 ALL 策略访问到正在更新的行。

    • 当 INSERT 命令有辅助 ON CONFLICT DO UPDATE 子句时，如果采用 UPDATE 路径，则将先针对 UPDATE 策略的 USING 表达式检查要更新的行，然后针对 WITH CHECK 表达式检查新的更新行。但是，请注意，与独立 UPDATE 命令不同，如果现有行不通过 USING 表达式，则会引发错误（ UPDATE 路径将被静默 never ）。

  • DELETE #

    • 为策略使用 DELETE 意味着它将应用到 DELETE 命令。通过此策略的只有行会被 DELETE 命令看到。有些行可能可以通过 SELECT 看见，但不能被删除，如果它们不通过 DELETE 策略的 USING 表达式。

    • 在大多数情况下， DELETE 命令还需要从其要从中删除的行所在的关系中的列中读取数据（例如，在 WHERE 子句或 RETURNING 子句中）。在这种情况下，关系还需要有 SELECT 权限，并且除了 DELETE 策略之外，还将应用适当的 SELECT 或 ALL 策略。因此，除了通过 DELETE 或 ALL 策略授予删除行的权限之外，用户必须通过 SELECT 或 ALL 策略访问到正在删除的行。

    • DELETE 策略不能有 WITH CHECK 表达式，因为它仅在从关系中删除记录的情况下适用，因此没有要检查的新行。

Table 292. Policies Applied by Command Type

Command

SELECT/ALL policy

INSERT/ALL policy

UPDATE/ALL policy

DELETE/ALL policy

USING expression

WITH CHECK expression

USING expression

WITH CHECK expression

USING expression

SELECT

Existing row

—

—

—

—

SELECT FOR UPDATE/SHARE

Existing row

—

Existing row

—

—

INSERT / MERGE …​ THEN INSERT

—

New row

—

—

—

INSERT …​ RETURNING

New row #ftn.RLS-SELECT-PRIV

New row

—

—

—

UPDATE / MERGE …​ THEN UPDATE

现有和新行 [id="a",role="bare"]sql-createpolicy.html#ftn.RLS-SELECT-PRIV [id="a"]

—

Existing row

New row

—

DELETE

Existing row sql-createpolicy.html#ftn.RLS-SELECT-PRIV

—

—

—

Existing row

ON CONFLICT DO UPDATE

Existing & new rows

—

Existing row

[id="a",role="bare"]#RLS-SELECT-PRIV [id="a"] 如果需要对现有行或新行进行读取访问（例如，引用关系中列的 WHERE 或 RETURNING 子句）。

Application of Multiple Policies

当不同命令类型的多个策略应用于同一命令时（例如， SELECT 和 UPDATE 策略应用于 UPDATE 命令），则用户必须具有两种类型的权限（例如，从关系中选择行的权限以及更新它们的权限）。因此，使用 AND 运算符将一种策略类型的表达式与另一种策略类型的表达式组合起来。

当同一命令类型的多个策略应用于同一命令时，则至少必须有一个 PERMISSIVE 策略授予对关系的访问权限，并且所有 RESTRICTIVE 策略都必须通过。因此，使用 OR 将所有 PERMISSIVE 策略表达式组合起来，使用 AND 将所有 RESTRICTIVE 策略表达式组合起来，然后使用 AND 将结果组合起来。如果没有 PERMISSIVE 策略，则拒绝访问。

请注意，为了组合多个策略， ALL 策略被视为与正在应用的其他策略类型具有相同的类型。

例如，在一个同时需要 SELECT 和 UPDATE 权限的 UPDATE 命令中，如果有多个适用于每种类型的策略，它们将按如下方式进行组合：

expression from RESTRICTIVE SELECT/ALL policy 1
AND
expression from RESTRICTIVE SELECT/ALL policy 2
AND
...
AND
(
  expression from PERMISSIVE SELECT/ALL policy 1
  OR
  expression from PERMISSIVE SELECT/ALL policy 2
  OR
  ...
)
AND
expression from RESTRICTIVE UPDATE/ALL policy 1
AND
expression from RESTRICTIVE UPDATE/ALL policy 2
AND
...
AND
(
  expression from PERMISSIVE UPDATE/ALL policy 1
  OR
  expression from PERMISSIVE UPDATE/ALL policy 2
  OR
  ...
)

Notes

您必须是表的拥有者才能为其创建或更改策略。

虽然策略将对针对数据库中的表的显式查询进行应用，但在系统执行内部参照完整性检查或验证约束时不会对它们进行应用。这意味着存在间接方法来确定给定值是否存在。一个示例是尝试在作为主键或具有唯一约束的列中插入重复值。如果插入失败，则用户可以推断该值已经存在。（此示例假定策略允许用户插入他们无权看到的记录。）另一个示例是用户被允许插入引用另一个隐藏表的表中。用户可以通过在引用表中插入值来确定是否存在，其中成功表示该值存在于所引用表中。这些问题可以通过仔细制定策略来解决，以防止用户以任何可能表明他们无法看到的值来插入、删除或更新记录，或通过使用生成值（例如代理键）代替具有外部含义的键。

通常，系统将强制在用户查询中出现的限定符之前执行使用安全策略施加的筛选条件，以防止保护数据意外暴露给可能不可信的用户定义函数。但是，系统（或系统管理员）标记为 LEAKPROOF 的函数和运算符可在策略表达式之前进行评估，因为假定它们是可信的。

由于策略表达式直接添加到用户的查询中，因此它们将使用运行整体查询的用户的权限运行。因此，使用给定策略的用户必须能够访问表达式中引用的任何表或函数，否则他们在尝试查询已启用行级安全性的表时只会收到权限拒绝错误。但是，这并未改变视图的工作方式。与正常查询和视图一样，对视图引用的表的权限检查和策略将使用视图所有者的权限和适用于视图所有者的任何策略，但如果视图是使用 security_invoker 选项定义的除外（请参阅 CREATE VIEW ）。

MERGE 没有单独的策略。相反，根据执行的 MERGE 操作，在执行 SELECT 、 INSERT 、 UPDATE 和 DELETE 时会应用它们的已定义策略。

可以在 Section 5.8 中找到其他讨论和实用示例。

Compatibility

CREATE POLICY 是 PostgreSQL 扩展。

See Also

ALTER POLICY 、 DROP POLICY 、 ALTER TABLE