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

Commit43cd468

Browse files
committed
Improve CREATE POLICY documentation
Clarify that SELECT policies are now applied when SELECT rightsare required for a given query, even if the query is an UPDATE orDELETE query. Pointed out by Noah.Additionally, note the risk regarding concurrently open transactionswhere a relation which controls access to the rows of another relationare updated and the rows of the primary relation are also beingmodified. Pointed out by Peter Geoghegan.Back-patch to 9.5.
1 parente5e11c8 commit43cd468

File tree

1 file changed

+86
-27
lines changed

1 file changed

+86
-27
lines changed

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

Lines changed: 86 additions & 27 deletions
Original file line numberDiff line numberDiff line change
@@ -217,12 +217,18 @@ CREATE POLICY <replaceable class="parameter">name</replaceable> ON <replaceable
217217
<listitem>
218218
<para>
219219
Using <literal>SELECT</literal> for a policy means that it will apply
220-
to <literal>SELECT</literal> commands. The result is that only those
221-
records from the relation that pass the <literal>SELECT</literal>
222-
policy will be returned, even if other records exist in the relation.
223-
The <literal>SELECT</literal> policy only accepts the <literal>USING</literal> expression
224-
as it only ever applies in cases where records are being retrieved from
225-
the relation.
220+
to <literal>SELECT</literal> queries and whenever
221+
<literal>SELECT</literal> permissions are required on the relation the
222+
policy is defined for. The result is that only those records from the
223+
relation that pass the <literal>SELECT</literal> policy will be
224+
returned during a <literal>SELECT</literal> query, even if other
225+
records exist in the relation and that queries which require
226+
<literal>SELECT</literal> permissions, such as
227+
<literal>UPDATE</literal>, will also only see those records
228+
which are allowed by the <literal>SELECT</literal> policy.
229+
The <literal>SELECT</literal> policy only accepts the
230+
<literal>USING</literal> expression as it only applies in cases where
231+
records are being retrieved from the relation.
226232
</para>
227233
</listitem>
228234
</varlistentry>
@@ -235,15 +241,15 @@ CREATE POLICY <replaceable class="parameter">name</replaceable> ON <replaceable
235241
to <literal>INSERT</literal> commands. Rows being inserted that do
236242
not pass this policy will result in a policy violation error, and the
237243
entire <literal>INSERT</literal> command will be aborted. The
238-
<literal>INSERT</literal> policy only accepts the <literal>WITH CHECK</literal> expression
239-
as it onlyeverapplies in cases where records are being added to the
240-
relation.
244+
<literal>INSERT</literal> policy only accepts the
245+
<literal>WITH CHECK</literal> expressionas it only applies in cases
246+
where records are being added to therelation.
241247
</para>
242248
<para>
243249
Note that <literal>INSERT</literal> with <literal>ON CONFLICT DO
244250
UPDATE</literal> requires that any <literal>INSERT</literal> policy
245-
<literal>WITH CHECK</literal> expression passes for any rows appended to the relation by
246-
the <literal>INSERT</literal> path only.
251+
<literal>WITH CHECK</literal> expression passes for any rows appended
252+
to the relation bythe <literal>INSERT</literal> path only.
247253
</para>
248254
</listitem>
249255
</varlistentry>
@@ -259,17 +265,41 @@ CREATE POLICY <replaceable class="parameter">name</replaceable> ON <replaceable
259265
record and then making changes to some portion (but possibly not all)
260266
of the record, the <literal>UPDATE</literal> policy accepts both a
261267
<literal>USING</literal> expression and a <literal>WITH CHECK</literal>
262-
expression. The <literal>USING</literal> expression will be used to
268+
expression.
269+
</para>
270+
271+
<para>
272+
The <literal>USING</literal> expression will be used to
263273
determine which records the <literal>UPDATE</literal> command will see
264274
to operate against, while the <literal>WITH CHECK</literal> expression
265275
defines what rows are allowed to be added back into the relation
266-
(similar to the <literal>INSERT</literal> policy). Any rows whose
267-
resulting values do not pass the <literal>WITH CHECK</literal>
268-
expression will cause an error, and the entire command will be aborted.
269-
Note that if only a <literal>USING</literal> clause is specified, then
270-
that clause will be used for both <literal>USING</literal> and
271-
<literal>WITH CHECK</literal> cases.
276+
(similar to the <literal>INSERT</literal> policy).
272277
</para>
278+
279+
<para>
280+
When an <literal>UPDATE</literal> command is used with a
281+
<literal>WHERE</literal> clause or a <literal>RETURNING</literal>
282+
clause, <literal>SELECT</literal> rights are also required on the
283+
relation being updated and the appropriate <literal>SELECT</literal>
284+
and <literal>ALL</literal> policies will be combined (using OR for any
285+
overlapping <literal>SELECT</literal> related policies found) with the
286+
<literal>USING</literal> clause of the <literal>UPDATE</literal> policy
287+
using AND. Therefore, in order for a user to be able to
288+
<literal>UPDATE</literal> a specific set of rows using a
289+
<literal>WHERE</literal> clause, the user must have access to the
290+
row(s) through a <literal>SELECT</literal> or <literal>ALL</literal>
291+
policy and the row(s) must be pass the <literal>UPDATE USING</literal>
292+
expression.
293+
</para>
294+
295+
<para>
296+
Any rows whose resulting values do not pass the
297+
<literal>WITH CHECK</literal> expression will cause an error, and the
298+
entire command will be aborted. If only a <literal>USING</literal>
299+
clause is specified, then that clause will be used for both
300+
<literal>USING</literal> and <literal>WITH CHECK</literal> cases.
301+
</para>
302+
273303
<para>
274304
Note, however, that <literal>INSERT</literal> with <literal>ON CONFLICT
275305
DO UPDATE</literal> requires that an <literal>UPDATE</literal> policy
@@ -295,15 +325,32 @@ CREATE POLICY <replaceable class="parameter">name</replaceable> ON <replaceable
295325
<para>
296326
Using <literal>DELETE</literal> for a policy means that it will apply
297327
to <literal>DELETE</literal> commands. Only rows that pass this
298-
policy will be seen by a <literal>DELETE</literal> command. There can be rows
299-
that are visible through a <literal>SELECT</literal> that are not seen by a
300-
<literal>DELETE</literal>, if they do not pass the <literal>USING</literal> expression
301-
for the <literal>DELETE</literal>. Conversely, there can be rows that are not visible
302-
through the <literal>SELECT</literal> policy but may be deleted if they
303-
pass the <literal>DELETE</literal> <literal>USING</literal> policy. The
304-
<literal>DELETE</literal> policy only accepts the <literal>USING</literal> expression as
305-
it only ever applies in cases where records are being extracted from
306-
the relation for deletion.
328+
policy will be seen by a <literal>DELETE</literal> command. There can
329+
be rows that are visible through a <literal>SELECT</literal> that are
330+
not seen by a <literal>DELETE</literal>, if they do not pass the
331+
<literal>USING</literal> expression for the <literal>DELETE</literal>.
332+
</para>
333+
334+
<para>
335+
When a <literal>DELETE</literal> command is used with a
336+
<literal>WHERE</literal> clause or a <literal>RETURNING</literal>
337+
clause, <literal>SELECT</literal> rights are also required on the
338+
relation being updated and the appropriate <literal>SELECT</literal>
339+
and <literal>ALL</literal> policies will be combined (using OR for any
340+
overlapping <literal>SELECT</literal> related policies found) with the
341+
<literal>USING</literal> clause of the <literal>DELETE</literal> policy
342+
using AND. Therefore, in order for a user to be able to
343+
<literal>DELETE</literal> a specific set of rows using a
344+
<literal>WHERE</literal> clause, the user must have access to the
345+
row(s) through a <literal>SELECT</literal> or <literal>ALL</literal>
346+
policy and the row(s) must be pass the <literal>DELETE USING</literal>
347+
expression.
348+
</para>
349+
350+
<para>
351+
The <literal>DELETE</literal> policy only accepts the
352+
<literal>USING</literal> expression as it only applies in cases where
353+
records are being extracted from the relation for deletion.
307354
</para>
308355
</listitem>
309356
</varlistentry>
@@ -349,6 +396,18 @@ CREATE POLICY <replaceable class="parameter">name</replaceable> ON <replaceable
349396
policies for the tables which are referenced by a view will use the view
350397
owner's rights and any policies which apply to the view owner.
351398
</para>
399+
400+
<para>
401+
When reducing the set of rows which a user has access to, through
402+
modifications to relations referenced by Row-Level Security Policies or
403+
Security Barrier Views, be aware that users with a currently open transaction
404+
may be able to see updates to the rows that they are no longer allowed
405+
access. Therefore, the best practice to avoid any possible leak of
406+
information when altering conditions that determine the visibility of
407+
specific rows is to ensure that affected users do not have any open
408+
transactions, perhaps by ensuring they have no concurrent sessions running.
409+
</para>
410+
352411
</refsect1>
353412

354413
<refsect1>

0 commit comments

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