Skip to content

Commit c663152

Browse files
deanrasheeddeanrasheed
committed
doc: Improve description of RLS policies applied by command type.
On the CREATE POLICY page, the "Policies Applied by Command Type" table was missing MERGE ... THEN DELETE and some of the policies applied during INSERT ... ON CONFLICT and MERGE. Fix that, and try to improve readability by listing the various MERGE cases separately, rather than together with INSERT/UPDATE/DELETE. Mention COPY ... TO along with SELECT, since it behaves in the same way. In addition, document which policy violations cause errors to be thrown, and which just cause rows to be silently ignored. Also, a paragraph above the table states that INSERT ... ON CONFLICT DO UPDATE only checks the WITH CHECK expressions of INSERT policies for rows appended to the relation by the INSERT path, which is incorrect -- all rows proposed for insertion are checked, regardless of whether they end up being inserted. Fix that, and also mention that the same applies to INSERT ... ON CONFLICT DO NOTHING. In addition, in various other places on that page, clarify how the different types of policy are applied to different commands, and whether or not errors are thrown when policy checks do not pass. Backpatch to all supported versions. Prior to v17, MERGE did not support RETURNING, and so MERGE ... THEN INSERT would never check new rows against SELECT policies. Prior to v15, MERGE was not supported at all. Author: Dean Rasheed <dean.a.rasheed@gmail.com> Reviewed-by: Viktor Holmberg <v@viktorh.net> Reviewed-by: Jian He <jian.universality@gmail.com> Discussion: https://postgr.es/m/CAEZATCWqnfeChjK=n1V_dYZT4rt4mnq+ybf9c0qXDYTVMsy8pg@mail.gmail.com Backpatch-through: 14
1 parent 21a9014 commit c663152

File tree

1 file changed

+159
-46
lines changed

1 file changed

+159
-46
lines changed

‎doc/src/sgml/ref/create_policy.sgml‎

