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

Thank you for the detailed report Charles. I think you may be missing the
“returning id” clause in the insert. Can you verify it works when you use
“returning id”? Thanks!
On Sun, Sep 30, 2018 at 7:57 PM Charles Clavadetscher (SwissPUG) <
clavadetscher(at)swisspug(dot)org> wrote:

> Hello
>
> On 30.09.2018 23:31:32, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> wrote:
> On 9/30/18 1:13 PM, Carl Sverre wrote:
> > 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.
>
> Yeah, big oops on my part, I was running as superuser. Running as
> non-superuser resulted in the failure you see. I tried to get around
> this with no success. My suspicion is that the new row in b is not
> visible to the returning(SELECT) query in a until after the transaction
> completes. Someone with more knowledge on this then I will have to
> confirm/deny my suspicion.
>
>
> >
> > Also yes, I forgot to add the policy names, sorry about that.
> > On Sun, Sep 30, 2018 at 1:34 AM Charles Clavadetscher (SwissPUG)
> > > wrote:
> >
> > Hello
>
> --
> Adrian Klaver
> adrian(dot)klaver(at)aklaver(dot)com
>
> *[Charles] :* I also made the first test as super. However I still don't
> get any errors when executing the test query as non superuser.
>
> The user is not superuser:
>
> testuser(at)charles(dot)localhost=> SELECT CURRENT_USER;
> current_user
> --------------
> testuser
> (1 row)
>
> testuser(at)charles(dot)localhost=> \du testuser
> List of roles
> Role name | Attributes | Member of
> -----------+------------+-----------
> testuser | | {}
>
> The table privileges show that RLS is enabled and that testuser has
> SELECT and INSERT privilege on both tables. This is not related to RLS but
> simple precondition for the test:
>
> testuser(at)charles(dot)localhost=> \d a
> Table "public.a"
> Column | Type | Collation | Nullable | Default
> --------+------+-----------+----------+---------
> id | text | | |
> Policies (forced row security enabled):
> POLICY "a_insert" FOR INSERT
> WITH CHECK (true)
> POLICY "a_select" FOR SELECT
> USING ((EXISTS ( SELECT b.id
> FROM b
> WHERE (a.id = b.id))))
> Triggers:
> reprotrigger BEFORE INSERT ON a FOR EACH ROW EXECUTE PROCEDURE
> reprohandler()
>
> testuser(at)charles(dot)localhost=> \dp a
> Access privileges
> Schema | Name | Type | Access privileges | Column privileges |
> Policies
>
> --------+------+-------+-------------------------+-------------------+------------------------------
> public | a | table | charles=arwdDxt/charles+| |
> a_select (r): +
> | | | testuser=ar/charles | |
> (u): (EXISTS ( SELECT b.id+
> | | | | |
> FROM b +
> | | | | |
> WHERE (a.id = b.id))) +
> | | | | |
> a_insert (a): +
> | | | | |
> (c): true
>
> testuser(at)charles(dot)localhost=> \d b
> Table "public.b"
> Column | Type | Collation | Nullable | Default
> --------+------+-----------+----------+---------
> id | text | | |
>
> testuser(at)charles(dot)localhost=> \dp b
> Access privileges
> Schema | Name | Type | Access privileges | Column privileges |
> Policies
>
> --------+------+-------+-------------------------+-------------------+----------
> public | b | table | charles=arwdDxt/charles+| |
> | | | testuser=ar/charles | |
>
> And now the test:
>
> testuser(at)charles(dot)localhost=> SELECT * FROM a;
> id
> ----
> (0 rows)
>
> testuser(at)charles(dot)localhost=> SELECT * FROM b;
> id
> ----
> (0 rows)
>
> testuser(at)charles(dot)localhost=> INSERT INTO a VALUES ('fails');
> NOTICE: inside trigger handler
> INSERT 0 1
> testuser(at)charles(dot)localhost=> SELECT * FROM a;
> id
> -------
> fails
> (1 row)
>
> testuser(at)charles(dot)localhost=> SELECT * FROM b;
> id
> -------
> fails
> (1 row)
>
> Version of PG:
> testuser(at)charles(dot)localhost=> SELECT version();
> version
> ------------------------------------------------------------
> PostgreSQL 10.5, compiled by Visual C++ build 1800, 64-bit
> (1 row)
>
> Regards
> Charles
>
> --
Carl Sverre