How to properly sign rows to provide integrity in PostgreSQL

With the rsa keys created in my previous blog entry,, we can now properly sign rows to provide integrity and non-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 to signature:

CREATE TABLE secure_demo2 (
        id SERIAL, car_type TEXT, license TEXT, activity TEXT, 
        event_timestamp TIMESTAMP WITH TIME ZONE, username NAME, signature BYTEA);

Now, let's do the insert as before:

INSERT INTO secure_demo2
VALUES (DEFAULT, 'Mazda Miata', 'AWR-331', 'flat tire',
CURRENT_TIMESTAMP, 'user1', NULL) RETURNING *;
 id |  car_type   | license | activity  |        event_timestamp        | username | signature
----+-------------+---------+-----------+-------------------------------+----------+-----------
  1 | Mazda Miata | AWR-331 | flat tire | 2017-07-08 10:20:30.842572-04 | user1    | (null)

Now, we sign it using our private rsa key, rather than creating a random key for this:

SELECT ROW(id, car_type, license, activity, event_timestamp, username)
FROM secure_demo2
WHERE id = 1
 
-- set psql variables to match output columns
\gset
 
\set signature `echo :'row' | openssl pkeyutl -sign -inkey ~user1/.pgkey/rsa.key | xxd -p | tr -d '\n'`
 
UPDATE secure_demo2 SET signature = decode(:'signature', 'hex') WHERE id = 1;
 
SELECT * FROM secure_demo2;
 id |  car_type   | license | activity  |        event_timestamp        | username | signature
----+-------------+---------+-----------+-------------------------------+----------+-------------
  1 | Mazda Miata | AWR-331 | flat tire | 2017-07-08 10:20:30.842572-04 | user1    | \x857310...

To later verify that the data row has not been modified, we can do:

SELECT ROW(id, car_type, license, activity, event_timestamp, username), signature
FROM secure_demo2
WHERE id = 1;
                                     row                                     | signature
-----------------------------------------------------------------------------+-------------
 (1,"Mazda Miata",AWR-331,"flat tire","2017-07-08 10:20:30.842572-04",user1) | \x857310...
\gset
 
\echo `echo :'signature' | xxd -p -revert > sig.tmp`
\echo `echo :'row' | openssl pkeyutl -verify -pubin -inkey /u/postgres/keys/user1.pub -sigfile sig.tmp`
Signature Verified Successfully
 
\! rm sig.tmp

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. The first one explained how to create and store a simple message authentication code  (mac). The second one explained 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.

(Article originally published in Bruce's personal blog - Friday, September 7, 2018, https://momjian.us/main/blogs/pgblog/2018.html#September_7_2018)