Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commitaf4bd07

Browse files
committed
Improve the CREATE POLICY documentation.
Provide a correct description of how multiple policies are combined,clarify when SELECT permissions are required, mention SELECT FORUPDATE/SHARE, and do some other more minor tidying up.Reviewed by Stephen FrostDiscussion:https://postgr.es/m/CAEZATCVrxyYbOFU8XbGHicz%2BmXPYzw%3DhfNL2XTphDt-53TomQQ%40mail.gmail.comBack-patch to 9.5.
1 parentb5ee532 commitaf4bd07

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-
Forcommands that can have both <literal>USING</literal>
83-
and <literal>WITH CHECK</literal>policies (<literal>ALL</literal>
79+
Forpolicies 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-
bothforwhich rows are visible (normal <literal>USING</literal> case)
87-
and forwhich 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+
usedbothto determinewhich rows are visible (normal
84+
<literal>USING</literal> case) andwhichnewrows will be allowed to be
85+
added (<literal>WITHCHECK</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)
[8]ページ先頭
©2009-2025 Movatter.jp