PostgreSQL解密逻辑迁移至BigQuery遇密钥长度问题求助

你的错误根源有两点：

1. 密钥处理逻辑错误：PostgreSQL的pgp_sym_decrypt_bytea会从文本密码通过PGP的S2K（字符串转密钥）算法派生出符合AES-256要求的32字节密钥，而你在BigQuery中错误地使用FROM_HEX()处理文本密码，导致生成的密钥长度不符合AES-256的要求（18字节≠32字节）。
2. 密文格式不匹配：PGP加密的密文包含额外的头信息、IV、校验和等元数据，并非单纯的AES-CBC加密数据，直接用BigQuery的AEAD.DECRYPT_STRING()无法解析。

BigQuery没有内置的PGP解密函数，最直接的方法是创建JavaScript UDF调用OpenPGP库实现解密：

1. 创建PGP解密UDF #

CREATE OR REPLACE FUNCTION `your-project.your-dataset.pgp_sym_decrypt`(ciphertext_base64 STRING, password STRING)
RETURNS STRING
LANGUAGE js AS r"""
const openpgp = require('openpgp');
async function decrypt() {
  const ciphertextBytes = Uint8Array.from(atob(ciphertext_base64), c => c.charCodeAt(0));
  const message = await openpgp.readMessage({
    armored: false,
    binaryMessage: ciphertextBytes
  });
  const { data: decryptedText } = await openpgp.decrypt({
    message,
    passwords: [password],
    format: 'utf8'
  });
  return decryptedText;
}
return decrypt();
"""
OPTIONS (
  library="https://unpkg.com/openpgp@5.11.0/dist/openpgp.min.js"
);

2. 使用UDF解密 #

替换你的项目、数据集ID，直接调用UDF即可：

SELECT `your-project.your-dataset.pgp_sym_decrypt`('base64_byte', 'secret_key') AS plaintext;

替代方案（无外部库限制） #

如果无法访问外部库，需要手动实现PGP的S2K密钥派生和AES-CBC解密逻辑，步骤如下：

• 从文本密码派生32字节密钥：使用SHA-1迭代（PGP默认S2K算法），迭代次数、盐值需从PGP密文头中提取。
• 从PGP密文中提取IV（前16字节）和实际加密数据。
• 用AES-CBC-PKCS7解密实际数据。

这种方法复杂度较高，仅推荐在严格限制网络访问的场景下使用。

内容的提问来源于stack exchange，提问作者Hai Nguyen