Re: Row Level Security UPDATE Confusion

On Thu, Apr 13, 2017 at 5:31 PM, Stephen Frost <sfrost(at)snowman(dot)net> wrote:

> Rod, all,
>
> * Joe Conway (mail(at)joeconway(dot)com) wrote:
> > On 04/13/2017 01:31 PM, Stephen Frost wrote:
> > > * Robert Haas (robertmhaas(at)gmail(dot)com) wrote:
> > >> On Thu, Apr 6, 2017 at 4:05 PM, Rod Taylor <rod(dot)taylor(at)gmail(dot)com>
> wrote:
> > >> > I'm a little confused on why a SELECT policy fires against the NEW
> record
> > >> > for an UPDATE when using multiple FOR policies. The ALL policy
> doesn't seem
> > >> > to have that restriction.
> > >>
> > >> My guess is that you have found a bug.
> > >
> > > Indeed. Joe's been looking into it and I'm hoping to find some time to
> > > dig into it shortly.
> >
> > >> CREATE POLICY split_select ON t FOR SELECT TO split
> > >> USING (value > 0);
> > >> CREATE POLICY split_update ON t FOR UPDATE TO split
> > >> USING (true) WITH CHECK (true);
> >
> > Yes -- from what I can see in gdb:
>
> Actually, looking at this again, the complaint appears to be that you
> can't "give away" records. That was a topic of much discussion and I'm
> reasonably sure that was what we ended up deciding made the most sense.
> You have to be able to see records to be able to update them (you can't
> update records you can't see), and you have to be able to see the result
> of your update. I don't doubt that we could improve the documentation
> around this (and apparently the code comments, according to Joe..).
>
>
Then there is a bug in the simpler statement which happily lets you give
away records.

CREATE POLICY simple_all ON t TO simple USING (value > 0) WITH CHECK (true);

SET session authorization simple;
SELECT * FROM t;
UPDATE t SET value = value * -1 WHERE value = 1;
-- No error and value is -1 at the end.

--
Rod Taylor