Thread: 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