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

Dean,
Thank you for the pointer towards visibility/volatility. I think that
completely explains the effect that I am seeing in my repro. I
experimented with using a VOLATILE function for the SELECT RLS using
statement and while it completely solves my issue, it incurs too high a
cost for query execution due to the RLS policy no longer being inlined into
the scan.

I have documented your answer and my experimentation on the stack overflow
answer:
https://stackoverflow.com/questions/52565720/postgres-trigger-side-effect-is-occurring-out-of-order-with-row-level-security-s

Feel free to make edits/suggestions if you feel I missed something in
summarizing the solution. Also, this thread is still open to anyone who
can provide a solution which does not incur an optimization penalty -
however based on my new understanding of the underlying behavior I don't
believe this is possible.

Thank's to everyone for their contribution in figuring this out - much
appreciated.

Carl Sverre

http://www.carlsverre.com

On Mon, Oct 1, 2018 at 4:02 AM Dean Rasheed <dean(dot)a(dot)rasheed(at)gmail(dot)com>
wrote:

> The real issue here is to do with the visibility of the data inserted
> by the trigger function from within the same command. In general, data
> inserted by a command is not visible from within that same command.
>
> The easiest way to see what's going on is with a simple example.
> Consider the following (based on the original example, but without any
> RLS):
>
>
> DROP TABLE IF EXISTS a,b;
>
> CREATE TABLE a (id text);
> CREATE TABLE b (id text);
>
> CREATE OR REPLACE 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();
>
> CREATE OR REPLACE FUNCTION check_b1(text) RETURNS boolean AS $$
> BEGIN
> RETURN (EXISTS (SELECT * FROM b WHERE b.id = $1));
> END
> $$ LANGUAGE plpgsql STABLE;
>
> CREATE OR REPLACE FUNCTION check_b2(text) RETURNS boolean AS $$
> BEGIN
> RETURN (EXISTS (SELECT * FROM b WHERE b.id = $1));
> END
> $$ LANGUAGE plpgsql VOLATILE;
>
> INSERT INTO a VALUES ('xxx')
> RETURNING id, check_b1(id), check_b2(id),
> (EXISTS (SELECT * FROM b WHERE b.id = a.id));
>
> NOTICE: inside trigger handler
> id | check_b1 | check_b2 | exists
> -----+----------+----------+--------
> xxx | f | t | f
> (1 row)
>
> INSERT 0 1
>
>
> Notice that the functions check_b1() and check_b2() are identical,
> except that check_b1() is declared STABLE and check_b2() is declared
> VOLATILE, and that makes all the difference. Quoting from the
> documentation for function volatility [1]:
>
> 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.
>
> [1] https://www.postgresql.org/docs/10/static/xfunc-volatility.html
>
> Also notice that the inline EXISTS query behaves in the same way as
> the STABLE function -- i.e., it does not see changes made in the
> current query.
>
> So returning to the RLS example, because the RLS SELECT policy is
> defined using inline SQL, it cannot see the changes made by the
> trigger. If you want to see such changes, you need to define a
> VOLATILE function to do the RLS check.
>
> Regards,
> Dean
>