Lines changed: 159 additions & 46 deletions
Original file line numberDiff line numberDiff line change
@@ -49,6 +49,8 @@ CREATE POLICY <replaceable class="parameter">name</replaceable> ON <replaceable
4949
in <literal>WITH CHECK</literal>. When a <literal>USING</literal>
5050
expression returns true for a given row then that row is visible to the
5151
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.
5254
When a <literal>WITH CHECK</literal> expression returns true for a row
5355
then that row is inserted or updated, while if false or null is returned
5456
then an error occurs.
@@ -194,8 +196,9 @@ CREATE POLICY <replaceable class="parameter">name</replaceable> ON <replaceable
194196
rows for which the expression returns false or null will not be
195197
visible to the user (in a <command>SELECT</command>), and will not be
196198
available for modification (in an <command>UPDATE</command>
197-
or <command>DELETE</command>). Such rows are silently suppressed; no error
198-
is reported.
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).
199202
</para>
200203
</listitem>
201204
</varlistentry>
@@ -251,8 +254,10 @@ CREATE POLICY <replaceable class="parameter">name</replaceable> ON <replaceable
251254
otherwise). If an <command>INSERT</command>
252255
or <command>UPDATE</command> command attempts to add rows to the
253256
table that do not pass the <literal>ALL</literal>
254-
policy's <literal>WITH CHECK</literal> expression, the entire
255-
command will be aborted.
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.
256261
</para>
257262
</listitem>
258263
</varlistentry>
@@ -268,11 +273,50 @@ CREATE POLICY <replaceable class="parameter">name</replaceable> ON <replaceable
268273
relation that pass the <literal>SELECT</literal> policy will be
269274
returned during a <literal>SELECT</literal> query, and that queries
270275
that require <literal>SELECT</literal> permissions, such as
271-
<literal>UPDATE</literal>, will also only see those records
276+
<literal>UPDATE</literal>, <literal>DELETE</literal>, and
277+
<literal>MERGE</literal>, will also only see those records
272278
that are allowed by the <literal>SELECT</literal> policy.
273279
A <literal>SELECT</literal> policy cannot have a <literal>WITH
274280
CHECK</literal> expression, as it only applies in cases where
275-
records are being retrieved from the relation.
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+
NOTHING/UPDATE</literal> clause, <literal>SELECT</literal>
298+
permissions are required on the relation, and the rows proposed for
299+
insertion are checked using the relation's <literal>SELECT</literal>
300+
policies. If a row proposed for insertion does not satisfy the
301+
relation's <literal>SELECT</literal> policies, an error is thrown
302+
(the <literal>INSERT</literal> is <emphasis>never</emphasis> silently
303+
avoided). In addition, if the <literal>UPDATE</literal> path is
304+
taken, the row to be updated and the new updated row are checked
305+
against the relation's <literal>SELECT</literal> policies, and an
306+
error is thrown if they are not satisfied (an auxiliary
307+
<literal>UPDATE</literal> is <emphasis>never</emphasis> silently
308+
avoided).
309+
</para>
310+
<para>
311+
A <literal>MERGE</literal> command requires <literal>SELECT</literal>
312+
permissions on both the source and target relations, and so each
313+
relation's <literal>SELECT</literal> policies are applied before they
314+
are joined, and the <literal>MERGE</literal> actions will only see
315+
those records that are allowed by those policies. In addition, if
316+
an <literal>UPDATE</literal> action is executed, the target relation's
317+
<literal>SELECT</literal> policies are applied to the updated row, as
318+
for a standalone <literal>UPDATE</literal>, except that an error is
319+
thrown if they are not satisfied.
276320
</para>
277321
</listitem>
278322
</varlistentry>
@@ -292,10 +336,11 @@ CREATE POLICY <replaceable class="parameter">name</replaceable> ON <replaceable
292336
where records are being added to the relation.
293337
</para>
294338
<para>
295-
Note that <literal>INSERT</literal> with <literal>ON CONFLICT DO
296-
UPDATE</literal> checks <literal>INSERT</literal> policies'
297-
<literal>WITH CHECK</literal> expressions only for rows appended
298-
to the relation by the <literal>INSERT</literal> path.
339+
Note that an <literal>INSERT</literal> with an <literal>ON CONFLICT
340+
DO NOTHING/UPDATE</literal> clause will check the
341+
<literal>INSERT</literal> policies' <literal>WITH CHECK</literal>
342+
expressions for all rows proposed for insertion, regardless of
343+
whether or not they end up being inserted.
299344
</para>
300345
</listitem>
301346
</varlistentry>
@@ -305,12 +350,12 @@ CREATE POLICY <replaceable class="parameter">name</replaceable> ON <replaceable
305350
<listitem>
306351
<para>
307352
Using <literal>UPDATE</literal> for a policy means that it will apply
308-
to <literal>UPDATE</literal>, <literal>SELECT FOR UPDATE</literal>
353+
to <literal>UPDATE</literal>, <literal>SELECT FOR UPDATE</literal>,
309354
and <literal>SELECT FOR SHARE</literal> commands, as well as
310355
auxiliary <literal>ON CONFLICT DO UPDATE</literal> clauses of
311-
<literal>INSERT</literal> commands.
312-
<literal>MERGE</literal> commands containing <literal>UPDATE</literal>
313-
actions are affected as well. Since <literal>UPDATE</literal>
356+
<literal>INSERT</literal> commands, and <literal>MERGE</literal>
357+
commands containing <literal>UPDATE</literal> actions.
358+
Since an <literal>UPDATE</literal> command
314359
involves pulling an existing record and replacing it with a new
315360
modified record, <literal>UPDATE</literal>
316361
policies accept both a <literal>USING</literal> expression and
@@ -356,7 +401,8 @@ CREATE POLICY <replaceable class="parameter">name</replaceable> ON <replaceable
356401
command, if the existing row does not pass the
357402
<literal>USING</literal> expressions, an error will be thrown (the
358403
<literal>UPDATE</literal> path will <emphasis>never</emphasis> be silently
359-
avoided).
404+
avoided). The same applies to an <literal>UPDATE</literal> action
405+
of a <command>MERGE</command> command.
360406
</para>
361407
</listitem>
362408
</varlistentry>
@@ -366,12 +412,18 @@ CREATE POLICY <replaceable class="parameter">name</replaceable> ON <replaceable
366412
<listitem>
367413
<para>
368414
Using <literal>DELETE</literal> for a policy means that it will apply
369-
to <literal>DELETE</literal> commands. Only rows that pass this
370-
policy will be seen by a <literal>DELETE</literal> command. There can
371-
be rows that are visible through a <literal>SELECT</literal> that are
372-
not available for deletion, if they do not pass the
373-
<literal>USING</literal> expression for
374-
the <literal>DELETE</literal> policy.
415+
to <literal>DELETE</literal> commands and <literal>MERGE</literal>
416+
commands containing <literal>DELETE</literal> actions. For a
417+
<literal>DELETE</literal> command, only rows that pass this policy
418+
will be seen by the <literal>DELETE</literal> command. There can
419+
be rows that are visible through a <literal>SELECT</literal> policy
420+
that are not available for deletion, if they do not pass the
421+
<literal>USING</literal> expression for the <literal>DELETE</literal>
422+
policy. Note, however, that a <literal>DELETE</literal> action in a
423+
<literal>MERGE</literal> command will see rows that are visible
424+
through <literal>SELECT</literal> policies, and if the
425+
<literal>DELETE</literal> policy does not pass for such a row, an
426+
error will be thrown.
375427
</para>
376428

