Re: pgcrypto functions fail for asymmetric encryption/decryption - Mailing list pgsql-general

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