Re: GPG signing - Mailing list pgsql-general

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