在Postgres中搜索加密字段 [英] Searching encrypted field in Postgres

问题描述

我正在尝试使用"pgp_sym_encrypt"在postgres中查询一个加密字段.我通过将表中的所有名字设置为加密值来运行测试:

I'm attempting to query an encrypted field in postgres using "pgp_sym_encrypt". I'm running my test by setting all the first names in my table to an encrypted value:

update person set first_name = pgp_sym_encrypt('test', 'password');

然后选择:

select * from person where first_name = pgp_sym_encrypt('test', 'password');

这不会返回任何结果.

如果我将其更改为使用常规的postgres加密，它将返回表中的所有行:

If I change it to use the normal postgres encryption it will return all the rows in the table:

update person set first_name = encrypt('test', 'password', 'aes');
select * from person where first_name = encrypt('test', 'password', 'aes');

我当前的postgres版本是:postgres(PostgreSQL)9.4.0.在这种情况下，first_name字段是一个bytea字段.

My current postgres version is: postgres (PostgreSQL) 9.4.0. The first_name field in this case is a bytea field.

有人知道为什么使用"pgp_sym_encrypt"无法正常工作吗?

Does anyone know why this is not working using "pgp_sym_encrypt"?

谢谢！

推荐答案

如果您查看PostgreSQL文档(

If you look at PostgreSQL Documentation (Appendix F.25. pgcrypto - F.25.3. PGP Encryption Functions):

使用String2Key(S2K)算法对给定的密码进行哈希处理.这与crypt()算法非常相似-故意变慢并且随机加盐-但它会生成全长的二进制密钥.

The given password is hashed using a String2Key (S2K) algorithm. This is rather similar to crypt() algorithms — purposefully slow and with random salt — but it produces a full-length binary key.

(强调我的.)

因此，每次运行时，以下内容都会得出不同的结果:

So the following gives different results every time you run it:

select pgp_sym_encrypt('test', 'password');

在测试密码时，请使用 pgp_sym_decrypt 代替，可以这样进行测试:

When testing the password use pgp_sym_decrypt instead, it can be tested like this:

select pgp_sym_decrypt(pgp_sym_encrypt('test', 'password'), 'password');

这篇关于在Postgres中搜索加密字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！