Thanks for the initial results. Can you check that you are not using super permissions and are enabling row security when running the test? Super ignores row security.
Also yes, I forgot to add the policy names, sorry about that. On Sun, Sep 30, 2018 at 1:34 AM Charles Clavadetscher (SwissPUG) < clavadetsc...@swisspug.org> wrote: > Hello > > > On 29.09.2018 20:24:45, Adrian Klaver <adrian.kla...@aklaver.com> wrote: > On 9/28/18 11:35 PM, Carl Sverre wrote: > > *Context* > > I am using row-level security along with triggers to implement a pure > > SQL RBAC implementation. While doing so I encountered a weird behavior > > between INSERT triggers and SELECT row-level security policies. > > > > *Question* > > I have posted a very detailed question on StackOverflow here: > > > https://stackoverflow.com/questions/52565720/postgres-trigger-side-effect-is-occurring-out-of-order-with-row-level-security-s > > > > For anyone who is just looking for a summary/repro, I am seeing the > > following behavior: > > > > CREATE TABLE a (id TEXT); > > ALTER TABLE a ENABLE ROW LEVEL SECURITY; > > ALTER TABLE a FORCE ROW LEVEL SECURITY; > > > > CREATE TABLE b (id TEXT); > > > > CREATE POLICY ON a FOR SELECT > > USING (EXISTS( > > select * from b where a.id = b.id > > )); > > > > CREATE POLICY ON a FOR INSERT > > WITH CHECK (true); > > > > CREATE FUNCTION reproHandler() RETURNS TRIGGER AS $$ > > BEGIN > > RAISE NOTICE USING MESSAGE = 'inside trigger handler'; > > INSERT INTO b (id) VALUES (NEW.id); > > RETURN NEW; > > END; > > $$ LANGUAGE plpgsql; > > > > CREATE TRIGGER reproTrigger BEFORE INSERT ON a > > FOR EACH ROW EXECUTE PROCEDURE reproHandler(); > > > > INSERT INTO a VALUES ('fails') returning id; > > NOTICE: inside trigger handler > > ERROR: new row violates row-level security policy for table "a" > > > > Rather than the error, I expect that something along these lines should > > occur instead: > > > > 1. A new row ('fails') is staged for INSERT > > 2. The BEFORE trigger fires with NEW set to the new row > > 3. The row ('fails') is inserted into b and returned from the trigger > > procedure unchanged > > 4. The INSERT's WITH CHECK policy true is evaluated to true > > 5. The SELECT's USING policy select * from b where a.id = > > b.id is evaluated. *This should return true due to step 3* > > > 6. Having passed all policies, the row ('fails') is inserted in table > > 7. The id (fails) of the inserted row is returned > > > > If anyone can point me in the right direction I would be extremely > thankful. > > When I tried to reproduce the above I got: > > test=# CREATE POLICY ON a FOR SELECT > test-# USING (EXISTS( > test(# select * from b where a.id = b.id > test(# )); > ERROR: syntax error at or near "ON" > LINE 1: CREATE POLICY ON a FOR SELECT > ^ > test=# > test=# CREATE POLICY ON a FOR INSERT > test-# WITH CHECK (true); > ERROR: syntax error at or near "ON" > LINE 1: CREATE POLICY ON a FOR INSERT > > Changing your code to: > > CREATE TABLE a (id TEXT); > ALTER TABLE a ENABLE ROW LEVEL SECURITY; > ALTER TABLE a FORCE ROW LEVEL SECURITY; > > CREATE TABLE b (id TEXT); > > CREATE POLICY a_select ON a FOR SELECT > USING (EXISTS( > select * from b where a.id = b.id > )); > > CREATE POLICY a_insert ON a FOR INSERT > WITH CHECK (true); > > CREATE FUNCTION reproHandler() RETURNS TRIGGER AS $$ > BEGIN > RAISE NOTICE USING MESSAGE = 'inside trigger handler'; > INSERT INTO b (id) VALUES (NEW.id); > RETURN NEW; > END; > $$ LANGUAGE plpgsql; > > CREATE TRIGGER reproTrigger BEFORE INSERT ON a > FOR EACH ROW EXECUTE PROCEDURE reproHandler(); > > Resulted in: > > test=# INSERT INTO a VALUES ('fails') returning id; > NOTICE: inside trigger handler > id > ------- > fails > (1 row) > > INSERT 0 1 > test=# select * from a; > id > ------- > fails > (1 row) > > > > > > Carl Sverre > > > > http://www.carlsverre.com > > > -- > Adrian Klaver > adrian.kla...@aklaver.com > > *[Charles] :* I did the same test with PG version 10 on Windows and PG > 9.6.2 on Linux (RedHat) with exactly the same result. > > db=# INSERT INTO a VALUES ('fails') returning id; > NOTICE: inside trigger handler > id > ------- > fails > (1 row) > > INSERT 0 1 > db=# select * from a; > id > ------- > fails > (1 row) > > db=# select * from b; > id > ------- > fails > (1 row) > > Regards > Charles > > > -- Carl Sverre
