BUG #7923: PGP secret key with password decryption not working - Mailing list pgsql-bugs

The following bug has been logged on the website:

Bug reference:      7923
Logged by:          Keith Fiske
Email address:      keith@omniti.com
PostgreSQL version: 9.2.3
Operating system:   Debian/Ubuntu/Solaris
Description:        =


Running into an issue when we tried to add a password to a gpg secret key
used to encrypt data in the database. Things have been working fine with no
password on they key, but that isn't ideal. I was able to recreate this
issue on my local install of postgres, so it's not just an issue on the
server where I first noticed it. Below is the process I went through,
generating a key, moving it to where postgres can read it, encrypting and
then attempting to decrypt the data. I know the password I gave is correct
because I used the same password to remove it from the key and then things
just worked.

Ran into someone else reporting pretty much the same issue but no resolution
other than removing the password.
http://bytes.com/topic/postgresql/answers/943536-corrupt-data-error-decrypt=
ion-postgresql-using-gnupg

gpg --gen-key
gpg (GnuPG) 1.4.11; Copyright (C) 2010 Free Software Foundation, Inc.
This is free software: you are free to change and redistribute it.
There is NO WARRANTY, to the extent permitted by law.

Please select what kind of key you want:
   (1) RSA and RSA (default)
   (2) DSA and Elgamal
   (3) DSA (sign only)
   (4) RSA (sign only)
Your selection? 1
RSA keys may be between 1024 and 4096 bits long.
What keysize do you want? (2048) =

Requested keysize is 2048 bits
Please specify how long the key should be valid.
         0 =3D key does not expire
      <n>  =3D key expires in n days
      <n>w =3D key expires in n weeks
      <n>m =3D key expires in n months
      <n>y =3D key expires in n years
Key is valid for? (0) =

Key does not expire at all
Is this correct? (y/N) y

You need a user ID to identify your key; the software constructs the user
ID
from the Real Name, Comment and Email Address in this form:
    "Heinrich Heine (Der Dichter) <heinrichh@duesseldorf.de>"

Real name: Test Pgcrypto
Email address: test@pgcrypto.com
Comment: =

You selected this USER-ID:
    "Test Pgcrypto <test@pgcrypto.com>"

Change (N)ame, (C)omment, (E)mail or (O)kay/(Q)uit? O
You need a Passphrase to protect your secret key.

We need to generate a lot of random bytes. It is a good idea to perform
some other action (type on the keyboard, move the mouse, utilize the
disks) during the prime generation; this gives the random number
generator a better chance to gain enough entropy.
...+++++
+++++
We need to generate a lot of random bytes. It is a good idea to perform
some other action (type on the keyboard, move the mouse, utilize the
disks) during the prime generation; this gives the random number
generator a better chance to gain enough entropy.

Not enough random bytes available.  Please do some other work to give
the OS a chance to collect more entropy! (Need 8 more bytes)
..+++++
..+++++
gpg: key 734ECABE marked as ultimately trusted
public and secret key created and signed.

gpg: checking the trustdb
gpg: 3 marginal(s) needed, 1 complete(s) needed, PGP trust model
gpg: depth: 0  valid:   1  signed:   0  trust: 0-, 0q, 0n, 0m, 0f, 1u
pub   2048R/734ECABE 2013-03-06
      Key fingerprint =3D C25C 7B89 B2B4 674F 551A  2E5F FA95 0DAF 734E CABE
uid                  Test Pgcrypto <test@pgcrypto.com>
sub   2048R/F122A512 2013-03-06

#### Exported public & secrect keys in binary form and copied to
$datadir/keys/

keith=3D# select
pgcrypto.pgp_key_id(pg_read_binary_file('keys/test_pgcrypto_public.key'));
    pgp_key_id    =

------------------
 3E265C23F122A512
(1 row)

keith=3D# select
pgcrypto.pgp_key_id(pg_read_binary_file('keys/test_pgcrypto_secret.key'));
    pgp_key_id    =

------------------
 3E265C23F122A512
(1 row)

keith=3D# create table test_pgcrypto(data_plain text, data bytea);
CREATE TABLE
keith=3D# insert into test_pgcrypto (data_plain, data) values ('1234567890',
pgcrypto.pgp_pub_encrypt('1234567890',
pg_read_binary_file('keys/test_pgcrypto_public.key')));
INSERT 0 1

keith=3D# select pgcrypto.pgp_pub_decrypt(data,
pg_read_binary_file('keys/test_pgcrypto_secret.key')) from test_pgcrypto ;
ERROR:  Need password for secret key
keith=3D# select pgcrypto.pgp_pub_decrypt(data,
pg_read_binary_file('keys/test_pgcrypto_secret.key'), 'stuff') from
test_pgcrypto ;
ERROR:  Corrupt data

$ gpg --list-secret-keys

------------------------------
sec   2048R/734ECABE 2013-03-06
uid                  Test Pgcrypto <test@pgcrypto.com>
ssb   2048R/F122A512 2013-03-06

$ gpg --edit-key F122A512
gpg (GnuPG) 1.4.11; Copyright (C) 2010 Free Software Foundation, Inc.
This is free software: you are free to change and redistribute it.
There is NO WARRANTY, to the extent permitted by law.

Secret key is available.

pub  2048R/734ECABE  created: 2013-03-06  expires: never       usage: SC  =

                     trust: ultimate      validity: ultimate
sub  2048R/F122A512  created: 2013-03-06  expires: never       usage: E   =

[ultimate] (1). Test Pgcrypto <test@pgcrypto.com>

gpg> passwd
Key is protected.

You need a passphrase to unlock the secret key for
user: "Test Pgcrypto <test@pgcrypto.com>"
2048-bit RSA key, ID 734ECABE, created 2013-03-06

Enter the new passphrase for this secret key.

You don't want a passphrase - this is probably a *bad* idea!

Do you really want to do this? (y/N) y

gpg> save


$ gpg --export-secret-keys F122A512 > test_pgcrypto_secret.key
#### Copied passwordless key to $datadir/keys/

keith=3D# select pgcrypto.pgp_pub_decrypt(data,
pg_read_binary_file('keys/test_pgcrypto_secret.key')) from test_pgcrypto ;
 pgp_pub_decrypt =

-----------------
 1234567890