Core concepts¶

Four ideas, and that is nearly all of it.

1. The access method¶

PostgreSQL lets an extension supply a table access method — the code that actually stores and retrieves rows. This extension provides one called vault.

You choose it with USING vault:

CREATE TABLE t (id int) USING vault WITH (permissions = 'insert');

Underneath, a vault table stores its data exactly the way an ordinary table does. The access method simply checks your permissions before letting a change through.

2. Permissions¶

permissions is a comma-separated list, and it is compulsory. It says what may ever be done to the table.

WITH (permissions = 'insert,delete')

Anything not in the list is refused. There are six to choose from, covered in Choosing Permissions.

3. Retention¶

retention is an optional number of days. When set, a row cannot be deleted until that many days have passed since it was inserted.

WITH (permissions = 'insert', retention = 2555)   -- seven years

The extension adds a hidden-ish column called _$purge_ts that records the exact moment each row becomes eligible for deletion. Covered in Retention.

4. Refusals are recorded¶

When the extension refuses something, it writes a line to the PostgreSQL log as well as returning an error to whoever tried. That way an attempt is visible to somebody other than the person who made it.

Normal work produces no records at all, so anything in the log is worth looking at. Covered in When Something Is Refused.

How they fit together¶

CREATE TABLE ledger (...)
  USING vault                              <- 1. use this extension
  WITH (permissions = 'insert,delete',     <- 2. what is allowed
        retention   = 2555);               <- 3. and for how long rows must be kept

                                           <- 4. anything refused gets logged