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.

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.

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;

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.

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.