3

Over the past week, I’ve been working on implementing text search in a NestJS application using PostgreSQL. The challenge is that the app supports multiple tenants, all sharing the same tables, so we use Row-Level Security (RLS) to isolate data between tenants.

I want to perform simple text searches using LIKE and ILIKE, but the catch is: the data is stored case-sensitive in the database, and I want the searches to be case-insensitive.I also would like to extend this in the future to do some more complex searching.

At first, I used GIN indexes because I read they are well-suited for text search in PostgreSQL.

When I tested queries as a user without Row-Level Security (RLS), the indexes were used as expected. However, when I ran the same queries as a user with RLS enabled, the GIN indexes were ignored.

After some research I learned that, due to “leakproof” requirements in PostgreSQL RLS, GIN indexes cannot be used in queries because they are not leakproof.

Now I’m wondering: is it even possible to do efficient text search while using RLS? And what are the recommended approaches to handle this scenario?

Improve this question
3
  • 1
    We resolved this issue a few years ago by using the customer_id in the WHERE condition, not just the RLS policy. The btree_gin extension was used for the multi-column GIN index. I'm sorry, I no longer have the details. Commented Jul 10 at 17:48
  • LEAKPROOF is unrelated to indexes, so I don't think that's the reason. You have to analyze the execution with EXPLAIN (ANALYZE, BUFFERS). Commented Jul 10 at 19:59
  • Postgres' FTS search is ok in a pinch, but generally there are much better choices. Even their trigram extension tends to be more useful. Unless there's some important feature that only it can provide, you might want to evaluate other alternatives. Commented Jul 11 at 20:14
Related questions
2642
How to show tables in PostgreSQL?
1993
How can I change a PostgreSQL user password?
1975
How to exit from PostgreSQL command line utility: psql
Load 7 more related questions Show fewer related questions

0

Reset to default

Your Answer

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

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.