Re: Row Level Security UPDATE Confusion

Hmm.

UPDATE seems to work as described (unable to create records you cannot
select); both the single rule version and multi-rule version seem to work
the same.

This combination works too though it seems funny that UPDATE can create an
entry it cannot reverse. I can set the value to 100 (going to -1 fails) but
the UPDATE cannot see the record to set it back. I can see use cases for
it, for example you might be able to promote someone to manager but not
demote a manager to front-desk. We also allow INSERT on tables you cannot
delete from, so it's not inconsistent.

CREATE POLICY split_select ON t FOR SELECT TO split USING (value > 0);
CREATE POLICY split_update ON t FOR UPDATE TO split USING (value < 10) WITH
CHECK (value > 2);
SET session authorization split;
update t set value = 100 where value = 4; -- 1 record changed
update t set value = 5 where value = 100; -- 0 records changed

However, despite INSERT also functioning the same for both styles of
commands it's definitely not obeying the `cannot give away records` rule:

CREATE USER simple;
CREATE USER split;
CREATE TABLE t(value int);
grant select, update, insert, delete on table t to simple, split;

INSERT INTO t values (1), (2);

ALTER TABLE t ENABLE ROW LEVEL SECURITY;
CREATE POLICY simple_all ON t TO simple USING (value > 0) WITH CHECK (true);

CREATE POLICY split_select ON t FOR SELECT TO split USING (value > 0);
CREATE POLICY split_insert ON t FOR INSERT TO split WITH CHECK (true);

SET session authorization simple;
INSERT INTO t VALUES (3), (-3);
SELECT * FROM t;
value
-------
1
2
3
(3 rows)

SET session authorization split;
INSERT INTO t VALUES (4), (-4);
SELECT * FROM t;
value
-------
1
2
3
4
(4 rows)

SET session authorization default;
SELECT * FROM t;
value
-------
1
2
3
-3
4
-4
(6 rows)

regards,

Rod

On Fri, May 5, 2017 at 12:10 PM, Stephen Frost <sfrost(at)snowman(dot)net> wrote:

> Rod, Robert,
>
> * Robert Haas (robertmhaas(at)gmail(dot)com) wrote:
> > On Fri, Apr 14, 2017 at 9:16 AM, Stephen Frost <sfrost(at)snowman(dot)net>
> wrote:
> > > I agreed already up-thread that there's an issue there and will be
> > > looking to fix it. That comment was simply replying to Rod's point
> that
> > > the documentation could also be improved.
> >
> > OK, thanks. The wrap for the next set of minor releases is, according
> > to my understanding, scheduled for Monday, so you'd better jump on
> > this soon if you're hoping to get a fix out this time around.
>
> The attached patch against master fixes this issue. Rod, if you get a
> chance, would be great for you to check that you no longer see a
> difference between the single ALL policy and the split SELECT/UPDATE
> policies.
>
> Thanks!
>
> Stephen
>

--
Rod Taylor