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

Thanks for the initial results. Can you check that you are not using super
permissions and are enabling row security when running the test? Super
ignores row security.

Also yes, I forgot to add the policy names, sorry about that.
On Sun, Sep 30, 2018 at 1:34 AM Charles Clavadetscher (SwissPUG) <
clavadetscher(at)swisspug(dot)org> wrote:

> Hello
>
>
> On 29.09.2018 20:24:45, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> 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(dot)klaver(at)aklaver(dot)com
>
> *[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
>
>
> --
Carl Sverre