Re: Index selection issues with RLS using expressions

Greetings,

We prefer to use in-line responses, please don't top-post on these
lists.

* Alastair McKinley (a(dot)mckinley(at)analyticsengines(dot)com) wrote:
> Thanks for looking at this! It seems like there are quite a few performance gotchas around leaky operators and RLS, this is my second encounter with this issue in the last few weeks.

It ends up being a choice between being correct (as in, implementing the
security that RLS is intended to provide) vs. being fast (better
performance). If you aren't concerned with the security risk posed by
leaky functions, you can use regular views, but you need to realize that
users who can execute arbitrary SQL on the database system would very
likely be able to then circumvent your views and get access to all of
the data in the table.

> What would you recommend as a reasonable workaround?

The right solution is really to hack on PG to make it support having
the && operator (and equality ones, as needed, though many already are)
be marked leakproof, with enough intelligence to also pass that
information through to the parts that need to know, as Tom mentions.
That's not going to happen for v13 at this point as I don't think
anyone's actively working on that, but it could possibly be worked on
for v14.

> I have a large table with a gin index that I would like to use RLS on and use the @@ text search operator. My initial thought is to use a security definer set-returning function that implements the RLS policy explicitly. Would a security barrier view also potentially work?

A security barrier view is also going to prevent non-leakproof functions
from being pushed down (that's what being a security barrier view
more-or-less exactly means).

Using a security-definer function could possible work, but you can't
just mark a function as leakproof that actually isn't leakproof or, as
Tom said before, you're creating a security hole.

Thanks,

Stephen