Can PostgreSQL do a query on encrypted records?
Let's say I have an entire column in a table that is encrypted, the table also has unencrypted columns like IDs, and I have the encryption key for the entire column and I used the DBMS' encrypt() function with AES to store it.
I'm wondering if there is anyway to execute something like
SELECT * FROM table开发者_Go百科1 WHERE decrypt(col1, 'fooz', 'aes') = 'aValue'
I've already tried that in PostgreSQL and the above syntax is not supported. If there is no way to do this, what are the workarounds?
I've looked into decrypting into a temporary table and then execute the query and drop it but that seems extremely inefficient and also unsafe because there's a chance the decrypted table can remain on disk
Pseudo code
SELECT * FROM table1 WHERE col1 = encrypt('avalue','fooz','aes');
Or more specifically:
Real code
SELECT * FROM table1
WHERE col1 = pgp_sym_encrypt('avalue', 'apasswordwithsomeentropy'
,'compress-algo=1, cipher-algo=aes256');
http://www.postgresql.org/docs/8.3/static/pgcrypto.html
精彩评论