pgcrypto functions fail for asymmetric encryption/decryption

On 11/29/07, Stefan Niantschur <sniantschur@web.de> wrote:

I have a table with userids and public keys. I want to write a function
which does a select and returns the result pgp encrypted.

However, I have some problems:

Could you send the keys you have problems with? If actual keys
then ofcourse generate temp-keys instead.

Or at least send key parameters (gpg --list-keys output).

Also I need PostgreSQL version, if its compiled with OpenSSL,
then OpenSSL version, your OS and CPU info, just in case.
Compiler + compiler options maybe too.

SELECT encode(decode((SELECT ens_pubkey FROM
ens_user)::text,'escape'),'escape'::text)::text;
-> returns the public key, => ok

SELECT armor(dearmor((SELECT ens_pubkey FROM ens_user)::text));
-> returns the key in a different format, => problem

You mean it gives fixed header? Both pgcrypto and gpg ignore
it anyway, so I did not bother guessing it. But if it really
causes problems (doubtful) it can be fixed by looking at data.

SELECT
armor(pgp_pub_encrypt_bytea(armor(pgp_sym_encrypt('geheim'::text,'test'::text))::bytea,dearmor((SELECT
ens_pubkey FROM ens_user WHERE ens_userid = 10112)::text)));
-> returns a pgp-encrypted message which cannot be decrypted by GnuPG,
=> problem

This query does not parse, but if I remove the bytea case it works.

How does GnuPG fail?

SELECT
pgp_pub_decrypt(dearmor(armor(pgp_pub_encrypt(armor(pgp_sym_encrypt('geheim'::text,'test'::text)),dearmor((SELECT
ens_pubkey FROM ens_user WHERE ens_userid =
10112)::text)))),dearmor((SELECT ens_privkey FROM ens_user WHERE
ens_userid = 10112)::text),'test'::text);
-> returns 'ERROR: Corrupt data' => problem

Works for me.

SELECT
pgp_key_id(pgp_pub_encrypt_bytea(armor(pgp_sym_encrypt('geheim'::text,'test'::text))::bytea,dearmor((SELECT
ens_pubkey FROM ens_user WHERE ens_userid = 10112)::text)));
-> returns the correct key id of the deployed public key

So, if I cannot decrypt the message which I have been encrypting with
the appropriate keys, how can I proceed?

I want to encrypt messages in postgres and decrypt it elsewhere,
However, the result of the encryption algorithm seems to deliver a
wrong result. Otherwise I cannot explain why encrypting and immidiately
decrypting the message fails.

The same proceeding is succesful when using symmetric keys:
SELECT
pgp_sym_decrypt((pgp_sym_encrypt('geheim'::text,'test'::text)),'test'::text);
-> returns 'geheim' which is the encrypted and then again decrypted
message.

What did I wrong when trying to use asymmetric encryption?

Generally the stuff you try should work, although some of the
dermor(armor()) and pgp_pub_encrypt(pgp_sym_encrypt()) stuff
seem to be excessive.

So either you have found a bug in pgcrypto which is dependant
on public key algo/OS/CPU/OpenSSL/compiler details or you
have some mistake on your own (eg, your private and public key
does not match).

So I need more details to understand your problem.

--
marko

Am Fri, 30 Nov 2007 12:06:37 +0200
schrieb "Marko Kreen" <markokr@gmail.com>:

Hi Marko,

first of all, thank you for your help.

Please find my answers below:

On 11/29/07, Stefan Niantschur <sniantschur@web.de> wrote:

I have a table with userids and public keys. I want to write a
function which does a select and returns the result pgp encrypted.

However, I have some problems:

Could you send the keys you have problems with?  If actual keys
then ofcourse generate temp-keys instead.

Or at least send key parameters (gpg --list-keys output).

pub   1024D/0476AD06 2007-11-27 [verfällt: 2008-11-26]
uid                  Test User (Probebenutzer) <test@ens-it.de>
sub   2048g/879D6C41 2007-11-27 [verfällt: 2008-11-26]

