如何解决PostgreSQL中pgp_sym_decrypt解密Java加密数据报错问题

我用Java代码加密数据后，执行PostgreSQL查询解密时触发错误：Wrong key or corrupt data

PostgreSQL解密查询：

SELECT pgp_sym_decrypt(
    decode(email, 'base64'), 
    'secretKeyForTest', 'cipher-algo=aes256'
) 
from table
where user_id = '1';

Java加密代码：

public static String encrypt(
            String data,
            String passPhrase
    ) throws IOException, PGPException, NoSuchProviderException, NoSuchAlgorithmException {
        // Create a PGPEncryptedDataGenerator to perform encryption
        PGPDataEncryptorBuilder encryptorBuilder = new JcePGPDataEncryptorBuilder(PGPEncryptedData.AES_256)
                .setWithIntegrityPacket(true)
                .setSecureRandom(new SecureRandom());

        PGPEncryptedDataGenerator encryptedDataGenerator = new PGPEncryptedDataGenerator(encryptorBuilder);
        encryptedDataGenerator.addMethod(new JcePBEKeyEncryptionMethodGenerator(passPhrase.toCharArray()));

        // Encrypt data
        ByteArrayOutputStream encryptedOut = new ByteArrayOutputStream();
        try (OutputStream encryptedStream = encryptedDataGenerator.open(encryptedOut, new byte[4096])) {
            encryptedStream.write(data.getBytes(StandardCharsets.UTF_8));
        }

        return Base64.getEncoder().encodeToString(encryptedOut.toByteArray());
    }

你的Java代码直接将原始明文字节写入PGP加密流，不符合OpenPGP规范格式。PostgreSQL的pgp_sym_decrypt要求输入是完整的OpenPGP加密消息，必须包含PGP字面量数据封装结构，而非直接加密原始字节。

import org.bouncycastle.openpgp.*;
import org.bouncycastle.openpgp.jcajce.JcePGPDataEncryptorBuilder;
import org.bouncycastle.openpgp.jcajce.JcePBEKeyEncryptionMethodGenerator;

import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.nio.charset.StandardCharsets;
import java.security.NoSuchAlgorithmException;
import java.security.NoSuchProviderException;
import java.security.SecureRandom;

public class PGPEncryptor {
    public static String encrypt(String data, String passPhrase) throws IOException, PGPException, NoSuchProviderException, NoSuchAlgorithmException {
        // 构建加密器
        PGPDataEncryptorBuilder encryptorBuilder = new JcePGPDataEncryptorBuilder(PGPEncryptedData.AES_256)
                .setWithIntegrityPacket(true)
                .setSecureRandom(new SecureRandom())
                .setProvider("BC");

        PGPEncryptedDataGenerator encryptedDataGenerator = new PGPEncryptedDataGenerator(encryptorBuilder);
        encryptedDataGenerator.addMethod(new JcePBEKeyEncryptionMethodGenerator(passPhrase.toCharArray())
                .setProvider("BC"));

        ByteArrayOutputStream encryptedOut = new ByteArrayOutputStream();
        OutputStream encryptedStream = encryptedDataGenerator.open(encryptedOut, new byte[4096]);

        // 用ZLIB压缩数据（可选，符合OpenPGP规范）
        PGPCompressedDataGenerator compressedDataGenerator = new PGPCompressedDataGenerator(PGPCompressedData.ZLIB);
        OutputStream compressedStream = compressedDataGenerator.open(encryptedStream);

        // 包装成PGP字面量数据（必须，PostgreSQL需识别该结构）
        PGPLiteralDataGenerator literalDataGenerator = new PGPLiteralDataGenerator();
        OutputStream literalStream = literalDataGenerator.open(compressedStream,
                PGPLiteralData.BINARY,
                PGPLiteralData.CONSOLE,
                data.getBytes(StandardCharsets.UTF_8).length,
                new java.util.Date());

        // 写入明文数据
        literalStream.write(data.getBytes(StandardCharsets.UTF_8));

        // 按顺序关闭流，确保结构完整写入
        literalStream.close();
        compressedDataGenerator.close();
        encryptedStream.close();
        encryptedDataGenerator.close();

        return Base64.getEncoder().encodeToString(encryptedOut.toByteArray());
    }
}

1. 确保项目引入BouncyCastle的PGP依赖（如bcpg-jdk15on）
2. PostgreSQL端查询无需修改，加密后的Base64数据存入email字段即可
3. 若不需要压缩，可去掉PGPCompressedDataGenerator步骤，直接将literalStream绑定到encryptedStream

内容的提问来源于stack exchange，提问作者nandan pandey