Thread: pgcrypto functions fail for asymmetric encryption/decryption
Hi, 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: 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 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 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 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? tia
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----- > > 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.
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