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.

If you want a table that cannot have its data altered by a Superuser, a DBA, a Developer or someone who should not be in your system (insider or outsider), then Vault Tables may be right for you. If you are satisifed by using standard PostgreSQL controls that permit Superuser override, then you don't need Vault Tables.

In a nutshell:

You can restrict a table to allow an initial INSERT only, subsequent INSERTS are rejected
You can restrict a table to reject UPDATES
You can restrict a table to reject DELETES
You can restrict a table to reject TRUNCATES
You can restrict a table to reject being DROPPED
You can restrict a table to protect its TRIGGERS from being disabled. Note that this does not interfere with the DISABLE ALL TRIGGERS action.
Vault tables cannot be ALTERED in ways that change the data or its shape, apart from whichever definition-level permissions you choose to grant; tuning, indexing and comments are always available
Superusers cannot override
It is still a completely normal table that can be indexed with triggers and ALL data types
The COLUMN attributes of a table that impact data are restricted from being changed
You can restrict a table to reject additional columns
Table names, column names and assigned schemas cannot be altered
Data in vault tables can be automatically purged based on a number of retention days set as part of the table definition.

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. See the 12 permissions below that control its behaviour.

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.

What you get¶

Thirteen permissions you choose from at creation. Six govern the data — insert, insertonce, update, delete, truncate, drop — and seven govern the definition: trigger, rls, policy, log, tablespace, addcolumn, coldefault.
update can be narrowed to named columns — update(status,notes) permits changing those and refuses every other column, for every role including superuser.
Ordinary DBA work is never locked away. Indexes, column storage, planner statistics, clustering, replica identity, comments and vacuuming all work on a vault table exactly as they do on any other, with no permission required.
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.

Be Careful !¶

The use of vault tables needs careful planning ! Made a typo in the table name ? Wrong schema ? Missing a column ? There are no overrides -- this is a deliberate locked action that cannot be overridden - even uninstalling the extension will not have the desired affect. In the case of 'Missing a Column', if you defined the table with the 'addcolumn' permission you can add columns, the example before only applies when that permission was not part of the CREATE TABLE statement executed.

If you have permitted the 'drop' permission, then you can drop the table and start again (after copying the data to a intermediate temporary table). If you have not given the 'drop' permission to the vault table, there are two remedies available to you when your vault table is 'wrong' and you don't :

Create a new vault table with the correct details and copy any existing data from the old vault table to it. You cannot drop the old vault table so they will have to co-exist forever.
Create a new database and export everything but the vault table and create the vault table in the new database and copy/load the correct data.

Vault tables are serious about being protected and therefore careful consideration does need to be made in their use. We recommend that vault tables are confined to important sets of data that needs to be protected, such as immutable rules, lookups or history of events and prior data. They are ideal for storing data that if manipulated could cause serious consequences elsewhere in your system. It is unlikely you will want vault tables for your main transaction tables -- for the primary reason that there are real-world restrictions in altering the table ! For example, no new columns unless 'addcolumn' permission was granted.

Vault tables are excellent audit tables, however due to the restrictions of ALTER TABLE capability we strongly recommend that for storing audit records, the audit vault table stores a JSON object of the source table row rather than attempt to mirror its structure as DDL to the source table cannot be reflected in the vault table if changes are made.

Vault tables have side affects:

You cannot DROP CASCADE a Schema that contains a vault table (if it does not have 'drop' permission)
You cannot DROP a Role that contains a vault table (if it does not have 'drop' permission)
You cannot DISABLE a Trigger on a vault table (if it does not have 'trigger' permission)
You cannot SET TABLESPACE for a vault table (if it does not have 'tablespace' permission)
You cannot ENABLE ROW LEVEL SECURITY after a vault table has been created (if it does not have 'rls' permission)
You cannot CREATE / ALTER POLICY after a vault table has been created (if it does not have 'policy' permission)
You cannot SET LOGGED / UNLOGGED after a vault table has been created (if it does not have 'log' permission)

Whilst all typical DBA activities that do not impact the data are permitted, be aware of the implications of each permission and ensure careful planning occurs before designing and implementing vault tables.

We recommend that you script the creation of vault tables in a file and test in a throw-away database after confirming it is as intended. If you plan on not granting 'drop' permission, we also recommend that your vault table naming convention caters for later iterations like 'my_table_1a' so that a real change required in the future requires a new vault table of 'my_table_1b' and you could have a single table view over the vault tables called 'my_table' that initially resolves to my_table_1a but you later change it to my_table_1b so that application code change is minimised.

Remember, whilst they are stored as normal tables, they don't behave like normal tables and are in fact 'vault' tables ! You can't crack a vault table!

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
TRUNCATE TABLE ledger;                                          -- refused
ALTER TABLE ledger ADD COLUMN created_at timestamptz;           -- refused
ALTER TABLE ledger RENAME COLUMN account to account_number;     -- refused
DROP TABLE ledger;                                              -- refused

-- Something a bit more complex:
WITH
-- purge the incriminating account
purged AS (
    DELETE FROM ledger
     WHERE account = 'SOMETHING'
    RETURNING id, amount
),
-- halve the suspicious credit and backdate it
rewritten AS (
    UPDATE ledger
       SET amount    = amount / 2,
           account   = 'SOMETHING'
     WHERE id        = 1
    RETURNING id
),
-- roll the deleted amount into a surviving row so the totals still foot
balanced AS (
    UPDATE ledger l
       SET amount = l.amount + t.amount
      FROM another_table t
     WHERE l.account = 'ACME-OPS'
       AND l.id = 2
    RETURNING l.id
)
SELECT
    (SELECT count(*) FROM purged)    AS entries_removed,
    (SELECT count(*) FROM rewritten) AS entries_altered,
    (SELECT count(*) FROM balanced)  AS entries_rebalanced;     -- refused

Nothing else to configure. The table enforces itself from the moment it exists. The inserted row will be automatically purged 2,555 days from date of row insertion (7 years).

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 four enforcement layers, how the options are stored, and the security model.
Wondering if it will get in your way?

What Still Works Normally lists everything that behaves exactly as it always has — which is nearly all of it.
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.