ADR-051: PostgreSQL audit-chain lock
Status
Accepted. Supersedes the platform restriction in ADR-050: Local mutation transactions.
Date
2026-11-05
Context
TYPO3 supports MySQL, MariaDB, PostgreSQL and SQLite. nr-vault wrote audit
entries only on SQLite and MySQL/MariaDB. Every non-SQLite connection took the
MySQL branch of Audit and sent `GET_ and reads the double-issue #494 <https://github.com/netresearch/t3x-nr-vault/issues/494>`__).
ADR-050 rejected PostgreSQL on the mutation path with code 1793180003; the
plain audit path had no such check.
Decision
Support PostgreSQL with a session-level advisory lock on one fixed bigint key.
pg_ is polled every 50 ms for up to 5 seconds, the
bound GET_ uses. pg_ releases it at the point
where the MySQL branch calls RELEASE_.
The session-level lock matches the protocol the MySQL branch already follows:
- It is counted per session. Master-key rotation and a coordinated mutation acquire it again for the audit writes inside them, and the inner acquire returns at once.
- It survives a commit. A coordinated mutation (
Vault) keeps it until its own commit and releases it before publishing to sinks, as on MySQL. Master-key rotation takes it for the chain re-key and releases it after its outer commit or rollback (issue #496).Mutation Transaction
The non-blocking function is chosen over pg_ with a
lock_. A lock wait that ends in an error aborts the whole
PostgreSQL transaction, including a caller's outer transaction during
master-key rotation. pg_ answers false and leaves the
transaction usable.
The transaction-level pg_ is not used either.
Its release at commit would fit the coordinator, which already releases the
MySQL lock after the commit and before publishing. But it has to be taken
inside the transaction, so the first lock poll starts the transaction's
snapshot. Under REPEATABLE READ the writer would then read the chain tip
as it was before the lock was granted. The session lock is taken before
the writer's own BEGIN, so a standalone writer and a coordinated mutation
read the tip after the lock is granted at any isolation level. Master-key
rotation is the exception: its outer transaction is already open when the
audit writes take the lock, so under REPEATABLE READ its snapshot could
predate the lock, on MySQL with the InnoDB default as well. The rotation
therefore runs its transaction in READ COMMITTED
(issue #496).
PostgreSQL sequences do not roll back, like MySQL AUTO_. The
explicit-UID allocation from ADR-050 therefore applies to PostgreSQL too: when
a verified enabled anchor matches the current tip, the next entry takes the
following UID. An explicit UID does not advance a PostgreSQL sequence, so the
writer sets the sequence to that UID with setval under the same lock.
Without it, the next entry that takes the sequence (anchoring disabled or not
matching) would collide with an existing row.
Two more PostgreSQL defects surfaced once the lock worked, and are fixed alongside it:
value_,checksum hash_andbefore hash_wereafter char. PostgreSQL returns(64) charvalues padded with spaces, which broke the cryptographic-field check and hash verification. They arevarchar.(64) Auditordered byLog Service:: query () crdatealone. Entries from the same second came back in arbitrary order;uidnow breaks the tie.
Consequences
Vault accepts PostgreSQL. Any platform other than
SQLite, MySQL/MariaDB and PostgreSQL is still rejected with 1793180003.
setval is not transactional. When the transaction that set it rolls
back, the sequence stays one ahead of the table. The next anchored write takes
an explicit UID again, so nothing changes there. A write without a matching
anchor then leaves a one-UID gap, which is the same gap MySQL leaves after a
rollback in that mode.
setval needs the UPDATE privilege on the sequence behind
tx_. The table owner holds it. A database user that
was granted only table privileges fails the first anchored audit write, and
the mutation it belongs to is rolled back.
The advisory-lock key is shared by every database user of the same database.
Another application that picks the same bigint would serialise with the audit
writer. The key is the ASCII value of nrvaudit, which a small-integer key
cannot reach.
A session-level advisory lock needs one server connection per database
session. PgBouncer in transaction pooling mode does not provide that: its
feature table lists session-level advisory locks as not supported in that mode.
The lock would then not serialise the audit writes, and its release could miss.
TYPO3 has the same requirement independently of nr-vault, because the
database driver reads a new row's ID with a separate SELECT LASTVAL.
Run PostgreSQL behind PgBouncer in session pooling mode, or connect directly.
Verification
Unit tests pin the lock protocol per platform. The functional suite runs
against PostgreSQL with `Build/`.