pgcrypto bug or my brain?

From: daniel <jmf(at)zeus(dot)bwh(dot)harvard(dot)edu>
To: pgsql-bugs(at)postgresql(dot)org
Subject: pgcrypto bug or my brain?
Date: 2010-12-03 17:48:26
Message-ID: 2CFE8790-1613-4721-8001-6F37DB78F91A@zeus.bwh.harvard.edu
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

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.

Thanks,

Daniel

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Alexia Lau 2010-12-03 18:39:39 No UUID support on One-Click installer PostgreSQL 9.0.1 64-bit Windows
Previous Message Tom Lane 2010-12-03 15:57:55 Re: Bug: table inheritance. Trigger before DELETE for each statement is ignored