What you cannot change¶

This page exists because the answer surprises people, and because none of it can be worked around after the fact.

The short version¶

Everything about a vault table is fixed when you create it.

In detail¶

You cannot change the permissions. There is no ALTER that adds one, removes one, or widens the set. A table created with 'insert' is append-only forever.

Because of that, one mistake is caught for you at creation: a permissions list granting neither insert nor insertonce is refused outright, since it would describe a table that could never hold a row and could never be corrected. See Choosing Permissions.

You cannot alter the table at all. No adding or dropping columns, no renaming the table or a column, no moving it to another schema, no changing its access method back to heap.

ALTER TABLE audit_log ADD COLUMN note text;

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

You cannot add retention later, or change the number of days, or remove it.

You cannot drop a table that does not grant drop. Not as its owner, not as a superuser, not with CASCADE, and not by dropping the schema around it. The schema drop is refused too.

You cannot partition a vault table, or make a vault table into a partition. Both are refused at creation.

You cannot make a vault table inherit from another table. CREATE TABLE ... INHERITS (...) with the vault access method is refused, and so is converting a table that already inherits. ALTER TABLE on a parent reaches into its children directly, so an inherited vault table could have a column dropped or retyped by naming the parent instead. A vault table may still be a parent; it is being a child that is refused.

What you can change afterwards falls into two groups.

Ordinary tuning needs no permission and is always available: indexes, column storage and compression, planner statistics, clustering, replica identity, comments and vacuuming all behave as they would on any table.

Beyond that, only what you granted at creation. The seven definition-level permissions — trigger, rls, policy, log, tablespace, addcolumn, coldefault — each unlock one ALTER TABLE form. Grant all seven and DROP COLUMN, ALTER COLUMN TYPE, RENAME, SET SCHEMA and SET ACCESS METHOD are still refused. See Choosing Permissions.

One quirk worth knowing: table-level storage parameters cannot be changed afterwards — ALTER TABLE ... SET (fillfactor = 70) is refused even though the column-level equivalents are not. Set them in the CREATE TABLE statement, where they work normally.

One documented exception: bulk tablespace moves¶

The tablespace permission gates ALTER TABLE <table> SET TABLESPACE. It does not gate the bulk form:

ALTER TABLE ALL IN TABLESPACE old_space SET TABLESPACE new_space;

That statement moves every table the caller owns in the named tablespace, a vault table included, whether or not the table grants tablespace. This is deliberate and is not treated as a gap.

The reason is what the statement can and cannot do. Moving a tablespace relocates the files on disk and nothing else: not one row is added, changed, removed or exposed, the table's declared permissions are untouched, retention deadlines are carried across byte for byte, and enforcement is live throughout and afterwards. It is also not available to a passer-by — PostgreSQL requires the caller to be a superuser or to own every table the statement matches.

Gating it would mean this extension second-guessing a whole-tablespace maintenance operation on the strength of a permission about physical placement. A DBA decommissioning storage should not find one table in ten thousand pinned in place by a declaration that was never about storage location in the first place.

What this means in practice: treat the tablespace permission as governing whether a vault table may be singled out and moved, not as a guarantee about which filesystem the rows sit on. If where the data physically lives is part of your control requirement, that belongs in tablespace ownership and filesystem permissions, which is where PostgreSQL puts it.

So how do you fix a mistake?¶

Create a new table with the settings you meant, copy the data across, and use the old one no longer:

CREATE TABLE audit_log_v2 (...) USING vault WITH (permissions = 'insert');

INSERT INTO audit_log_v2 SELECT ... FROM audit_log;

For a table with no retention, that is the whole story, and a single CREATE TABLE ... AS SELECT will do it:

CREATE TABLE audit_log_v2 USING vault WITH (permissions = 'insert')
  AS SELECT * FROM audit_log;

If the table sets retention, stop and read the box below before you copy anything.

Copying a retained table: preserve the deadlines explicitly

_$purge_ts is ordinary column data, and the insert path recomputes it unless told not to. Copy a retained table without restore_mode and every row silently gets a fresh deadline, measured from the moment of the copy.

That is not merely untidy. Migrating a table from retention = 2555 to retention = 365 with a naive copy took a deadline of 2033 down to 2027 in testing — six years of retention destroyed, and those rows become purgeable six years early. Copying into a table with the same period pushes every deadline out instead, which is safe from early deletion but records a date that is simply wrong.

Set pg_vault_tables.restore_mode for the copy, and name the column:

CREATE TABLE ledger_v2 (id int, amount numeric) USING vault
  WITH (permissions = 'insert', retention = 2555);

SET pg_vault_tables.restore_mode = on;          -- superuser only
INSERT INTO ledger_v2 (id, amount, _$purge_ts)
  SELECT id, amount, _$purge_ts FROM ledger_v1;
SET pg_vault_tables.restore_mode = off;

Then check, before you rely on it:

SELECT count(*) FROM ledger_v1 a JOIN ledger_v2 b USING (id)
 WHERE a._$purge_ts <> b._$purge_ts;             -- must be 0

CREATE TABLE ... AS SELECT cannot be used for a retained table at all. It refuses retention, because the deadline column cannot be added to a table whose columns come from a query. Use the two-statement form above.

If the original grants drop, you can then remove it. If it does not, it stays where it is — taking up space but harming nothing. You may want to rename it out of the way, except of course you cannot, because renaming is an ALTER TABLE.

This is why Before You Start suggests trying it in a scratch database first.

Note that this is about replacing one vault table with another. Turning an existing ordinary table into a vault table is a different and better-behaved operation — it happens in place and keeps everything that depends on the table. Your DBA will find it under Converting Existing Tables.

Why it works this way¶

A control you can switch off is not really a control.

If permissions could be widened by an ALTER, then anyone who could run that ALTER could do anything to the table, and the guarantee would be worth nothing. The whole value of the extension is that the answer to "could somebody have changed this?" is no, without needing to audit who held which role at the time.

The cost of that is the inflexibility on this page. It is a deliberate trade, not an oversight.

The one exception you might notice¶

Two very specific ALTER TABLE forms are accepted: changing the table's owner, and adding a constraint. They exist because pg_dump writes both into every backup, and without them a backup could not be restored at all.

Neither reaches your data. An owner still cannot insert, update, delete, truncate or drop beyond what the permissions allow, and a constraint can only make the rules for storing a row stricter, never looser.