🌐 AI搜索 & 代理 主页
Skip to content

Commit 4d5d08c

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 bfd5515 commit 4d5d08c

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-
For commands that can have both <literal>USING</literal>
84-
and <literal>WITH CHECK</literal> policies (<literal>ALL</literal>
80+
For policies 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-
both for which rows are visible (normal <literal>USING</literal> case)
88-
and for which 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+
used both to determine which rows are visible (normal
85+
<literal>USING</literal> case) and which new rows will be allowed to be
86+
added (<literal>WITH CHECK</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)