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?

  • What collation are you using?
    – McNets
    May 27, 2022 at 15:59
  • show lc_collate; -> en_US.UTF-8
    – Joe
    May 27, 2022 at 16:02
  • Why you're not using a case insensitive collation?
    – McNets
    May 27, 2022 at 16:05
  • Unless I missed something, I'm pretty sure they don't exist outside of citext and non-deterministic collations for postgres, which don't work for reasons listed above.
    – Joe
    May 27, 2022 at 16:10
  • 1
    Use case insensitive collations and work around the LIKE problem by using col COLLATE "C" ILIKE 'pattern'. May 27, 2022 at 17:48


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.