Обсуждение: pgcrypto bug or my brain?
I have discovered either a bug in pgcrypto (postgresql-9.0.1) or at least a=
n implementation change that is incompatible with how I've been using pgcry=
pto. I'm hoping a discussion here on bugs@ is an appropriate first course o=
f 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 usin=
g 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 wor=
k fine in postgresql-9.0.1.
During testing of upgrade to 9.0.1 (I _love_ streaming replication!), my en=
crypted 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', 'b=
f'));
--
-- Fetch the data:
--
select
id,
plaint,
decrypt(enct, E'I know this is not a proper key but it _should_ w=
ork', '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 so=
rt of testing (if it should prove to be necessary) though my skills in this=
are somewhat limited.
Thanks,
Daniel
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
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 leas=
t an implementation change that is incompatible with how I've been using pg=
crypto. I'm hoping a discussion here on bugs@ is an appropriate first cours=
e of discussion.
>>=20
>> I have a couple of databases in which I have been using pgcrypto for abo=
ut 10 years (one relation has> 1.8 million records). I believe I started u=
sing pgcrypto with postgresql-7.2.x and have had absolutely no adverse issu=
es with my data during regular upgrades through postgresql-8.4.5. I know th=
at 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.
>>=20
>> 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_dum=
p>) 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):
>>=20
>> --
>> -- Pull in pgcrypto functions:
>> --
>> \i /usr/local/pgsql/share/contrib/pgcrypto.sql
>>=20
>>=20
>> --
>> -- Create a test table:
>> --
>> create table cryptest (
>> id serial not null primary key,
>> plaint character varying not null,
>> enct bytea
>> );
>>=20
>>=20
>> --
>> -- 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'));
>>=20
>>=20
>> --
>> -- Fetch the data:
>> --
>> select
>> id,
>> plaint,
>> decrypt(enct, E'I know this is not a proper key but it _should=
_ work', 'bf')
>> from
>> cryptest;
>>=20
>>=20
>> Platform:
>> CentOS-5.5 (fully up to date with 'yum update') both i386 and x86_64
>> Postgresql configured with './configure --with-openssl'
>>=20
>>=20
>> 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 t=
his are somewhat limited.
>=20
> decrypt() returns a bytea, and the default representation of bytea was ch=
anged in 9.0. The result is the same but it's just displayed differently. T=
ry "set bytea_output TO 'escape'" to get the old familiar output.
>=20
> 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.
>=20
> --=20
> Heikki Linnakangas
> EnterpriseDB http://www.enterprisedb.com
>=20
> --=20
> 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 g=
uess I just didn't dig deep enough to find the answer my self. I need to re=
ad the release notes more thoroughly.
Thanks much for your help!
Daniel