*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. Carl Sverre http://www.carlsverre.com
