PostgreSQL 可以对加密记录进行查询吗？

发布于 2024-12-09 18:42:27 字数 325 浏览 0 评论 0原文

假设我的表中有一整列已加密，该表还有未加密的列（例如 ID），并且我有整列的加密密钥，并且我使用 DBMS 的 encrypt() 函数和 AES 来存储它。

我想知道是否有办法执行类似

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

我已经在 PostgreSQL 中尝试过的操作，并且不支持上述语法。如果没有办法做到这一点，有哪些解决方法？

我已经考虑解密到临时表，然后执行查询并将其删除，但这似乎效率极低且不安全，因为解密的表有可能保留在磁盘上

原文

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'

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

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

半城柳色半声笛 2024-12-16 18:42:27

伪代码

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

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 原文

~没有更多了~