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) <
clavadetsc...@swisspug.org> wrote:

> Hello
>
>
> On 29.09.2018 20:24:45, Adrian Klaver <adrian.kla...@aklaver.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.kla...@aklaver.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

Reply via email to