8000 Improve the CREATE POLICY documentation. · sddowns/postgres@6a21eb3 · GitHub
[go: up one dir, main page]
Skip to content

Commit 6a21eb3

Browse files
deanrasheeddeanrasheed
committed
Improve the CREATE POLICY documentation.
Provide a correct description of how multiple policies are combined, clarify when SELECT permissions are required, mention SELECT FOR UPDATE/SHARE, and do some other more minor tidying up. Reviewed by Stephen Frost Discussion: https://postgr.es/m/CAEZATCVrxyYbOFU8XbGHicz%2BmXPYzw%3DhfNL2XTphDt-53TomQQ%40mail.gmail.com Back-patch to 9.5.
1 parent 2e7f6b6 commit 6a21eb3

File tree

1 file changed

+108
-62
lines changed

1 file changed

+108
-62
lines changed

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

Lines changed: 108 additions & 62 deletions
Original file line numberDiff line numberDiff line change
@@ -72,20 +72,17 @@ CREATE POLICY <replaceable class="parameter">name</replaceable> ON <replaceable
7272
<para>
7373
Policies can be applied for specific commands or for specific roles. The
7474
default for newly created policies is that they apply for all commands and
75-
roles, unless otherwise specified. If multiple policies apply to a given
76-
statement, they will be combined using OR (although <literal>ON CONFLICT DO
77-
UPDATE</> and <literal>INSERT</> policies are not combined in this way, but
78-
rather enforced as noted at each stage of <literal>ON CONFLICT</> execution).
75+
roles, unless otherwise specified.
7976
</para>
8077

