Postgres trigger side-effect is occurring out of order with row-level security select policy

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

http://www.carlsverre.com

Responses

Browse pgsql-general by date

  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