377429
<para>
@@ -400,6 +452,15 @@ CREATE POLICY <replaceable class="parameter">name</replaceable> ON <replaceable
400452

401453
</variablelist>
402454

455+
<para>
456+
<xref linkend="sql-createpolicy-summary"/> summarizes how the different
457+
types of policy apply to specific commands. In the table,
458+
<quote>check</quote> means that the policy expression is checked and an
459+
error is thrown if it returns false or null, whereas <quote>filter</quote>
460+
means that the row is silently ignored if the policy expression returns
461+
false or null.
462+
</para>
463+
403464
<table id="sql-createpolicy-summary">
404465
<title>Policies Applied by Command Type</title>
405466
<tgroup cols="6">
@@ -424,72 +485,124 @@ CREATE POLICY <replaceable class="parameter">name</replaceable> ON <replaceable
424485
</thead>
425486
<tbody>
426487
<row>
427-
<entry><command>SELECT</command></entry>
428-
<entry>Existing row</entry>
488+
<entry><command>SELECT</command> / <command>COPY ... TO</command></entry>
489+
<entry>Filter existing row</entry>
429490
<entry>&mdash;</entry>
430491
<entry>&mdash;</entry>
431492
<entry>&mdash;</entry>
432493
<entry>&mdash;</entry>
433494
</row>
434495
<row>
435496
<entry><command>SELECT FOR UPDATE/SHARE</command></entry>
436-
<entry>Existing row</entry>
497+
<entry>Filter existing row</entry>
437498
<entry>&mdash;</entry>
438-
<entry>Existing row</entry>
499+
<entry>Filter existing row</entry>
439500
<entry>&mdash;</entry>
440501
<entry>&mdash;</entry>
441502
</row>
442503
<row>
443-
<entry><command>INSERT</command> / <command>MERGE ... THEN INSERT</command></entry>
504+
<entry><command>INSERT</command></entry>
505+
<entry>
506+
Check new row&nbsp;<footnote id="rls-select-priv">
507+
<para>
508+
If read access is required to either the existing or new row (for
509+
example, a <literal>WHERE</literal> or <literal>RETURNING</literal>
510+
clause that refers to columns from the relation).
511+
</para>
512+
</footnote>
513+
</entry>
514+
<entry>Check new row</entry>
515+
<entry>&mdash;</entry>
516+
<entry>&mdash;</entry>
444517
<entry>&mdash;</entry>
445-
<entry>New row</entry>
518+
</row>
519+
<row>
520+
<entry><command>UPDATE</command></entry>
521+
<entry>
522+
Filter existing row&nbsp;<footnoteref linkend="rls-select-priv"/> &amp;
523+
check new row&nbsp;<footnoteref linkend="rls-select-priv"/>
524+
</entry>
525+
<entry>&mdash;</entry>
526+
<entry>Filter existing row</entry>
527+
<entry>Check new row</entry>
528+
<entry>&mdash;</entry>
529+
</row>
530+
<row>
531+
<entry><command>DELETE</command></entry>
532+
<entry>
533+
Filter existing row&nbsp;<footnoteref linkend="rls-select-priv"/>
534+
</entry>
446535
<entry>&mdash;</entry>
447536
<entry>&mdash;</entry>
448537
<entry>&mdash;</entry>
538+
<entry>Filter existing row</entry>
449539
</row>
450540
<row>
451-
<entry><command>INSERT ... RETURNING</command></entry>
541+
<entry><command>INSERT ... ON CONFLICT</command></entry>
452542
<entry>
453-
New row <footnote id="rls-select-priv">
543+
Check new row&nbsp;<footnote id="rls-on-conflict-priv">
454544
<para>
455-
If read access is required to the existing or new row (for example,
456-
a <literal>WHERE</literal> or <literal>RETURNING</literal> clause
457-
that refers to columns from the relation).
545+
Row proposed for insertion is checked regardless of whether or not a
546+
conflict occurs.
458547
</para>
459548
</footnote>
460549
</entry>
461-
<entry>New row</entry>
550+
<entry>
551+
Check new row&nbsp;<footnoteref linkend="rls-on-conflict-priv"/>
552+
</entry>
462553
<entry>&mdash;</entry>
463554
<entry>&mdash;</entry>
464555
<entry>&mdash;</entry>
465556
</row>
466557
<row>
467-
<entry><command>UPDATE</command> / <command>MERGE ... THEN UPDATE</command></entry>
558+
<entry><command>ON CONFLICT DO UPDATE</command></entry>
468559
<entry>
469-
Existing &amp; new rows <footnoteref linkend="rls-select-priv"/>
560+
Check existing &amp; new rows&nbsp;<footnote id="rls-on-conflict-update-priv">
561+
<para>
562+
New row of the auxiliary <command>UPDATE</command> command, which
563+
might be different from the new row of the original
564+
<command>INSERT</command> command.
565+
</para>
566+
</footnote>
470567
</entry>
471568
<entry>&mdash;</entry>
472-
<entry>Existing row</entry>
473-
<entry>New row</entry>
569+
<entry>Check existing row</entry>
570+
<entry>
571+
Check new row&nbsp;<footnoteref linkend="rls-on-conflict-update-priv"/>
572+
</entry>
474573
<entry>&mdash;</entry>
475574
</row>
476575
<row>
477-
<entry><command>DELETE</command></entry>
478-
<entry>
479-
Existing row <footnoteref linkend="rls-select-priv"/>
480-
</entry>
576+
<entry><command>MERGE</command></entry>
577+
<entry>Filter source &amp; target rows</entry>
578+
<entry>&mdash;</entry>
481579
<entry>&mdash;</entry>
482580
<entry>&mdash;</entry>
483581
<entry>&mdash;</entry>
484-
<entry>Existing row</entry>
485582
</row>
486583
<row>
487-
<entry><command>ON CONFLICT DO UPDATE</command></entry>
488-
<entry>Existing &amp; new rows</entry>
584+
<entry><command>MERGE ... THEN INSERT</command></entry>
585+
<entry>&mdash;</entry>
586+
<entry>Check new row</entry>
587+
<entry>&mdash;</entry>
588+
<entry>&mdash;</entry>
589+
<entry>&mdash;</entry>
590+
</row>
591+
<row>
592+
<entry><command>MERGE ... THEN UPDATE</command></entry>
593+
<entry>Check new row</entry>
594+
<entry>&mdash;</entry>
595+
<entry>Check existing row</entry>
596+
<entry>Check new row</entry>
597+
<entry>&mdash;</entry>
598+
</row>
599+
<row>
600+
<entry><command>MERGE ... THEN DELETE</command></entry>
601+
<entry>&mdash;</entry>
602+
<entry>&mdash;</entry>
489603
<entry>&mdash;</entry>
490-
<entry>Existing row</entry>
491-
<entry>New row</entry>
492604
<entry>&mdash;</entry>
605+
<entry>Check existing row</entry>
493606
</row>
494607
</tbody>
495608
</tgroup>

0 commit comments

Comments
 (0)