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

["Charles Clavadetscher (SwissPUG)" <clavadetscher@xxxxxxxxxxxx>]

Hello

On 29.09.2018 20:24:45, Adrian Klaver <adrian.klaver@xxxxxxxxxxx> wrote:

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 = 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.

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.klaver@xxxxxxxxxxx

[Charles] : I did the same test with PG version 10 on Windows and PG 9.6.2 on Linux (RedHat) with exactly the same result.

db=# INSERT INTO a VALUES ('fails') returning id;

NOTICE:  inside trigger handler

  id

-------

 fails

(1 row)

INSERT 0 1

db=# select * from a;

  id

-------

 fails

(1 row)

db=# select * from b;

  id

-------

 fails

(1 row)

Regards

Charles