From: | Carl Sverre <sverre(dot)carl(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Postgres trigger side-effect is occurring out of order with row-level security select policy |
Date: | 2018-09-29 06:35:44 |
Message-ID: | CADUo9RHPvJhKXs33t-39KqZ831JhQs=P_Ckoeg7C5OHvNLP0TQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
*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
From | Date | Subject | |
---|---|---|---|
Next Message | Arup Rakshit | 2018-09-29 08:11:24 | Re: Why my query not using index to sort? |
Previous Message | Raghavendra Rao J S V | 2018-09-29 02:05:58 | Re: How to maintain the csv log files in pg_log directory only for past 30 days |