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:
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(
));
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.