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

* Robert Haas (robertmhaas@gmail.com) wrote:
> On Wed, Sep 23, 2015 at 11:24 AM, Stephen Frost <sfrost@snowman.net> wrote:
> > I'm working on a documentation patch with Adam to improve the docs
> > around this (and other parts as well).  I agree it doesn't come off as
> > naturally intuitive to everyone (it did to me, but I'm clearly biased
> > as, I think anyway, it was my idea) and so I'm not sure that's enough.
> >
> > Is there strong feeling that USING and WITH CHECK should both always be
> > required when specifying ALL and UPDATE policies?  It's not a difficult
> > change to make if people want it.
>
> My expectation would have been:
>
> If you specify USING, you can see only those rows, but you can give
> rows away freely.  If you don't want to allow giving rows away under
> any circumstances, then specify the same expression for USING and WITH
> CHECK.

Having an implicit 'true' for WITH CHECK would be very much against what
I would ever expect.  If anything, I'd think we would have an implicit
'false' there or simply not allow it to ever be unspecified.

> > I will mention that on another thread there was discussion about having
> > WITH CHECK for all policy types as a way to let users control if an
> > error should be thrown rather than skipping over a row due to lack of
> > visibility.  In all cases, USING controls visibility and WITH CHECK will
> > throw an error on a violation and that would remain the case with this
> > approach.  Now that I think about it, it might be a bit cleaner if
> > USING and WITH CHECK are always kept independent for that case, but I'm
> > not sure it's really all that much of a difference.  The USING will
> > always be applied first and then the WITH CHECK applied to any rows
> > which remain, which comes across, to me at least (which isn't fair, of
> > course, but it's what I can comment on) as quite clear to understand.
>
> I don't really get that.  If you could make skipping a row trigger an
> error, then that would create a bunch of covert channel attacks.

Apparently I didn't explain it correctly.  Skipping a row doesn't
trigger an error.  An example would perhaps help here to clarify:

CREATE POLICY p1 ON t1 FOR DELETE
USING (true)
WITH CHECK (inserted_by = current_user);

What would happen above is that, in a DELETE case, you're allowed to
*try* and delete any record in the table, but if you try to delete a
record which isn't yours, we throw an error.  Currently the only option,
if you want to prevent users from deleteing records which are not
theirs, is to have:

CREATE POLICY p1 ON t1 FOR DELETE
USING (inserted_by = current_user)

Which certainly has the effect that you can only delete records you own,
but I can see use-cases where you'd like to know that someone tried to
delete a record which isn't their own and that isn't something you can
get directly today.

Thanks!

Stephen