Postgresql 中文操作指南

CREATE POLICY

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

CREATE POLICY — define a new row-level security policy for a table

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 ），才能应用已创建的策略。

The CREATE POLICY command defines a new row-level security policy for a table. Note that row-level security must be enabled on the table (using ALTER TABLE …​ ENABLE ROW LEVEL SECURITY) in order for created policies to be applied.

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

A policy grants the permission to select, insert, update, or delete rows that match the relevant policy expression. Existing table rows are checked against the expression specified in USING, while new rows that would be created via INSERT or UPDATE are checked against the expression specified in WITH CHECK. When a USING expression returns true for a given row then that row is visible to the user, while if false or null is returned then the row is not visible. When a WITH CHECK expression returns true for a row then that row is inserted or updated, while if false or null is returned then an error occurs.

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

For INSERT, UPDATE, and MERGE statements, WITH CHECK expressions are enforced after BEFORE triggers are fired, and before any actual data modifications are made. Thus a BEFORE ROW trigger may modify the data to be inserted, affecting the result of the security policy check. WITH CHECK expressions are enforced before any other constraints.

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

Policy names are per-table. Therefore, one policy name can be used for many different tables and have a definition for each table which is appropriate to that table.

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

Policies can be applied for specific commands or for specific roles. The default for newly created policies is that they apply for all commands and roles, unless otherwise specified. Multiple policies may apply to a single command; see below for more details. Table 292 summarizes how the different types of policy apply to specific commands.

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

For policies that can have both USING and WITH CHECK expressions (ALL and UPDATE), if no WITH CHECK expression is defined, then the USING expression will be used both to determine which rows are visible (normal USING case) and which new rows will be allowed to be added (WITH CHECK case).

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

If row-level security is enabled for a table, but no applicable policies exist, a “default deny” policy is assumed, so that no rows will be visible or updatable.

Parameters

  • name

    • The name of the policy to be created. This must be distinct from the name of any other policy for the table.

  • table_name

    • The name (optionally schema-qualified) of the table the policy applies to.

  • PERMISSIVE

    • Specify that the policy is to be created as a permissive policy. All permissive policies which are applicable to a given query will be combined together using the Boolean “OR” operator. By creating permissive policies, administrators can add to the set of records which can be accessed. Policies are permissive by default.

  • RESTRICTIVE

    • Specify that the policy is to be created as a restrictive policy. All restrictive policies which are applicable to a given query will be combined together using the Boolean “AND” operator. By creating restrictive policies, administrators can reduce the set of records which can be accessed as all restrictive policies must be passed for each record.

    • Note that there needs to be at least one permissive policy to grant access to records before restrictive policies can be usefully used to reduce that access. If only restrictive policies exist, then no records will be accessible. When a mix of permissive and restrictive policies are present, a record is only accessible if at least one of the permissive policies passes, in addition to all the restrictive policies.

  • command

    • The command to which the policy applies. Valid options are ALL, SELECT, INSERT, UPDATE, and DELETE. ALL is the default. See below for specifics regarding how these are applied.

  • role_name

    • The role(s) to which the policy is to be applied. The default is PUBLIC, which will apply the policy to all roles.

  • using_expression

    • Any SQL conditional expression (returning boolean). The conditional expression cannot contain any aggregate or window functions. This expression will be added to queries that refer to the table if row-level security is enabled. Rows for which the expression returns true will be visible. Any rows for which the expression returns false or null will not be visible to the user (in a SELECT), and will not be available for modification (in an UPDATE or DELETE). Such rows are silently suppressed; no error is reported.

  • check_expression

    • Any SQL conditional expression (returning boolean). The conditional expression cannot contain any aggregate or window functions. This expression will be used in INSERT and UPDATE queries against the table if row-level security is enabled. Only rows for which the expression evaluates to true will be allowed. An error will be thrown if the expression evaluates to false or null for any of the records inserted or any of the records that result from the update. Note that the check_expression is evaluated against the proposed new contents of the row, not the original contents.

