Hi! Haven't found discussions on possible ways to encrypt indexes. Let's start!
The problem
==========
I'd like to encrypt some columns (first name, last name, SSN, etc...) in a nondeterministic way. This can be done using `pg_crypto`, but then I loose full-text-search (and any other indexing) capabilities on these fields.
Blind indexing also isn't a good enough option.
Obviously we don't want create expression-based indexes, that perform decryption during index build. This will store plaintexts inside index buffers, and decryption key will be included in database dump.
We don't trust full-disk-encryption or any other transparent encryption, because of possible SQL injections.
Solution 1 (possibly can be used even now)
========
- perform full-disk encryption
- perform encryption of column
- add decrypting expression-based index with decryption key
- limit ways on disclosing index internals. Ideally if no one except admin can do that
- limit ways to read index definitions - so it's not possible for application to uncover decryption key from database itself, it should know it on it's own.
Solution 2 (feature request)
========
- full-disk encryption is optional
- data column is encrypted
- index is decrypted by construction, but each it's block is encrypted, even in memory.
- lookups over index do lazy index buffer decrypt and close buffers ASAP
- make every query that has to touch encrypted column or encrypted index require decryption key. This means, SELECT, DELETE, UPDATE, INSERT, VACUUM, CLUSTER, CREATE INDEX, pg_dump, pg_restore all should have decryption key supplied in order to be executed. This also means, that autovacuum daemon can't work.
What do you think about both solutions? Is it hard to implement soluition 2?