Pgcrypto doesn't work correctly on Windows

Question

I execute the same query on 2 different servers and I get a different results. Does anyone know why?

select decrypt('\x792135887dace2af15d3f8548cc20919','\x265bb788ef6762abf50577f8a6669aa0','aes-ecb')

Debian postgresql 9.3 server output (result expected):

"\xafb8967640bd0400309e7b0008acbb23"

Windows postgresql 9.3 server output (result wrong):

"\257\270\226v@\275\004\0000\236{\000\010\254\273#"

Answer 1

Your Windows 9.3 server has a non-default configuration; it has bytea_output set to escape mode, not hex mode.

The result is actually the same, it's just being displayed in a different text representation of the underlying binary.

regress=> SHOW bytea_output;
 bytea_output 
--------------
 hex
(1 row)

regress=> SELECT BYTEA '\257\270\226v@\275\004\0000\236{\000\010\254\273#';
               bytea                
------------------------------------
 \xafb8967640bd0400309e7b0008acbb23
(1 row)