Converting MySQL AES_DECRYPT() to Postgres decrypt() I have this MySQL query using AES_DECRYPT() that is the following:

SELECT * 
FROM data_tbl
WHERE AES_DECRYPT(data_point,'aBcDeF')='data_1';


I've figured out as much that the equivalent in Postgres 11 is decrypt() (F.25.4. Raw Encryption Functions) and the query would look something like this:

SELECT * 
FROM data_tbl
AND decrypt(data_point,'aBcDeF','aes')='data_1';


At least, the documentation for both leads me to believe that these are equivalent.

The data was dumped from MySQL and imported into PostgreSQL.


The data-point field in MySQL is type varbinary(255) and the values all show BLOB in MySQL Workbench. 
The data_point field in Postgres is type bytea and the values all show [binary data] in pgAdmin. 


Whether those data types are equivalent, I admit, I'm not sure.

The MySQL query works and finds a record based on he criteria. The PostgreSQL does not, although the record is in there. So it seems like one of three things:


The data types (varbinary(255) vs bytea) are not equivalent
The data does not match (BLOB vs [binary data]) are not equivalent
The functions (AES_DECRYPT vs decrypt()) are not equivalent


I did try to decrypt the value in PostgreSQL just to see what it is in a readable format by first trying:

SELECT decrypt(data_point, 'aBcDeF', 'aes') 
FROM data_tbl;


But that just returned [binary data] for all the rows.

Then I came across this answer to use convert_from as in:

SELECT convert_from(decrypt(data_point, 'aBcDeF', 'aes'), 'SQL_ASCII')
FROM data_tbl;


However, I just get this error:

ERROR:  invalid byte sequence for encoding "UTF8": 0xcf 0xf5
SQL state: 22021


So at this point I'm stumped as to how to resolve the primary issue. The primary issue being doing the equivalent of AES_DECRYPT but in PostgreSQL 11. The last error message isn't so much an issue, but it is likely related to decrypt() not returning the same results.

Any suggestions?
Actually, I suspect all three of your assumptions are correct and all play a part in why you cannot decrypt the data. I will attempt to explain a little.

One of the most important factors in encryption &amp; decryption is binary compatibility - to be more precise, how does each database physically store the data? Is the data stored big-endian or little-endian, how are the internal data structures implemented (what is the binary difference between VARBINARY and BYTEA), what is the character set &amp; encoding in use on each system? Encryption is bit-sensitive and so any variations in the bit/byte order will affect the outcome of any encryption or decryption.

Additionally, even though both systems can use AES their default settings vary slightly. On MySQL the default for AES is a 128bit key using ECB mode ; whereas on PostgreSQL the default is a 128bit key using CBC mode to encrypt the data. You will need to ensure that all of the options used to encrypt/decrypt are the same (even if you were trying to decrypt it within the same system).

Logically, if everything was identical between the two systems you should be able to decrypt the data. In practice, it's going to be a lot harder than that and if you have access to the source system, decrypt in on there, export the data, then import &amp; encrypt it in PostgreSQL.