A complete example¶

A worked example of the sort of thing this extension is for: a financial audit trail whose figures can never change, whose notes can be annotated, and which is kept for seven years.

The requirement¶

Every approval is recorded.
Nobody can change the financial facts of a record — who approved it, for how much, against which invoice, and when — including administrators.
A reviewer may annotate a record afterwards, because a note is commentary rather than a fact.
Records must be kept for seven years, then removed.
After seven years they should be cleared automatically.

The table¶

CREATE SCHEMA audit;

CREATE TABLE audit.approvals (
    id          bigserial PRIMARY KEY,
    approved_at timestamptz NOT NULL DEFAULT now(),
    approver    text        NOT NULL,
    invoice_id  bigint      NOT NULL,
    amount      numeric(12,2) NOT NULL,
    notes       text
)
USING vault
WITH (permissions = 'insert,update(notes)', retention = 2555);

insert, and an update narrowed to one column — no delete, no truncate, no drop. retention = 2555 is seven years.

update(notes) is the part worth pausing on. A bare update would permit changing every column, including amount. Naming the column instead permits exactly that one and refuses all the others, for every role including superuser. That matches the requirement precisely: the facts are fixed at the moment of insertion, the commentary is not.

The list works as an allowlist, so it needs no maintenance as the table grows: a column added later is not in it, and is therefore immutable by default rather than accidentally editable.

Note that delete is not granted. Rows still get removed after seven years, because retention permits that by itself. Granting delete as well would allow any row to be removed at any time, which would leave the retention period protecting nothing.

Add the indexes you need¶

Ordinary DDL, no special handling:

CREATE INDEX approvals_invoice_idx  ON audit.approvals (invoice_id);
CREATE INDEX approvals_approver_idx ON audit.approvals (approver, approved_at);

Use it¶

INSERT INTO audit.approvals (approver, invoice_id, amount, notes)
VALUES ('alice', 4501, 12500.00, 'Q3 capital works');

SELECT id, approved_at, approver, amount, _$purge_ts
  FROM audit.approvals;

 id |         approved_at         | approver |  amount  |         _$purge_ts
----+-----------------------------+----------+----------+----------------------------
  1 | 2026-08-17 09:14:22.10+10   | alice    | 12500.00 | 2033-08-16 09:14:22.10+10

Confirm it is actually protected¶

Worth doing once, so you have seen it with your own eyes:

A column outside the update grant cannot be changed:

UPDATE audit.approvals SET amount = 0 WHERE id = 1;

ERROR:  column "amount" is not updatable on vault table "approvals"
DETAIL:  The table permits updating only: notes.
HINT:  A vault table's updatable columns are fixed at creation and cannot be altered.

The column that is named works normally, which is the point of naming it:

UPDATE audit.approvals SET notes = notes || ' — reviewed by bob, 2026-08-21'
 WHERE id = 1;

UPDATE 1

 id | approver |  amount  |                     notes
----+----------+----------+------------------------------------------------
  1 | alice    | 12500.00 | Q3 capital works — reviewed by bob, 2026-08-21

Being updatable does not make it ordinary, though. The column still cannot be renamed:

ALTER TABLE audit.approvals RENAME COLUMN notes TO commentary;

ERROR:  ALTER TABLE ... RENAME is not permitted on vault table "approvals"
DETAIL:  A vault table's definition is fixed at creation. No permission grants it, including to its owner or a superuser.

nor dropped:

ALTER TABLE audit.approvals DROP COLUMN notes;

ERROR:  ALTER TABLE is not permitted on vault table "approvals"

That distinction is deliberate. Permitting the value to change says nothing about the column itself: renaming it would leave every reader looking at a different column than they meant, and dropping it would take seven years of commentary with it.

DELETE FROM audit.approvals WHERE id = 1;

ERROR:  row in vault table "approvals" is still within its retention period

TRUNCATE audit.approvals;

ERROR:  truncate is not permitted on vault table "approvals"

DROP TABLE audit.approvals;

ERROR:  drop is not permitted on vault table "approvals"

Try the same as postgres. The answers do not change.

Correcting a mistake¶

notes can be annotated, but the amount cannot be corrected in place — so a correction is a new row:

INSERT INTO audit.approvals (approver, invoice_id, amount, notes)
VALUES ('alice', 4501, -12500.00, 'Reversal of approval id 1 — wrong invoice');

That is normal practice for a ledger, and it is what makes the trail worth having: the original entry and its correction are both visible.

Clear out expired records¶

Once rows pass seven years:

CALL pgvault_tables.purge_vault('audit', 'approvals');

Ask your DBA to run this nightly — see Scheduling the Purge.

Check on it later¶

SELECT * FROM pgvault_tables.view_vault_tables()
 WHERE schema_name = 'audit';

 schema_name | table_name |  permissions  | update_columns | retention_days
-------------+------------+---------------+----------------+----------------
 audit       | approvals  | insert,update | notes          |           2555

permissions reports the capability set and update_columns the narrowing, so a table granting update for everything and one granting it for a single column are told apart by the second column rather than by reading a longer string in the first.