Also I need PostgreSQL version, if its compiled with OpenSSL,
then OpenSSL version, your OS and CPU info, just in case.
Compiler + compiler options maybe too.

The database system runs on a virtualised box in qemu 0.9.0-14

PostgreSQL version: PostgreSQL 8.2.5 on i686-pc-linux-gnu, compiled
by GCC gcc (GCC) 4.2.2

compiled with option: --with-openssl
OpenSSL-version: 0.9.8g
OS: Archlinux (current)
CPU: AMD Athlon(tm) 64 Processor 3000+

SELECT encode(decode((SELECT ens_pubkey FROM
ens_user)::text,'escape'),'escape'::text)::text;
-> returns the public key, => ok

SELECT armor(dearmor((SELECT ens_pubkey FROM ens_user)::text));
-> returns the key in a different format, => problem

You mean it gives fixed header?  Both pgcrypto and gpg ignore
it anyway, so I did not bother guessing it.  But if it really
causes problems (doubtful) it can be fixed by looking at data.

The pub-key looks like this:
 -----BEGIN PGP PUBLIC KEY BLOCK-----
 Version: GnuPG v2.0.7 (GNU/Linux)
 
 mQGiBEdMDtkRBACk0FB5oLGW2hI3DAZ+Q15UJmEZC2neK8WWnK+1f2fjtvs4Y7L+
 3uxQTt2issRN+6P/oD62nQhxNS5QZYeWRxUiP/881aoT2uKJMkMRpf6Uwp/Np+4k

The armor/dearmor version looks like this:
 -----BEGIN PGP MESSAGE-----
 
 mQGiBEdMDtkRBACk0FB5oLGW2hI3DAZ+Q15UJmEZC2neK8WWnK+1f2fjtvs4Y7L+3uxQTt2issRN
 +6P/oD62nQhxNS5QZYeWRxUiP/881aoT2uKJMkMRpf6Uwp/Np+4k1gIyssWkic1GwPYjk+uXiDMP

SELECT

armor(pgp_pub_encrypt_bytea(armor(pgp_sym_encrypt('geheim'::text,'test'::text))::bytea,dearmor((SELECT

ens_pubkey FROM ens_user WHERE ens_userid = 10112)::text)));
-> returns a pgp-encrypted message which cannot be decrypted by
GnuPG, => problem

This query does not parse, but if I remove the bytea case it works.

At my side both versions work pgp_pub_encrypt and
pgp_pub_encrypt_bytea. The only thing is that a cast to bytea has to be
prepare beforehand for the pgp_pub_encrypt_bytea:
CREATE CAST (BYTEA AS TEXT) WITHOUT function;

How does GnuPG fail?

[test@test ~]gpg2 message
gpg: Keine gültigen OpenPGP-Daten gefunden.
gpg: processing message failed: Unbekannter Systemfehler

Meaning: No valid OpenPGP data found. Unknown system error.

SELECT

pgp_pub_decrypt(dearmor(armor(pgp_pub_encrypt(armor(pgp_sym_encrypt('geheim'::text,'test'::text)),dearmor((SELECT

ens_pubkey FROM ens_user WHERE ens_userid =
10112)::text)))),dearmor((SELECT ens_privkey FROM ens_user WHERE
ens_userid = 10112)::text),'test'::text);
-> returns 'ERROR: Corrupt data' => problem

Works for me.

SELECT

pgp_key_id(pgp_pub_encrypt_bytea(armor(pgp_sym_encrypt('geheim'::text,'test'::text))::bytea,dearmor((SELECT

ens_pubkey FROM ens_user WHERE ens_userid = 10112)::text)));
-> returns the correct key id of the deployed public key

So, if I cannot decrypt the message which I have been encrypting
with the appropriate keys, how can I proceed?

