1

In Elasticsearch we can configure an index to not store the source data. Is it possible to recreate this in PostgreSQL?

Today, we are building an application that encrypts all (sensitive) data before inserting it into the database, and we want to keep it that way. This makes searching hard, but solvable using blind indexes. However, full-text search - or "searching as you type" like features - seem nigh on impossible.

As mentioned above, there is an acceptable solution using Elasticsearch. It indexes the inserted data, and then promptly forgets that data, only keeping the index. We are well aware that one could - in principle - restore some if not all of the source data from the indexes themselves. This is acceptable.

Can this behaviour be replicated in PostgreSQL somehow?

1 Answer 1

1

No, and sorry, but that is not acceptable from a security point of view. If the index knows the clear text data, the database (and the DBA) know the clear text data.

You cannot index an encrypted column (other than for equality searches, where you can search for the encrypted data). That is the price you are paying for security.

4
  • I understand the sentiment, and - as with a lot of things - security and usability are a tradeoff. At this stage we are exploring what is possible. Commented Jun 14, 2021 at 11:49
  • It is the natural goal of security to restrict usability, hopefully within reasonable limits. But you can either have the database see the encrypted values (and index them) or not see them. If you are looking for a way to circumvent security measures, you may find a way. Commented Jun 14, 2021 at 12:19
  • you could index on an expression the decrypts the data, but that could make it harder to keep the key secret.
    – Jasen
    Commented Jun 15, 2021 at 3:22
  • @Jasen That's what I mean: then the index key will be unencrypted, and it is so simple to get the clear text data that you might as well store them unencrypted. For one, the index expression (which is stored in the database) would contain the secret. Commented Jun 15, 2021 at 5:53

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.