Marc Munro <marc@xxxxxxxxxxxx> 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
