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

Dean,
Thank you for catching that bug, I have updated the StackOverflow answer to
account for that issue.

As for the optimization problem I mentioned, the issue seems to be that
running a function that acquires a snapshot for each row is much slower
than in-lining a nested loop over table b into the query. I have attached
a psql session that demonstrates the exact performance issue I am referring
to.

Carl Sverre

http://www.carlsverre.com

On Tue, Oct 2, 2018 at 1:28 AM Dean Rasheed <dean(dot)a(dot)rasheed(at)gmail(dot)com>
wrote:

> 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
>