Choosing permissions¶

There are six. You pick the ones the table genuinely needs, and everything else is refused.

Permission	What it allows
`insert`	Adding rows, by any route — `INSERT`, `COPY`, `MERGE`
`insertonce`	One insert, ever
`update`	Changing existing rows
`delete`	Removing rows
`truncate`	Emptying the table with `TRUNCATE`
`drop`	Dropping the table

Those six govern the data. Seven more govern the table's definition — what a DBA may reconfigure about it later. None of them can add, change or remove a row, and each is refused unless you name it:

Permission	What it allows
`trigger`	Enabling and disabling triggers, including `DISABLE TRIGGER ALL`
`rls`	Enabling and disabling row level security, and `FORCE`/`NO FORCE`
`policy`	Creating, altering and dropping row-security policies
`log`	`SET LOGGED` and `SET UNLOGGED`
`tablespace`	`SET TABLESPACE` for this table by name. The bulk `ALTER TABLE ALL IN TABLESPACE` form is not gated — see What You Cannot Change
`addcolumn`	`ADD COLUMN`. Not `DROP COLUMN` — that hides data, and is never grantable
`coldefault`	`ALTER COLUMN ... SET DEFAULT` and `DROP DEFAULT`

Leave them all out and the table behaves as it always has: no ALTER TABLE of any kind, and no policy changes. Every form outside the list above stays refused whatever you grant — ADD COLUMN, SET ACCESS METHOD, RENAME, storage parameters and the rest.

Spacing and capitals do not matter. 'INSERT, Update' means the same as 'insert,update'.

Narrowing `update` to named columns¶

update on its own permits changing every column. Follow it with a list in parentheses and it permits only those, refusing every other column for every role including superuser:

CREATE TABLE ledger (
    id       bigint,
    amount   numeric,
    occurred timestamptz,
    status   text,
    notes    text
) USING vault WITH (permissions = 'insert,update(status,notes)', retention = 2555);

status and notes may be corrected. id, amount and occurred are fixed from the moment the row is written — which is the shape most ledgers and audit tables actually want, and which previously needed two tables and a join.

It is the value moving that is refused, not the column being mentioned. UPDATE ledger SET amount = amount succeeds because nothing changed; SET amount = amount + 1 does not. Setting a column to NULL, or away from NULL, is a change like any other.

Every route is covered, because the check is on the row rather than the statement. An update through a view, a MERGE, an INSERT ... ON CONFLICT DO UPDATE, a BEFORE trigger that rewrites NEW, and a logical replication apply worker all reach the same place and are all refused. There is no statement form that slips past it.

Three things to decide up front, because none can be changed afterwards:

A column added later is permanently immutable. It cannot be in a list fixed at creation, and the list can never be widened — the same permanence as retention never being addable.
Generated columns cannot be named. Their value follows the columns they are computed from, so freezing one would silently freeze everything feeding it. Naming one is refused at creation.
_$purge_ts cannot be named. The retention deadline is the extension's to set, and is never updatable by anyone.

Names follow ordinary SQL rules: update(Status) means the column status, and a column deliberately created as "Status" is named as update("Status"). A name that does not exist is refused at creation, and the error lists the columns that may be named.

What narrowing `update` costs — and what it does not¶

This costs nothing unless you use it. Every figure below applies only to a table that names columns in its update grant. A table granting plain update, and every table that existed before this feature did, performs exactly as it did before — no comparison runs, no extra work happens, and there is no measurable difference. That was a hard requirement of the design rather than a hope, and it is measured on every release rather than assumed.

Where you do narrow the grant, PostgreSQL compares the protected columns on each row being updated. Measured on a narrow five-column table:

What your application does	Table granting plain `update`	Table using `update(...)`
Reading rows	no change	no change
Updating one row by key — the shape most applications use	no change	about 2%
Inserting rows	no change	about 5%
Deleting rows	no change	about 6%
Updating every row in one statement, with `retention`	no change	about 7%
Updating every row in one statement, without `retention`	no change	about 25%

The left-hand column is the point. If you do not name columns, nothing in this feature runs and nothing changes — that is measured, not assumed, and it was a hard requirement of the design rather than a target. The percentages on the right apply only to a table whose declaration actually carries a column list.

Three things are worth knowing when you read those numbers.

