Enforcing Append-Only Guarantees at the Database Layer
This article explores various database-level patterns to enforce append-only guarantees for audit logs, moving beyond application-level conventions. It details techniques such as using restrictive database roles, triggers, foreign key constraints, tombstones for data erasure, and write-once-read-many (WORM) storage. The core architectural insight is that true tamper-evidence requires a layered approach, including cryptographic hash chains and external trusted timestamps, to protect against even database superuser compromises.
Read original on Dev.to #architectureAchieving true append-only properties for critical data like audit logs is a fundamental requirement for compliance, security, and data integrity. Relying solely on application-level logic often leads to vulnerabilities, where bugs, operator errors, or malicious actors can inadvertently or intentionally alter historical records. This article delves into several database-centric approaches to enforce append-only behavior, highlighting their strengths and weaknesses.
Database Patterns for Append-Only Enforcement
- INSERT-only Role: Granting the application a database role with only `INSERT` and `SELECT` privileges prevents accidental `UPDATE` or `DELETE` operations from the application layer. This is a high-leverage change that stops common bugs and low-privilege compromises.
- Triggers as a Backstop: Database triggers can be set to reject `UPDATE` or `DELETE` operations on a table, acting as a defense-in-depth mechanism. While effective against most connections, superusers can disable them, making them not a primary perimeter.
- Foreign Key Pointing Backward: A foreign key constraint where each row references the hash of the previous row effectively creates a hash chain. Deleting any intermediate row breaks the chain, failing the constraint. However, the newest row (the tail) can still be deleted, and chain healing after bulk operations can be complex.
- Tombstones Instead of DELETE: For compliance with 'right to erasure' regulations (e.g., GDPR), instead of deleting data, an 'erasure event' (tombstone) is appended to the log. This preserves the original record and its verifiable history while recording the request for erasure.
- WORM at the Storage Layer: Utilizing write-once-read-many (WORM) storage, such as read-only tablespaces or object-lock buckets, or setting table partitions to read-only, provides platform-enforced immutability. This is effective for freezing historical periods but is coarse-grained and still relies on trusting the platform admin.
The Power of Layered Defense
No single pattern is foolproof against all threats, especially a compromised database superuser. A robust append-only system combines multiple layers of defense: restrictive roles, triggers, tombstones, WORM storage, and crucially, an underlying cryptographic hash chain coupled with an external trusted timestamp. This layered approach ensures tamper-evidence and verifiability by external parties.
The Ultimate Guarantee: Hash Chains and External Timestamps
The most secure append-only system includes a cryptographic hash chain, where each record incorporates a hash of its payload and the previous record's hash. This, combined with an external, trusted timestamping mechanism, creates a verifiable ledger. Even if a database superuser manages to alter a record, the change will break the cryptographic chain, making the tampering detectable. This pushes the trust boundary beyond the internal database infrastructure, allowing independent verification.
Implementing an append-only architecture is a critical security and integrity decision. It requires careful consideration of data access patterns, compliance needs, and the various threat vectors. By enforcing immutability at multiple levels, from application roles to cryptographic guarantees, systems can achieve a high degree of confidence in their audit trails and historical data.