On Sat, 18 May 2024 16:54:52 -0700 Josh Snyder <j...@code406.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 <nag...@sraoss.co.jp>
