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

Greetings,

Please don't top-post on these lists.

* Derek Hans (derek(dot)hans(at)gmail(dot)com) wrote:
> Thanks for the detailed response, super helpful in understanding what's
> happening, in particular understanding the risk of not marking functions as
> leakproof. I'll take a look at the underlying code to understand what's
> involved in getting a function to be leakproof.

Great.

> That said, it does seem like it should be possible and reasonable to
> specify that a user should have access to the table stats so that the query
> planner works as expected. Maybe it comes down to the fact that RLS is
> still a work in progress, and I shouldn't be relying on it unless I'm
> really certain it supports the functionality I need.

PostgreSQL is still very much a work in progress. :)

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

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

Thanks,

Stephen