Skip to content

Commit 7aa83ea

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 84f1bf4 commit 7aa83ea

File tree

1 file changed

+102
-39
lines changed

1 file changed

+102
-39
lines changed

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

Lines changed: 102 additions & 39 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.
@@ -193,8 +195,9 @@ CREATE POLICY <replaceable class="parameter">name</replaceable> ON <replaceable
193195
rows for which the expression returns false or null will not be
194196
visible to the user (in a <command>SELECT</command>), and will not be
195197
available for modification (in an <command>UPDATE</command>
196-
or <command>DELETE</command>). Such rows are silently suppressed; no error
197-
is reported.
198+
or <command>DELETE</command>). Typically, such rows are silently
199+
suppressed; no error is reported (but see
200+
<xref linkend="sql-createpolicy-summary"/> for exceptions).
198201
</para>
199202
</listitem>
200203
</varlistentry>
@@ -250,8 +253,10 @@ CREATE POLICY <replaceable class="parameter">name</replaceable> ON <replaceable
250253
otherwise). If an <command>INSERT</command>
251254
or <command>UPDATE</command> command attempts to add rows to the
252255
table that do not pass the <literal>ALL</literal>
253-
policy's <literal>WITH CHECK</literal> expression, the entire
254-
command will be aborted.
256+
policy's <literal>WITH CHECK</literal> expression (or its
257+
<literal>USING</literal> expression, if it does not have a
258+
<literal>WITH CHECK</literal> expression), the entire command will
259+
be aborted.
255260
</para>
256261
</listitem>
257262
</varlistentry>
@@ -267,11 +272,39 @@ CREATE POLICY <replaceable class="parameter">name</replaceable> ON <replaceable
267272
relation that pass the <literal>SELECT</literal> policy will be
268273
returned during a <literal>SELECT</literal> query, and that queries
269274
that require <literal>SELECT</literal> permissions, such as
270-
<literal>UPDATE</literal>, will also only see those records
275+
<literal>UPDATE</literal> and <literal>DELETE</literal>,
276+
will also only see those records
271277
that are allowed by the <literal>SELECT</literal> policy.
272278
A <literal>SELECT</literal> policy cannot have a <literal>WITH
273279
CHECK</literal> expression, as it only applies in cases where
274-
records are being retrieved from the relation.
280+
records are being retrieved from the relation, except as described
281+
below.
282+
</para>
283+
<para>
284+
If a data-modifying query has a <literal>RETURNING</literal> clause,
285+
<literal>SELECT</literal> permissions are required on the relation,
286+
and any newly inserted or updated rows from the relation must satisfy
287+
the relation's <literal>SELECT</literal> policies in order to be
288+
available to the <literal>RETURNING</literal> clause. If a newly
289+
inserted or updated row does not satisfy the relation's
290+
<literal>SELECT</literal> policies, an error will be thrown (inserted
291+
or updated rows to be returned are <emphasis>never</emphasis>
292+
silently ignored).
293+
</para>
294+
<para>
295+
If an <literal>INSERT</literal> has an <literal>ON CONFLICT DO
296+
NOTHING/UPDATE</literal> clause, <literal>SELECT</literal>
297+
permissions are required on the relation, and the rows proposed for
298+
insertion are checked using the relation's <literal>SELECT</literal>
299+
policies. If a row proposed for insertion does not satisfy the
300+
relation's <literal>SELECT</literal> policies, an error is thrown
301+
(the <literal>INSERT</literal> is <emphasis>never</emphasis> silently
302+
avoided). In addition, if the <literal>UPDATE</literal> path is
303+
taken, the row to be updated and the new updated row are checked
304+
against the relation's <literal>SELECT</literal> policies, and an
305+
error is thrown if they are not satisfied (an auxiliary
306+
<literal>UPDATE</literal> is <emphasis>never</emphasis> silently
307+
avoided).
275308
</para>
276309
</listitem>
277310
</varlistentry>
@@ -289,10 +322,11 @@ CREATE POLICY <replaceable class="parameter">name</replaceable> ON <replaceable
289322
where records are being added to the relation.
290323
</para>
291324
<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.
325+
Note that an <literal>INSERT</literal> with an <literal>ON CONFLICT
326+
DO NOTHING/UPDATE</literal> clause will check the
327+
<literal>INSERT</literal> policies' <literal>WITH CHECK</literal>
328+
expressions for all rows proposed for insertion, regardless of
329+
whether or not they end up being inserted.
296330
</para>
297331
</listitem>
298332
</varlistentry>
@@ -363,10 +397,10 @@ CREATE POLICY <replaceable class="parameter">name</replaceable> ON <replaceable
363397
Using <literal>DELETE</literal> for a policy means that it will apply
364398
to <literal>DELETE</literal> commands. Only rows that pass this
365399
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.
400+
be rows that are visible through a <literal>SELECT</literal> policy
401+
that are not available for deletion, if they do not pass the
402+
<literal>USING</literal> expression for the <literal>DELETE</literal>
403+
policy.
370404
</para>
371405

372406
<para>
@@ -395,6 +429,15 @@ CREATE POLICY <replaceable class="parameter">name</replaceable> ON <replaceable
395429

396430
</variablelist>
397431

