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

Commitaf44cbd

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 parent639928c commitaf44cbd

File tree

1 file changed

+134
-72
lines changed

1 file changed

+134
-72
lines changed

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

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

8279
<para>
83-
Forcommands that can have both <literal>USING</literal>
84-
and <literal>WITH CHECK</literal>policies (<literal>ALL</literal>
80+
Forpolicies that can have both <literal>USING</literal>
81+
and <literal>WITH CHECK</literal>expressions (<literal>ALL</literal>
8582
and <literal>UPDATE</literal>), if no <literal>WITH CHECK</literal>
86-
policy is defined, then the <literal>USING</literal>policy will be used
87-
bothforwhich rows are visible (normal <literal>USING</literal> case)
88-
and forwhich rows will be allowed to be added (<literal>WITH
89-
CHECK</literal> case).
83+
expression is defined, then the <literal>USING</literal>expression will be
84+
usedbothto determinewhich rows are visible (normal
85+
<literal>USING</literal> case) andwhichnewrows will be allowed to be
86+
added (<literal>WITHCHECK</literal> case).
9087
</para>
9188

9289
<para>
@@ -144,6 +141,16 @@ CREATE POLICY <replaceable class="parameter">name</replaceable> ON <replaceable
144141
which can be accessed as all restrictive policies must be passed for
145142
each record.
146143
</para>
144+
145+
<para>
146+
Note that there needs to be at least one permissive policy to grant
147+
access to records before restrictive policies can be usefully used to
148+
reduce that access. If only restrictive policies exist, then no records
149+
will be accessible. When a mix of permissive and restrictive policies
150+
are present, a record is only accessible if at least one of the
151+
permissive policies passes, in addition to all the restrictive
152+
policies.
153+
</para>
147154
</listitem>
148155
</varlistentry>
149156

@@ -210,7 +217,7 @@ CREATE POLICY <replaceable class="parameter">name</replaceable> ON <replaceable
210217

211218
</variablelist>
212219

213-
<refsect2>
220+
<refsect2>
214221
<title>Per-Command Policies</title>
215222

216223
<variablelist>
@@ -223,8 +230,7 @@ CREATE POLICY <replaceable class="parameter">name</replaceable> ON <replaceable
223230
to all commands, regardless of the type of command. If an
224231
<literal>ALL</literal> policy exists and more specific policies
225232
exist, then both the <literal>ALL</literal> policy and the more
226-
specific policy (or policies) will be combined using
227-
OR, as usual for overlapping policies.
233+
specific policy (or policies) will be applied.
228234
Additionally, <literal>ALL</literal> policies will be applied to
229235
both the selection side of a query and the modification side, using
230236
the <literal>USING</literal> expression for both cases if only
@@ -293,11 +299,12 @@ CREATE POLICY <replaceable class="parameter">name</replaceable> ON <replaceable
293299
<listitem>
294300
<para>
295301
Using <literal>UPDATE</literal> for a policy means that it will apply
296-
to <literal>UPDATE</literal> commands (or auxiliary <literal>ON
297-
CONFLICT DO UPDATE</literal> clauses of <literal>INSERT</literal>
298-
commands). Since <literal>UPDATE</literal> involves pulling an
299-
existing record and then making changes to some portion (but
300-
possibly not all) of the record, <literal>UPDATE</literal>
302+
to <literal>UPDATE</literal>, <literal>SELECT FOR UPDATE</literal>
303+
and <literal>SELECT FOR SHARE</literal> commands, as well as
304+
auxiliary <literal>ON CONFLICT DO UPDATE</literal> clauses of
305+
<literal>INSERT</literal> commands. Since <literal>UPDATE</literal>
306+
involves pulling an existing record and replacing it with a new
307+
modified record, <literal>UPDATE</literal>
301308
policies accept both a <literal>USING</literal> expression and
302309
a <literal>WITH CHECK</literal> expression.
303310
The <literal>USING</literal> expression determines which records
@@ -306,22 +313,6 @@ CREATE POLICY <replaceable class="parameter">name</replaceable> ON <replaceable
306313
modified rows are allowed to be stored back into the relation.
307314
</para>
308315

309-
<para>
310-
When an <literal>UPDATE</literal> command is used with a
311-
<literal>WHERE</literal> clause or a <literal>RETURNING</literal>
312-
clause, <literal>SELECT</literal> rights are also required on the
313-
relation being updated and the appropriate <literal>SELECT</literal>
314-
and <literal>ALL</literal> policies will be combined (using OR for any
315-
overlapping <literal>SELECT</literal> related policies found) with the
316-
<literal>USING</literal> clause of the <literal>UPDATE</literal> policy
317-
using AND. Therefore, in order for a user to be able to
318-
<literal>UPDATE</literal> specific rows, the user must have access
319-
to the row(s) through a <literal>SELECT</literal>
320-
or <literal>ALL</literal> policy and the row(s) must pass
321-
the <literal>UPDATE</literal> policy's <literal>USING</>
322-
expression.
323-
</para>
324-
325316
<para>
326317
Any rows whose updated values do not pass the
327318
<literal>WITH CHECK</literal> expression will cause an error, and the
@@ -331,21 +322,33 @@ CREATE POLICY <replaceable class="parameter">name</replaceable> ON <replaceable
331322
</para>
332323

333324
<para>
334-
Note, however, that <literal>INSERT</literal> with <literal>ON CONFLICT
335-
DO UPDATE</literal> requires that an <literal>UPDATE</literal> policy
336-
<literal>USING</literal> expression always be enforced as a
337-
<literal>WITH CHECK</literal> expression. This
338-
<literal>UPDATE</literal> policy must always pass when the
339-
<literal>UPDATE</literal> path is taken. Any existing row that
340-
necessitates that the <literal>UPDATE</literal> path be taken must
341-
pass the (<literal>UPDATE</literal> or <literal>ALL</literal>)
342-
<literal>USING</literal> qualifications (combined using OR), which
343-
are always enforced as <literal>WITH CHECK</literal>
344-
options in this context. (The <literal>UPDATE</literal> path will
345-
<emphasis>never</> be silently avoided; an error will be thrown
346-
instead.) Finally, the final row appended to the relation must pass
347-
any <literal>WITH CHECK</literal> options that a conventional
348-
<literal>UPDATE</literal> is required to pass.
325+
Typically an <literal>UPDATE</literal> command also needs to read
326+
data from columns in the relation being updated (e.g., in a
327+
<literal>WHERE</literal> clause or a <literal>RETURNING</literal>
328+
clause, or in an expression on the right hand side of the
329+
<literal>SET</literal> clause). In this case,
330+
<literal>SELECT</literal> rights are also required on the relation
331+
being updated, and the appropriate <literal>SELECT</literal> or
332+
<literal>ALL</literal> policies will be applied in addition to
333+
the <literal>UPDATE</literal> policies. Thus the user must have
334+
access to the row(s) being updated through a <literal>SELECT</literal>
335+
or <literal>ALL</literal> policy in addition to being granted
336+
permission to update the row(s) via an <literal>UPDATE</literal>
337+
or <literal>ALL</literal> policy.
338+
</para>
339+
340+
<para>
341+
When an <literal>INSERT</literal> command has an auxiliary
342+
<literal>ON CONFLICT DO UPDATE</literal> clause, if the
343+
<literal>UPDATE</literal> path is taken, the row to be updated is
344+
first checked against the <literal>USING</literal> expressions of
345+
any <literal>UPDATE</literal> policies, and then the new updated row
346+
is checked against the <literal>WITH CHECK</literal> expressions.
347+
Note, however, that unlike a standalone <literal>UPDATE</literal>
348+
command, if the existing row does not pass the
349+
<literal>USING</literal> expressions, an error will be thrown (the
350+
<literal>UPDATE</literal> path will <emphasis>never</> be silently
351+
avoided).
349352
</para>
350353
</listitem>
351354
</varlistentry>
@@ -364,19 +367,18 @@ CREATE POLICY <replaceable class="parameter">name</replaceable> ON <replaceable
364367
</para>
365368

366369
<para>
367-
When a <literal>DELETE</literal> command is used with a
368-
<literal>WHERE</literal> clause or a <literal>RETURNING</literal>
369-
clause, <literal>SELECT</literal> rights are also required on the
370-
relation being updated and the appropriate <literal>SELECT</literal>
371-
and <literal>ALL</literal> policies will be combined (using OR for any
372-
overlapping <literal>SELECT</literal> related policies found) with the
373-
<literal>USING</literal> clause of the <literal>DELETE</literal> policy
374-
using AND. Therefore, in order for a user to be able to
375-
<literal>DELETE</literal> specific rows, the user must have access
376-
to the row(s) through a <literal>SELECT</literal>
377-
or <literal>ALL</literal> policy and the row(s) must pass
378-
the <literal>DELETE</literal> policy's <literal>USING</>
379-
expression.
370+
In most cases a <literal>DELETE</literal> command also needs to read
371+
data from columns in the relation that it is deleting from (e.g.,
372+
in a <literal>WHERE</literal> clause or a
373+
<literal>RETURNING</literal> clause). In this case,
374+
<literal>SELECT</literal> rights are also required on the relation,
375+
and the appropriate <literal>SELECT</literal> or
376+
<literal>ALL</literal> policies will be applied in addition to
377+
the <literal>DELETE</literal> policies. Thus the user must have
378+
access to the row(s) being deleted through a <literal>SELECT</literal>
379+
or <literal>ALL</literal> policy in addition to being granted
380+
permission to delete the row(s) via a <literal>DELETE</literal> or
381+
<literal>ALL</literal> policy.
380382
</para>
381383

382384
<para>
@@ -390,6 +392,76 @@ CREATE POLICY <replaceable class="parameter">name</replaceable> ON <replaceable
390392

391393
</variablelist>
392394
</refsect2>
395+
396+
<refsect2>
397+
<title>Application of Multiple Policies</title>
398+
399+
<para>
400+
When multiple policies of different command types apply to the same command
401+
(for example, <literal>SELECT</literal> and <literal>UPDATE</literal>
402+
policies applied to an <literal>UPDATE</literal> command), then the user
403+
must have both types of permissions (for example, permission to select rows
404+
from the relation as well as permission to update them). Thus the
405+
expressions for one type of policy are combined with the expressions for
406+
the other type of policy using the <literal>AND</literal> operator.
407+
</para>
408+
409+
<para>
410+
When multiple policies of the same command type apply to the same command,
411+
then there must be at least one <literal>PERMISSIVE</literal> policy
412+
granting access to the relation, and all of the
413+
<literal>RESTRICTIVE</literal> policies must pass. Thus all the
414+
<literal>PERMISSIVE</literal> policy expressions are combined using
415+
<literal>OR</literal>, all the <literal>RESTRICTIVE</literal> policy
416+
expressions are combined using <literal>AND</literal>, and the results are
417+
combined using <literal>AND</literal>. If there are no
418+
<literal>PERMISSIVE</literal> policies, then access is denied.
419+
</para>
420+
421+
<para>
422+
Note that, for the purposes of combining multiple policies,
423+
<literal>ALL</literal> policies are treated as having the same type as
424+
whichever other type of policy is being applied.
425+
</para>
426+
427+
<para>
428+
For example, in an <literal>UPDATE</literal> command requiring both
429+
<literal>SELECT</literal> and <literal>UPDATE</literal> permissions, if
430+
there are multiple applicable policies of each type, they will be combined
431+
as follows:
432+
433+
<programlisting>
434+
<replaceable>expression</replaceable> from RESTRICTIVE SELECT/ALL policy 1
435+
AND
436+
<replaceable>expression</replaceable> from RESTRICTIVE SELECT/ALL policy 2
437+
AND
438+
...
439+
AND
440+
(
441+
<replaceable>expression</replaceable> from PERMISSIVE SELECT/ALL policy 1
442+
OR
443+
<replaceable>expression</replaceable> from PERMISSIVE SELECT/ALL policy 2
444+
OR
445+
...
446+
)
447+
AND
448+
<replaceable>expression</replaceable> from RESTRICTIVE UPDATE/ALL policy 1
449+
AND
450+
<replaceable>expression</replaceable> from RESTRICTIVE UPDATE/ALL policy 2
451+
AND
452+
...
453+
AND
454+
(
455+
<replaceable>expression</replaceable> from PERMISSIVE UPDATE/ALL policy 1
456+
OR
457+
<replaceable>expression</replaceable> from PERMISSIVE UPDATE/ALL policy 2
458+
OR
459+
...
460+
)
461+
</programlisting>
462+
</para>
463+
464+
</refsect2>
393465
</refsect1>
394466

395467
<refsect1>
@@ -418,16 +490,6 @@ CREATE POLICY <replaceable class="parameter">name</replaceable> ON <replaceable
418490
keys) instead of keys with external meanings.
419491
</para>
420492

421-
<para>
422-
Note that there needs to be at least one permissive policy to grant
423-
access to records before restrictive policies can be usefully used to
424-
reduce that access. If only restrictive policies exist, then no records
425-
will be accessible. When a mix of permissive and restrictive policies
426-
are present, a record is only accessible if at least one of the
427-
permissive policies passes, in addition to all the restrictive
428-
policies.
429-
</para>
430-
431493
<para>
432494
Generally, the system will enforce filter conditions imposed using
433495
security policies prior to qualifications that appear in user queries,

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp