I would like to protect sensitive information stored on a Postgresql database, but I would not like to encrypt it all. The idea is to encrypt only those fields which hold sensitive information. I was thinking on using AES 256 to store the data, and I've read some posts here with some insights and recommendations about this topic.
The problem is, AES with CBC generates a different ciphertext for the same plaintext (provided the IV is different everytime) so I lose the ability to search on those fields. One of those fields is the person ID number, which is used on our webapp to search for a person's data. What I would like to implement in our app is that when the request comes from a user to search for a person with id '123', the app encrypts 123 and searches for the encrypted value (hex-encoded) in the database. Note that the ID field is not a primary key in the table.
However I read that providing the same IV for AES is not a good idea. Is there any cipher suite I can use that produces the same ciphertext given the same plaintext, and offers a good level of security against cracking? Or is it OK to use AES 256 CBC with the same IV to produce the same ciphertext in this scenario?
One of the posts I read suggested adding a new field to the table to store the hash of the plaintext for searching. While this is a good idea, I prefer to keep my tables without changes because we want to use the same persistence beans for encrypted and unencrypted databases.
What I'm trying to prevent is DBAs seeing sensitive person data performing selects on tables (the database is not under our control).
Any advice is greatly appreciated!