Snowflake实现客户端加密报错：S3本地加密数据在Snowflake解密失败

• Amazon S3加密客户端采用信封加密机制，不会直接用主密钥加密原始内容，而是先生成随机数据密钥（DEK）加密内容，再用主密钥加密DEK后存在S3对象元数据中。你直接用主密钥解密S3对象内容，相当于用错误的密钥解密，输出自然是二进制乱码，无法识别为有效UTF8字符串。
• S3加密客户端默认采用AES-CBC-PKCS7加密模式，加密时会生成随机初始化向量（IV）存储在S3对象自定义元数据中，解密时需要传入对应IV才能得到正确明文，缺失IV参数也会导致解密结果乱码。
• 你从S3同步数据到Snowflake时仅同步了对象本体内容，未同步x-amz-key（加密后DEK）、x-amz-iv（加密IV）等必要元数据，导致解密参数缺失。

1. 先提取S3对象的必要元数据字段，从S3对象自定义属性中获取以下值：
   • 你已拿到的Base64编码加密后DEK：uqkB9xGodxCD+Y/SA9Fq2XkhSmW+uiXFNbVg5fXTX/gVADS3vxTNfeZ/kosT+KN6jf7WL04TIzg9QhXZ
   • Base64编码的AES初始化向量x-amz-iv
2. 在Snowflake中先用主密钥解密得到明文DEK，参考SQL：

-- 先解码主密钥和加密后的DEK
WITH key_material AS (
    SELECT 
        BASE64_DECODE_BINARY('9Yn7sWaQZC+YVYODb0ijSBubgF+zfGB5Le+3welJ2A4=') AS master_key,
        BASE64_DECODE_BINARY('uqkB9xGodxCD+Y/SA9Fq2XkhSmW+uiXFNbVg5fXTX/gVADS3vxTNfeZ/kosT+KN6jf7WL04TIzg9QhXZ') AS encrypted_dek
)
-- S3客户端用AES-ECB模式加密DEK，因此用对应模式解密
SELECT DECRYPT(encrypted_dek, master_key, 'AES-ECB-PAD') AS plain_dek 
FROM key_material;

3. 用明文DEK+IV解密实际S3对象内容，参考SQL：

SELECT 
    DECRYPT(
        -- 你的S3对象二进制内容，从外部表读取时注意保持二进制格式不要提前转字符串
        $s3_object_content,
        plain_dek,
        'AES-CBC-PAD',
        -- 替换为你提取到的x-amz-iv字段Base64值
        BASE64_DECODE_BINARY('你的x-amz-iv字段Base64值')
    ) AS decrypted_binary,
    -- 转成UTF8字符串
    CAST(decrypted_binary AS VARCHAR) AS decrypted_string
FROM your_s3_external_table;

4. 如果仍报UTF8错误，可先校验解密后二进制内容的编码格式，再做编码转换：

SELECT CONVERT(decrypted_binary, 'UTF8', 'ISO-8859-1') AS fixed_encoding_string

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