Re: pgcrypto functions fail for asymmetric encryption/decryption

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

>
> 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.