8178
<para>
82-
For commands that can have both <literal>USING</literal>
83-
and <literal>WITH CHECK</literal> policies (<literal>ALL</literal>
79+
For policies that can have both <literal>USING</literal>
80+
and <literal>WITH CHECK</literal> expressions (<literal>ALL</literal>
8481
and <literal>UPDATE</literal>), if no <literal>WITH CHECK</literal>
85-
policy is defined, then the <literal>USING</literal> policy will be used
86-
both for which rows are visible (normal <literal>USING</literal> case)
87-
and for which rows will be allowed to be added (<literal>WITH
88-
CHECK</literal> case).
82+
expression is defined, then the <literal>USING</literal> expression will be
83+
used both to determine which rows are visible (normal
84+
<literal>USING</literal> case) and which new rows will be allowed to be
85+
added (<literal>WITH CHECK</literal> case).
8986
</para>
9087

9188
<para>
@@ -182,7 +179,7 @@ CREATE POLICY <replaceable class="parameter">name</replaceable> ON <replaceable
182179

183180
</variablelist>
184181

185-
<refsect2>
182+
<refsect2>
186183
<title>Per-Command Policies</title>
187184

188185
<variablelist>
@@ -195,8 +192,7 @@ CREATE POLICY <replaceable class="parameter">name</replaceable> ON <replaceable
195192
to all commands, regardless of the type of command. If an
196193
<literal>ALL</literal> policy exists and more specific policies
197194
exist, then both the <literal>ALL</literal> policy and the more
198-
specific policy (or policies) will be combined using
199-
OR, as usual for overlapping policies.
195+
specific policy (or policies) will be applied.
200196
Additionally, <literal>ALL</literal> policies will be applied to
201197
both the selection side of a query and the modification side, using
202198
the <literal>USING</literal> expression for both cases if only
@@ -265,11 +261,12 @@ CREATE POLICY <replaceable class="parameter">name</replaceable> ON <replaceable
265261
<listitem>
266262
<para>
267263
Using <literal>UPDATE</literal> for a policy means that it will apply
268-
to <literal>UPDATE</literal> commands (or auxiliary <literal>ON
269-
CONFLICT DO UPDATE</literal> clauses of <literal>INSERT</literal>
270-
commands). Since <literal>UPDATE</literal> involves pulling an
271-
existing record and then making changes to some portion (but
272-
possibly not all) of the record, <literal>UPDATE</literal>
264+
to <literal>UPDATE</literal>, <literal>SELECT FOR UPDATE</literal>
265+
and <literal>SELECT FOR SHARE</literal> commands, as well as
266+
auxiliary <literal>ON CONFLICT DO UPDATE</literal> clauses of
267+
<literal>INSERT</literal> commands. Since <literal>UPDATE</literal>
268+
involves pulling an existing record and replacing it with a new
269+
modified record, <literal>UPDATE</literal>
273270
policies accept both a <literal>USING</literal> expression and
274271
a <literal>WITH CHECK</literal> expression.
275272
The <literal>USING</literal> expression determines which records
@@ -278,22 +275,6 @@ CREATE POLICY <replaceable class="parameter">name</replaceable> ON <replaceable
278275
modified rows are allowed to be stored back into the relation.
279276
</para>
280277

281-
<para>
282-
When an <literal>UPDATE</literal> command is used with a
283-
<literal>WHERE</literal> clause or a <literal>RETURNING</literal>
284-
clause, <literal>SELECT</literal> rights are also required on the
285-
relation being updated and the appropriate <literal>SELECT</literal>
286-
and <literal>ALL</literal> policies will be combined (using OR for any
287-
overlapping <literal>SELECT</literal> related policies found) with the
288-
<literal>USING</literal> clause of the <literal>UPDATE</literal> policy
289-
using AND. Therefore, in order for a user to be able to
290-
<literal>UPDATE</literal> specific rows, the user must have access
291-
to the row(s) through a <literal>SELECT</literal>
292-
or <literal>ALL</literal> policy and the row(s) must pass
293-
the <literal>UPDATE</literal> policy's <literal>USING</>
294-
expression.
295-
</para>
296-
297278
<para>
298279
Any rows whose updated values do not pass the
299280
<literal>WITH CHECK</literal> expression will cause an error, and the
@@ -303,21 +284,33 @@ CREATE POLICY <replaceable class="parameter">name</replaceable> ON <replaceable
303284
</para>
304285

305286
<para>
306-
Note, however, that <literal>INSERT</literal> with <literal>ON CONFLICT
307-
DO UPDATE</literal> requires that an <literal>UPDATE</literal> policy
308-
<literal>USING</literal> expression always be enforced as a
309-
<literal>WITH CHECK</literal> expression. This
310-
<literal>UPDATE</literal> policy must always pass when the
311-
<literal>UPDATE</literal> path is taken. Any existing row that
312-
necessitates that the <literal>UPDATE</literal> path be taken must
313-
pass the (<literal>UPDATE</literal> or <literal>ALL</literal>)
314-
<literal>USING</literal> qualifications (combined using OR), which
315-
are always enforced as <literal>WITH CHECK</literal>
316-
options in this context. (The <literal>UPDATE</literal> path will
317-
<emphasis>never</> be silently avoided; an error will be thrown
318-
instead.) Finally, the final row appended to the relation must pass
319-
any <literal>WITH CHECK</literal> options that a conventional
320-
<literal>UPDATE</literal> is required to pass.
287+
Typically an <literal>UPDATE</literal> command also needs to read
288+
data from columns in the relation being updated (e.g., in a
289+
<literal>WHERE</literal> clause or a <literal>RETURNING</literal>
290+
clause, or in an expression on the right hand side of the
291+
<literal>SET</literal> clause). In this case,
292+
<literal>SELECT</literal> rights are also required on the relation
293+
being updated, and the appropriate <literal>SELECT</literal> or
294+
<literal>ALL</literal> policies will be applied in addition to
295+
the <literal>UPDATE</literal> policies. Thus the user must have
296+
access to the row(s) being updated through a <literal>SELECT</literal>
297+
or <literal>ALL</literal> policy in addition to being granted
298+
permission to update the row(s) via an <literal>UPDATE</literal>
299+
or <literal>ALL</literal> policy.
300+
</para>
301+
302+
<para>
303+
When an <literal>INSERT</literal> command has an auxiliary
304+
<literal>ON CONFLICT DO UPDATE</literal> clause, if the
305+
<literal>UPDATE</literal> path is taken, the row to be updated is
306+
first checked against the <literal>USING</literal> expressions of
307+
any <literal>UPDATE</literal> policies, and then the new updated row
308+
is checked against the <literal>WITH CHECK</literal> expressions.
309+
Note, however, that unlike a standalone <literal>UPDATE</literal>
310+
command, if the existing row does not pass the
311+
<literal>USING</literal> expressions, an error will be thrown (the
312+
<literal>UPDATE</literal> path will <emphasis>never</> be silently
313+
avoided).
321314
</para>
322315
</listitem>
323316
</varlistentry>
@@ -336,19 +329,18 @@ CREATE POLICY <replaceable class="parameter">name</replaceable> ON <replaceable
336329
</para>
337330

338331
<para>
339-
When a <literal>DELETE</literal> command is used with a
340-
<literal>WHERE</literal> clause or a <literal>RETURNING</literal>
341-
clause, <literal>SELECT</literal> rights are also required on the
342-
relation being updated and the appropriate <literal>SELECT</literal>
343-
and <literal>ALL</literal> policies will be combined (using OR for any
344-
overlapping <literal>SELECT</literal> related policies found) with the
345-
<literal>USING</literal> clause of the <literal>DELETE</literal> policy
346-
using AND. Therefore, in order for a user to be able to
347-
<literal>DELETE</literal> specific rows, the user must have access
348-
to the row(s) through a <literal>SELECT</literal>
349-
or <literal>ALL</literal> policy and the row(s) must pass
350-
the <literal>DELETE</literal> policy's <literal>USING</>
351-
expression.
332+
In most cases a <literal>DELETE</literal> command also needs to read
333+
data from columns in the relation that it is deleting from (e.g.,
334+
in a <literal>WHERE</literal> clause or a
335+
<literal>RETURNING</literal> clause). In this case,
336+
<literal>SELECT</literal> rights are also required on the relation,
337+
and the appropriate <literal>SELECT</literal> or
338+
<literal>ALL</literal> policies will be applied in addition to
339+
the <literal>DELETE</literal> policies. Thus the user must have
340+
access to the row(s) being deleted through a <literal>SELECT</literal>
341+
or <literal>ALL</literal> policy in addition to being granted
342+
permission to delete the row(s) via a <literal>DELETE</literal> or
343+
<literal>ALL</literal> policy.
352344
</para>
353345

354346
<para>
@@ -362,6 +354,60 @@ CREATE POLICY <replaceable class="parameter">name</replaceable> ON <replaceable
362354

363355
</variablelist>
364356
</refsect2>
357+
358+
<refsect2>
359+
<title>Application of Multiple Policies</title>
360+
361+
<para>
362+
When multiple policies of different command types apply to the same command
363+
(for example, <literal>SELECT</literal> and <literal>UPDATE</literal>
364+
policies applied to an <literal>UPDATE</literal> command), then the user
365+
must have both types of permissions (for example, permission to select rows
366+
from the relation as well as permission to update them). Thus the
367+
expressions for one type of policy are combined with the expressions for
368+
the other type of policy using the <literal>AND</literal> operator.
369+
</para>
370+
371+
<para>
372+
When multiple policies of the same command type apply to the same command,
373+
then at least one of the policies must grant access to the relation. Thus
374+
the expressions from all the policies of that type are combined using the
375+
<literal>OR</literal> operator. If there are no applicable policies, then
376+
access is denied.
377+
</para>
378+
379+
<para>
380+
Note that, for the purposes of combining multiple policies,
381+
<literal>ALL</literal> policies are treated as having the same type as
382+
whichever other type of policy is being applied.
383+
</para>
384+
385+
<para>
386+
For example, in an <literal>UPDATE</literal> command requiring both
387+
<literal>SELECT</literal> and <literal>UPDATE</literal> permissions, if
388+
there are multiple applicable policies of each type, they will be combined
389+
as follows:
390+
391+
<programlisting>
392+
(
393+
<replaceable>expression</replaceable> from SELECT/ALL policy 1
394+
OR
395+
<replaceable>expression</replaceable> from SELECT/ALL policy 2
396+
OR
397+
...
398+
)
399+
AND
400+
(
401+
<replaceable>expression</replaceable> from UPDATE/ALL policy 1
402+
OR
403+
<replaceable>expression</replaceable> from UPDATE/ALL policy 2
404+
OR
405+
...
406+
)
407+
</programlisting>
408+
</para>
409+
410+
</refsect2>
365411
</refsect1>
366412

367413
<refsect1>

0 commit comments

Comments
 (0)
0