Re: unclear about row-level security USING vs. CHECK

* Dean Rasheed (dean(dot)a(dot)rasheed(at)gmail(dot)com) wrote:
> On 28 September 2015 at 20:15, Stephen Frost <sfrost(at)snowman(dot)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.

That's my feeling on it as well, particularly as...

> 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.

exactly this. Many people are going to want them to be the same and not
supporting a single-expression syntax is going to frustrate them, to no
particularly good end, in my view. The "USING AND WITH CHECK"
technically solves that but feels very odd to me.

> 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.

Agreed.

> 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.
> """

I'm not convinced that this really helps, but I don't have anything
dramatically better yet either. I'll try to come up with something
though.

Thanks!

Stephen