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(at)joh(dot)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