---
title: "ADR-051: PostgreSQL audit-chain lock"
manual: "Secrets Vault"
version: "1.4"
permalink: "https://docs.typo3.org/permalink/netresearch/nr-vault:adr-051-postgresql-audit-lock@1.4"
source: "Developer/Adr/ADR-051-PostgreSqlAuditLock.rst"
rendered: "2026-11-10T17:46:36+00:00"
---

# ADR-051: PostgreSQL audit-chain lock {#adr-051-postgresql-audit-lock}

## Status {#status}

Accepted. Supersedes the platform restriction in
[ADR-050: Local mutation transactions](https://docs.typo3.org/permalink/netresearch/nr-vault:adr-050-local-mutation-transactions@1.4).

## Date {#date}

2026-11-05

## Context {#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 `AuditChainLockTrait` and sent \``GET_LOCK("nr_vault_audit",
5)``. PostgreSQL has no ``GET_LOCK()`` and reads the double-quoted name as a
column, so every audited operation failed with SQLSTATE 42703: reads, HTTP
calls through the vault client, mutations and scheduler tasks
(`issue #494 <[https://github.com/netresearch/t3x-nr-vault/issues/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 {#decision}

Support PostgreSQL with a session-level advisory lock on one fixed bigint key.
`pg_try_advisory_lock()` is polled every 50 ms for up to 5 seconds, the
bound `GET_LOCK` uses. `pg_advisory_unlock()` releases it at the point
where the MySQL branch calls `RELEASE_LOCK()`.

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 (`VaultMutationTransaction`)
    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](https://github.com/netresearch/t3x-nr-vault/issues/496)).

The non-blocking function is chosen over `pg_advisory_lock()` with a
`lock_timeout`. A lock wait that ends in an error aborts the whole
PostgreSQL transaction, including a caller's outer transaction during
master-key rotation. `pg_try_advisory_lock()` answers false and leaves the
transaction usable.

The transaction-level `pg_try_advisory_xact_lock()` 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](https://github.com/netresearch/t3x-nr-vault/issues/496)).

PostgreSQL sequences do not roll back, like MySQL `AUTO_INCREMENT`. 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_before` and `hash_after` were `char(64)`.
    PostgreSQL returns `char` values padded with spaces, which broke the
    cryptographic-field check and hash verification. They are `varchar(64)`.
-   `AuditLogService::query()` ordered by `crdate` alone. Entries from the
    same second came back in arbitrary order; `uid` now breaks the tie.

## Consequences {#consequences}

`VaultMutationTransaction` 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_nrvault_audit_log.uid`. 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 {#verification}

Unit tests pin the lock protocol per platform. The functional suite runs
against PostgreSQL with \``Build/Scripts/runTests.sh -s functional -d
postgres`\`.
