postgres中使用pgcrypto模块加密，java中使用解密

Question

我创建了一个触发器和一个触发器函数，用于调用表上的每个更新操作，并加密一个特定的值，如下所示:

create trigger project_trigger
before update
on projects
for each row
execute procedure project_function();
create or replace function project_function()
returns trigger as
$BODY$
begin
IF (TG_OP = 'UPDATE') THEN
NEW.title = armor(pgp_sym_encrypt(NEW.title, 'cipher-algo=aes256' ));
return NEW;
END IF;
end;
$BODY$
language plpgsql;

上面的加密方法工作得很好，并且铠装的PGP加密值被保存如下:

-----BEGIN PGP MESSAGE-----
ww0EBwMCneBsNZw1gYFq0jYB9y58EoTaRXWmDFqvQArWU5tZ+wS+7yAm9ycVUpkH1EzvYLbfRoDj
rqR83I0nGErHcLSLlAs=
=IYg8
-----END PGP MESSAGE-----

解密需要在应用程序级别完成，为此我遵循了以下两个步骤:

• 增加bcpg-jdk15on和bcprov-jdk15on依赖项。(v1.47)
• 实现:

String key = "aes_key";
File file = new File("D:\file.txt.asc"); //this file contains the PGP encrypted value as shown above
InputStream input = new FileInputStream(file);
byte[] byt = new byte[input.available()];
input.read(byt);
input.close();
Security.addProvider(new BouncyCastleProvider());
System.out.println(new String(ByteArrayHandler.decrypt(byt, 
key.toCharArray())));

在使用上述方法解密值时，我一直得到以下异常:

线程"main"异常org.bouncycastle.openpgp.PGPDataValidationException:数据检查失败了。在org.bouncycastle.openpgp.PGPPBEEncryptedData.getDataStream(未知源)org.bouncycastle.openpgp.examples.ByteArrayHandler.decrypt(未知源)abc.demo.encryption.SymmetricDecyption.main (SymmetricDecyption.java: 59)

那么有人能指导我在应用程序级别(而不是在查询中)实现解密的适当方法吗?

Answer 1

有两个问题。PGPDataValidationException是由于使用不同的密码短语进行加密和解密造成的。如果您使用了正确的密码短语，那么您就会发现Bouncy Castle示例代码的功能不全。

触发可能不是你想要的。对pgp_sym_encrypt的调用应该更像这样:

create or replace function project_function()
returns trigger as
$BODY$
begin
IF (TG_OP = 'UPDATE') THEN
NEW.title = armor(pgp_sym_encrypt(NEW.title, 'my-secret-passphrase', 'cipher-algo=aes256, compress-algo=2' ));
return NEW;
END IF;
end;
$BODY$
language plpgsql;

pgp_sym_encrypt的三个输入参数为待加密的文本、导出密钥的密码短语和选项。在你的问题中，你省略了密码短语。

其次，BouncyCastle示例代码假设纯文本已被压缩。我已将RFC1950压缩(ZLIB)添加到pgp_sym_encrypt。

通过对触发器的这些更改，我得到:

postgres=# update projects set title = 'My secret compressed title.';
UPDATE 1
postgres=# t off
postgres=# select * from projects;
title
-----BEGIN PGP MESSAGE-----
ww0ECQMCuN3MyfrWhBt50lcBGbUtjOlTBxGpAFCl7aYEybhhXRJodDsikWxdLmOsXnE6vWr9mwd7
dGy7N1eE5VFmwI5N29eCNhEvG5U4YmVC7fV1A1sBeoJMtsO/nz2mi2jbFiZHlzo=
=s6uI
-----END PGP MESSAGE-----
(1 row)
postgres=#

输入到Java程序中:

String value = "-----BEGIN PGP MESSAGE-----n"
+ "n"
+ "ww0ECQMCuN3MyfrWhBt50lcBGbUtjOlTBxGpAFCl7aYEybhhXRJodDsikWxdLmOsXnE6vWr9mwd7n"
+ "dGy7N1eE5VFmwI5N29eCNhEvG5U4YmVC7fV1A1sBeoJMtsO/nz2mi2jbFiZHlzo=n"
+ "=s6uIn"
+ "-----END PGP MESSAGE-----n";
String key = "my-secret-passphrase";
byte[] byt = value.getBytes(StandardCharsets.UTF_8);
Security.addProvider(new BouncyCastleProvider());
System.out.println(new String(ByteArrayHandler.decrypt(byt, key.toCharArray()), StandardCharsets.UTF_8));

产生输出:

My secret compressed title.

完全符合要求。

如果您希望在加密之前不压缩纯文本，那么您可以查看示例PBEFileProcessor，因为它处理压缩和未压缩的数据，或者您可以使用以下代码:

public static byte[] decrypt(
byte[] encrypted,
char[] passPhrase
) throws IOException, PGPException {
JcaPGPObjectFactory pgpF = new JcaPGPObjectFactory(PGPUtil.getDecoderStream(new ByteArrayInputStream(encrypted)));
// Find the encrypted data list. The first object might be a PGP marker packet, or the actual data list
PGPEncryptedDataList enc;
Object o = pgpF.nextObject();
if (o instanceof PGPEncryptedDataList) {
enc = (PGPEncryptedDataList) o;
} else {
enc = (PGPEncryptedDataList) pgpF.nextObject();
}
// Do the decryption
PGPPBEEncryptedData pbe = (PGPPBEEncryptedData) enc.get(0);
InputStream clear = pbe.getDataStream(new JcePBEDataDecryptorFactoryBuilder(
new JcaPGPDigestCalculatorProviderBuilder().setProvider("BC").build()).setProvider("BC").build(passPhrase)
);
// Process the decrypted data. It may be compressed, or it may be literal
JcaPGPObjectFactory pgpFact = new JcaPGPObjectFactory(clear);
o = pgpFact.nextObject();
if (o instanceof PGPCompressedData) {
// Need to decompress the data
PGPCompressedData cData = (PGPCompressedData) o;
pgpFact = new JcaPGPObjectFactory(cData.getDataStream());
o = pgpFact.nextObject();
}
// We should have the literal data now, so convert it into bytes
PGPLiteralData ld = (PGPLiteralData) o;
return Streams.readAll(ld.getInputStream());
}

最后，在postgresql中解密时，你不需要指定纯文本是否被压缩，也不需要指定它是如何加密的，因为PGP数据指定了这一点，所以你可以这样做:

select pgp_sym_decrypt(dearmor(title), 'my-secret-passphrase') from projects;