CREATE POLICY

CREATE POLICY¶

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

语法

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

描述

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

一条策略授予权限以选择、插入、更新或者删除匹配相关策略表达式的行。现有的表行会按照USING中指定的表达式进行检查，而将要通过INSERT或UPDATE创建的新行会按照WITH CHECK中指定的表达式进行检查。

当创建策略时显示指定column_name时，没有指定的列还会正常显示，指定的列会根据USING表达式的结果显示为原值或NULL，USING表达式的结果为真时显示原值，USING表达式的结果为假或空时显示为NULL。

当创建策略时没有指定column_name时，安全策略针对整行，当USING表达式对于一个给定行返回真时，该行对用户可见，而返回假或空时该行不可见。当一个WITH CHECK表达式对一行返回真时，该行会被插入或更新，而如果返回假或空时会发生一个错误。

注意：column_name指定只对USING ( using_expression )有效.

对于INSERT和 UPDATE语句，在BEFORE 触发器被引发后并且在任何数据修改真正发生之前， WITH CHECK表达式会被强制。因此，一个BEFORE ROW触发器可以修改要被插入的数据，从而影响安全性策略检查的结果。WITH CHECK表达式在任何其他约束之前被强制。

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

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

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

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

参数

该SQL命令参数说明见下

name

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

table_name

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

column_name

该策略适用的列的名称。

PERMISSIVE

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

RESTRICTIVE

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

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

command

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

role_name

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

using_expression

任意的SQL条件表达式（返回 boolean）。该条件表达式不能包含任何聚集或者窗口函数。如果行级安全性被启用，这个表达式将被增加到引用该表的查询中，根据是否存在column_name决定是放在where中还是case表达式中。

让这个表达式返回真的行或列将可见。让这个表达式返回假或者空的任何行或列将对用户不可见（在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命令。没有通过这种策略的正在被插入的行会导致策略违背错误，并且整个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需要提取现有的记录并且用新修改的记录代替，故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表达式，因为它只适用于正在从关系中删除记录的情况，所以没有新行需要检查。

表按命令类型应用的策略

命令	USING表达式（SELECT/ALL策略）	WITH CHECK表达式（INSERT/ALL策略）	USING表达式（UPDATE/ALL策略）	WITH CHECK表达式（UPDATE/ALL策略）	USING表达式（DELETE/ALL策略）
SELECT	现有行	—	—	—	—
SELECT FOR UPDATE/SHARE	现有行	—	现有行	—	—
INSERT	—	新行	—	—	—
INSERT … RETURNING	新行	新行	—	—	—
UPDATE	现有 & 新行	—	现有行	新行	—
DELETE	现有行	—	—	—	现有行
ON CONFLICT DO UPDATE	现有 & 新行	—	现有行	新行	—

对于现有行或新行，如果需要读访问的话（例如涉及到关系内列的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的函数和操作符可以在策略表达式之前被计算，因为它们已经被假定为可信。

因为策略表达式会被直接加到用户查询上，它们将使用运行整个查询的用户的权限运行。因此，使用一条给定策略的用户必须能够访问表达式中引用的任何表或函数，否则在尝试查询启用了行级安全性的表时，他们将简单地收到一条没有权限的错误。不过，这不会改变视图的工作方式。就普通查询和视图来说，权限检查和视图所引用的表的策略将使用视图拥有者的权限以及任何适用于视图拥有者的策略。

在"行安全性策略"中可以找到额外的讨论和实际的例子。

兼容性说明

CREATE POLICY是一种SeaboxSQL扩展。

相关SQL命令

ALTER POLICY, DROP POLICY, ALTER TABLE