The single-row figure is the one an application feels. Parsing the statement, planning it and looking up the row dominate the time, and the per-row comparison disappears into them. If your workload is ordinary application traffic, about 2% is the number that applies to you.

The bulk figure is a deliberate worst case. A narrow table, every row rewritten in one statement, nothing else running on the server. Real bulk work is usually wider rows doing more per row, and the proportion falls accordingly.

Retention makes it cheaper, not dearer. A table with retention is already reading the row being replaced in order to carry its deadline across, so the column comparison shares that read and costs very little on top.

Cost also scales with how many columns you protect: two mutable columns out of twenty sits at the higher end of that table, two out of four at the lower.

For context, and unchanged by any of this: a vault table granting plain update runs about 3% behind an ordinary PostgreSQL table on a single-row update, and about 60% behind on a bulk rewrite of every row. Reads are identical either way — this extension has nothing to do with SELECT, and never touches the read path.

Common combinations¶

Append-only — the usual choice for audit trails and event logs:

WITH (permissions = 'insert')

Append-only, but tidy-able — rows can be removed once old enough:

WITH (permissions = 'insert', retention = 2555)

Normal working table that cannot be dropped or wiped — useful for reference data:

WITH (permissions = 'insert,update,delete')

Write once and never again:

WITH (permissions = 'insertonce')

Facts fixed, commentary correctable — the shape most ledgers and audit trails actually want, and the reason update can be narrowed:

WITH (permissions = 'insert,update(status,notes)', retention = 2555)

The figures cannot be touched; status and notes can be corrected. See Narrowing update to named columns.

About `insertonce`¶

The first insert into the empty table succeeds. Every insert after that fails, for the life of the table.

CREATE TABLE opening_balance (account text, amount numeric)
  USING vault WITH (permissions = 'insertonce');

INSERT INTO opening_balance VALUES ('4501', 1000.00);   -- fine
INSERT INTO opening_balance VALUES ('4502', 2000.00);   -- refused

What uses up the one insert is rows arriving in the table, not a statement running. Everything below follows from that single rule:

The first statement can insert many rows. INSERT INTO t VALUES (1),(2),(3) is one insert, not three.
A rolled-back insert does not use up your one chance. If the transaction aborts, the table is still empty and the next attempt works.
CREATE TABLE ... AS SELECT uses it up, if the query returns any rows. The table is created and populated in one go, and that counts as the one permitted insert — so you cannot add to it afterwards:
```
CREATE TABLE opening_balance USING vault WITH (permissions = 'insertonce')
  AS SELECT account, amount FROM staging;     -- creates and fills it

INSERT INTO opening_balance VALUES ('4502', 2000.00);   -- refused
```
This is usually what you want: it loads the table and seals it in a single statement.
A CREATE TABLE ... AS SELECT that leaves the table empty does not use it up. WITH NO DATA, or a query that happens to return no rows, leaves an empty table, so the next insert still works — and is then the last one.

insert and insertonce cannot both be given — insertonce is a variant of insert, not an addition to it.

About `drop`¶

Think carefully about this one.

If you do not grant drop, the table can never be removed. Not by you, not by your DBA, not by a superuser, not with CASCADE, not by dropping the schema around it. The only way to get rid of it is to drop the entire database.

That is the intended behaviour — a table that can be dropped on a whim is not really protected. But it does mean you should be deliberate about it.

If you want the protection but also a way out, grant drop. Data still cannot be edited, but the table as a whole can be removed by somebody who means to.

Worth noting if you are also setting retention: drop removes the table outright rather than deleting rows, so a retention period does not stand in its way. On a table granting both, the rows are kept safe from DELETE until their deadline, but the table as a whole can still be retired at any time. That is usually the intent — just be aware it is not the same promise as "these rows will exist for seven years".

Every table needs a way to get data in¶

Your permissions list must include either insert or insertonce. A list with neither is refused when you create the table:

ERROR:  "permissions" must include "insert" or "insertonce"
DETAIL:  A vault table granting neither can never hold any rows, and cannot be
         altered afterwards.

None of the other four put rows into a table — they only govern what happens to rows that are already there. So permissions = 'delete', or permissions = 'update,delete', describes a table that could never hold anything, and because you cannot alter a vault table afterwards, you could never fix it. The only way out would be to create a replacement table.

