1<!-- 2doc/src/sgml/ref/create_policy.sgml 3PostgreSQL documentation 4--> 5 6<refentry id="SQL-CREATEPOLICY"> 7 <indexterm zone="sql-createpolicy"> 8 <primary>CREATE POLICY</primary> 9 </indexterm> 10 11 <refmeta> 12 <refentrytitle>CREATE POLICY</refentrytitle> 13 <manvolnum>7</manvolnum> 14 <refmiscinfo>SQL - Language Statements</refmiscinfo> 15 </refmeta> 16 17 <refnamediv> 18 <refname>CREATE POLICY</refname> 19 <refpurpose>define a new row level security policy for a table</refpurpose> 20 </refnamediv> 21 22 <refsynopsisdiv> 23<synopsis> 24CREATE POLICY <replaceable class="parameter">name</replaceable> ON <replaceable class="parameter">table_name</replaceable> 25 [ AS { PERMISSIVE | RESTRICTIVE } ] 26 [ FOR { ALL | SELECT | INSERT | UPDATE | DELETE } ] 27 [ TO { <replaceable class="parameter">role_name</replaceable> | PUBLIC | CURRENT_USER | SESSION_USER } [, ...] ] 28 [ USING ( <replaceable class="parameter">using_expression</replaceable> ) ] 29 [ WITH CHECK ( <replaceable class="parameter">check_expression</replaceable> ) ] 30</synopsis> 31 </refsynopsisdiv> 32 33 <refsect1> 34 <title>Description</title> 35 36 <para> 37 The <command>CREATE POLICY</command> command defines a new row-level 38 security policy for a table. Note that row-level security must be 39 enabled on the table (using <command>ALTER TABLE ... ENABLE ROW LEVEL 40 SECURITY</command>) in order for created policies to be applied. 41 </para> 42 43 <para> 44 A policy grants the permission to select, insert, update, or delete rows 45 that match the relevant policy expression. Existing table rows are 46 checked against the expression specified in <literal>USING</literal>, 47 while new rows that would be created via <literal>INSERT</literal> 48 or <literal>UPDATE</literal> are checked against the expression specified 49 in <literal>WITH CHECK</literal>. When a <literal>USING</literal> 50 expression returns true for a given row then that row is visible to the 51 user, while if false or null is returned then the row is not visible. 52 When a <literal>WITH CHECK</literal> expression returns true for a row 53 then that row is inserted or updated, while if false or null is returned 54 then an error occurs. 55 </para> 56 57 <para> 58 For <command>INSERT</command> and <command>UPDATE</command> statements, 59 <literal>WITH CHECK</literal> expressions are enforced after 60 <literal>BEFORE</literal> triggers are fired, and before any actual data 61 modifications are made. Thus a <literal>BEFORE ROW</literal> trigger may 62 modify the data to be inserted, affecting the result of the security 63 policy check. <literal>WITH CHECK</literal> expressions are enforced 64 before any other constraints. 65 </para> 66 67 <para> 68 Policy names are per-table. Therefore, one policy name can be used for many 69 different tables and have a definition for each table which is appropriate to 70 that table. 71 </para> 72 73 <para> 74 Policies can be applied for specific commands or for specific roles. The 75 default for newly created policies is that they apply for all commands and 76 roles, unless otherwise specified. Multiple policies may apply to a single 77 command; see below for more details. 78 <xref linkend="sql-createpolicy-summary"> summarizes how the different types 79 of policy apply to specific commands. 80 </para> 81 82 <para> 83 For policies that can have both <literal>USING</literal> 84 and <literal>WITH CHECK</literal> expressions (<literal>ALL</literal> 85 and <literal>UPDATE</literal>), if no <literal>WITH CHECK</literal> 86 expression is defined, then the <literal>USING</literal> expression will be 87 used both to determine which rows are visible (normal 88 <literal>USING</literal> case) and which new rows will be allowed to be 89 added (<literal>WITH CHECK</literal> case). 90 </para> 91 92 <para> 93 If row-level security is enabled for a table, but no applicable policies 94 exist, a <quote>default deny</> policy is assumed, so that no rows will 95 be visible or updatable. 96 </para> 97 </refsect1> 98 99 <refsect1> 100 <title>Parameters</title> 101 102 <variablelist> 103 <varlistentry> 104 <term><replaceable class="parameter">name</replaceable></term> 105 <listitem> 106 <para> 107 The name of the policy to be created. This must be distinct from the 108 name of any other policy for the table. 109 </para> 110 </listitem> 111 </varlistentry> 112 113 <varlistentry> 114 <term><replaceable class="parameter">table_name</replaceable></term> 115 <listitem> 116 <para> 117 The name (optionally schema-qualified) of the table the 118 policy applies to. 119 </para> 120 </listitem> 121 </varlistentry> 122 123 <varlistentry> 124 <term><literal>PERMISSIVE</literal></term> 125 <listitem> 126 <para> 127 Specify that the policy is to be created as a permissive policy. 128 All permissive policies which are applicable to a given query will 129 be combined together using the Boolean <quote>OR</quote> operator. By creating 130 permissive policies, administrators can add to the set of records 131 which can be accessed. Policies are permissive by default. 132 </para> 133 </listitem> 134 </varlistentry> 135 136 <varlistentry> 137 <term><literal>RESTRICTIVE</literal></term> 138 <listitem> 139 <para> 140 Specify that the policy is to be created as a restrictive policy. 141 All restrictive policies which are applicable to a given query will 142 be combined together using the Boolean <quote>AND</quote> operator. By creating 143 restrictive policies, administrators can reduce the set of records 144 which can be accessed as all restrictive policies must be passed for 145 each record. 146 </para> 147 148 <para> 149 Note that there needs to be at least one permissive policy to grant 150 access to records before restrictive policies can be usefully used to 151 reduce that access. If only restrictive policies exist, then no records 152 will be accessible. When a mix of permissive and restrictive policies 153 are present, a record is only accessible if at least one of the 154 permissive policies passes, in addition to all the restrictive 155 policies. 156 </para> 157 </listitem> 158 </varlistentry> 159 160 <varlistentry> 161 <term><replaceable class="parameter">command</replaceable></term> 162 <listitem> 163 <para> 164 The command to which the policy applies. Valid options are 165 <command>ALL</command>, <command>SELECT</command>, 166 <command>INSERT</command>, <command>UPDATE</command>, 167 and <command>DELETE</command>. 168 <command>ALL</command> is the default. 169 See below for specifics regarding how these are applied. 170 </para> 171 </listitem> 172 </varlistentry> 173 174 <varlistentry> 175 <term><replaceable class="parameter">role_name</replaceable></term> 176 <listitem> 177 <para> 178 The role(s) to which the policy is to be applied. The default is 179 <literal>PUBLIC</literal>, which will apply the policy to all roles. 180 </para> 181 </listitem> 182 </varlistentry> 183 184 <varlistentry> 185 <term><replaceable class="parameter">using_expression</replaceable></term> 186 <listitem> 187 <para> 188 Any <acronym>SQL</acronym> conditional expression (returning 189 <type>boolean</type>). The conditional expression cannot contain 190 any aggregate or window functions. This expression will be added 191 to queries that refer to the table if row level security is enabled. 192 Rows for which the expression returns true will be visible. Any 193 rows for which the expression returns false or null will not be 194 visible to the user (in a <command>SELECT</>), and will not be 195 available for modification (in an <command>UPDATE</> 196 or <command>DELETE</>). Such rows are silently suppressed; no error 197 is reported. 198 </para> 199 </listitem> 200 </varlistentry> 201 202 <varlistentry> 203 <term><replaceable class="parameter">check_expression</replaceable></term> 204 <listitem> 205 <para> 206 Any <acronym>SQL</acronym> conditional expression (returning 207 <type>boolean</type>). The conditional expression cannot contain 208 any aggregate or window functions. This expression will be used in 209 <command>INSERT</command> and <command>UPDATE</command> queries against 210 the table if row level security is enabled. Only rows for which the 211 expression evaluates to true will be allowed. An error will be thrown 212 if the expression evaluates to false or null for any of the records 213 inserted or any of the records that result from the update. Note that 214 the <replaceable class="parameter">check_expression</replaceable> is 215 evaluated against the proposed new contents of the row, not the 216 original contents. 217 </para> 218 </listitem> 219 </varlistentry> 220 221 </variablelist> 222 223 <refsect2> 224 <title>Per-Command Policies</title> 225 226 <variablelist> 227 228 <varlistentry id="SQL-CREATEPOLICY-ALL"> 229 <term><literal>ALL</></term> 230 <listitem> 231 <para> 232 Using <literal>ALL</literal> for a policy means that it will apply 233 to all commands, regardless of the type of command. If an 234 <literal>ALL</literal> policy exists and more specific policies 235 exist, then both the <literal>ALL</literal> policy and the more 236 specific policy (or policies) will be applied. 237 Additionally, <literal>ALL</literal> policies will be applied to 238 both the selection side of a query and the modification side, using 239 the <literal>USING</literal> expression for both cases if only 240 a <literal>USING</literal> expression has been defined. 241 </para> 242 <para> 243 As an example, if an <literal>UPDATE</literal> is issued, then the 244 <literal>ALL</literal> policy will be applicable both to what the 245 <literal>UPDATE</literal> will be able to select as rows to be 246 updated (applying the <literal>USING</literal> expression), 247 and to the resulting updated rows, to check if they are permitted 248 to be added to the table (applying the <literal>WITH CHECK</literal> 249 expression, if defined, and the <literal>USING</literal> expression 250 otherwise). If an <command>INSERT</command> 251 or <command>UPDATE</command> command attempts to add rows to the 252 table that do not pass the <literal>ALL</literal> 253 policy's <literal>WITH CHECK</literal> expression, the entire 254 command will be aborted. 255 </para> 256 </listitem> 257 </varlistentry> 258 259 <varlistentry id="SQL-CREATEPOLICY-SELECT"> 260 <term><literal>SELECT</></term> 261 <listitem> 262 <para> 263 Using <literal>SELECT</literal> for a policy means that it will apply 264 to <literal>SELECT</literal> queries and whenever 265 <literal>SELECT</literal> permissions are required on the relation the 266 policy is defined for. The result is that only those records from the 267 relation that pass the <literal>SELECT</literal> policy will be 268 returned during a <literal>SELECT</literal> query, and that queries 269 that require <literal>SELECT</literal> permissions, such as 270 <literal>UPDATE</literal>, will also only see those records 271 that are allowed by the <literal>SELECT</literal> policy. 272 A <literal>SELECT</literal> policy cannot have a <literal>WITH 273 CHECK</literal> expression, as it only applies in cases where 274 records are being retrieved from the relation. 275 </para> 276 </listitem> 277 </varlistentry> 278 279 <varlistentry id="SQL-CREATEPOLICY-INSERT"> 280 <term><literal>INSERT</></term> 281 <listitem> 282 <para> 283 Using <literal>INSERT</literal> for a policy means that it will apply 284 to <literal>INSERT</literal> commands. Rows being inserted that do 285 not pass this policy will result in a policy violation error, and the 286 entire <literal>INSERT</literal> command will be aborted. 287 An <literal>INSERT</literal> policy cannot have 288 a <literal>USING</literal> expression, as it only applies in cases 289 where records are being added to the relation. 290 </para> 291 <para> 292 Note that <literal>INSERT</literal> with <literal>ON CONFLICT DO 293 UPDATE</literal> checks <literal>INSERT</literal> policies' 294 <literal>WITH CHECK</literal> expressions only for rows appended 295 to the relation by the <literal>INSERT</literal> path. 296 </para> 297 </listitem> 298 </varlistentry> 299 300 <varlistentry id="SQL-CREATEPOLICY-UPDATE"> 301 <term><literal>UPDATE</></term> 302 <listitem> 303 <para> 304 Using <literal>UPDATE</literal> for a policy means that it will apply 305 to <literal>UPDATE</literal>, <literal>SELECT FOR UPDATE</literal> 306 and <literal>SELECT FOR SHARE</literal> commands, as well as 307 auxiliary <literal>ON CONFLICT DO UPDATE</literal> clauses of 308 <literal>INSERT</literal> commands. Since <literal>UPDATE</literal> 309 involves pulling an existing record and replacing it with a new 310 modified record, <literal>UPDATE</literal> 311 policies accept both a <literal>USING</literal> expression and 312 a <literal>WITH CHECK</literal> expression. 313 The <literal>USING</literal> expression determines which records 314 the <literal>UPDATE</literal> command will see to operate against, 315 while the <literal>WITH CHECK</literal> expression defines which 316 modified rows are allowed to be stored back into the relation. 317 </para> 318 319 <para> 320 Any rows whose updated values do not pass the 321 <literal>WITH CHECK</literal> expression will cause an error, and the 322 entire command will be aborted. If only a <literal>USING</literal> 323 clause is specified, then that clause will be used for both 324 <literal>USING</literal> and <literal>WITH CHECK</literal> cases. 325 </para> 326 327 <para> 328 Typically an <literal>UPDATE</literal> command also needs to read 329 data from columns in the relation being updated (e.g., in a 330 <literal>WHERE</literal> clause or a <literal>RETURNING</literal> 331 clause, or in an expression on the right hand side of the 332 <literal>SET</literal> clause). In this case, 333 <literal>SELECT</literal> rights are also required on the relation 334 being updated, and the appropriate <literal>SELECT</literal> or 335 <literal>ALL</literal> policies will be applied in addition to 336 the <literal>UPDATE</literal> policies. Thus the user must have 337 access to the row(s) being updated through a <literal>SELECT</literal> 338 or <literal>ALL</literal> policy in addition to being granted 339 permission to update the row(s) via an <literal>UPDATE</literal> 340 or <literal>ALL</literal> policy. 341 </para> 342 343 <para> 344 When an <literal>INSERT</literal> command has an auxiliary 345 <literal>ON CONFLICT DO UPDATE</literal> clause, if the 346 <literal>UPDATE</literal> path is taken, the row to be updated is 347 first checked against the <literal>USING</literal> expressions of 348 any <literal>UPDATE</literal> policies, and then the new updated row 349 is checked against the <literal>WITH CHECK</literal> expressions. 350 Note, however, that unlike a standalone <literal>UPDATE</literal> 351 command, if the existing row does not pass the 352 <literal>USING</literal> expressions, an error will be thrown (the 353 <literal>UPDATE</literal> path will <emphasis>never</> be silently 354 avoided). 355 </para> 356 </listitem> 357 </varlistentry> 358 359 <varlistentry id="SQL-CREATEPOLICY-DELETE"> 360 <term><literal>DELETE</></term> 361 <listitem> 362 <para> 363 Using <literal>DELETE</literal> for a policy means that it will apply 364 to <literal>DELETE</literal> commands. Only rows that pass this 365 policy will be seen by a <literal>DELETE</literal> command. There can 366 be rows that are visible through a <literal>SELECT</literal> that are 367 not available for deletion, if they do not pass the 368 <literal>USING</literal> expression for 369 the <literal>DELETE</literal> policy. 370 </para> 371 372 <para> 373 In most cases a <literal>DELETE</literal> command also needs to read 374 data from columns in the relation that it is deleting from (e.g., 375 in a <literal>WHERE</literal> clause or a 376 <literal>RETURNING</literal> clause). In this case, 377 <literal>SELECT</literal> rights are also required on the relation, 378 and the appropriate <literal>SELECT</literal> or 379 <literal>ALL</literal> policies will be applied in addition to 380 the <literal>DELETE</literal> policies. Thus the user must have 381 access to the row(s) being deleted through a <literal>SELECT</literal> 382 or <literal>ALL</literal> policy in addition to being granted 383 permission to delete the row(s) via a <literal>DELETE</literal> or 384 <literal>ALL</literal> policy. 385 </para> 386 387 <para> 388 A <literal>DELETE</literal> policy cannot have a <literal>WITH 389 CHECK</literal> expression, as it only applies in cases where 390 records are being deleted from the relation, so that there is no 391 new row to check. 392 </para> 393 </listitem> 394 </varlistentry> 395 396 </variablelist> 397 398 <table id="sql-createpolicy-summary"> 399 <title>Policies Applied by Command Type</title> 400 <tgroup cols="6"> 401 <colspec colnum="4" colname="update-using"> 402 <colspec colnum="5" colname="update-check"> 403 <spanspec namest="update-using" nameend="update-check" spanname="update"> 404 <thead> 405 <row> 406 <entry morerows="1">Command</entry> 407 <entry><literal>SELECT/ALL policy</literal></entry> 408 <entry><literal>INSERT/ALL policy</literal></entry> 409 <entry spanname="update"><literal>UPDATE/ALL policy</literal></entry> 410 <entry><literal>DELETE/ALL policy</literal></entry> 411 </row> 412 <row> 413 <entry><literal>USING expression</literal></entry> 414 <entry><literal>WITH CHECK expression</literal></entry> 415 <entry><literal>USING expression</literal></entry> 416 <entry><literal>WITH CHECK expression</literal></entry> 417 <entry><literal>USING expression</literal></entry> 418 </row> 419 </thead> 420 <tbody> 421 <row> 422 <entry><command>SELECT</command></entry> 423 <entry>Existing row</entry> 424 <entry>—</entry> 425 <entry>—</entry> 426 <entry>—</entry> 427 <entry>—</entry> 428 </row> 429 <row> 430 <entry><command>SELECT FOR UPDATE/SHARE</command></entry> 431 <entry>Existing row</entry> 432 <entry>—</entry> 433 <entry>Existing row</entry> 434 <entry>—</entry> 435 <entry>—</entry> 436 </row> 437 <row> 438 <entry><command>INSERT</command></entry> 439 <entry>—</entry> 440 <entry>New row</entry> 441 <entry>—</entry> 442 <entry>—</entry> 443 <entry>—</entry> 444 </row> 445 <row> 446 <entry><command>INSERT ... RETURNING</command></entry> 447 <entry> 448 New row <footnote id="rls-select-priv"> 449 <para> 450 If read access is required to the existing or new row (for example, 451 a <literal>WHERE</literal> or <literal>RETURNING</literal> clause 452 that refers to columns from the relation). 453 </para> 454 </footnote> 455 </entry> 456 <entry>New row</entry> 457 <entry>—</entry> 458 <entry>—</entry> 459 <entry>—</entry> 460 </row> 461 <row> 462 <entry><command>UPDATE</command></entry> 463 <entry> 464 Existing & new rows <footnoteref linkend="rls-select-priv"> 465 </entry> 466 <entry>—</entry> 467 <entry>Existing row</entry> 468 <entry>New row</entry> 469 <entry>—</entry> 470 </row> 471 <row> 472 <entry><command>DELETE</command></entry> 473 <entry> 474 Existing row <footnoteref linkend="rls-select-priv"> 475 </entry> 476 <entry>—</entry> 477 <entry>—</entry> 478 <entry>—</entry> 479 <entry>Existing row</entry> 480 </row> 481 <row> 482 <entry><command>ON CONFLICT DO UPDATE</command></entry> 483 <entry>Existing & new rows</entry> 484 <entry>—</entry> 485 <entry>Existing row</entry> 486 <entry>New row</entry> 487 <entry>—</entry> 488 </row> 489 </tbody> 490 </tgroup> 491 </table> 492 493 </refsect2> 494 495 <refsect2> 496 <title>Application of Multiple Policies</title> 497 498 <para> 499 When multiple policies of different command types apply to the same command 500 (for example, <literal>SELECT</literal> and <literal>UPDATE</literal> 501 policies applied to an <literal>UPDATE</literal> command), then the user 502 must have both types of permissions (for example, permission to select rows 503 from the relation as well as permission to update them). Thus the 504 expressions for one type of policy are combined with the expressions for 505 the other type of policy using the <literal>AND</literal> operator. 506 </para> 507 508 <para> 509 When multiple policies of the same command type apply to the same command, 510 then there must be at least one <literal>PERMISSIVE</literal> policy 511 granting access to the relation, and all of the 512 <literal>RESTRICTIVE</literal> policies must pass. Thus all the 513 <literal>PERMISSIVE</literal> policy expressions are combined using 514 <literal>OR</literal>, all the <literal>RESTRICTIVE</literal> policy 515 expressions are combined using <literal>AND</literal>, and the results are 516 combined using <literal>AND</literal>. If there are no 517 <literal>PERMISSIVE</literal> policies, then access is denied. 518 </para> 519 520 <para> 521 Note that, for the purposes of combining multiple policies, 522 <literal>ALL</literal> policies are treated as having the same type as 523 whichever other type of policy is being applied. 524 </para> 525 526 <para> 527 For example, in an <literal>UPDATE</literal> command requiring both 528 <literal>SELECT</literal> and <literal>UPDATE</literal> permissions, if 529 there are multiple applicable policies of each type, they will be combined 530 as follows: 531 532<programlisting> 533<replaceable>expression</replaceable> from RESTRICTIVE SELECT/ALL policy 1 534AND 535<replaceable>expression</replaceable> from RESTRICTIVE SELECT/ALL policy 2 536AND 537... 538AND 539( 540 <replaceable>expression</replaceable> from PERMISSIVE SELECT/ALL policy 1 541 OR 542 <replaceable>expression</replaceable> from PERMISSIVE SELECT/ALL policy 2 543 OR 544 ... 545) 546AND 547<replaceable>expression</replaceable> from RESTRICTIVE UPDATE/ALL policy 1 548AND 549<replaceable>expression</replaceable> from RESTRICTIVE UPDATE/ALL policy 2 550AND 551... 552AND 553( 554 <replaceable>expression</replaceable> from PERMISSIVE UPDATE/ALL policy 1 555 OR 556 <replaceable>expression</replaceable> from PERMISSIVE UPDATE/ALL policy 2 557 OR 558 ... 559) 560</programlisting> 561 </para> 562 563 </refsect2> 564 </refsect1> 565 566 <refsect1> 567 <title>Notes</title> 568 569 <para> 570 You must be the owner of a table to create or change policies for it. 571 </para> 572 573 <para> 574 While policies will be applied for explicit queries against tables 575 in the database, they are not applied when the system is performing internal 576 referential integrity checks or validating constraints. This means there are 577 indirect ways to determine that a given value exists. An example of this is 578 attempting to insert a duplicate value into a column that is a primary key 579 or has a unique constraint. If the insert fails then the user can infer that 580 the value already exists. (This example assumes that the user is permitted by 581 policy to insert records which they are not allowed to see.) Another example 582 is where a user is allowed to insert into a table which references another, 583 otherwise hidden table. Existence can be determined by the user inserting 584 values into the referencing table, where success would indicate that the 585 value exists in the referenced table. These issues can be addressed by 586 carefully crafting policies to prevent users from being able to insert, 587 delete, or update records at all which might possibly indicate a value they 588 are not otherwise able to see, or by using generated values (e.g., surrogate 589 keys) instead of keys with external meanings. 590 </para> 591 592 <para> 593 Generally, the system will enforce filter conditions imposed using 594 security policies prior to qualifications that appear in user queries, 595 in order to prevent inadvertent exposure of the protected data to 596 user-defined functions which might not be trustworthy. However, 597 functions and operators marked by the system (or the system 598 administrator) as <literal>LEAKPROOF</literal> may be evaluated before 599 policy expressions, as they are assumed to be trustworthy. 600 </para> 601 602 <para> 603 Since policy expressions 604 are added to the user's query directly, they will be run with the rights of 605 the user running the overall query. Therefore, users who are using a given 606 policy must be able to access any tables or functions referenced in the 607 expression or they will simply receive a permission denied error when 608 attempting to query the table that has row-level security enabled. 609 This does not change how views 610 work, however. As with normal queries and views, permission checks and 611 policies for the tables which are referenced by a view will use the view 612 owner's rights and any policies which apply to the view owner. 613 </para> 614 615 <para> 616 Additional discussion and practical examples can be found 617 in <xref linkend="ddl-rowsecurity">. 618 </para> 619 620 </refsect1> 621 622 <refsect1> 623 <title>Compatibility</title> 624 625 <para> 626 <command>CREATE POLICY</command> is a <productname>PostgreSQL</productname> 627 extension. 628 </para> 629 </refsect1> 630 631 <refsect1> 632 <title>See Also</title> 633 634 <simplelist type="inline"> 635 <member><xref linkend="sql-alterpolicy"></member> 636 <member><xref linkend="sql-droppolicy"></member> 637 <member><xref linkend="sql-altertable"></member> 638 </simplelist> 639 </refsect1> 640 641</refentry> 642