PostgreSQL可以对加密记录进行查询吗? [英] Can PostgreSQL do a query on encrypted records?

问题描述

比方说，我在表中有一整列已加密，该表还具有未加密的列(例如ID)，并且我拥有整列的加密密钥，并且我将DBMS的crypto()函数与AES一起使用来存储它.

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 table1 WHERE decrypt(col1, 'fooz', 'aes') = 'aValue'

我已经在PostgreSQL中尝试过，并且不支持以上语法.如果无法执行此操作，有什么解决方法?

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

推荐答案

伪代码

SELECT * FROM table1 WHERE col1 = encrypt('avalue','fooz','aes');

或更具体地说:

真实代码

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

这篇关于PostgreSQL可以对加密记录进行查询吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！