I have a set of tables protected by RLS, and those tables contain TEXT columns. I want to be able to query on these columns case-independently.
Previously, this was done by simply querying á la lower(Foo) = lower(Bar)
together with an appropriate index with expression. However, lower()
is not leakproof, which causes it to be evaluated after any RLS policy. This results in a sequence scan across entire tables, applying the RLS policy. Since our introduction of RLS it has been a major headache to figure out how to handle this issue.
As the platform is hosted on AWS RDS, we do not have "true" superuser access, which severely limits our options. The following options don't seem to work:
- Creating a custom "lower()" function that is leakproof (fails because we lack the true superuser rights on AWS to create leakproof functions)
- Forcing built-in lower() to be leakproof through modifying the pg-catalog (obviously fails due to same reasoning as above)
- Using citext in place of text columns (fails because apparently citext does not pass through the security barrier, either)
- Using non-deterministic collations (works actually, but the lack of 'LIKE' support makes this unviable)
Outside of creating a new column for every column we want to query on in combination with a trigger that automatically saves it as a lowercase copy, I'm kinda running low on ideas here. Is there a way that I have missed? Is there maybe a built-in lower() method from an extension that happens to be leakproof?
LIKE
problem by usingcol COLLATE "C" ILIKE 'pattern'
.