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:
('fails')
is staged for INSERT
BEFORE
trigger fires with NEW
set to the new row('fails')
is inserted into b
and returned from the trigger procedure unchangedINSERT
's WITH CHECK
policy true
is evaluated to true
SELECT
's USING
policy select * from b where a.id = b.id
is evaluated. This should return true due to step 3
('fails')
is inserted in table fails
) of the inserted row is returnedUnfortunately (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?
BEFORE
versus AFTER
in the trigger definition
AFTER
results in the trigger not executing at all
ALL
commands (with the same using/with check expression)
Appendix
PostgreSQL 10.3 on x86_64-pc-linux-musl, compiled by gcc (Alpine 6.4.0) 6.4.0, 64-bit
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With