Re: pgcrypto functions fail for asymmetric encryption/decryption

From: Stefan Niantschur <sniantschur(at)web(dot)de>
To: "Marko Kreen" <markokr(at)gmail(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: pgcrypto functions fail for asymmetric encryption/decryption
Date: 2007-12-03 09:21:02
Message-ID: 200712031021.02627.sniantschur@web.de
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Am Fri, 30 Nov 2007 12:06:37 +0200
schrieb "Marko Kreen" <markokr(at)gmail(dot)com>:

Hi Marko,

first of all, thank you for your help.

Please find my answers below:

> On 11/29/07, Stefan Niantschur <sniantschur(at)web(dot)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(at)ens-it(dot)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(at)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.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message rokj 2007-12-03 10:50:57 Re: One or more tables?
Previous Message Pascal Cohen 2007-12-03 08:57:30 Re: HA and Replication - how to choose among all the available solutions