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_ROLE | 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,UPDATE, andMERGE 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 297 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 andMERGE commands that containINSERT actions. 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.MERGE commands containingUPDATE actions are affected as well. 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 297. 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` /`MERGE ... THEN INSERT`	—	New row	—	—	—
`INSERT ... RETURNING`	New row^[a]	New row	—	—	—
`UPDATE` /`MERGE ... THEN 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  ...)

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.

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) asLEAKPROOF may be evaluated before policy expressions, as they are assumed to be trustworthy.

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 thesecurity_invoker option (seeCREATE VIEW).

No separate policy exists forMERGE. Instead, the policies defined forSELECT,INSERT,UPDATE, andDELETE are applied while executingMERGE, depending on the actions that are performed.

Additional discussion and practical examples can be found inSection 5.9.

Compatibility

CREATE POLICY is aPostgreSQL extension.