PostgreSQL能否查询加密记录？

Question

假设我有一个表中的整个列都是加密的，该表还有未加密的列（如IDs），我拥有整个列的加密密钥，并且我使用DBMS的AES encrypt()函数存储它。

我想知道是否有任何方法执行像下面这样的操作：

SELECT * FROM table1 WHERE decrypt(col1, 'fooz', 'aes') = 'aValue'

我已经在PostgreSQL中尝试过了，但是不支持上述语法。如果没有其他方法，有什么解决方法？

我尝试过将其解密到临时表中，然后执行查询并删除它，但这似乎非常低效，并且不安全，因为解密的表有可能仍然存储在磁盘上。

- user986139

---

回答自己的问题：SELECT * FROM table1 WHERE decrypt(col1::bytea,'fooz','aes') = 'aValue'; 解密需要显式类型转换。 - user986139

请注意，我希望能够在加密数据列中执行一般查询。因此，我建议使用decrypt()函数。使用col1=encrypt()函数太过受限制。例如，我可能会查询WHERE col1 > 10。 - user986139

Answer 1

伪代码

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

- Johan

---

你能否使用索引函数来加快搜索速度？或者这会暴露你试图隐藏在索引中的数据吗？ - Kuberchaun

然而，如果您正在执行select decrypt(col1) from t1并且您将要使用覆盖索引，以便实际上从未调用解密函数，而是使用了覆盖索引，那么您就有了一个观点。如果这是可能的，那么您有一个安全漏洞，因为这意味着您在任何地方都使用相同的密码。您永远不应该这样做，并且始终应该使用每行不同的盐来加密密码。并使用盐+密码作为加密密钥。如果未能这样做，则会使您暴露于彩虹表攻击之下。也许如果您在索引中包含盐。 - Johan

没错，我也是这么想的。如果最终索引仍然是未加密的，那么对保护数据进行索引的细节就变得毫无意义了。虽然你可以确保存储索引数据的目录被锁定，以减轻任何风险。此外，当前版本的PostgreSQL还不能实现覆盖索引，你是在谈论9.2dev吗？ - Kuberchaun

@StarShip3000，热切期待9.2版本。在我看来，如果你加密了，就应该全部加密，而不是在后台留下一些未加密的数据。我认为这会让我陷入各种法律和道德困境。试想一下，要向客户解释他们的加密数据泄漏了，因为你决定将未加密的数据存储在索引中。 - Johan

@RonaldChan，你不能在加密的值上使用like。你需要使用select * from table1 where pgp_sym_decrypt(col1, 'password','compress-algo=1, cipher-algo=aes256') like '%test%'。但是要准备好这个查询会非常慢。如果你有超过一小撮的行，这个查询可能会运行数小时、数天或数月。 - Johan