I want to encrypt messages in postgres and decrypt it elsewhere,
However, the result of the encryption algorithm seems to deliver a
wrong result. Otherwise I cannot explain why encrypting and
immidiately decrypting the message fails.

The same proceeding is succesful when using symmetric keys:
SELECT

pgp_sym_decrypt((pgp_sym_encrypt('geheim'::text,'test'::text)),'test'::text);

-> returns 'geheim' which is the encrypted and then again decrypted
message.

What did I wrong when trying to use asymmetric encryption?

Generally the stuff you try should work, although some of the
dermor(armor()) and pgp_pub_encrypt(pgp_sym_encrypt()) stuff
seem to be excessive.

So either you have found a bug in pgcrypto which is dependant
on public key algo/OS/CPU/OpenSSL/compiler details or you
have some mistake on your own (eg, your private and public key
does not match).

So I need more details to understand your problem.

The public/private keys should match, as I can encrypt/decrypt the
message using gpg alone. Inserting the data into a table and using
pgcrypto functions fail.

No matter whether I try to encrypt a message in postgres and decrypt it
again in gnupg or the other way round, I always end in an error
message.

thx.

On 12/3/07, Stefan Niantschur <sniantschur@web.de> wrote:

Or at least send key parameters (gpg --list-keys output).

pub 1024D/0476AD06 2007-11-27 [verfällt: 2008-11-26]
uid Test User (Probebenutzer) <test@ens-it.de>
sub 2048g/879D6C41 2007-11-27 [verfällt: 2008-11-26]

Elgamal 2048 works here, plus it should be tested in buildfarm also.

Also I need PostgreSQL version, if its compiled with OpenSSL,
then OpenSSL version, your OS and CPU info, just in case.
Compiler + compiler options maybe too.

The database system runs on a virtualised box in qemu 0.9.0-14

PostgreSQL version: PostgreSQL 8.2.5 on i686-pc-linux-gnu, compiled
by GCC gcc (GCC) 4.2.2

compiled with option: --with-openssl
OpenSSL-version: 0.9.8g
OS: Archlinux (current)
CPU: AMD Athlon(tm) 64 Processor 3000+

Could you run regression tests for both pgcrypto and Postgres
itself on your env:

$ cd pg-8.2.5
$ make check
$ cd contrib/pgcrypto
$ make install installcheck

That should give more reproducible case hopefully.

The "make check" creates its own temp installation but
the pgcrypto's "make installcheck" works on already running
postgres installation. So main postgres instance should
be running.

SELECT encode(decode((SELECT ens_pubkey FROM
ens_user)::text,'escape'),'escape'::text)::text;
-> returns the public key, => ok

SELECT armor(dearmor((SELECT ens_pubkey FROM ens_user)::text));
-> returns the key in a different format, => problem

You mean it gives fixed header? Both pgcrypto and gpg ignore
it anyway, so I did not bother guessing it. But if it really
causes problems (doubtful) it can be fixed by looking at data.

The pub-key looks like this:
-----BEGIN PGP PUBLIC KEY BLOCK-----
Version: GnuPG v2.0.7 (GNU/Linux)

mQGiBEdMDtkRBACk0FB5oLGW2hI3DAZ+Q15UJmEZC2neK8WWnK+1f2fjtvs4Y7L+
3uxQTt2issRN+6P/oD62nQhxNS5QZYeWRxUiP/881aoT2uKJMkMRpf6Uwp/Np+4k

The armor/dearmor version looks like this:
-----BEGIN PGP MESSAGE-----

mQGiBEdMDtkRBACk0FB5oLGW2hI3DAZ+Q15UJmEZC2neK8WWnK+1f2fjtvs4Y7L+3uxQTt2issRN
+6P/oD62nQhxNS5QZYeWRxUiP/881aoT2uKJMkMRpf6Uwp/Np+4k1gIyssWkic1GwPYjk+uXiDMP

Is the following part really missing:

XXXX
-----END PGP MESSAGE-----

???

