Thread: GPG signing

I need to be able to cryptographically sign objects in my database
using a public key scheme.

Is my only option to install plpython or some such thing?   Python
generally makes me unhappy as I'm never convinced of its stability or
the quality of its APIs, and it is not obvious to me which of the many
different gpg-ish packages I should choose.

Any other options?  Am I missing something?

Thanks.

__
Marc

On 5/26/20 12:01 PM, Marc Munro wrote:
> I need to be able to cryptographically sign objects in my database
> using a public key scheme.
> 
> Is my only option to install plpython or some such thing?   Python
> generally makes me unhappy as I'm never convinced of its stability or
> the quality of its APIs, and it is not obvious to me which of the many
> different gpg-ish packages I should choose.
> 
> Any other options?  Am I missing something?

https://www.postgresql.org/docs/12/pgcrypto.html#id-1.11.7.34.7

> 
> Thanks.
> 
> __
> Marc
> 
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

I need to be able to cryptographically sign objects in my database
using a public key scheme.

Any other options?  Am I missing something?

On Tue, 2020-05-26 at 12:04 -0700, Adrian Klaver wrote:
> On 5/26/20 12:01 PM, Marc Munro wrote:
> > I need to be able to cryptographically sign objects in my database
> > using a public key scheme.
> > [ . . . ]
> > Any other options?  Am I missing something?
> 
> https://www.postgresql.org/docs/12/pgcrypto.html#id-1.11.7.34.7

I looked at that but I must be missing something.  In order to usefully
sign something, the private, secret, key must be used to encrypt a
disgest of the thing being signed (something of a simplification, but
that's the gist).  This can then be verified, by anyone, using the
public key.

But the pgcrypto functions, for good reasons, do not allow the private
(secret) key to be used in this way.  Encryption and signing algorithms
are necessarily different as the secret key must be protected; and we
don't want signatures to be huge, and it seems that pgcrypto has not
implemented signing algorithms.

What am I missing?

__
Marc

Marc Munro wrote:

> On Tue, 2020-05-26 at 12:04 -0700, Adrian Klaver wrote:
> > On 5/26/20 12:01 PM, Marc Munro wrote:
> > > I need to be able to cryptographically sign objects in my database
> > > using a public key scheme.
> > > [ . . . ]
> > > Any other options?  Am I missing something?
> > 
> > https://www.postgresql.org/docs/12/pgcrypto.html#id-1.11.7.34.7
> 
> I looked at that but I must be missing something.  In order to usefully
> sign something, the private, secret, key must be used to encrypt a
> disgest of the thing being signed (something of a simplification, but
> that's the gist).  This can then be verified, by anyone, using the
> public key.
> 
> But the pgcrypto functions, for good reasons, do not allow the private
> (secret) key to be used in this way.  Encryption and signing algorithms
> are necessarily different as the secret key must be protected; and we
> don't want signatures to be huge, and it seems that pgcrypto has not
> implemented signing algorithms.
> 
> What am I missing?
> 
> __
> Marc

That page linked to above says:

  F.25.3.10. Limitations of PGP Code

    No support for signing.

Marc Munro <marc@bloodnok.com> writes:

> I need to be able to cryptographically sign objects in my database
> using a public key scheme.
>
> Is my only option to install plpython or some such thing?   Python
> generally makes me unhappy as I'm never convinced of its stability or
> the quality of its APIs, and it is not obvious to me which of the many
> different gpg-ish packages I should choose.
>
> Any other options?  Am I missing something?
>

This is something you would normally implement at the application level,
using the database as just the store for the data and signatures or
signed digests.

Main reason for this is to allow for secure key management. It is very
difficult to implement a secure key management solution at the database
level unless it is designed into the fundamental architecture of the
rdbms. It is the same age old problem - how can you encrypt data AND
have the keys for the encrypted data in the same place. The main reason
for encryption is so that if your store gets compromised, the data
cannot be read. However, if your key is also in the store, then when
your compromised, your key is compromised and your encryption becomes a
mute issue.

If on the other hand you handle the encryption/signing at the application level,
you can separate your key store and data store so that compromise of one
doesn't also compromise the other. This of course does create other
issues - most notably being that now you have an additional mandatory
layer between you and your data (for example, you can use psql to query
your database, but all you can see is encrypted objects.

In your case, this may not be as big an issue because you state you want
to sign rather than encrypt. You could, for example, design your
application so that the data is in one column and the signature is in
the other (or use json or other 'object' types that allow
attributes/properties). This would allow simple querying of the data and
verification of data integrity to be performed as separate operations.
All you then need to ensure is that every time data is modified, a new
signature is generated.

I would also verify you really do need full cryptographic signing rather
than just some less rigid integrity verification, like a basic checksum
hash. Crypto signing is most useful when you want to both verify the
integrity of something and it's source. i.e. this data has not been
changed and was signed by X. In this case, each source is
encrypted/signed with a different private/secret key. If on the other
hand you just want to know that the data has not been modified, you can
generate a checksum/hash of the data when it is inserted/updated and
store that in a separate column. This data may or may not be encrypted
depending on your use case. In this situation, you only need one key,
the key used to encrypt the column or no keys if you don't actually need
to encrypt it.

While non-encrypted checksums is not as secure, not all applications
need that level of security. In some cases, having a separate checksum
is sufficient. If someone wants to modify the data 'unofficially', in
addition to compromising the db and modifying the data, they have to
also generate a new hash of the modified data and store that in the
appropriate column. If you just want to protect against accidental
modification of the data or have reasonable confidence (for some measure
of reasonable), just having a checksum hash may be sufficient. big
advantage with the simpler case with no actual data encryption is that
other clients can access/use the data and not require access to the
encryption key. 

-- 
Tim Cross

On Wed, 2020-05-27 at 14:42 -0700, Michel Pelletier wrote:
> Hi Marc,
> 
> You can sign content with pgsodium:
> 
> https://github.com/michelp/pgsodium

Michel,
Yay!  A modern crypto implementation.  And it seems to do most of what
I need right out of the box with way less work than pgcrypto.

Any chance that crypto_sign_detatched() and
crypto_sign_verify_detatched() will be implemented soon?

I'll implement it and provide a patch if you'd like.

__
Marc

On Wed, May 27, 2020 at 10:14:46AM +1000, Tim Cross wrote:
> 
> Marc Munro <marc@bloodnok.com> writes:
> 
> > I need to be able to cryptographically sign objects in my database
> > using a public key scheme.
> >
> > Is my only option to install plpython or some such thing?   Python
> > generally makes me unhappy as I'm never convinced of its stability or
> > the quality of its APIs, and it is not obvious to me which of the many
> > different gpg-ish packages I should choose.
> >
> > Any other options?  Am I missing something?
> >
> 
> This is something you would normally implement at the application level,
> using the database as just the store for the data and signatures or
> signed digests.
> 
> Main reason for this is to allow for secure key management. It is very
> difficult to implement a secure key management solution at the database
> level unless it is designed into the fundamental architecture of the
> rdbms. It is the same age old problem - how can you encrypt data AND
> have the keys for the encrypted data in the same place. The main reason
> for encryption is so that if your store gets compromised, the data
> cannot be read. However, if your key is also in the store, then when
> your compromised, your key is compromised and your encryption becomes a
> mute issue.

This blog entry illustrates row signing on the client side:

    https://momjian.us/main/blogs/pgblog/2018.html#September_7_2018

-- 
  Bruce Momjian  <bruce@momjian.us>        https://momjian.us
  EnterpriseDB                             https://enterprisedb.com

  The usefulness of a cup is in its emptiness, Bruce Lee