432+
<para>
433+
<xref linkend="sql-createpolicy-summary"/> summarizes how the different
434+
types of policy apply to specific commands. In the table,
435+
<quote>check</quote> means that the policy expression is checked and an
436+
error is thrown if it returns false or null, whereas <quote>filter</quote>
437+
means that the row is silently ignored if the policy expression returns
438+
false or null.
439+
</para>
440+
398441
<table id="sql-createpolicy-summary">
399442
<title>Policies Applied by Command Type</title>
400443
<tgroup cols="6">
@@ -419,71 +462,91 @@ CREATE POLICY <replaceable class="parameter">name</replaceable> ON <replaceable
419462
</thead>
420463
<tbody>
421464
<row>
422-
<entry><command>SELECT</command></entry>
423-
<entry>Existing row</entry>
465+
<entry><command>SELECT</command> / <command>COPY ... TO</command></entry>
466+
<entry>Filter existing row</entry>
424467
<entry>&mdash;</entry>
425468
<entry>&mdash;</entry>
426469
<entry>&mdash;</entry>
427470
<entry>&mdash;</entry>
428471
</row>
429472
<row>
430473
<entry><command>SELECT FOR UPDATE/SHARE</command></entry>
431-
<entry>Existing row</entry>
474+
<entry>Filter existing row</entry>
432475
<entry>&mdash;</entry>
433-
<entry>Existing row</entry>
476+
<entry>Filter existing row</entry>
434477
<entry>&mdash;</entry>
435478
<entry>&mdash;</entry>
436479
</row>
437480
<row>
438481
<entry><command>INSERT</command></entry>
439-
<entry>&mdash;</entry>
440-
<entry>New row</entry>
441-
<entry>&mdash;</entry>
442-
<entry>&mdash;</entry>
443-
<entry>&mdash;</entry>
444-
</row>
445-
<row>
446-
<entry><command>INSERT ... RETURNING</command></entry>
447482
<entry>
448-
New row <footnote id="rls-select-priv">
483+
Check new row&nbsp;<footnote id="rls-select-priv">
449484
<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).
485+
If read access is required to either the existing or new row (for
486+
example, a <literal>WHERE</literal> or <literal>RETURNING</literal>
487+
clause that refers to columns from the relation).
453488
</para>
454489
</footnote>
455490
</entry>
456-
<entry>New row</entry>
491+
<entry>Check new row</entry>
457492
<entry>&mdash;</entry>
458493
<entry>&mdash;</entry>
459494
<entry>&mdash;</entry>
460495
</row>
461496
<row>
462497
<entry><command>UPDATE</command></entry>
463498
<entry>
464-
Existing &amp; new rows <footnoteref linkend="rls-select-priv"/>
499+
Filter existing row&nbsp;<footnoteref linkend="rls-select-priv"/> &amp;
500+
check new row&nbsp;<footnoteref linkend="rls-select-priv"/>
465501
</entry>
466502
<entry>&mdash;</entry>
467-
<entry>Existing row</entry>
468-
<entry>New row</entry>
503+
<entry>Filter existing row</entry>
504+
<entry>Check new row</entry>
469505
<entry>&mdash;</entry>
470506
</row>
471507
<row>
472508
<entry><command>DELETE</command></entry>
473509
<entry>
474-
Existing row <footnoteref linkend="rls-select-priv"/>
510+
Filter existing row&nbsp;<footnoteref linkend="rls-select-priv"/>
511+
</entry>
512+
<entry>&mdash;</entry>
513+
<entry>&mdash;</entry>
514+
<entry>&mdash;</entry>
515+
<entry>Filter existing row</entry>
516+
</row>
517+
<row>
518+
<entry><command>INSERT ... ON CONFLICT</command></entry>
519+
<entry>
520+
Check new row&nbsp;<footnote id="rls-on-conflict-priv">
521+
<para>
522+
Row proposed for insertion is checked regardless of whether or not a
523+
conflict occurs.
524+
</para>
525+
</footnote>
526+
</entry>
527+
<entry>
528+
Check new row&nbsp;<footnoteref linkend="rls-on-conflict-priv"/>
475529
</entry>
476530
<entry>&mdash;</entry>
477531
<entry>&mdash;</entry>
478532
<entry>&mdash;</entry>
479-
<entry>Existing row</entry>
480533
</row>
481534
<row>
482535
<entry><command>ON CONFLICT DO UPDATE</command></entry>
483-
<entry>Existing &amp; new rows</entry>
536+
<entry>
537+
Check existing &amp; new rows&nbsp;<footnote id="rls-on-conflict-update-priv">
538+
<para>
539+
New row of the auxiliary <command>UPDATE</command> command, which
540+
might be different from the new row of the original
541+
<command>INSERT</command> command.
542+
</para>
543+
</footnote>
544+
</entry>
484545
<entry>&mdash;</entry>
485-
<entry>Existing row</entry>
486-
<entry>New row</entry>
546+
<entry>Check existing row</entry>
547+
<entry>
548+
Check new row&nbsp;<footnoteref linkend="rls-on-conflict-update-priv"/>
549+
</entry>
487550
<entry>&mdash;</entry>
488551
</row>
489552
</tbody>

0 commit comments

Comments
 (0)