That would be indeed broken output. Did you paste rest of the
output intact? The lengths differ for some reason. Weird.

SELECT

armor(pgp_pub_encrypt_bytea(armor(pgp_sym_encrypt('geheim'::text,'test'::text))::bytea,dearmor((SELECT

ens_pubkey FROM ens_user WHERE ens_userid = 10112)::text)));
-> returns a pgp-encrypted message which cannot be decrypted by
GnuPG, => problem

This query does not parse, but if I remove the bytea case it works.

At my side both versions work pgp_pub_encrypt and
pgp_pub_encrypt_bytea. The only thing is that a cast to bytea has to be
prepare beforehand for the pgp_pub_encrypt_bytea:
CREATE CAST (BYTEA AS TEXT) WITHOUT function;

Ah, ok. But why do you need to use the _bytea version?

The public/private keys should match, as I can encrypt/decrypt the
message using gpg alone. Inserting the data into a table and using
pgcrypto functions fail.

No matter whether I try to encrypt a message in postgres and decrypt it
again in gnupg or the other way round, I always end in an error
message.

I really would like to know if the regtests pass on your box or not.

If not please send 'regression.diffs' to me. I'm interested
what exactly fails.

--
marko

Am Montag, 3. Dezember 2007 schrieb Marko Kreen:

Hi Marko,

I finally made it. I created a brand-new key, reworked the query and voila.

It seems that the GnuPG key has to be created with
paramter --cipher-algo=blowfish before it can be used together with pgcrypto.
The generated key with the default settings failed for some reason.

This query returned the correct result:
SELECT pgp_pub_decrypt(pgp_pub_encrypt('geheim'::text,
dearmor((SELECT ens_pubkey FROM ens_user WHERE ens_userid = 1)::text)),
dearmor((SELECT ens_privkey FROM ens_user WHERE ens_userid = 1)::text),
'<passwort>'::text);

Thank you very much for your help.

On 12/3/07, Stefan Niantschur <sniantschur@web.de> wrote:

Or at least send key parameters (gpg --list-keys output).

pub 1024D/0476AD06 2007-11-27 [verfällt: 2008-11-26]
uid Test User (Probebenutzer) <test@ens-it.de>
sub 2048g/879D6C41 2007-11-27 [verfällt: 2008-11-26]

Elgamal 2048 works here, plus it should be tested in buildfarm also.

Also I need PostgreSQL version, if its compiled with OpenSSL,
then OpenSSL version, your OS and CPU info, just in case.
Compiler + compiler options maybe too.

The database system runs on a virtualised box in qemu 0.9.0-14

PostgreSQL version: PostgreSQL 8.2.5 on i686-pc-linux-gnu, compiled
by GCC gcc (GCC) 4.2.2

compiled with option: --with-openssl
OpenSSL-version: 0.9.8g
OS: Archlinux (current)
CPU: AMD Athlon(tm) 64 Processor 3000+

Could you run regression tests for both pgcrypto and Postgres
itself on your env:

$ cd pg-8.2.5
$ make check
$ cd contrib/pgcrypto
$ make install installcheck

That should give more reproducible case hopefully.

The "make check" creates its own temp installation but
the pgcrypto's "make installcheck" works on already running
postgres installation. So main postgres instance should
be running.

SELECT encode(decode((SELECT ens_pubkey FROM
ens_user)::text,'escape'),'escape'::text)::text;
-> returns the public key, => ok

SELECT armor(dearmor((SELECT ens_pubkey FROM ens_user)::text));
-> returns the key in a different format, => problem

You mean it gives fixed header? Both pgcrypto and gpg ignore
it anyway, so I did not bother guessing it. But if it really
causes problems (doubtful) it can be fixed by looking at data.

The pub-key looks like this:
-----BEGIN PGP PUBLIC KEY BLOCK-----
Version: GnuPG v2.0.7 (GNU/Linux)

