Re: unclear about row-level security USING vs. CHECK - Mailing list pgsql-hackers

On 28 September 2015 at 20:15, Stephen Frost <sfrost@snowman.net> wrote:
> I listed out the various alternatives but didn't end up getting any
> responses to it.  I'm still of the opinion that the documentation is the
> main thing which needs improving here, but we can also change CREATE
> POLICY, et al, to require an explicit WITH CHECK clause for the commands
> where that makes sense if that's the consensus.
>

My vote would be to keep it as-is.

It feels perfectly natural to me. USING clauses add to the query's
WHERE clause controlling which existing rows you can SELECT, UPDATE or
DELETE. WITH CHECK clauses control what new data you can add via
INSERT or UPDATE. UPDATE allows both, but most of the time I expect
you'll want them to be the same.

So having the WITH CHECK clause default to being the same as the USING
clause for UPDATE matches what I expect to be the most common usage.
Users granted permission to update a subset of the table's rows
probably don't want to give those rows away. More advanced use-cases
are still supported, but the simplest/most common case is the default,
which means that you don't have to supply the same expression twice.

I agree that the documentation could be improved.

As things stand, you have to read quite a lot of text on the CREATE
POLICY page before you get to the description of how the USING and
WITH CHECK expressions interact. I'd suggest rewording the 2nd
paragraph where these clauses are first introduced. Perhaps something
like:

"""
A policy grants the ability to SELECT, INSERT, UPDATE, or DELETE rows
which match the relevant policy expression. For SELECT, UPDATE and
DELETE, the USING expression from the policy is combined with the
query's WHERE clause to control which existing table rows can be
retrieved, updated or deleted. For INSERT and UPDATE, the WITH CHECK
expression is used to constrain what new data can be added to the
table. A policy that applies to UPDATE may have both USING and WITH
CHECK expressions, which may be different from one another, but if
they are the same, the WITH CHECK expression can be omitted and the
USING expression will be used automatically in its place.

Policy expressions may be any expressions that evaluate to give a
result of type boolean. When a USING expression returns true for a
given row then the query is allowed to act upon that row, while rows
for which the expression returns false or null are skipped. When a
WITH CHECK expression returns true for a new row then the system
allows that row to be added to the table, but if the expression
returns false or null an error is raised.
"""

Regards,
Dean