开发者

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

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