We encountered a situation where encrypted data exported from Informatica could not be decrypted by PostgreSQL, once we’d loaded it into a table.

If you found this page looking for a solution, save your time. I don’t have a solution, I just wanted to make some notes.

How does Informatica encrypt data?

According to this knowledgebase article:

AES_DECRYPT needs binary data as input. Hence when we first encrypt the data using the AES_ENCRYPT function, the data transformed by this function has to be encoded in base64 format. This transformed data can then be used as an input to the AES_DECRYPT function.

In other words:

CIPHERTEXT = Base64_Encode( AES_Encrypt( PLAINTTEXT ))

and

PLAINTEXT = AES_Decrypt( Base64_Decode( CIPHERTEXT ))

According to this thread:

[informatica’s] AES_ENCRYPT function uses the combination as follows which is expressed in terms of functions in DBMS_CRYPTO package of Oracle: DBMS_CRYPTO.ENCRYPT_AES128 + DBMS_CRYPTO.CHAIN_CBC + DBMS_CRYPTO.PAD_ZERO

From Oracle’s documentation, we get:

DBMS_CRYPTO.ENCRYPT_AES128 Advanced Encryption Standard. Block cipher. Uses 128-bit key size.
DBMS_CRYPTO.CHAIN_CBC Cipher Block Chaining. Plaintext is XORed with the previous ciphertext block before it is encrypted.
DBMS_CRYPTO.PAD_ZERO Provides padding consisting of zeroes

So far, so good.

Ensure the pgcrypto extension is installed in PostgreSQL:

In psql:

=> \dx
 hstore  | 1.4     | public     | data type for storing sets of (key, value) pairs
 plpgsql | 1.0     | pg_catalog | PL/pgSQL procedural language
 
=> create extension if not exists pgcrypto;
CREATE EXTENSION
 
=> \dx
 hstore   | 1.4     | public     | data type for storing sets of (key, value) pairs
 pgcrypto | 1.3     | public     | cryptographic functions
 plpgsql  | 1.0     | pg_catalog | PL/pgSQL procedural language

Looking at the docs for the raw base functions in pgcrypto, it suggests that the default settings for encrypt/descrypt will use MODE=CBC and PADDING=PKCS (any length) which suggests zero padding but that’s a guess. So I think it should match Informatica, at least according to the docs.
See https://www.postgresql.org/docs/9.5/pgcrypto.html

And yet, using the following test code yields an error:

=> select pgp_sym_decrypt( decode('BI830ZHd2kimJTfMD8E07mSTa8HDUbU/lbKFUDl73FrqI6uwAH7oCwPRGjxUkUZw','base64'), 
                           'Abc123Abc123Abc123', 
                           'cipher-algo=aes128' );

ERROR:  Wrong key or corrupt data

In this example, I’ve replaced the key text with a random string value – we didn’t actually use that value.

I am pretty confident that the encryption key is correct; less confident that the cipher text is not corrupt, but in simple tests where I DO have confidence, we still get the same error. Where does this leave us? With data we can’t decrypt, unless we can figure out the difference.

Of course, we can encrypt and decrypt values successfully if we stay in the DB domain and use only the PostgreSQL pgcrypto API to perform the encryption. It’s only data coming from Informatica that is giving us trouble.

Assuming the process that is extracting data from Informatica is not corrupting the data, it has to be some difference between the two encryption algorithms.

I’ll continue to research this and if we find a solution, update this post, of course.