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

On Mon, 1 Oct 2018 at 21:45, Carl Sverre <sverre(dot)carl(at)gmail(dot)com> wrote:
> 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
>

I had a quick look at that and found a bug in your implementation. The
RLS check function is defined as follows:

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

which is incorrect because of the ambiguous reference to "id". That
final "id" will, by default, refer to the table column b.id, not the
parameter "id". Thus that function will return every row of b, and
your check won't be doing what you want. That's also going to hurt
performance, but you didn't provide enough information to diagnose the
actual performance problem that you are seeing.

In any case, the above needs to be written as

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

to work as expected.

Regards,
Dean