PostgreSQL 主键约束对使用 PG加密扩展的加密数据

Question

我正在使用PostgreSQL 8.4，并使用pgcrypto扩展中的pgp_sym_encrypt函数在插入时加密数据。 所以我的插入查询如下所示：

insert into myTable (
                     column1
                    ,column2
                     ) 
values (
        pgp_sym_encrypt('value1','key')
       ,pgp_sym_encrypt('value1','key')
       );

我对表中的 column1 应用了主键约束，但该约束并不总是检测到插入已经存在的值，因为对于相同的解密值，加密数据并不总是相同的。

问题：

如何应用此约束，以便它检查解密的数据是否匹配并且在这种情况下不允许插入？

Answer 1

如果您希望强制实施唯一性，最好的办法是散列您的信息并将该哈希存储在具有唯一索引的单独列中，我经常使用摘要来检查较长文本的唯一性，纯粹是因为它的空间要求很小，但是由于哈希的不可逆性，其余部分应该是无法发现的。首先，您需要一列：

alter table myTable add column column3 bytea unique;

然后对于您的插入，只需包含第 3 列

insert into myTable(
column1
,column2
,column3
)
values (
pgp_sym_encrypt('value1','key')
,pgp_sym_encrypt('value1','key')
,digest('value1', 'sha256')
)

SHA256 应该很容易使数据乱码，以至于以这种形式存储的任何内容都永远不会被成功破译，并且在二进制列中只需要 32 个字节和索引。对于记录，不同哈希的数据长度为：

select length(digest('your message goes here', 'sha1'));
-- 20
select length(digest('your message goes here', 'sha256'));
-- 32
select length(digest('your message goes here', 'sha512'));
-- 64

最后，您是用"密钥"加密"value1"两次，还是它们是两条独立的信息？我猜它们应该是两列不同的数据列，如果是这样，并且您需要两者的唯一性，您当然也需要创建另一列并对其进行哈希处理。