mQGiBEdMDtkRBACk0FB5oLGW2hI3DAZ+Q15UJmEZC2neK8WWnK+1f2fjtvs4Y7L+
3uxQTt2issRN+6P/oD62nQhxNS5QZYeWRxUiP/881aoT2uKJMkMRpf6Uwp/Np+4k

The armor/dearmor version looks like this:
-----BEGIN PGP MESSAGE-----

mQGiBEdMDtkRBACk0FB5oLGW2hI3DAZ+Q15UJmEZC2neK8WWnK+1f2fjtvs4Y7L+3uxQTt2is
sRN
+6P/oD62nQhxNS5QZYeWRxUiP/881aoT2uKJMkMRpf6Uwp/Np+4k1gIyssWkic1GwPYjk+uXi
DMP

Is the following part really missing:

XXXX
-----END PGP MESSAGE-----

???

I apologise for not having pasted the complete key. It did in deed end
with -----END PGP MESSAGE-----

On 12/3/07, Stefan Niantschur <sniantschur@web.de> wrote:

I finally made it. I created a brand-new key, reworked the query and voila.

It seems that the GnuPG key has to be created with
paramter --cipher-algo=blowfish before it can be used together with pgcrypto.
The generated key with the default settings failed for some reason.

Well, that really does not explain why the old keys failed.
And you have no guarantee you wont get some failures in the
future. If the key cipher would have been unsupported, you
should have gotten errors not random corruptions.

I really suggest you run regression tests for both PostgreSQL
and pgcrypto. This should sanity-check your build and runtime
environment.

Also, as I understand you are experimenting with test keys?
Could you send both public and private keys to me, fully.
You can send privately if you wish. If they are not test
keys, then only public key and pgpdump output of private key,
if should not inlude any secret info.
(http://www.mew.org/~kazu/proj/pgpdump/)

I really like to understand whats going on...

--
marko

Am Montag, 3. Dezember 2007 schrieben Sie:

On 12/3/07, Stefan Niantschur <sniantschur@web.de> wrote:

I finally made it. I created a brand-new key, reworked the query and
voila.

It seems that the GnuPG key has to be created with
paramter --cipher-algo=blowfish before it can be used together with
pgcrypto. The generated key with the default settings failed for some
reason.

Well, that really does not explain why the old keys failed.
And you have no guarantee you wont get some failures in the
future. If the key cipher would have been unsupported, you
should have gotten errors not random corruptions.

I really suggest you run regression tests for both PostgreSQL
and pgcrypto. This should sanity-check your build and runtime
environment.

Also, as I understand you are experimenting with test keys?
Could you send both public and private keys to me, fully.
You can send privately if you wish. If they are not test
keys, then only public key and pgpdump output of private key,
if should not inlude any secret info.
(http://www.mew.org/~kazu/proj/pgpdump/)

I really like to understand whats going on...

Funny thing is, that I now can also use the old keys which have not been
working before. It seems that my initial query has been way too weird. Now
with the shorter (and correct) version even the old keys do work.

In my initial version of the query I did lots of armor/dearmor calls which
were very likely in the wrong place. So, your hint that the use of these
calls is excessive helped me to correct the query.

Running regression tests is not so easy as the distribution I use does not
support it. It seems that they had some trouble to build the software with
the regressions target in make.

Best Regards

On 11/29/07, Stefan Niantschur <sniantschur@web.de> wrote:

SELECT
pgp_pub_decrypt(dearmor(armor(pgp_pub_encrypt(armor(pgp_sym_encrypt('geheim'::text,'test'::text)),dearmor((SELECT
ens_pubkey FROM ens_user WHERE ens_userid =
10112)::text)))),dearmor((SELECT ens_privkey FROM ens_user WHERE
ens_userid = 10112)::text),'test'::text);
-> returns 'ERROR: Corrupt data' => problem

Reason for this seems to be wrong password for the key.

And pgcrypto is not very helpful pointing out the problem.

I need to review that part of code to give better error messages.
This is pretty common mistake that can happen.

--
marko