When storing data in the database, there is an assumption that you have to trust the database administrator to not modify data in the database. While this is generally true, it is possible to detect changes (but not removal) of database rows.
To illustrate this, let's first create a table:
CREATE TABLE secure_demo (
id SERIAL, car_type TEXT, license TEXT, activity TEXT,
event_timestamp TIMESTAMP WITH TIME ZONE, username NAME, hmac BYTEA);
The last column (hmac) is used for change detection. Let's insert a row into the table:
INSERT INTO secure_demo
VALUES (DEFAULT, 'Mazda Miata', 'AWR-331', 'flat tire',
CURRENT_TIMESTAMP, 'user1', NULL) RETURNING *;
id | car_type | license | activity | event_timestamp | username | hmac
----+-------------+---------+-----------+------------------------------+----------+------
1 | Mazda Miata | AWR-331 | flat tire | 2017-07-06 20:15:59.16807-04 | user1 |
Notice that this query also returns a text representation of the inserted row, including the computed columns id and event_timestamp.
To detect row changes, it is necessary to generate a message authentication code (mac) which is generated with a secret known only to the client. It is necessary to generate the mac on the client so the secret is never transferred to the server. These psql queries update the inserted row to store the mac:
SELECT ROW(id, car_type, license, activity, event_timestamp, username)
FROM secure_demo
WHERE id = 1;
-- set psql variables to match output columns
\gset
\set hmac `echo :'row' | openssl dgst -sha256 -binary -hmac 'MY-SECRET' | xxd -p | tr -d '\n'`
UPDATE secure_demo SET hmac = decode(:'hmac', 'hex') WHERE id = 1;
SELECT * FROM secure_demo;
id | car_type | license | activity | event_timestamp | username | hmac
----+-------------+---------+-----------+------------------------------+----------+-------------
1 | Mazda Miata | AWR-331 | flat tire | 2017-07-06 20:15:59.16807-04 | user1 | \x9549f1...
To later verify that the data row has not been modified, do:
SELECT ROW(id, car_type, license, activity, event_timestamp, username), hmac
FROM secure_demo
WHERE id = 1;
row | hmac
----------------------------------------------------------------------------+-------------
(1,"Mazda Miata",AWR-331,"flat tire","2017-07-06 20:15:59.16807-04",user1) | \x9549f1...
\gset
\echo ' E''\\\\x'`echo :'row' | openssl dgst -sha256 -binary -hmac 'MY-SECRET' | xxd -p | tr -d '\n'`''''
E'\\x9549f10d54c6a368499bf98eaca716128c732132680424f74cb00e1d5a175b63'
\echo :'hmac'
E'\\x9549f10d54c6a368499bf98eaca716128c732132680424f74cb00e1d5a175b63'
The database administrator could replace or remove the hmac value, but this would be detected. This is because computing a proper hmac requires the MY-SECRET key, which is never sent to the server.
The above solution only allows someone with access to the secret key to determine if the row has been modified, meaning only they can check the message's integrity. A more sophisticated solution would be to use a private key to sign a hash of the row value — this would allow anyone with access to the public key to check that the row has not been modified, but still only allow those with access to the private key to generate a new hmac. This would also allow for non-repudiation.
There is the risk that the row values returned by the insert do not match those that were supplied, so some client-side checks would need to be added. There is also no detection for removed rows; this is similar to the problem of trying to detect a blocked tls connection attempt.
Such a setup is clearly overkill for many databases, but there are some use-cases where data integrity guaranteed by the client, independent of the database administrator, is useful. Non-repudiation using public key infrastructure is also sometimes useful.
(Article originally published in Bruce's personal blog - August 31, 2018, @ https://momjian.us/main/blogs/pgblog/2018.html#August_31_2018)