Re: PATCH: Add query for operators unusable with RLS to documentation

On Sat, 18 May 2024 16:54:52 -0700
Josh Snyder <josh(at)code406(dot)com> wrote:

> When deploying RLS, I was surprised to find that certain queries which used
> only builtin indexes and operators had dramatically different query plans when
> a policy is applied. In my case, the query `tsvector @@ tsquery` over a GIN
> index was no longer able to use that index. I was able to find one other
> instance [1] of someone being surprised by this behavior on the mailing lists.
>
> The docs already discuss the LEAKPROOF semantics in the abstract, but I think
> they place not enough focus on the idea that builtin operators can be (and
> frequently are) not leakproof. Based on the query given in the attached patch,
> I found that 387 operators are not leakproof versus 588 that are.
>
> The attached patch updates the documentation to provide an easy query over
> system catalogs as a way of determining which operators will no longer perform
> well under RLS or a security-barrier view.

I think it would be worth mentioning an index involving non-LEAKPROOF operator
could not work with RLS or a security-barrier view in the documentation.
(e.g. like https://www.postgresql.org/message-id/2273225.DEBA8KRT0r%40peanuts2)
It may help to avoid other users from facing the surprise you got.

However, I am not sure if it is appropriate to write the query consulting
pg_amop in this part of the documentation.It is enough to add a reference to
the other part describing operation familiar, for example, "11.10. Operator Classes
and Operator Families"? Additionally, is it useful to add LEAKPROOF information
to the result of psql \dAo(+) meta-comand, or a function that can check given index
or operator is leakproof or not?

Regards,
Yugo Nagata

> Thanks,
> Josh
>
> [1] https://www.postgresql.org/message-id/CAGrP7a2t%2BJbeuxpQY%2BRSvNe4fr3%2B%3D%3DUmyimwV0GCD%2BwcrSSb%3Dw%40mail.gmail.com

--
Yugo NAGATA <nagata(at)sraoss(dot)co(dot)jp>