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

Hello

On 01.10.2018 05:00:02, Carl Sverre <sverre(dot)carl(at)gmail(dot)com> wrote:
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!
[Charles] : You are right:

testuser(at)charles(dot)localhost=> INSERT INTO a VALUES ('fails2') RETURNING id;
NOTICE:  inside trigger handler
ERROR:  new row violates row-level security policy for table "a"

This implies that the returning_id is evaluated before the trigger executes, i.e. Adrian's assumption is probably correct.

Regards
Charles

On Sun, Sep 30, 2018 at 7:57 PM Charles Clavadetscher (SwissPUG) <clavadetscher(at)swisspug(dot)org [mailto:clavadetscher(at)swisspug(dot)org]> wrote:

Hello

On 30.09.2018 23:31:32, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com [mailto: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 [mailto: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 [http://b.id]
   FROM b
  WHERE (a.id [http://a.id] = b.id [http://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 [http://b.id]+
        |      |       |                         |                   |    FROM b                   +
        |      |       |                         |                   |   WHERE (a.id [http://a.id] = b.id [http://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