I want to adhere to the best practices and obviously encrypt the data sent to me by user. And I also want to provide the full-text search feature to the user (user can only search their own data, if that is not quite obvious).
There's a messaging app which is privacy oriented (for the simplicity of the example, we omit the e2e-encryption). This app stores messages in an encrypted form in a PostgreSQL database. I would like to provide a fulltext search feature, thus I need to build an fts-index.
I, however, cannot think of any possible solution. How to achieve it?
I am not asking about messaging app architecture, just a way to use fulltextsearch on encrypted data. And think of some other usecases, where the data to be searched is in orders of terrabytes, so offsetting the fts indexing to the client itself does not seem like a viable solution.