Re: GIST/GIN index not used with Row Level Security

>
>
> > I've updated word_similarity_op(text,text) to be leakproof, and
> > pg_proc agrees it is. I'm assuming word_similarity_op() is equivalent to
> > <%, though I haven't found explicit confirmation. However, using
> > word_similarity() instead of <% on a 100k row table, without any RLS
> > involved, doesn't make use of the index, while using <% does. Obviously,
> > adding the RLS doesn't make that any better. Any idea what might be the
> > cause?
>
> Just to be clear, you should be looking at pg_operator (oprcode) to
> determine the function that is under the operator that you wish to
> change to being leakproof.
>
>
Thanks for that pointer.

> Note that the selectivity functions are associated with the operator,
> not the function itself.
>

That was the missing piece, thanks. How come operators get optimized but
functions don't?

Quick summary:
The text similarity/full text search/like operators are not marked as
leakproof, which stops them from having access to table statistics. When
combined with row level security, operators that aren't leakproof can't get
pushed down and therefore happen after the RLS check, preventing use of
GIN/GIST indexes. A workaround is marking the underlying function as
leakproof but that is only reasonable because our particular setup makes it
acceptable if information leaks via database error messages.

To resolve:
- Lookup function associated with operator being used via the pg_operator
table
- Check if that function is leakproof based on info in pg_proc table
- ALTER FUNCTION func LEAKPROOF
- Use original operator in code - the underlying function doesn't get
optimized and bypasses the index

While those steps work on my local machine, unfortunately we're deployed on
AWS Aurora which doesn't allow marking functions as leakproof. Functions
are owned by the rdsadmin user and controlled by AWS. In practice, that
appears to mean that fuzzy search/full text search with reasonable
performance isn't compatible with RLS on Amazon Aurora. We may end up
setting up Elasticsearch to support text search. In any case, we need to
separate search from checking who is allowed to see the results.

Thanks for the help from everyone!