From: | Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> |
---|---|
To: | Carl Sverre <sverre(dot)carl(at)gmail(dot)com>, pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Re: Postgres trigger side-effect is occurring out of order with row-level security select policy |
Date: | 2018-09-29 18:23:55 |
Message-ID: | 7e9e8e8b-c368-8007-4a1d-b67308d26dd6@aklaver.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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 <http://a.id> = b.id <http://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 <http://a.id> =
> b.id <http://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(dot)klaver(at)aklaver(dot)com
From | Date | Subject | |
---|---|---|---|
Next Message | Adrian Klaver | 2018-09-29 18:37:11 | Re: ORM |
Previous Message | Arup Rakshit | 2018-09-29 16:58:35 | Re: Why my query not using index to sort? |