Decrypting data (in C#) encrypted by pgcrypto

Question

I'm trying to decrypt data which was encrypted using pgcrypto. I didn't use an IV as it's only a test, but I can't decrypt the data in C#.

Encrypting in PostGres:

enc_key := '\\xAACE38F289EC3EA209B48D';

-- Time insertions
ts_start := clock_timestamp();
FOR i IN 1..num_loops LOOP

    -- The text to insert and its key
    plaintext := 'Number: ' || i;
    plaintext_pk := gen_random_uuid();
    plaintext_pk_as_text := plaintext_pk::text;

    -- The ref entries
    user_pk := gen_random_uuid();
    user_ref_pk := encrypt(plaintext_pk_as_text::bytea, enc_key, 'aes');

    -- Add the enries
    INSERT INTO "Text" VALUES(plaintext_pk, plaintext);
    INSERT INTO "User" VALUES(user_ref_pk, user_pk);

END LOOP;
ts_end := clock_timestamp();
elapsed_raw := cast(extract(epoch from (ts_end - ts_start)) as numeric(18,3));

Decrypting in C#:

// The decryption key
byte[] enc_key = new byte[] {   0xAA, 0xCE, 0x38, 0xF2, 0x89, 0xEC, 0x3E, 0xA2, 0x09, 0xB4, 0x8D,
0x00, 0x00, 0x00, 0x00, 0x00 };

public static string AESDecryptByteArray(byte [] encoded_data, byte [] key)
{
    string result = "";
    byte [] result_ba = new byte[64];

    using (Aes myAes = Aes.Create())
    {
        if (myAes == null)
        {
            throw new Exception("Failed to create AES object.");
        }

        myAes.Key = key;
        myAes.Mode = CipherMode.CBC;
        myAes.Padding = PaddingMode.PKCS7;

        MemoryStream streamMem = new MemoryStream(encoded_data);

        byte[] IV = new byte[16];
        // streamMem.Read(IV, 0, 16);
        for (int i = 0; i < 16; ++i )
        {
            IV[i] = 0;
        }
        myAes.IV = IV;

        int iNumBytes = 0;
        var decryptor = myAes.CreateDecryptor();
        using (CryptoStream streamCrypt = new CryptoStream(streamMem, decryptor, CryptoStreamMode.Read))
        {
           iNumBytes = streamCrypt.Read(result_ba, 0, 48);
        }

        result = System.Text.Encoding.ASCII.GetString(result_ba);
    }

    return result;

} // AESDecryptByteArray

I copied the resulting encrypted data from one of the rows, and the binary key, but the C# code keeps blowing with a CryptographicException ("Padding is invalid and cannot be removed") exception. My understanding is that pgcrypto's encrypt() defaults to cbc \ pkcs. Obviously, I'm missing something.

Any help gratefully received.

Adam.

Answer 1

Tried Michael's suggestion and was not getting the right results, of course. Found the issue. PG's string to bytea conversion is not for the unwary. The vital clue came from

DO $$

    declare enc_data    bytea;
        enc_key     bytea;

        dec_bytea   bytea;
        dec_text    text;

begin

    enc_data := '\305\347fyau\030 \223\014E\307\346\267|\365R\3236l\322f\344\312z\220\271\207C\003\255\210+\316\330&\205l>\342\203\350\214$W\253\370D';
    enc_key := '\\xAACE38F289EC3EA209B48D';

    dec_bytea := decrypt(enc_data, enc_key, 'aes');
    dec_text := dec_bytea::text;

    raise info 'Decoded text ->  %', dec_text;

    DROP TABLE IF EXISTS tmpTable;
    CREATE TEMPORARY TABLE tmpTable AS
        select  dec_text as "Decoded text",
            char_length(dec_text) as "Decoded length",
            length(enc_data) as "Encoded length",
            enc_key as "Enc Key",
            length(enc_key) as "Enc Key Len",
            encode(enc_key, 'hex') as "Hex key",
            encode(enc_key, 'escape') as "Esc key";

END $$;

select * from tmpTable;

This showed the binary key in PG was 24 bytes long - not 11 as I expected. It was down to a misunderstanding on my part of how PG's string to bytea conversion works. I thought "\\xAACE38F289EC3EA209B48D" would translate into an 11 byte array (https://www.postgresql.org/docs/9.6/static/datatype-binary.html, section 8.4.1) but the doubled backslash is not needed. So my string translates into '\', 'x', 'A' ... 'D' - a 24 byte array.

//
// In C# this is the key needed
//
byte[] enc_key_aaaahhhh =
new byte[] {    0x5c,   0x78,   0x41,   0x41,   0x43,   0x45,   0x33,   0x38,
                0x46,   0x32,   0x38,   0x39,   0x45,   0x43,   0x33,   0x45,
                0x41,   0x32,   0x30,   0x39,   0x42,   0x34,   0x38,   0x44    };

//
// This is wrong.
// For this key you'd need to enter '\xAACE38F289EC3EA209B48D' in PG - only one backslash
//
byte[] enc_key = new byte[] {   0xAA, 0xCE, 0x38, 0xF2, 0x89, 0xEC, 0x3E, 0xA2, 0x09, 0xB4, 0x8D,
                                0x00, 0x00, 0x00, 0x00, 0x00 };

(Didn't help that I copied the wrong GUID into my C# code to compare against - the real GUID was "d6edd775-47c5-4779-a761-7f8297130073".)

Hope this maybe helps someone one day.

• Adam.