PostgreSQL Question posted in
The official documentation can be found here.

How to encrypt data with Postgresql pgp_sym_encrypt in Python?

  • 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)
  1. However Postgresql can’t decrypt that successfully.
  2. It will report a error like [39000]: ERROR: Not text data
Tags:

2

Answers


  1. 0

    PostgreSQL uses a specific implementation of the OpenPGP protocol that may not align fully with GPG generated output. PostgreSQL expects a specific binary format, and GPG introduces metadata or other variations that PostgreSQL cannot parse.

    Explanation given as comments inside the code to achieve the desired result.

    import base64
import gnupg

# Initialize GPG
gpg = gnupg.GPG(gpgbinary='C:\Program Files (x86)\gnupg\bin\gpg.exe')

# Message to encrypt
message = "hello world, 你好世界"

# Encrypt using symmetric encryption
encrypted_data = gpg.encrypt(
    data=message,
    recipients=[],  # No recipients for symmetric encryption
    symmetric=True,
    passphrase="123456",
    armor=False,  # Binary output
    extra_args=[
        '--cipher-algo', 'AES256',
        '--compress-algo', '0',
        '--s2k-mode', '3',
        '--s2k-digest-algo', 'SHA1',
        '--s2k-cipher-algo', 'AES256',
        '--no-emit-version',
        '--no-comments'
    ]
)

if encrypted_data.ok:
    # Convert to Base64 for PostgreSQL
    encrypted_base64 = base64.b64encode(encrypted_data.data)
    print("Encrypted Base64:", encrypted_base64.decode('utf-8'))
else:
    print("Encryption failed:", encrypted_data.status)
    Login or Signup to reply.
  2. 0

    I can’t test your full example because my python library gnupg seems to be incompatible with yours, it just gives me errors about unexpected keyword arguments.

    But plugging your encrypted message directly into PostgreSQL, I see that the problem is that you encrypted the data as binary, not as text. This sets a header in the output which pgp_sym_decrypt then refuses to work with, you have to use pgp_sym_decrypt_bytea instead.

    So either replace pgp_sym_decrypt with pgp_sym_decrypt_bytea, or change your python to tell gnupg to use text mode (which I don’t know how to do, and can’t test, see first paragraph).

    Also, the cast to bytea is not needed as decode automatically returns bytea, and the third argument to pgp_sym_decrypt is not needed as the algorithm to be used is already included in the encrypted message header.

    So this works, although I can’t verify the non-ASCII part of the output is correct:

    with test_encrypt as (select 'jA0ECQMCVVAUXiPjSDt50koBeTZ/NymLGFy+ofg18OOCeoDKWM3W0rzHI0SkH+OPdlr9txL++iddGtYtYeHkVwNvdrLoeMIkbSl2wQx95AOKm3UCJb7L0tTlSw==' encrypted_data)
select
    case
        when encrypted_data is not null then
            pgp_sym_decrypt_bytea(
                decode(encrypted_data,
        'base64'),
        '123456'
            )
        else
            null
    end as decrypted_data
from
    test_encrypt;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search