Debian Codegraph @

doc/src/sgml/ref/create_policy.sgml

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