Re: pgcrypto: PGP signatures

On 2014-09-07 19:28, Jeff Janes wrote:
> On Wed, Sep 3, 2014 at 2:13 PM, Marko Tiikkaja <marko@joh.to> wrote:
>> To sign without encrypting?
>
>
> To verify signatures of things that are not encrypted.  I'm not really
> interested in storing private keys in PostgreSQL, just things that can be
> done with public keys.  (But I will make a dummy private key for testing if
> I get that far.)

Right.  That functionality might be useful, but I think it should be a 
separate patch completely.  (And I doubt I have any interest in 
implementing it).

>>   Once I wrap it in dearmor, I get the ERROR:  No signature matching the key
>>> id present in the message
>>>
>>> The public key block I am giving it is for the keyid that is reported
>>> by pgp_sym_signatures, so I don't know what the problem might be.
>>>
>>
>> Have you tried with the debug=1 option?  (It's undocumented, but it was
>> like that before this patch and I didn't touch it).
>
> I have now, but it didn't produce any output for this situation.  I have
> two theories for the problem.  My test signed message was signed with a
> keyring that had a signing subkey, so it was signed with that, not with the
> master.  Maybe it doesn't like that.

Yeah, this patch only supports signing and verifying signatures with 
main keys.

> Also, I created the signed message in
> gpg, then imported it to PostgreSQL, and maybe it doesn't like that.

That should not be a problem.  I used gpg extensively when testing the 
patch.

> I've never used the pgp functions of pgcrypto before, so I decided to take
> a step back and try some of the functions that predate the proposed patch.
>   And I can't get them to work well, either.
>
> If I use pgp_sym_encrypt to encrypt a message with AES, then
> pgp_sym_decrypt will decrypt, and so will gpg command line tool.  But if I
> use gpg to encrypt a message, pgp_sym_decrypt will not decrypt it.
>
> select pgp_sym_decrypt(dearmor('-----BEGIN PGP MESSAGE-----
> Version: GnuPG v2.0.14 (GNU/Linux)
> Password: foobar
>
> jA0EBwMCqywsAv/hXJ7D0j8BWsD+9H7DY4KhrIIw2oV/6tBueVQ28+VDjBw9rGiy
> 3JRPmyXNN4wRTZXIyTVzK3LylWLomD9pQkao4hrQwSs=
> =02RI
> -----END PGP MESSAGE-----
> '),'foobar','debug=1');
> NOTICE:  dbg: parse_literal_data: data type=b
> ERROR:  Not text data
>
> So I don't know if I am doing something wrong, or if the PostgreSQL
> implementation of pgp is just not interoperable with other implementations.
>   That makes it hard to test the new features if I can't make the old ones
> work.

The NOTICE here says what's wrong: the message has been marked to 
contain binary data, not text.  You should be able to decrypt it with 
pgp_sym_decrypt_bytea() (and you can use convert_from() to get a text 
value out).



.marko