Content-Type: multipart/mixed; boundary="------------q2VW1OkGnake4WkGn8XY3x0n"
Message-ID: <5af3bf0c-5e0c-4128-81dc-084c5258b1af@code406.com>
Date: Sat, 18 May 2024 16:54:52 -0700
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Content-Language: en-US
From: Josh Snyder <josh@code406.com>
Subject: PATCH: Add query for operators unusable with RLS to documentation
To: pgsql-hackers@postgresql.org
Archived-At: <https://www.postgresql.org/message-id/5af3bf0c-5e0c-4128-81dc-084c5258b1af%40code406.com>
Precedence: bulk

This is a multi-part message in MIME format.
--------------q2VW1OkGnake4WkGn8XY3x0n
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 7bit

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.

Thanks,
Josh

[1] https://www.postgresql.org/message-id/CAGrP7a2t%2BJbeuxpQY%2BRSvNe4fr3%2B%3D%3DUmyimwV0GCD%2BwcrSSb%3Dw%40mail.gmail.com
--------------q2VW1OkGnake4WkGn8XY3x0n
Content-Type: text/x-patch; charset=UTF-8;
 name="0001-Add-query-for-operators-unusable-with-RLS.patch"
Content-Disposition: attachment;
 filename="0001-Add-query-for-operators-unusable-with-RLS.patch"
Content-Transfer-Encoding: base64

RnJvbSBkYjM4MjY5N2YxNGJkMTJkZDllMjk2MDZjMzE0ZDdhMzViZDI5MGVhIE1vbiBTZXAg
MTcgMDA6MDA6MDAgMjAwMQpGcm9tOiBKb3NoIFNueWRlciA8am9zaEBjb2RlNDA2LmNvbT4K
RGF0ZTogU2F0LCAxOCBNYXkgMjAyNCAxNTo1MDo0NyAtMDcwMApTdWJqZWN0OiBbUEFUQ0hd
IEFkZCBxdWVyeSBmb3Igb3BlcmF0b3JzIHVudXNhYmxlIHdpdGggUkxTCgotLS0KIGRvYy9z
cmMvc2dtbC9ydWxlcy5zZ21sIHwgMjYgKysrKysrKysrKysrKysrKysrKysrKysrKysKIDEg
ZmlsZSBjaGFuZ2VkLCAyNiBpbnNlcnRpb25zKCspCgpkaWZmIC0tZ2l0IGEvZG9jL3NyYy9z
Z21sL3J1bGVzLnNnbWwgYi9kb2Mvc3JjL3NnbWwvcnVsZXMuc2dtbAppbmRleCA3YTkyOGJk
N2I5Li4zYjEyODNjMDAyIDEwMDY0NAotLS0gYS9kb2Mvc3JjL3NnbWwvcnVsZXMuc2dtbAor
KysgYi9kb2Mvc3JjL3NnbWwvcnVsZXMuc2dtbApAQCAtMjE2Nyw2ICsyMTY3LDMyIEBAIENS
RUFURSBWSUVXIHBob25lX251bWJlciBXSVRIIChzZWN1cml0eV9iYXJyaWVyKSBBUwogICAg
IHZpZXcncyByb3cgZmlsdGVycy4KIDwvcGFyYT4KIAorPHBhcmE+CisgICAgV2hlbiB3b3Jr
aW5nIHdpdGggPGxpdGVyYWw+c2VjdXJpdHlfYmFycmllcjwvbGl0ZXJhbD4gdmlld3Mgb3Ig
cm93LWxldmVsCisgICAgc2VjdXJpdHkgcG9saWNpZXMsIGEgcXVlcnkgYXV0aG9yIGNhbiBj
aGVjayBpbiBhZHZhbmNlIHdoaWNoIGluZGV4LWFjY2VzcworICAgIG9wZXJhdG9ycyBjYW5u
b3QgY3Jvc3MgdGhlIHNlY3VyaXR5IGJhcnJpZXIgYnkgcXVlcnlpbmcgc3lzdGVtIGNhdGFs
b2dzOgorPC9wYXJhPgorCis8cHJvZ3JhbWxpc3Rpbmc+CitTRUxFQ1QKKyAgYW1uYW1lLAor
ICBmb3JtYXQoJyVzKCVzLCVzKSBbJXNdJywgb3ByY29kZSwgbGVmdHR5cGUudHlwbmFtZSwg
cmlnaHR0eXBlLnR5cG5hbWUsIG9wcm5hbWUpCitGUk9NIHBnX29wZXJhdG9yCisgIEpPSU4g
cGdfYW1vcCBPTiBwZ19vcGVyYXRvci5vaWQgPSBwZ19hbW9wLmFtb3BvcHIKKyAgSk9JTiBw
Z19hbSBPTiBwZ19hbW9wLmFtb3BtZXRob2QgPSBwZ19hbS5vaWQKKyAgSk9JTiBwZ19wcm9j
IE9OIHBnX29wZXJhdG9yLm9wcmNvZGUgPSBwZ19wcm9jLm9pZAorICBKT0lOIHBnX3R5cGUg
bGVmdHR5cGUgT04gcGdfYW1vcC5hbW9wbGVmdHR5cGUgPSBsZWZ0dHlwZS5vaWQKKyAgSk9J
TiBwZ190eXBlIHJpZ2h0dHlwZSBPTiBwZ19hbW9wLmFtb3ByaWdodHR5cGUgPSByaWdodHR5
cGUub2lkCitXSEVSRSBwcm9sZWFrcHJvb2YgPSBmYWxzZTsKKzwvcHJvZ3JhbWxpc3Rpbmc+
CisKKzxwYXJhPgorICAgIEFueSBvcGVyYXRvciByZXR1cm5lZCBieSB0aGUgYWJvdmUgcXVl
cnkgd2lsbCBub3QgYmUgYWJsZSB0byBwZXJmb3JtIGFuCisgICAgaW5kZXhlZCBsb29rdXAg
dGhyb3VnaCB0aGUgc2VjdXJpdHkgYmFycmllci4gSW4gdGhhdCBjYXNlLCB0aGUgdmlldydz
IG93bgorICAgIGNvbmRpdGlvbnMgd2lsbCBiZSBhcHBsaWVkIGZpcnN0LCBmb2xsb3dlZCBi
eSBjb25kaXRpb25zIGFkZGVkIGJ5IHRoZSBxdWVyeQorICAgIGF1dGhvci4KKzwvcGFyYT4K
KwogPHBhcmE+CiAgICAgSXQgaXMgaW1wb3J0YW50IHRvIHVuZGVyc3RhbmQgdGhhdCBldmVu
IGEgdmlldyBjcmVhdGVkIHdpdGggdGhlCiAgICAgPGxpdGVyYWw+c2VjdXJpdHlfYmFycmll
cjwvbGl0ZXJhbD4gb3B0aW9uIGlzIGludGVuZGVkIHRvIGJlIHNlY3VyZSBvbmx5Ci0tIAoy
LjQwLjEKCg==

--------------q2VW1OkGnake4WkGn8XY3x0n--