PostgreSQL 15 文档: CREATE POLICY

CREATE POLICY
上一页	上一级	SQL 命令	起始页	下一页

CREATE POLICY

CREATE POLICY — 为一个表定义一条新的行级安全性策略

大纲

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 ) ]

描述

CREATE POLICY为一个表定义一条行级安全性策略。注意为了应用已被创建的策略，在表上必须启用行级安全性（使用ALTER TABLE ... ENABLE ROW LEVEL SECURITY）。

一条策略授予权限以选择、插入、更新或者删除匹配相关策略表达式的行。现有的表行会按照USING中指定的表达式进行检查，而将要通过INSERT或UPDATE创建的新行会按照WITH CHECK中指定的表达式进行检查。当USING表达式对于一个给定行返回真时，该行对用户可见，而返回假或空时该行不可见。当一个WITH CHECK 表达式对一行返回真时，该行会被插入或更新，而如果返回假或空时会发生一个错误。

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

策略名称是针对每个表的。因此，一个策略名称可以被用于很多个不同的表并且对于不同的表呈现适合于该表的定义。

策略可以被应用于特定的命令或者特定的角色。除非特别指定，新创建的策略的默认行为是适用于所有命令和角色。多个策略可以应用于单个命令，更多细节请见下文。表 287总结了不同类型的策略如何应用于特定的命令。

对同时具有USING和WITH CHECK 表达式（ALL和UPDATE）的策略，如果没有定义WITH CHECK表达式，那么 USING表达式将被用于决定哪些行可见（普通 USING情况）以及允许哪些新行被增加（ WITH CHECK情况）。

如果为一个表启用了行级安全性但是没有适用的策略存在，将假定为一种 “默认否定”策略，这样任何行都不可见也不可更新。

参数

name

要创建的策略的名称。这必须和该表上已有的任何其他策略名称相区分。

table_name

该策略适用的表的名称（可以被模式限定）。

PERMISSIVE

指定策略被创建为宽容性策略。适用于一个给定查询的所有宽容性策略将被使用布尔“OR”操作符组合在一起。通过创建宽容性策略，管理员可以在能被访问的记录集合中进行增加。策略默认是宽容性的。

RESTRICTIVE

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

注意在限制性策略真正能发挥作用减少访问之前，需要至少一条宽容性策略来授予对记录的访问。如果只有限制性策略存在，则没有记录能被访问。当宽容性和限制性策略混合存在时，只有当一个记录能通过至少一条宽容性策略以及所有的限制性策略时，该记录才是可访问的。

command

该策略适用的命令。合法的选项是 ALL、SELECT、 INSERT、UPDATE 以及DELETE。 ALL为默认。有关这些策略如何被应用的细节见下文。

role_name

该策略适用的角色。默认是PUBLIC，它将把策略应用到所有的角色。

using_expression

任意的SQL条件表达式（返回 boolean）。该条件表达式不能包含任何聚集或者窗口函数。如果行级安全性被启用，这个表达式将被增加到引用该表的查询。让这个表达式返回真的行将可见。让这个表达式返回假或者空的任何行将对用户不可见（在SELECT中）并且将对修改不可用（在UPDATE或DELETE中）。这类行会被悄悄地禁止而不会报告错误。

check_expression

任意的SQL条件表达式（返回 boolean）。该条件表达式不能包含任何聚集或者窗口函数。如果行级安全性被启用，这个表达式将被用在该表上的 INSERT以及 UPDATE查询中。只有让该表达式计算为真的行才被允许。如果任何被插入的记录或者跟新后的记录导致该表达式计算为假或者空，则会抛出一个错误。注意 check_expression 是根据行的新内容而不是原始内容计算的。

针对每种命令的策略

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动作和MERGE命令的INSERT命令。插入的行如果不符合该策略，将导致策略违规错误，并且整个INSERT命令将被中止。 INSERT策略不能有USING表达式，因为它仅适用于向关系添加记录的情况。

