Your first vault table¶

Let us make an append-only audit log — a table you can add to but never change.

Create it¶

CREATE TABLE audit_log (
    id         bigserial PRIMARY KEY,
    occurred   timestamptz NOT NULL DEFAULT now(),
    actor      text NOT NULL,
    action     text NOT NULL
)
USING vault
WITH (permissions = 'insert');

Two things are different from an ordinary CREATE TABLE:

USING vault tells PostgreSQL to use this extension's access method.
WITH (permissions = 'insert') says inserting is the only thing allowed.

Everything else — the primary key, the bigserial, the default — is completely ordinary.

Use it¶

Inserting works as normal:

INSERT INTO audit_log (actor, action) VALUES ('alice', 'approved invoice 4501');
INSERT INTO audit_log (actor, action) VALUES ('bob',   'exported ledger');

SELECT * FROM audit_log;

 id |           occurred            | actor |          action
----+-------------------------------+-------+--------------------------
  1 | 2026-08-17 09:14:22.104+10    | alice | approved invoice 4501
  2 | 2026-08-17 09:14:22.118+10    | bob   | exported ledger

Reading is unaffected — vault tables have nothing to do with SELECT.

Watch it refuse¶

UPDATE audit_log SET action = 'nothing to see here' WHERE id = 1;

ERROR:  update is not permitted on vault table "audit_log"
DETAIL:  The table's permissions are "insert".
HINT:   A vault table's permissions are fixed at creation and cannot be altered.

The same happens for DELETE, TRUNCATE and DROP TABLE. And it happens no matter who you are — try it as postgres and you will get exactly the same error.

What about COPY and MERGE?¶

They are covered too. insert means inserting by any route:

COPY audit_log (actor, action) FROM stdin;      -- allowed, it is an insert

MERGE INTO audit_log t USING (...) s ON ...
  WHEN MATCHED THEN UPDATE SET ...;             -- refused, it is an update

The extension checks what is actually happening to the row, not which SQL command you typed. There is no back door via COPY, MERGE, or INSERT ... ON CONFLICT.

Common mistakes¶

Forgetting the permissions:

CREATE TABLE t (id int) USING vault;

ERROR:  a vault table must specify "permissions"

That is deliberate. A table with no permissions would refuse everything forever and could never be altered, so it is treated as a mistake rather than a configuration.

Getting a name wrong:

WITH (permissions = 'insert,select')

ERROR:  unrecognised permission "select"
HINT:   Valid entries are: insert, insertonce, update, delete, truncate, drop.

SELECT is not in the list because this extension does not control reading at all.