Skip to content

What is pg_vault_tables?

It is a PostgreSQL extension that lets a table declare, once and for all, which operations are allowed against it.

The ordinary way

Normally you control access with GRANT and REVOKE:

GRANT SELECT, INSERT ON audit_log TO app_user;
REVOKE UPDATE, DELETE ON audit_log FROM app_user;

That works, but it has two gaps.

The first is that permissions belong to roles, and roles change. Somebody grants a bit more access to solve a problem on a Friday afternoon, and the table is no longer protected.

The second is that a superuser ignores all of it. Anyone connecting as postgres can update or delete whatever they like.

What this extension does instead

You attach the rules to the table, when you create it:

CREATE TABLE audit_log (id bigint, event text)
  USING vault
  WITH (permissions = 'insert');

From that moment, the table accepts inserts and refuses everything else. There is no GRANT that changes it, and no role that is exempt — not even a superuser.

What it does not do is get in a DBA's way. Indexes, vacuuming, statistics, replication, backups and every data type work exactly as they do on any other table. The extension protects the rows; it does not take over the table. What Still Works Normally sets that out in full.

What that is useful for

  • Audit and compliance tables that must be provably append-only.
  • Financial ledgers where a correction should be a new row, never an edit.
  • Records with a legal retention period, where deleting something early is the thing you are guarding against.
  • Anything you will one day be asked to prove was not tampered with.

What it is not

  • It is not encryption. The data is stored normally and is readable by anyone with SELECT permission.
  • It is not access control for reading. Ordinary PostgreSQL permissions still decide who can see the table.
  • It is not a backup. It stops rows being changed; it does not protect you from losing the disk.

What it does not change

A vault table is an ordinary PostgreSQL table in every respect this extension does not deliberately alter. Primary keys, foreign keys, indexes, triggers, views, VACUUM, pg_dump and replication all behave exactly as they always do.

That is not a happy accident. The vault access method is a thin wrapper over PostgreSQL's own heap: it replaces the handful of operations that modify a row and delegates everything else to heap's code unchanged. There is no separate storage engine to behave differently.

See What Still Works Normally for the complete list, and the three things a DBA does need to know.