Movatterモバイル変換

CREATE POLICY
Prev	Up	SQL Commands	Home	Next

CREATE POLICY

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

Synopsis

CREATE POLICYname ONtable_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 ) ]

Description

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

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 inUSING, while new rows that would be created viaINSERT orUPDATE are checked against the expression specified inWITH CHECK. When aUSING 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 aWITH 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.

ForINSERT andUPDATE statements,WITH CHECK expressions are enforced afterBEFORE triggers are fired, and before any actual data modifications are made. Thus aBEFORE 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.

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 242 summarizes how the different types of policy apply to specific commands.

For policies that can have bothUSING andWITH CHECK expressions (ALL andUPDATE), if noWITH CHECK expression is defined, then theUSING expression will be used both to determine which rows are visible (normalUSING 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 areALL,SELECT,INSERT,UPDATE, andDELETE.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 isPUBLIC, which will apply the policy to all roles.

using_expression

AnySQL conditional expression (returningboolean). 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 aSELECT), and will not be available for modification (in anUPDATE orDELETE). Such rows are silently suppressed; no error is reported.

check_expression

Any SQL conditional expression (returningboolean). The conditional expression cannot contain any aggregate or window functions. This expression will be used inINSERT andUPDATE 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 thecheck_expression is evaluated against the proposed new contents of the row, not the original contents.

Per-Command Policies

ALL

UsingALL for a policy means that it will apply to all commands, regardless of the type of command. If anALL policy exists and more specific policies exist, then both theALL 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 theUSING expression for both cases if only aUSING expression has been defined.

As an example, if anUPDATE is issued, then theALL policy will be applicable both to what theUPDATE will be able to select as rows to be updated (applying theUSING expression), and to the resulting updated rows, to check if they are permitted to be added to the table (applying theWITH CHECK expression, if defined, and theUSING expression otherwise). If anINSERT orUPDATE command attempts to add rows to the table that do not pass theALL policy'sWITH CHECK expression, the entire command will be aborted.

SELECT

UsingSELECT for a policy means that it will apply toSELECT queries and wheneverSELECT permissions are required on the relation the policy is defined for. The result is that only those records from the relation that pass theSELECT policy will be returned during aSELECT query, and that queries that requireSELECT permissions, such asUPDATE, will also only see those records that are allowed by theSELECT policy. ASELECT policy cannot have aWITH CHECK expression, as it only applies in cases where records are being retrieved from the relation.

INSERT

UsingINSERT for a policy means that it will apply toINSERT commands. Rows being inserted that do not pass this policy will result in a policy violation error, and the entireINSERT command will be aborted. AnINSERT policy cannot have aUSING expression, as it only applies in cases where records are being added to the relation.

Note thatINSERT withON CONFLICT DO UPDATE checksINSERT policies'WITH CHECK expressions only for rows appended to the relation by theINSERT path.

UPDATE

UsingUPDATE for a policy means that it will apply toUPDATE,SELECT FOR UPDATE andSELECT FOR SHARE commands, as well as auxiliaryON CONFLICT DO UPDATE clauses ofINSERT commands. SinceUPDATE involves pulling an existing record and replacing it with a new modified record,UPDATE policies accept both aUSING expression and aWITH CHECK expression. TheUSING expression determines which records theUPDATE command will see to operate against, while theWITH CHECK expression defines which modified rows are allowed to be stored back into the relation.

Any rows whose updated values do not pass theWITH CHECK expression will cause an error, and the entire command will be aborted. If only aUSING clause is specified, then that clause will be used for bothUSING andWITH CHECK cases.

Typically anUPDATE command also needs to read data from columns in the relation being updated (e.g., in aWHERE clause or aRETURNING clause, or in an expression on the right hand side of theSET clause). In this case,SELECT rights are also required on the relation being updated, and the appropriateSELECT orALL policies will be applied in addition to theUPDATE policies. Thus the user must have access to the row(s) being updated through aSELECT orALL policy in addition to being granted permission to update the row(s) via anUPDATE orALL policy.

When anINSERT command has an auxiliaryON CONFLICT DO UPDATE clause, if theUPDATE path is taken, the row to be updated is first checked against theUSING expressions of anyUPDATE policies, and then the new updated row is checked against theWITH CHECK expressions. Note, however, that unlike a standaloneUPDATE command, if the existing row does not pass theUSING expressions, an error will be thrown (theUPDATE path willnever be silently avoided).

DELETE

UsingDELETE for a policy means that it will apply toDELETE commands. Only rows that pass this policy will be seen by aDELETE command. There can be rows that are visible through aSELECT that are not available for deletion, if they do not pass theUSING expression for theDELETE policy.

In most cases aDELETE command also needs to read data from columns in the relation that it is deleting from (e.g., in aWHERE clause or aRETURNING clause). In this case,SELECT rights are also required on the relation, and the appropriateSELECT orALL policies will be applied in addition to theDELETE policies. Thus the user must have access to the row(s) being deleted through aSELECT orALL policy in addition to being granted permission to delete the row(s) via aDELETE orALL policy.

ADELETE policy cannot have aWITH 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 242. Policies Applied by Command Type

Command	`SELECT/ALL policy`	`INSERT/ALL policy`	`UPDATE/ALL policy`		`DELETE/ALL policy`
Command	`USING expression`	`WITH CHECK expression`	`USING expression`	`WITH CHECK expression`	`USING expression`
`SELECT`	Existing row	—	—	—	—
`SELECT FOR UPDATE/SHARE`	Existing row	—	Existing row	—	—
`INSERT`	—	New row	—	—	—
`INSERT ... RETURNING`	New row^[a]	New row	—	—	—
`UPDATE`	Existing & new rows^[a]	—	Existing row	New row	—
`DELETE`	Existing row^[a]	—	—	—	Existing row
`ON CONFLICT DO UPDATE`	Existing & new rows	—	Existing row	New row	—
^[a] 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

When multiple policies of different command types apply to the same command (for example,SELECT andUPDATE policies applied to anUPDATE 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 theAND operator.

When multiple policies of the same command type apply to the same command, then there must be at least onePERMISSIVE policy granting access to the relation, and all of theRESTRICTIVE policies must pass. Thus all thePERMISSIVE policy expressions are combined usingOR, all theRESTRICTIVE policy expressions are combined usingAND, and the results are combined usingAND. If there are noPERMISSIVE policies, then access is denied.

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.

For example, in anUPDATE command requiring bothSELECT andUPDATE permissions, if there are multiple applicable policies of each type, they will be combined as follows:

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