On Dec 3, 2010, at 1:49 PM, Heikki Linnakangas wrote:
> 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 incompatible with 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 data during regular upgrades through postgresql-8.4.5. I know that
>> the raw encrypt() and decrypt() are no longer recommended when the pgp_*()
>> can be used, but this is now a legacy issue since the pgp_*() functions did
>> not even exist 10 years 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's an 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
>
> --
> Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs
Heikki,
I figured (and hoped) that it would turn out to be something like that--I guess
I just didn't dig deep enough to find the answer my self. I need to read the
release notes more thoroughly.
Thanks much for your help!
Daniel
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