This catches a genuine mistake. If what you actually want is "rows are kept for 90 days and then removed", the declaration is permissions = 'insert' with retention = 90 — retention does the removing, and delete is not required for it.

What is always allowed, permission or not¶

Separately from any permission, everything a DBA does that cannot affect the data is always allowed, on a table granting nothing but insert:

Always allowed
Column storage	`ALTER COLUMN ... SET STORAGE`, `SET COMPRESSION`
Planner	`ALTER COLUMN ... SET STATISTICS`, `SET (n_distinct)`, `RESET (...)`
Clustering	`CLUSTER ON`, `SET WITHOUT CLUSTER`
Replication	`REPLICA IDENTITY` in all its forms
Indexes	Everything. `CREATE INDEX`, `DROP INDEX`, `REINDEX`, `ALTER INDEX` — this extension never touches an index
Comments	`COMMENT ON TABLE`, `COMMENT ON COLUMN`
Maintenance	`VACUUM`, `VACUUM FULL`, `ANALYZE`, `CLUSTER`

One exception, and it is a limitation rather than a policy. Table-level storage parameters — ALTER TABLE ... SET (fillfactor = 70) and friends — cannot be changed after creation. PostgreSQL validates a table's whole storage-parameter list as a unit, and this extension keeps permissions and retention in that same list without registering them with core, so the statement is refused before this extension is even consulted.

Set them when you create the table instead, which works normally:

CREATE TABLE t (id int) USING vault
  WITH (permissions = 'insert', fillfactor = 70,
        autovacuum_vacuum_scale_factor = 0.01);

The same property is why no ALTER TABLE can reach permissions or retention, which is worth more than the convenience it costs.

Advice on the definition-level permissions¶

These are tools, not guard rails. Nothing below is refused — they are simply the choices worth thinking about twice.

rls without policy lets row level security be switched on while the policies underneath stay fixed. Turning it on with no policies defined makes the table unreadable to everyone except its owner and roles with BYPASSRLS. That is recoverable — switch it off again — but only because rls was granted.

policy without rls lets the policies be rewritten while row level security stays as it is. If it is off, the policies sit there doing nothing until somebody turns it on, which needs rls.

log with retention is the one to weigh carefully. SET UNLOGGED means the table's contents are no longer written to the write-ahead log, so they do not reach a standby, and the table is emptied on crash recovery. A retained table can therefore lose every row without a single DELETE. The retention deadlines themselves survive a SET LOGGED/SET UNLOGGED round trip intact — that is guaranteed — but the rows only survive if the server does.

trigger includes DISABLE TRIGGER ALL, which reaches the internal triggers PostgreSQL uses to enforce foreign keys. Granting trigger therefore allows referential integrity to be switched off on that table.

coldefault changes no existing row, which is exactly why it is easy to underrate. On an append-only table it changes what every future row records wherever the column is omitted from the INSERT — so a default of 'pending' becoming 'approved' silently rewrites the meaning of everything inserted afterwards, with no UPDATE anywhere and nothing in the table to show it happened. Grant it where defaults are genuinely operational, and leave it out where the default carries meaning.

Combinations that are refused¶

These fail when you create the table, which is the cheap moment to find out — a declaration cannot be corrected afterwards.

insert with insertonce — pick one.

truncate with retention — TRUNCATE empties the whole table in one go without looking at retention at all, so a table granting both would claim a retention period that a single statement could ignore. If you need rows removable before their deadline, grant delete instead.

ERROR:  "truncate" cannot be granted on a table that sets "retention"
HINT:   Grant "delete" instead if rows must be removable before their deadline.

A column list that does not describe the table. Every name in update(...) is checked against the table as it is created, so each of these is refused rather than stored:

Written	Why it is refused
`update(nosuch)`	No such column. The error lists the ones that may be named
`update(status,status)`	Named twice
`update()`	Empty. Write `update` on its own to permit every column
`update(status`	Unterminated list
`delete(status)`	Only `update` may be narrowed
`update(total)` where `total` is generated	A generated column follows its inputs and is never assigned directly
`update(_$purge_ts)`	The retention deadline belongs to the extension

A column of your own named _$purge_ts. That name is reserved on any vault table, whatever type you give it. Set retention and the column is created for you; pick another name for a column of your own.