Re: pgcrypto bug or my brain?

On 03.12.2010 19:48, daniel wrote:
> I have discovered either a bug in pgcrypto (postgresql-9.0.1) or at least an implementation change that is
incompatiblewith how I've been using pgcrypto. I'm hoping a discussion here on bugs@ is an appropriate first course of
discussion.
>
> I have a couple of databases in which I have been using pgcrypto for about 10 years (one relation has>  1.8 million
records).I believe I started using pgcrypto with postgresql-7.2.x and have had absolutely no adverse issues with my
dataduring regular upgrades through postgresql-8.4.5. I know that the raw encrypt() and decrypt() are no longer
recommendedwhen the pgp_*() can be used, but this is now a legacy issue since the pgp_*() functions did not even exist
10years ago. Please note that the pgp_*() functions do work fine in postgresql-9.0.1. 
>
> During testing of upgrade to 9.0.1 (I _love_ streaming replication!), my encrypted data gets mangled during import
(psql-f<file_dumped_with_pg_dump>) and, in fact, I can't even use encrypt() or decrypt() on new data in my "usual way".
Here'san example that works on 7.2.x through 8.4.5 but not 9.0.1 (additional platform details are below): 
>
>      --
>      -- Pull in pgcrypto functions:
>      --
>          \i /usr/local/pgsql/share/contrib/pgcrypto.sql
>
>
>      --
>      -- Create a test table:
>      --
>          create table cryptest (
>            id serial not null primary key,
>            plaint character varying not null,
>            enct bytea
>          );
>
>
>      --
>      -- Insert some data:
>      --
>          insert into cryptest (plaint, enct) values
>            ('Testing blowfish...', encrypt('Testing blowfish...',
>                  E'I know this is not a proper key but it _should_ work', 'bf'));
>
>
>      --
>      -- Fetch the data:
>      --
>          select
>            id,
>            plaint,
>            decrypt(enct, E'I know this is not a proper key but it _should_ work', 'bf')
>          from
>            cryptest;
>
>
> Platform:
> CentOS-5.5 (fully up to date with 'yum update') both i386 and x86_64
> Postgresql configured with './configure --with-openssl'
>
>
> I'll be happy to provide any additional information necessary and do any sort of testing (if it should prove to be
necessary)though my skills in this are somewhat limited. 

decrypt() returns a bytea, and the default representation of bytea was
changed in 9.0. The result is the same but it's just displayed
differently. Try "set bytea_output TO 'escape'" to get the old familiar
output.

The proper way to do that is to use convert_to/from to convert from text
to bytea before encrypting, and from bytea to text after decrypting.

--
   Heikki Linnakangas
   EnterpriseDB   http://www.enterprisedb.com