Per-Command Policies

  • ALL #

    • Using ALL for a policy means that it will apply to all commands, regardless of the type of command. If an ALL policy exists and more specific policies exist, then both the ALL policy and the more specific policy (or policies) will be applied. Additionally, ALL policies will be applied to both the selection side of a query and the modification side, using the USING expression for both cases if only a USING expression has been defined.

    • As an example, if an UPDATE is issued, then the ALL policy will be applicable both to what the UPDATE will be able to select as rows to be updated (applying the USING expression), and to the resulting updated rows, to check if they are permitted to be added to the table (applying the WITH CHECK expression, if defined, and the USING expression otherwise). If an INSERT or UPDATE command attempts to add rows to the table that do not pass the ALL policy’s WITH CHECK expression, the entire command will be aborted.

  • SELECT #

    • Using SELECT for a policy means that it will apply to SELECT queries and whenever SELECT permissions are required on the relation the policy is defined for. The result is that only those records from the relation that pass the SELECT policy will be returned during a SELECT query, and that queries that require SELECT permissions, such as UPDATE, will also only see those records that are allowed by the SELECT policy. A SELECT policy cannot have a WITH CHECK expression, as it only applies in cases where records are being retrieved from the relation.

  • INSERT #

    • Using INSERT for a policy means that it will apply to INSERT commands and MERGE commands that contain INSERT actions. Rows being inserted that do not pass this policy will result in a policy violation error, and the entire INSERT command will be aborted. An INSERT policy cannot have a USING expression, as it only applies in cases where records are being added to the relation.

    • Note that INSERT with ON CONFLICT DO UPDATE checks INSERT policies' WITH CHECK expressions only for rows appended to the relation by the INSERT path.

  • UPDATE #

    • Using UPDATE for a policy means that it will apply to UPDATE, SELECT FOR UPDATE and SELECT FOR SHARE commands, as well as auxiliary ON CONFLICT DO UPDATE clauses of INSERT commands. MERGE commands containing UPDATE actions are affected as well. Since UPDATE involves pulling an existing record and replacing it with a new modified record, UPDATE policies accept both a USING expression and a WITH CHECK expression. The USING expression determines which records the UPDATE command will see to operate against, while the WITH CHECK expression defines which modified rows are allowed to be stored back into the relation.

    • Any rows whose updated values do not pass the WITH CHECK expression will cause an error, and the entire command will be aborted. If only a USING clause is specified, then that clause will be used for both USING and WITH CHECK cases.

    • Typically an UPDATE command also needs to read data from columns in the relation being updated (e.g., in a WHERE clause or a RETURNING clause, or in an expression on the right hand side of the SET clause). In this case, SELECT rights are also required on the relation being updated, and the appropriate SELECT or ALL policies will be applied in addition to the UPDATE policies. Thus the user must have access to the row(s) being updated through a SELECT or ALL policy in addition to being granted permission to update the row(s) via an UPDATE or ALL policy.

    • When an INSERT command has an auxiliary ON CONFLICT DO UPDATE clause, if the UPDATE path is taken, the row to be updated is first checked against the USING expressions of any UPDATE policies, and then the new updated row is checked against the WITH CHECK expressions. Note, however, that unlike a standalone UPDATE command, if the existing row does not pass the USING expressions, an error will be thrown (the UPDATE path will never be silently avoided).

  • DELETE #

    • Using DELETE for a policy means that it will apply to DELETE commands. Only rows that pass this policy will be seen by a DELETE command. There can be rows that are visible through a SELECT that are not available for deletion, if they do not pass the USING expression for the DELETE policy.

    • In most cases a DELETE command also needs to read data from columns in the relation that it is deleting from (e.g., in a WHERE clause or a RETURNING clause). In this case, SELECT rights are also required on the relation, and the appropriate SELECT or ALL policies will be applied in addition to the DELETE policies. Thus the user must have access to the row(s) being deleted through a SELECT or ALL policy in addition to being granted permission to delete the row(s) via a DELETE or ALL policy.

    • A DELETE policy cannot have a WITH CHECK expression, as it only applies in cases where records are being deleted from the relation, so that there is no new row to check.

Table 292. Policies Applied by Command Type

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

Existing & new rows sql-createpolicy.html#ftn.RLS-SELECT-PRIV

—

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 子句）。

#RLS-SELECT-PRIV If read access is required to the existing or new row (for example, a WHERE or RETURNING clause that refers to columns from the relation).

Application of Multiple Policies

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

When multiple policies of different command types apply to the same command (for example, SELECT and UPDATE policies applied to an UPDATE command), then the user must have both types of permissions (for example, permission to select rows from the relation as well as permission to update them). Thus the expressions for one type of policy are combined with the expressions for the other type of policy using the AND operator.

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

When multiple policies of the same command type apply to the same command, then there must be at least one PERMISSIVE policy granting access to the relation, and all of the RESTRICTIVE policies must pass. Thus all the PERMISSIVE policy expressions are combined using OR, all the RESTRICTIVE policy expressions are combined using AND, and the results are combined using AND. If there are no PERMISSIVE policies, then access is denied.

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

Note that, for the purposes of combining multiple policies, ALL policies are treated as having the same type as whichever other type of policy is being applied.

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

For example, in an UPDATE command requiring both SELECT and UPDATE permissions, if there are multiple applicable policies of each type, they will be combined as follows:

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

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

You must be the owner of a table to create or change policies for it.

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

While policies will be applied for explicit queries against tables in the database, they are not applied when the system is performing internal referential integrity checks or validating constraints. This means there are indirect ways to determine that a given value exists. An example of this is attempting to insert a duplicate value into a column that is a primary key or has a unique constraint. If the insert fails then the user can infer that the value already exists. (This example assumes that the user is permitted by policy to insert records which they are not allowed to see.) Another example is where a user is allowed to insert into a table which references another, otherwise hidden table. Existence can be determined by the user inserting values into the referencing table, where success would indicate that the value exists in the referenced table. These issues can be addressed by carefully crafting policies to prevent users from being able to insert, delete, or update records at all which might possibly indicate a value they are not otherwise able to see, or by using generated values (e.g., surrogate keys) instead of keys with external meanings.

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

Generally, the system will enforce filter conditions imposed using security policies prior to qualifications that appear in user queries, in order to prevent inadvertent exposure of the protected data to user-defined functions which might not be trustworthy. However, functions and operators marked by the system (or the system administrator) as LEAKPROOF may be evaluated before policy expressions, as they are assumed to be trustworthy.

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

Since policy expressions are added to the user’s query directly, they will be run with the rights of the user running the overall query. Therefore, users who are using a given policy must be able to access any tables or functions referenced in the expression or they will simply receive a permission denied error when attempting to query the table that has row-level security enabled. This does not change how views work, however. As with normal queries and views, permission checks and policies for the tables which are referenced by a view will use the view owner’s rights and any policies which apply to the view owner, except if the view is defined using the security_invoker option (see CREATE VIEW).

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

No separate policy exists for MERGE. Instead, the policies defined for SELECT, INSERT, UPDATE, and DELETE are applied while executing MERGE, depending on the actions that are performed.

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

Additional discussion and practical examples can be found in Section 5.8.

Compatibility

CREATE POLICY 是 PostgreSQL 扩展。

CREATE POLICY is a PostgreSQL extension.

See Also

ALTER POLICY 、 DROP POLICY 、 ALTER TABLE

ALTER POLICY, DROP POLICY, ALTER TABLE