注意在带有ON CONFLICT DO UPDATE的INSERT中，只有对通过 INSERT路径追加到关系的行才会检查 INSERT策略的WITH CHECK 表达式。

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路径永不会静默地避免）。

DELETE

为一条策略使用DELETE表示它适用于 DELETE命令。只有通过这条策略的行才将能被 DELETE命令所看到。如果有的行不能通过该 DELETE策略的USING表达式，则它们可以通过SELECT看到但不能被删除。

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

DELETE策略不能具有WITH CHECK表达式，因为它只适用于正在从关系中删除记录的情况，所以没有新行需要检查。

表 287. 按命令类型应用的策略

命令	`SELECT/ALL策略`	`INSERT/ALL策略`	`UPDATE/ALL策略`		`DELETE/ALL策略`
命令	`USING expression`	`WITH CHECK expression`	`USING expression`	`WITH CHECK expression`	`USING expression`
`SELECT`	现有行	—	—	—	—
`SELECT FOR UPDATE/SHARE`	现有行	—	现有行	—	—
`INSERT` / `MERGE ... THEN INSERT`	—	新行	—	—	—
`INSERT ... RETURNING`	新行^[a]	新行	—	—	—
`UPDATE` / `MERGE ... THEN UPDATE`	现有&新行^[a]	—	现有行	新行	—
`DELETE`	现有行^[a]	—	—	—	现有行
`ON CONFLICT DO UPDATE`	现有 & 新行	—	现有行	新行	—
^[a] 对于现有行或新行，如果需要读访问的话（例如涉及到关系内列的`WHERE`或`RETURNING`子句）。

多重策略的应用

当多种不同命令类型的策略应用于相同命令（例如SELECT和UPDATE策略应用于UPDATE命令）时，用户就必须同时具有这两种类型的权限（例如从关系中选取行和更新的权限）。这样一种策略类型的表达式就与另一种策略类型的表达式通过使用AND操作符组合在一起。

当相同命令类型的多种策略应用于同一命令时，则必须至少有一个PERMISSIVE策略授权对该关系的访问，所有的RESTRICTIVE策略必须通过。这样，所有的PERMISSIVE策略表达式都用OR来组合，所有的RESTRICTIVE策略表达式都用AND来组合，而结果用AND来组合。如果没有PERMISSIVE策略，则拒绝访问。

要注意的是，出于组合多种策略的目的，将ALL策略视为与所应用的任何其他类型的策略具有相同的类型。

例如，在UPDATE命令中，SELECT和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
  ...
)

注解

要为一个表创建或者修改策略，你必须是该表的拥有者。

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

通常，系统将在应用用户查询中出现的条件之前先强制由安全性策略施加的过滤条件，这是为了防止无意中把受保护的数据暴露给可能不可信的用户定义函数。不过，被系统（或者系统管理员）标记为 LEAKPROOF的函数和操作符可以在策略表达式之前被计算，因为它们已经被假定为可信。

由于策略表达式直接添加到用户的查询中，它们将以运行整体查询的用户的权限运行。因此，使用给定策略的用户必须能够访问表达式中引用的任何表或函数，否则当尝试查询启用了行级安全性的表时，他们将仅收到权限被拒绝的错误。然而，这并不改变视图的工作方式。与普通查询和视图一样，引用视图的表的权限检查和策略将使用视图所有者的权限以及适用于视图所有者的任何策略，除非视图是使用security_invoker选项定义的（请参见CREATE VIEW）。

MERGE没有单独的策略。相反，在执行MERGE时，应用为SELECT、 INSERT、UPDATE和DELETE定义的策略，具体取决于执行的操作。

在第 5.8 节中可以找到额外的讨论和实际的例子。

兼容性

CREATE POLICY是一种PostgreSQL扩展。

另见

ALTER POLICY, DROP POLICY, ALTER TABLE