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