Decrypting AES GCM 256 with pgcrypto (PostgresQL)

Question

I am trying to decrypt a message with pgcrypto that is encrypted with AES GCM 256.

Specifications of the encryption:

Encryption algorithm     AES
Key                      [secret of listener] (64-character-long hexadecimal string in configuration)
Key length               256 bits (32 bytes)
Block mode               GCM
Padding                  None
Initialization vector    In HTTP header (X-Initialization-Vector)
Authentication tag       In HTTP header (X-Authentication-Tag)

So I receive a:

• body
• key
• iv_header
• auth_tag

I tried the below

with base as (
select
    'F8E2F759E528CB69375E51DB2AF9B53734E393' as body,
    '000102030405060708090A0B0C0D0E0F000102030405060708090A0B0C0D0E0F' as key,
    '3D575574536D450F71AC76D8' as iv_header,
    '19FDD068C6F383C173D3A906F7BD1D83' as auth_tag
),
out as (
    select
        decrypt_iv(
            convert_to(concat(decode(body,'hex'),decode(auth_tag,'hex')),'LATIN1'),
            decode(key, 'hex'),
            decode(iv_header, 'hex'),
            'aes/pad:none'
        )
    from base
)

select * from out

I keep getting the error decrypt_iv error: Data not a multiple of block size while I would expect to get the encoded message {"type": "PAYMENT"}

I expect that something goes wrong in the decoding and concatenating of body and auth_tag, but can't figure out what.

Some notes on what/why I did that

• I concat the auth_tag to the body as several sources describe it that way.
• I use the convert_to function as it seems the only way to concatenate two bytea values

I manage to decrypt this message in other languages (i.e. Snowflake or Python)

If anyone sees what I am doing wrong or has some directions it is highly appreciated.

Answer 1

pgcrypto only says it supports cbc and ecb. I'm not a cryptographer, but I don't think either of those is the same thing as GCM. So I don't think you can do this with pgcrypto. I don't know why it leads to the exact error you get, but it also doesn't surprise me.

If I really needed to do this inside the database, I think I do it by writing a function using pl/python3u.