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@snowman.net> wrote:
Rod, Robert, * Robert Haas (robertmhaas@gmail.com) wrote: > On Fri, Apr 14, 2017 at 9:16 AM, Stephen Frost <sfrost@snowman.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.
