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
