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) < clavadetsc...@swisspug.org> wrote:
> Hello > > On 30.09.2018 23:31:32, Adrian Klaver <adrian.kla...@aklaver.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.kla...@aklaver.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@charles.localhost=> SELECT CURRENT_USER; > current_user > -------------- > testuser > (1 row) > > testuser@charles.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@charles.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@charles.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@charles.localhost=> \d b > Table "public.b" > Column | Type | Collation | Nullable | Default > --------+------+-----------+----------+--------- > id | text | | | > > testuser@charles.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@charles.localhost=> SELECT * FROM a; > id > ---- > (0 rows) > > testuser@charles.localhost=> SELECT * FROM b; > id > ---- > (0 rows) > > testuser@charles.localhost=> INSERT INTO a VALUES ('fails'); > NOTICE: inside trigger handler > INSERT 0 1 > testuser@charles.localhost=> SELECT * FROM a; > id > ------- > fails > (1 row) > > testuser@charles.localhost=> SELECT * FROM b; > id > ------- > fails > (1 row) > > Version of PG: > testuser@charles.localhost=> SELECT version(); > version > ------------------------------------------------------------ > PostgreSQL 10.5, compiled by Visual C++ build 1800, 64-bit > (1 row) > > Regards > Charles > > -- Carl Sverre