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

* Robert Haas (robertmhaas(at)gmail(dot)com) wrote:
> On Wed, Sep 23, 2015 at 12:01 PM, Stephen Frost <sfrost(at)snowman(dot)net> wrote:
> > * Robert Haas (robertmhaas(at)gmail(dot)com) wrote:
> >> 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.
>
> Huh? If you had an implicit false, wouldn't that prevent updating or
> deleting any rows at all?

Right, just the same as how, if RLS is enabled and no explicit policies
are provided, non-owners can't see the rows or insert/update/delete
anything in the table. The same is true for the GRANT system, where
there are no permissions granted by default. I view the lack of an
explicit definition of a WITH CHECK clause to be the same, excepting the
simple case where it's the same as USING.

> >> 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.
>
> Well, you can use a trigger, I think. But the point is that right
> now, if you try to delete a record that you don't own, it just says
> DELETE 0. Maybe there was a record there that you can't see, and
> maybe there wasn't.

Yes, a trigger would also work for this. I do understand that right now
the way it works is that there isn't an error thrown. The notion was to
provide the administrator with the option. The user in this case likely
would already have access to view the row or at least infer that the row
exists through a FK relationship. These are all post-9.5 considerations
though.

Thanks!

Stephen