What is the preferred way to implement personal information encryption / decryption? After some reading, the main options appear to be:
- Encrypt/Decrypt it at the database level
- Encrypt/Decrypt it at the backend level
- Encrypt/Decrypt it at the client level
Encrypting it at the database level, PostgreSQL offers PGP Encryption options which seems to allow encryption of the data but still being able to index it for searching if my understanding of PGP is correct? From what I've read so far, the commercial database vendors all have this as well. If the encrypted data can be indexed, is it really impossible to reverse engineer the indexes to get the encrypted data back? Also, the encryption / decryption then happens at the database level which is going to put overhead on the data base server. Also, what happens to the encryption / decryption keys, is this something that needs to be fed to a database or does databases handle this itself? (just wondering about how these keys should then be backed up if the database is managing this)
Encrypting it in the backend before inserting the data into the database and then decrypting it again seems like a very scalable way of doing this, adding more backend servers is cheap. If encryption were to happen in the backend, how does one do database queries for example to find all names like Jo%_n or all telephone numbers starting with 084? Encryption / Decryption key management, if someone gets the keys, then they can decrypt your database entries, what are the options to manage these decryption keys? I'm assuming something like AWS KMS is an option here and then restricting access to that KMS instance? Should one only store a master key in KMS which then decrypts all other keys allowing one to decrypt data with different keys for each company (assuming a multitenant setup)?
Last option, how feasible is it to encrypt data in the browser itself in which case the backend or database would have zero knowledge of what's in the data? Obviously this means zero chance of allowing searches on the data, unless there's still a way to do this? (I'm assuming password managers do this)? How are the keys managed in this scenario, I'm assuming the same key used for encryption should then be used for decryption too or is it feasible to have multiple decryption keys for the same data? (if the backend does the encryption with a private key and each user is issued a public key, then decryption can happen client side? This sounds like a very scalable way to handle lots of database reads with few inserts)
To sum up, what are the pros and cons of database, server and client side encryption / decryption to protect PII and what are the things to keep in mind for each option?