diff options
Diffstat (limited to 'doc/src/sgml/man7/CREATE_POLICY.7')
| -rw-r--r-- | doc/src/sgml/man7/CREATE_POLICY.7 | 655 |
1 files changed, 655 insertions, 0 deletions
diff --git a/doc/src/sgml/man7/CREATE_POLICY.7 b/doc/src/sgml/man7/CREATE_POLICY.7 new file mode 100644 index 0000000..735db02 --- /dev/null +++ b/doc/src/sgml/man7/CREATE_POLICY.7 @@ -0,0 +1,655 @@ +'\" t +.\" Title: CREATE POLICY +.\" Author: The PostgreSQL Global Development Group +.\" Generator: DocBook XSL Stylesheets vsnapshot <http://docbook.sf.net/> +.\" Date: 2023 +.\" Manual: PostgreSQL 15.4 Documentation +.\" Source: PostgreSQL 15.4 +.\" Language: English +.\" +.TH "CREATE POLICY" "7" "2023" "PostgreSQL 15.4" "PostgreSQL 15.4 Documentation" +.\" ----------------------------------------------------------------- +.\" * Define some portability stuff +.\" ----------------------------------------------------------------- +.\" ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ +.\" http://bugs.debian.org/507673 +.\" http://lists.gnu.org/archive/html/groff/2009-02/msg00013.html +.\" ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ +.ie \n(.g .ds Aq \(aq +.el .ds Aq ' +.\" ----------------------------------------------------------------- +.\" * set default formatting +.\" ----------------------------------------------------------------- +.\" disable hyphenation +.nh +.\" disable justification (adjust text to left margin only) +.ad l +.\" ----------------------------------------------------------------- +.\" * MAIN CONTENT STARTS HERE * +.\" ----------------------------------------------------------------- +.SH "NAME" +CREATE_POLICY \- define a new row\-level security policy for a table +.SH "SYNOPSIS" +.sp +.nf +CREATE POLICY \fIname\fR ON \fItable_name\fR + [ AS { PERMISSIVE | RESTRICTIVE } ] + [ FOR { ALL | SELECT | INSERT | UPDATE | DELETE } ] + [ TO { \fIrole_name\fR | PUBLIC | CURRENT_ROLE | CURRENT_USER | SESSION_USER } [, \&.\&.\&.] ] + [ USING ( \fIusing_expression\fR ) ] + [ WITH CHECK ( \fIcheck_expression\fR ) ] +.fi +.SH "DESCRIPTION" +.PP +The +\fBCREATE POLICY\fR +command defines a new row\-level security policy for a table\&. Note that row\-level security must be enabled on the table (using +\fBALTER TABLE \&.\&.\&. ENABLE ROW LEVEL SECURITY\fR) in order for created policies to be applied\&. +.PP +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\&. +.PP +For +\fBINSERT\fR, +\fBUPDATE\fR, and +\fBMERGE\fR +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\&. +.PP +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\&. +.PP +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\ \&287 +summarizes how the different types of policy apply to specific commands\&. +.PP +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)\&. +.PP +If row\-level security is enabled for a table, but no applicable policies exist, a +\(lqdefault deny\(rq +policy is assumed, so that no rows will be visible or updatable\&. +.SH "PARAMETERS" +.PP +\fIname\fR +.RS 4 +The name of the policy to be created\&. This must be distinct from the name of any other policy for the table\&. +.RE +.PP +\fItable_name\fR +.RS 4 +The name (optionally schema\-qualified) of the table the policy applies to\&. +.RE +.PP +PERMISSIVE +.RS 4 +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 +\(lqOR\(rq +operator\&. By creating permissive policies, administrators can add to the set of records which can be accessed\&. Policies are permissive by default\&. +.RE +.PP +RESTRICTIVE +.RS 4 +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 +\(lqAND\(rq +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\&. +.sp +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\&. +.RE +.PP +\fIcommand\fR +.RS 4 +The command to which the policy applies\&. Valid options are +\fBALL\fR, +\fBSELECT\fR, +\fBINSERT\fR, +\fBUPDATE\fR, and +\fBDELETE\fR\&. +\fBALL\fR +is the default\&. See below for specifics regarding how these are applied\&. +.RE +.PP +\fIrole_name\fR +.RS 4 +The role(s) to which the policy is to be applied\&. The default is +PUBLIC, which will apply the policy to all roles\&. +.RE +.PP +\fIusing_expression\fR +.RS 4 +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 +\fBSELECT\fR), and will not be available for modification (in an +\fBUPDATE\fR +or +\fBDELETE\fR)\&. Such rows are silently suppressed; no error is reported\&. +.RE +.PP +\fIcheck_expression\fR +.RS 4 +Any +SQL +conditional expression (returning +boolean)\&. The conditional expression cannot contain any aggregate or window functions\&. This expression will be used in +\fBINSERT\fR +and +\fBUPDATE\fR +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 +\fIcheck_expression\fR +is evaluated against the proposed new contents of the row, not the original contents\&. +.RE +.SS "Per\-Command Policies" +.PP +ALL +.RS 4 +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\&. +.sp +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 +\fBINSERT\fR +or +\fBUPDATE\fR +command attempts to add rows to the table that do not pass the +ALL +policy\*(Aqs +WITH CHECK +expression, the entire command will be aborted\&. +.RE +.PP +SELECT +.RS 4 +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\&. +.RE +.PP +INSERT +.RS 4 +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\&. +.sp +Note that +INSERT +with +ON CONFLICT DO UPDATE +checks +INSERT +policies\*(Aq +WITH CHECK +expressions only for rows appended to the relation by the +INSERT +path\&. +.RE +.PP +UPDATE +.RS 4 +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\&. +.sp +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\&. +.sp +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\&. +.sp +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 +\fInever\fR +be silently avoided)\&. +.RE +.PP +DELETE +.RS 4 +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\&. +.sp +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\&. +.sp +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\&. +.RE +.sp +.it 1 an-trap +.nr an-no-space-flag 1 +.nr an-break-flag 1 +.br +.B Table\ \&287.\ \&Policies Applied by Command Type +.TS +allbox tab(:); +lB lB lB lB s lB +^ lB lB lB lB lB. +T{ +Command +T}:T{ +SELECT/ALL policy +T}:T{ +INSERT/ALL policy +T}:T{ +UPDATE/ALL policy +T}:T{ +DELETE/ALL policy +T} +:T{ +USING expression +T}:T{ +WITH CHECK expression +T}:T{ +USING expression +T}:T{ +WITH CHECK expression +T}:T{ +USING expression +T} +.T& +l l l l l l +l l l l l l +l l l l l l +l l l l l l +l l l l l l +l l l l l l +l l l l l l +l s s s s s. +T{ +\fBSELECT\fR +T}:T{ +Existing row +T}:T{ +\(em +T}:T{ +\(em +T}:T{ +\(em +T}:T{ +\(em +T} +T{ +\fBSELECT FOR UPDATE/SHARE\fR +T}:T{ +Existing row +T}:T{ +\(em +T}:T{ +Existing row +T}:T{ +\(em +T}:T{ +\(em +T} +T{ +\fBINSERT\fR / \fBMERGE \&.\&.\&. THEN INSERT\fR +T}:T{ +\(em +T}:T{ +New row +T}:T{ +\(em +T}:T{ +\(em +T}:T{ +\(em +T} +T{ +\fBINSERT \&.\&.\&. RETURNING\fR +T}:T{ +New row [a] +T}:T{ +New row +T}:T{ +\(em +T}:T{ +\(em +T}:T{ +\(em +T} +T{ +\fBUPDATE\fR / \fBMERGE \&.\&.\&. THEN UPDATE\fR +T}:T{ +Existing & new rows [a] +T}:T{ +\(em +T}:T{ +Existing row +T}:T{ +New row +T}:T{ +\(em +T} +T{ +\fBDELETE\fR +T}:T{ +Existing row [a] +T}:T{ +\(em +T}:T{ +\(em +T}:T{ +\(em +T}:T{ +Existing row +T} +T{ +\fBON CONFLICT DO UPDATE\fR +T}:T{ +Existing & new rows +T}:T{ +\(em +T}:T{ +Existing row +T}:T{ +New row +T}:T{ +\(em +T} +T{ +---- +.br +[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)\&. +T} +.TE +.sp 1 +.SS "Application of Multiple Policies" +.PP +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\&. +.PP +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\&. +.PP +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\&. +.PP +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: +.sp +.if n \{\ +.RS 4 +.\} +.nf +\fIexpression\fR from RESTRICTIVE SELECT/ALL policy 1 +AND +\fIexpression\fR from RESTRICTIVE SELECT/ALL policy 2 +AND +\&.\&.\&. +AND +( + \fIexpression\fR from PERMISSIVE SELECT/ALL policy 1 + OR + \fIexpression\fR from PERMISSIVE SELECT/ALL policy 2 + OR + \&.\&.\&. +) +AND +\fIexpression\fR from RESTRICTIVE UPDATE/ALL policy 1 +AND +\fIexpression\fR from RESTRICTIVE UPDATE/ALL policy 2 +AND +\&.\&.\&. +AND +( + \fIexpression\fR from PERMISSIVE UPDATE/ALL policy 1 + OR + \fIexpression\fR from PERMISSIVE UPDATE/ALL policy 2 + OR + \&.\&.\&. +) +.fi +.if n \{\ +.RE +.\} +.SH "NOTES" +.PP +You must be the owner of a table to create or change policies for it\&. +.PP +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\&. +.PP +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\&. +.PP +Since policy expressions are added to the user\*(Aqs 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\*(Aqs rights and any policies which apply to the view owner, except if the view is defined using the +security_invoker +option (see +\fBCREATE VIEW\fR)\&. +.PP +No separate policy exists for +\fBMERGE\fR\&. Instead, the policies defined for +\fBSELECT\fR, +\fBINSERT\fR, +\fBUPDATE\fR, and +\fBDELETE\fR +are applied while executing +\fBMERGE\fR, depending on the actions that are performed\&. +.PP +Additional discussion and practical examples can be found in +Section\ \&5.8\&. +.SH "COMPATIBILITY" +.PP +\fBCREATE POLICY\fR +is a +PostgreSQL +extension\&. +.SH "SEE ALSO" +ALTER POLICY (\fBALTER_POLICY\fR(7)), DROP POLICY (\fBDROP_POLICY\fR(7)), ALTER TABLE (\fBALTER_TABLE\fR(7)) |