Postgres Trigger side-effect is occurring out of order with row-level security select policy

Question

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.

For simplicity the rest of this question will discuss the issue using the following simplified tables:

CREATE TABLE a (id TEXT);
ALTER TABLE a ENABLE ROW LEVEL SECURITY;
ALTER TABLE a FORCE ROW LEVEL SECURITY;

CREATE TABLE b (id TEXT);

Issue

Consider the following policies and triggers:

CREATE POLICY aSelect ON a FOR SELECT
USING (EXISTS(
    select * from b where a.id = b.id
));

CREATE POLICY aInsert 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();

Now consider the following statement:

INSERT INTO a VALUES ('fails') returning id;

The expectation I have based on reading the policies applied by command type table and general SQL understanding is that the following things should happen in order:

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

Unfortunately (as you may have guessed), rather than the above steps happening we see this:

test=> INSERT INTO a VALUES ('fails') returning id;
NOTICE:  inside trigger handler
ERROR:  new row violates row-level security policy for table "a"

The goal of this question is to discover why the expected behavior does not occur.

Note that the following statements operated correctly as expected:

test=> INSERT INTO a VALUES ('works');
NOTICE:  inside trigger handler
INSERT 0 1
test=> select * from a; select * from b;
  id   
-------
 works
(1 row)

  id   
-------
 works
(1 row)

What have I tried?

• Experimented with BEFORE versus AFTER in the trigger definition
  • AFTER results in the trigger not executing at all
• Experimented with defining a single policy which applies to ALL commands (with the same using/with check expression)
  • results in the same behavior

Appendix

• Postgres Version
  • PostgreSQL 10.3 on x86_64-pc-linux-musl, compiled by gcc (Alpine 6.4.0) 6.4.0, 64-bit
• If you try to repro the issue make sure you are not running with SUPER permissions as that will ignore row-security

Answer 1

After some back and forth with other PostgreSQL users/developers on the general mailing list, it was determined that this particular issue is caused by mutation visibility within a single statement. You can review the entire discussion here. Special thanks to Dean Rasheed for explaining the problem and coming up with a solution. I have summarized his answer here for the benefit of the Stack Overflow community.

In summary, the row inserted by the trigger is not visible by the subsequent EXISTS clause in the row-level security SELECT policy due to the entire statement running within a single PostgreSQL snapshot.

One way to get around this issue is to ensure that the EXISTS clause is run with a new snapshot. To do this, the EXISTS clause can use a PostgreSQL function marked VOLATILE. This function attribute will cause the function to be able to observe changes made within the same statement. For more information refer to the documentation. The relevant paragraph is extracted here for reference:

For functions written in SQL or in any of the standard procedural languages, there is a second important property determined by the volatility category, namely the visibility of any data changes that have been made by the SQL command that is calling the function. A VOLATILE function will see such changes, a STABLE or IMMUTABLE function will not. This behavior is implemented using the snapshotting behavior of MVCC (see Chapter 13): STABLE and IMMUTABLE functions use a snapshot established as of the start of the calling query, whereas VOLATILE functions obtain a fresh snapshot at the start of each query they execute.

So, one solution to this problem is to implement the RLS select policy as a VOLATILE function. An example modification to the policy would be:

CREATE OR REPLACE FUNCTION rlsCheck(_id text) RETURNS TABLE (id text) AS $$
    select * from b where b.id = _id
$$ LANGUAGE sql VOLATILE;

CREATE POLICY reproPolicySelect ON a FOR SELECT
USING (
    EXISTS(select * from rlsCheck(a.id))
);

In this solution, each row projected from table a will require that the function rlsCheck returns at least one row. This function will be run with a new snapshot for each projected row. The new snapshot generated by each invocation of rlsCheck will allow it to see the modification of table b by the INSERT trigger in the original example.

If you make the above modification and run the test, you will see the following behavior:

test=> select * from a;
id 
----
(0 rows)

test=> select * from b;
id 
----
(0 rows)

test=> insert into a values ('hi') returning id;
NOTICE:  inside trigger handler
id 
----
hi
(1 row)

INSERT 0 1

This behavior is consistent with my expectation so I am accepting this as an answer to the problem. Unfortunately, the function results in an unacceptable optimization fence during query execution so I will not be using this in my RBAC implementation. I don't believe that it is possible to have an optimizable solution to my problem since the EXISTS expression in the SELECT policy can not be inlined and VOLATILE at the same time.