Most database controls are about who can do something. Vault tables are about what can be done at all.
A vault table declares, when you create it, exactly which operations will ever be permitted against it. Anything not on that list is rejected — for every session, for every role, including superusers. The declaration is fixed at creation and cannot be altered afterwards. There is no override, no maintenance mode, and no flag that turns it off.
CREATE TABLE audit_log (
action_ts timestamptz,
action_type text,
impacted_data jsonb)
USING vault WITH (permissions = 'insert,addcolumn', retention = 2555);That table can be inserted into, and can gain new columns later. It cannot be updated, deleted from, truncated, renamed or dropped — by anyone — and its rows must be kept for seven years. Its still a normal table that does what normal tables can do, it just has particular permissions blocked that cannot be overridden.
The usual answer is that permissions are not enough. `GRANT` and `REVOKE` describe who may act, and anyone who can grant can also grant to themselves. A superuser can do anything, and in most organisations more people hold superuser than the compliance team would like to admit.
Vault tables are difficult to thwart, therefore it generates real barriers for bad insiders or unauthorised outsiders who wish to cause damage. In a less serious scenario, they excel at rejecting accidental deletion or update. Many will argue that this can all be achieved with proper implementation of standard security controls, in that case do not use Vault tables, that is perfectly acceptable. But these accidental and real issues do occur, we have seen it for ourselves in production systems across different clients.
Vault tables suit a narrow set of problems:
If your concern is accidental damage that is simple to restore, ordinary permissions and good backups are simpler and better. Vault tables are for when the impact of that accidental damage is too severe or the threat model includes people who hold the keys.
Vault tables take protection seriously and as a result, careful experimentation and planning is advised.
Thirteen permission tokens, comma-separated, plus a `retention` setting. Seven of these affect the data itself:
retention is the odd one out in form: it takes a number and is written as its own option, not as a word inside the permissions list. More on it below.
Seven more govern the table's definition, and grant nothing about the data:
rls and policy are separate on purpose: a table can carry policies with row level security switched off, and the two are different decisions.
A few rules are enforced when you create the table:
insert or insertonce — but never both. A table that can never hold data is not a useful thing to create by accident.truncate and retention cannot be combined. TRUNCATE empties a table in one statement, so a retention promise alongside it would mean nothing. However, delete and retention can co-exist as they may serve different purposes. When delete and retention are allowed, the retention is really a fallback, if the record has not been deleted by the purge date, then it is to be removed. Some things are never grantable at all: dropping a column, changing a column's type, renaming the table, moving it to another schema, or switching it back to an ordinary table.
Existing tables can be converted to vault tables, but vault tables cannot be converted to an existing table - another important protection.
retention = N keeps rows for N days. A companion routine removes rows once they are past their date; anything still inside its window cannot be deleted, and attempting to delete the record without the delete permission is a logged error rather than a silent skip.
delete and retention are independent. Grant both and delete wins — retention then only governs the scheduled purge, not what a person can delete. If rows must genuinely survive the period, grant retention and do not grant delete.
This is the part worth reading twice.
The declaration is permanent. You cannot add a permission later, change the retention period, or add retention to a table that did not start with it. Getting it wrong means creating a replacement table.
If you do not grant drop, the table is permanent too. Not removable by its owner, not by a superuser, not by dropping the schema around it. That is the entire point, and it is also the mistake people make. If your organisation may one day need to decommission the table, grant drop at creation — the data stays just as protected against editing. We recommend that initially you would create the vault table in Production with 'drop' enabled, and then after a period of time, for example at least 3 months when you are more comfortable with the use of vault tables, copy the data out of the vault table, drop it, and re-create it without drop permission and copy the data back in.
A retention table gains an extra column to hold each row's expiry date. Worth knowing before you write SELECT * into an application.
The extension must be loaded at server start, on the primary and every standby. A server that cannot enforce the rules will not let you read the tables at all — they freeze rather than quietly becoming ordinary tables. Nothing is lost, but it belongs on your failover and upgrade checklists.
There is a documented path off vault tables: copy the data into ordinary tables, move everything to a fresh database without the extension, and retire the old one. Further exit strategies are included in the documentation.
It is deliberately not a quick undo. A control you can reverse in an afternoon is not much of a control. It is this difficulty that makes vault tables valuable.
An audit table, with the variable part in JSON.
A vault table's structure is fixed, and the systems it records are not. Columns get added, renamed and retired upstream, and if your audit table mirrors those columns you will be stuck within a year — you cannot retype or drop a column, and a replacement table means a broken trail.
The way around it is to keep only the permanent facts as columns, and put everything that changes into a JSON payload:
CREATE TABLE audit_log (
action_ts timestamptz,
action_type text,
impacted_data jsonb,
created_by text)
USING vault WITH (permissions = 'insert,addcolumn', retention = 2555);
action_ts, action_type and created_by are true forever — something happened, at a time, of a kind. impacted_data absorbs whatever the source looked like on the day, including columns that no longer exist. addcolumn is there for the rare genuinely new top-level fact, and the seven-year retention will ensure the data is retained for the minimum statutory period in backups and in the data itself.
A rules table that supersedes rather than edits.
Reference data — pricing, thresholds, eligibility rules — is usually updated in place, which quietly destroys the answer to "what were the rules when this decision was made?". A vault table forces the better pattern, because updating is simply not available:
CREATE TABLE pricing_rules (
rule_key text,
starts_at timestamptz,
rate numeric,
created_by text)
USING vault WITH (permissions = 'insert,addcolumn');
Rules are never changed. To alter one, you insert a new row for the same rule_key with a later starts_at, and the newer record supersedes the older by date:
SELECT rate
FROM pricing_rules
WHERE rule_key = 'standard_freight'
AND starts_at <= now() /* omits future rules that don't yet apply */
ORDER BY starts_at DESC
LIMIT 1;
Every rule that ever applied stays readable, along with when it took effect and who put it there. addcolumn lets the rule set grow new attributes over time without disturbing the history. No retention here — these records are meant to be kept indefinitely, and no retention means nothing ever purges them. When you have no retention, you need to consider record growth over a 20 year life. You also have the alternative of creating a new vault table (e.g. rate_2030) and point the code to the new table, the prior vault exists forever but now your application code is interacting with a similar object but with less data.
A fact established once that everything downstream depends on.
Some records are not data so much as the ground the rest of the system stands on. Change one quietly and nothing errors — the numbers simply come out different, everywhere, and no one knows why.
Clinical trial randomisation is a strong example. A statistician generates the treatment allocation schedule before the trial opens: which participant number receives which medicine. Every efficacy result, every safety signal and every regulatory submission is computed against that allocation. If a single row were altered afterwards, nothing would break and nothing would complain. The analysis would just be wrong, the trial would be unblinded without anyone realising, and the error would surface — if at all — years later in a regulator's audit.
CREATE TABLE trial_allocation (
allocation_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
trial_no integer NOT NULL,
participant_no integer NOT NULL,
medicine text,
allocated_at timestamptz,
record_active boolean DEFAULT true)
USING vault
WITH (permissions = 'insertonce,update(record_active),rls,policy,log,tablespace');
insertonce is the strength here. The schedule is loaded in a single statement when the trial opens, and the table seals itself the moment those rows land. The exception wired in is the record_active column which can be changed from true to false and it has no bearing on the trial allocation, just a SQL optimisation used by the application. There is no second load, no correction of relevant columns, and no "just fixing one row" — not for the sponsor, not for the DBA, not for anyone with superuser.
The permissions granted alongside it are all definitional, and each earns its place:
update(record_active), because this table could grow large over time and the application has SQL that restricts the rows by the record_ active column, therefore it is allowed to be updated from true to false, and this has no bearing on the secure status of the remaining important columns which cannot be updated.rls and policy, because blinding is enforced with row level security. The trial team must not see allocations during the study, and at the end — or on a safety event requiring emergency unblinding — those policies have to change. Withholding them would make the table unusable for the one control that matters most.log and tablespace, so a DBA retains normal latitude over where the files live and how they are written. Neither touches a row.trigger is deliberately withheld. A trigger is code that runs whenever the table is touched, and the ability to switch one off is the ability to change what happens around the data. On a table whose entire value is that it never changes, triggers stay exactly as they were at creation.
There is no retention here either. The allocation must outlive the trial, the submission and the retention period of almost everything else.
The trade-off is real and worth stating: insertonce means one loading event. If the trial later adds a cohort, that cohort needs its own table, because this one will never accept another row. That is the cost of the guarantee, and for a randomisation schedule it provides the necessary controls. It is likely that this may be problematic with multi-write masters, but the workarounds are pretty simple.
As you can imagine, a solution that nullifies superuser actions requires substantial testing. Including full regression testing where we know we have touched PostgreSQL. A full security review has been undertaken and extensive testing conducted on PostgreSQL 16+ (not compatible with PostgreSQL 15 and below) and tested on Rocky 8/9, Debian 12, Fedora 44 and Ubuntu 24.04 LTS and 26.04 LTS. We will be catering for Rocky 10 later in the year as well as future versions of Debian & Fedora when released. PostgreSQL 19 will also be tested upon its release.
Documentation: https://pg-vault-tables.pebbleit.com.au/latest/
Gitlab: https://www.gitlab.com/pebble-it/postgresql-vault-tables
Github: https://www.github.com/PebbleIT-Australia/postgresql-vault-tables
Vault tables are worth it when the protection of data is required to be a matter of capability as opposed to policy. Decide the permissions carefully, because you get one chance. As we stated above, the use cases are narrow, however they may be extremely valuable and in this case, Vault tables could be the answer you have been searching for.
We would love to hear from you if you used Vault tables, even if in a throwaway database as an experiment. We have been working on this a while as part of a larger product we are experimenting with -- a flashback product that allows you to look at your data at any point in time like what you can do with Apple's Time Machine. That is nowhere near ready, maybe a 2027 product, maybe never -- it's growing a lot of complexity and might be a bridge too far! But Vault tables was something we originally desired as a guarantee of immutable data. We did research and found that there are genuine uses for these capabilities outside of our own requirements. It's not that we are against superusers and the people that care for databases (including us!), but to have an absolute control to fall back on that cannot be easily overridden is getting increasingly important as trust is harder to guarantee. What your use case is would be awesome to know and what features you used and didn't would be great too. We have commented the C code extensively so it should be easy to fork and customise for your own needs -- but we plan to maintain this a long time - and would be interested in what you believe it should do. postgresql@pebbleit.com.au
