pg_vault_tables¶

Tables that refuse to be changed.

PostgreSQL permissions are granted to roles. Anyone with enough privilege can grant themselves more, and a superuser bypasses them altogether. That is usually exactly what you want — but it means you cannot honestly tell an auditor that a table's contents cannot be quietly altered. Only that nobody has been given the ability today.

pg_vault_tables moves the decision off the role and onto the table itself.

How it works¶

When you create the table, you say what may ever be done to it:

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

That table now accepts inserts and refuses everything else — updates, deletes, truncates, drops, and any attempt to change its definition. Not just for ordinary users. For everyone, including the owner and including a superuser, for as long as the table exists.

The guarantee¶

No role, including superuser, can perform an operation a vault table's permissions set does not grant. No GRANT, SET ROLE, ALTER TABLE, or access-method change widens it. Enforcement fails closed: where the extension's state cannot be established, every operation is denied.

Being straight with you about the boundary: this is enforcement inside the database. It is not protection against somebody with operating-system access to the data files — but neither is any other database control, including foreign keys, row level security, or encryption at rest. The usual assumptions apply.

What you get¶

Six permissions you choose from at creation: insert, insertonce, update, delete, truncate, drop.
Retention, so rows cannot be deleted until a set number of days has passed.
A purge routine that removes expired rows and cannot touch anything else.
A record of every refusal, written to the PostgreSQL log so somebody other than the person who tried it can see it.
Ordinary tables in every other way — indexes, foreign keys, triggers, VACUUM, backups and replication all work normally.

pg_vault_tables does not encrypt anything and does not hide anything. It controls what may be done to a table, not who may read it. Ordinary SELECT permissions still apply as they always did.

Tested where you actually run it¶

The complete suite — regression, isolation and TAP — runs on PostgreSQL 16, 17 and 18, across Rocky Linux 8 and 9, Debian 12, Ubuntu 22.04, 24.04 and 26.04 LTS, and Fedora 44.

One set of sources covers all three PostgreSQL versions with no version-specific code. Full detail, including what "tested" means here, is in Tested Platforms.

A first look¶

-- A ledger that can be added to, and whose rows must be kept for seven years
CREATE TABLE ledger (
    id      bigserial PRIMARY KEY,
    account text NOT NULL,
    amount  numeric(12,2)
)
USING vault
WITH (permissions = 'insert', retention = 2555);

INSERT INTO ledger (account, amount) VALUES ('4501', 120.00);   -- fine

UPDATE ledger SET amount = 0;                                   -- refused
DELETE FROM ledger;                                             -- refused
DROP TABLE ledger;                                              -- refused

Nothing else to configure. The table enforces itself from the moment it exists.

Where to start¶

New to pg_vault_tables?

Start with the User Guide. It assumes you know how to create a table in PostgreSQL and nothing else.
Installing or running it?

The DBA Guide covers installation, backups, replication, purging, monitoring and — important — what uninstalling actually involves.
Want the internals?

The Technical Reference documents the three enforcement layers, how the options are stored, and the security model.
Hit an unfamiliar word?

The Glossary explains the terms used throughout.

Before you install¶

Two things matter more than anything else on this site, so they are said here as well:

The extension must be listed in shared_preload_libraries on every server, including every standby. It refuses to load any other way.
A vault table that does not grant drop can never be removed. Not by its owner, not by a superuser, not by dropping the schema. Read Uninstalling before you install, not after.