Am Fri, 30 Nov 2007 12:06:37 +0200
schrieb "Marko Kreen" <markokr@xxxxxxxxx>:
Hi Marko,
first of all, thank you for your help.
Please find my answers below:
> On 11/29/07, Stefan Niantschur <sniantschur@xxxxxx> 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@xxxxxxxxx>
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.
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
