• Previously, I used this method to encrypt data:

update
    test_encrypt
set
    encrypted_data = encode(pgp_sym_encrypt(origin_data ::text,
    '123456',
    'cipher-algo=aes256, compress-algo=0'),
    'base64')
where
    origin_data is not null
    and origin_data != '';

• Now I want to implement the same logic in Python, so that the encrypted data can be decrypted by PostgreSQL. I tried to solve this problem using GPG, but even after supplementing all the default parameters, it still cannot be decrypted by PostgreSQL.

• https://www.postgresql.org/docs/current/pgcrypto.html

• Here is the Python code I attempted:

import base64
import random

import gnupg

# Specify the path to the GPG executable
gpg = gnupg.GPG(gpgbinary='C:\Program Files (x86)\gnupg\bin\gpg.exe')

# The message to be encrypted
message = "hello world, 你好世界"

# Convert line endings to rn to comply with RFC 4880
message = message.replace('n', 'rn')

# Encode the message as UTF-8 bytes
message_bytes = message.encode('utf-8')

# Generate a random s2k-count value
s2k_count = random.randint(65536, 253952)

# Encrypt the data using symmetric encryption
encrypted_data = gpg.encrypt(
    data=message_bytes,
    recipients=[],
    symmetric=True,
    passphrase="123456",
    armor=False,  # Use ASCII armor format
    extra_args=[
        '--cipher-algo', 'aes256',
        '--compress-algo', '0',
        '--compress-level', '6',
        '--s2k-mode', '3',
        f'--s2k-count={s2k_count}',
        '--s2k-digest-algo', 'SHA1',
        '--s2k-cipher-algo', 'aes256',
        '--no-emit-version',
        '--no-comments'
    ]
)

# Check if encryption was successful
if encrypted_data.ok:
    # print("Successfully (Binary):", encrypted_data.data)
    # Convert the encrypted data to Base64 encoding
    encrypted_base64 = base64.b64encode(encrypted_data.data)
    print("Successfully (Base64):", encrypted_base64.decode('utf-8'))
else:
    print("Failed:", encrypted_data.status)

• And here’s my decrypt codes:

select
    case
        when encrypted_data is not null then
            pgp_sym_decrypt(
                cast(decode(encrypted_data,
        'base64') as bytea),
        '123456',
        'cipher-algo=AES256'
            )
        else
            null
    end as decrypted_data
from
    test_encrypt

1. I wrote a decrypt function, it works

import io
from base64 import b64decode

import gnupg

gpg = gnupg.GPG(gpgbinary='C:\Program Files (x86)\gnupg\bin\gpg.exe')
encrypted_data_base64 = "jA0ECQMCVVAUXiPjSDt50koBeTZ/NymLGFy+ofg18OOCeoDKWM3W0rzHI0SkH+OPdlr9txL++iddGtYtYeHkVwNvdrLoeMIkbSl2wQx95AOKm3UCJb7L0tTlSw=="
encrypted_data = b64decode(encrypted_data_base64)
encrypted_data_io = io.BytesIO(encrypted_data)
decrypted_data = gpg.decrypt_file(encrypted_data_io, passphrase='123456')
if decrypted_data.ok:
    print("Decrypted Data:", decrypted_data.data.decode('utf-8'))
else:
    print("Decryption failed:", decrypted_data.status)

2. However Postgresql can’t decrypt that successfully.
3. It will report a error like [39000]: ERROR: Not text data