With thersakeys created in my previousblog entry,we can now properly sign rows to provide integrityandnon-repudiation, which we did not have before. To show this, let's create a modified version of the previous schema by renaming the last column tosignature:
CREATE TABLE secure_demo2 (
id SERIAL, car_type TEXT, license TEXT, activity TEXT,
event_timestamp TIMESTAMP WITH TIME ZONE, username NAME, signature BYTEA);
Because signature verification is done using the public certificate, anyone can verify that the data has not been modified. It also allows non-authors to verify that the data was created by the owner of the private certificate.
This and the previous two blog entries are related. Thefirst oneexplained how to create and store a simplemessage authentication code(mac). Thesecond oneexplained how to encrypt data on the client side using symmetric and public key cryptography. This blog entry shows how to do message authentication via public key signing, so anyone with access to the public key can verify authorship.