GPG signing

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
11 messages Options
Reply | Threaded
Open this post in threaded view
|

GPG signing

Marc Munro
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



Reply | Threaded
Open this post in threaded view
|

Re: GPG signing

Adrian Klaver-4
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
[hidden email]


Reply | Threaded
Open this post in threaded view
|

Re: GPG signing

David G Johnston
In reply to this post by Marc Munro
On Tuesday, May 26, 2020, Marc Munro <[hidden email]> 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?

This feels like it should be an application (middleware...) concern, not the database proper.  i.e., store previously signed data into a normal text or bytea field.

That said there is:


David J.
Reply | Threaded
Open this post in threaded view
|

Re: GPG signing

Marc Munro
In reply to this post by Adrian Klaver-4
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


Reply | Threaded
Open this post in threaded view
|

Re: GPG signing

raf-6
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.



Reply | Threaded
Open this post in threaded view
|

Re: GPG signing

Tim Cross
In reply to this post by Marc Munro

Marc Munro <[hidden email]> 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


Reply | Threaded
Open this post in threaded view
|

Re: GPG signing

Michel Pelletier
In reply to this post by Marc Munro
Hi Marc,

You can sign content with pgsodium:


On Tue, May 26, 2020 at 12:21 PM Marc Munro <[hidden email]> 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


Reply | Threaded
Open this post in threaded view
|

Re: GPG signing

Michel Pelletier
As it's not well documented yet (sorry) I'm following up to add signing is done with `crypto_sign()` and `crypto_sign_open()`


On Wed, May 27, 2020 at 2:42 PM Michel Pelletier <[hidden email]> wrote:
Hi Marc,

You can sign content with pgsodium:


On Tue, May 26, 2020 at 12:21 PM Marc Munro <[hidden email]> 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


Reply | Threaded
Open this post in threaded view
|

Re: GPG signing

Marc Munro
In reply to this post by Michel Pelletier
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




Reply | Threaded
Open this post in threaded view
|

Re: GPG signing

Michel Pelletier


On Thu, May 28, 2020 at 5:14 PM Marc Munro <[hidden email]> wrote:
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.

Yes please!  Always happy to merge a PR.

-Michel


__
Marc


Reply | Threaded
Open this post in threaded view
|

Re: GPG signing

Bruce Momjian
In reply to this post by Tim Cross
On Wed, May 27, 2020 at 10:14:46AM +1000, Tim Cross wrote:

>
> Marc Munro <[hidden email]> 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  <[hidden email]>        https://momjian.us
  EnterpriseDB                             https://enterprisedb.com

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