Support tamper-proof audit log chain?

[ArthurClemens]

I need to support an auditing requirement to provide tamper-proof logs, that is, logs that contain integrity checks that the rows have not been altered afterwards.
Reading up on this, I’ve learned that this is done by adding a column that contains a signed hash with the contents of the previous row.

If you think this could be a valuable addition to Carbonite, I’d be happy to flesh out the idea further.

[maltoe]

Hey @ArthurClemens this was actually a requirement of ours as well when we originally built Carbonite, yet only "on export", hence we added the meta field in the outboxes table. See https://hexdocs.pm/carbonite/Carbonite.html#process/4-memo-passing for an idea how this would work. Is this enough for you or do you need tamper-proofness already on insert? That would be quite a bit harder though as you'd effectively need to serialize your writes to the transactions table.

[ArthurClemens]

Using memo passing is a pragmatic solution, and it might be sufficient if transactions are processed almost immediately after creation. When using delayed and/or batched processing, there would be a gap where a nefarious admin could modify the transaction without any trace.

Not having looked at the current code, I can’t say how complex this feature would be to implement. At a minimum it would require 2 additional columns on the transactions table: the current checksum hash and the previous hash. The previous hash is fetched from the last row. The current hash is calculated from the entire row data, including meta.

I’ve come across implementations that use block ID’s/hashes to improve lookup times when checking integrity. That would require a couple of more rows.

[maltoe]

At a minimum it would require 2 additional columns on the transactions table: the current checksum hash and the previous hash. The previous hash is fetched from the last row.

Fetching from last row would require a full table lock to serialize, which will give you a nasty bottleneck.

If I were you:

• Add last_transaction_id & last_checksum to triggers
• Add last_transaction_id & checksum to transactions (linking by last_transaction_id is better for traversal and doesn't hurt the tamper-proofness)
• Add a deferred trigger on transactions that row locks triggers and updates accordingly

But as said earlier, it still requires serialization of all statements, which would mean if you want to add it to Carbonite, it would need to be opt-in and not affect performance if not used. Happy to assist you with the implementation, if you want to move on with it.

[maltoe]

An alternative would also be to just add a simple immutability trigger (i.e. a trigger rejecting any UPDATE on transactions and changes, carbonite doesn't execute any of these)? Would eliminate the need to serialize and implementation is easier?

there would be a gap where a nefarious admin could modify the transaction without any trace

Keep in mind that none of these solutions helps you if you have admins with superuser-equivalent Postgres access. You need to lock down your roles.

[ArthurClemens]

An alternative would also be to just add a simple immutability trigger ...

That would only prevent changes to the transactions table, but would not provide proofs about the data that is exported and stored externally.

Add last_transaction_id & last_checksum to triggers

We are using a trigger for each tenant schema. How would the different "last" values be reconciled? Sort by last_transaction_id and take last?

[maltoe]

Add last_transaction_id & last_checksum to triggers
We are using a trigger for each tenant schema. How would the different "last" values be reconciled? Sort by last_transaction_id and take last?

Sorry, this was nonsense. I meant Carbonite's triggers table, but that has a record per audited table, so not a good fit for this. Nonetheless, what I meant to point out was that fetching the last checksum from the transactions table itself requires you to apply a full table lock on transactions which will be a worse bottleneck than having the value in a separate table and being able to row lock it.

[maltoe]

The immutability trigger I meant in addition to a hash chain on export

[ArthurClemens]

So basically the points presented in #151 (comment), except that the references to last_transaction_id and last_checksum are stored in a new table.
And the new functionality is opt-in.

This sounds good.

I came across the section Canonical Serialization: The Hard Part in The Architecture Behind Tamper-Proof Audit Logs that mentions that hashes need to be computed deterministically. Because the hash would be calculated on the meta column as well, all columns including meta need to get canonicalised.