PostgreSQL schema

This page describes every table persistence.mode=postgres uses, what goes in each column, and why the tables are shaped that way. Persistence covers turning the store on, the compose stack and what survives a restart. This page covers only what is in the database.

Schema version 12: 27 tables in a schema of their own, sts.

Where the schema is written down

It is written in two places, and a test keeps them in step:

• postgres/schema.sql is the script an owner runs once, with psql -v ON_ERROR_STOP=1 -f postgres/schema.sql <url>. It builds the tables and the least-privilege application role. The compose stack runs the same file through postgres/apply-schema.sh.
• persistence/persistence_postgres.js holds the same CREATE statements as its exported SCHEMA. On every open it probes for each object (to_regclass) and creates only what is missing. Against a database the script already built, it therefore needs no CREATE privilege.

tests/postgres_schema.js fails if either file holds a CREATE the other does not, or if the two version numbers differ. A column change goes in both files in one commit.

There are no migrations, only additions

Each schema change so far has added something: a table (CREATE TABLE IF NOT EXISTS) or a column (ALTER TABLE … ADD COLUMN IF NOT EXISTS). To upgrade an older database, run schema.sql again as the owner. Nothing is renamed or dropped, and nothing is rewritten. The version row in sts_schema records which shape is on disk.

The application role

The service connects as sts_app (set with -v sts_app_role=). That role holds USAGE on the schema and SELECT, INSERT, UPDATE and DELETE on its tables and sequences. It does not hold CREATE, TRUNCATE, REFERENCES or TRIGGER. CREATE is revoked on sts and on public, from PUBLIC and from the role. The script also stops if an existing role holds SUPERUSER, REPLICATION or BYPASSRLS, because only a superuser could remove those.

The default privileges extend the same grants to tables a later version adds. The database’s search_path is set to sts, public, so the driver can use unqualified table names.

Conventions that hold across the tables

• realm is a trust realm id. The default realm is default. The empty string '' means “belongs to the process, not to any realm”, for example the rate limiter’s buckets. It is never NULL, so primary keys need no COALESCE.
• Two time formats, each chosen per table. The tables from before the cluster work use timestamptz (written_at, at, applied_at). The cluster and risk tables use bigint milliseconds, taken from the database’s clock so that two nodes’ clocks never need to agree. sts_ldap_entries stores LDAP generalized time as text.
• origin is the process that wrote the row: a UUID made when each process starts. A process uses it to skip its own rows in the change log, and the cluster tables use it to show who holds a claim.
• Anything that could be used as a credential is sealed. The formats are $aesgcm$1$salt$iv$tag$body, or a SHA-256 or HMAC digest in place of the value. The key-encryption key is never in the database; encryption at rest says where it comes from. The directory is the deliberate exception (see below).

The tables, by what they hold

 directory & configuration      minted state            cluster coordination
 ─────────────────────────      ────────────            ────────────────────
 sts_ldap_entries  (JSONB)      sts_minted (sealed)     sts_cluster_nodes
 sts_realms                     sts_used_assertions     sts_cluster_leases
 sts_appconfig                                          sts_cluster_claims
 sts_keys          (sealed)     change log              sts_cluster_secrets (sealed)
                                ──────────              sts_cluster_counters
 bookkeeping                    sts_changes             sts_cluster_windows
 ───────────                    sts_change_readers      sts_node_snapshots
 sts_schema                                             risk scoring: 14 × sts_risk_*

There are no foreign keys. Every table is keyed by what its readers look up, and a row is re-read by key rather than joined.

Directory and configuration

sts_ldap_entries: the embedded directory

The LDAP directory has one row per entry, for every realm. The LDAP schema page describes what goes in attrs.

Primary key (realm, dn_key), with an index on realm.

The six generated columns (schema version 13, #349) are computed by the database from dn_key and attrs, so nothing writes them. They exist for a request worker with ldap.workerDirectory=postgres-lru, which holds the people and devices as a bounded window and asks the store instead of keeping an index in memory. They are indexed: (realm, parent_key, dn_key) for a container’s children, (realm, parent_key, rdn_value) for a login name, GIN (jsonb_path_ops) over name_keys, mail_keys and uuid_keys, GIN over attrs for a value as written (a DID, a SPIFFE ID), and GIN (the default operator class, for ?|) over class_keys. Adding the columns to a table that already has rows rewrites it once; running postgres/schema.sql again as the owner does it.

It is the one table you can query with SQL, and that is deliberate. An entry is a document without a fixed schema, and JSONB lets Postgres index into it while still giving a primary key and transactions:

SELECT dn, attrs->'mail'->>0 AS mail
  FROM sts.sts_ldap_entries
 WHERE realm = 'default' AND attrs->'uid' ? 'alice';

It is not sealed. Secrets in an entry are protected per attribute: userPassword is an scrypt hash, and the recovery codes and app passwords are hashed. See the LDAP schema.

When two nodes change one entry at the same time, the update is a three-way merge (persistence/directory_merge.js), not last-writer-wins.

The created_at and modified_at columns are text rather than timestamptz so that they match the LDAP attributes byte for byte. A round trip through timestamptz would re-render the value in the session’s format and time zone.

sts_realms: the realm registry

The default realm is not a row. Its settings are the process’s own.

sts_appconfig: runtime setting changes

One row per setting changed at runtime, from the console or /admin-api: key is the setting name (oauth2.consentRequired) and value is JSONB. On restart these come back as runtime overrides, not as a new configuration layer (persistence).

sts_keys: private keys this service generated

This table is used in product mode only. Development regenerates its keys on every start. The pki: prefix keeps CA hierarchies apart from signing keys without adding a discriminator column. The first writer wins: a process that finds a row already present adopts it instead of overwriting it (SELECT … FOR UPDATE). This is how every node ends up with the same kid. See PKI and encryption at rest.

Minted state

sts_minted: everything the process issued

These are sessions, codes, tokens, artifacts, nonces, pending flows, counters and the audit log, all in one table. It is written in product mode on postgres only, and persistence.minted turns it off.

Primary key (handle, realm, key), with indexes on (handle, realm), written_at, and expires_at where it is not NULL.

A start reads only the rows of the realms that exist: '' (the shared stores), the default realm, and every realm in sts_realms. Rows of a realm that is no longer defined are deleted by the same job once they are an hour old.

There is one table rather than one per family, so that persisting a new store costs one word at its declaration and no DDL. The price is that nothing in the table can be queried with SQL, because a session id is a cookie value and an authorization code can be redeemed. A dump of this table must not give anyone a live session.

The handles in use, grouped by family (a test.* handle is used only by tests):

To list them from a running tree: grep -rhoE "persist: *'[^']+'" --include=*.js --include=*.ts . | sort -u.

sts_used_assertions: accepted-once history for RFC 7523 and RFC 7522

This table holds every JWT and SAML assertion accepted as a client credential or a grant, kept until the assertion would have expired, so that none is accepted twice (JWT assertions, SAML assertions). It is written in both modes, because the key that verifies an assertion belongs to the client and outlives a restart.

It has a table of its own, rather than living in sts_minted, because recording a use has to be an atomic claim: INSERT … ON CONFLICT on the primary key. Two nodes therefore cannot both accept one assertion. A row in sts_minted would reach another node only after that node pulled the change log.

The change log

sts_changes

This table is how several processes against one database agree with each other, not just share it.

Every change-log row is written in the same transaction as the change it describes, so “I have applied up to N” means “I have seen everything committed before N”. LISTEN/NOTIFY on the channel sts_ldap_change only wakes the poll early. Losing a notification costs latency, never correctness. The payload is only a pointer, so neither the 8000-byte NOTIFY limit nor encryption applies to it.

sts_change_readers

Each process that reads the log reports the highest seq it has applied (applied), with its node_id and times. sts_changes is trimmed below the lowest of these marks by the scheduler job that holds the lease ops.change-log-purge.

Cluster coordination (#46)

These tables are used when several containers share one store. Every time is the database clock in milliseconds.

cluster/CLAUDE.md explains why each of these is a separate table and why a row in sts_minted would not do.

Risk scoring (#62)

Risk scoring explains the model. What follows is only the storage. realm is '' for datasets shared across realms.

Datasets, stored by version

External data (geolocation, ASN, Tor exits, IP reputation, the FIDO MDS3 BLOB, an operator’s allow and deny lists) is imported as a version. A version is verified before it becomes active, and the previous version is kept so an import can be rolled back. Lookups are SQL range queries over inet.

The history the model keeps

Only address_prefix is stored in the clear, and it is the network, not the host. The full address is sealed like any minted row.

sts_schema

This table holds one row per version applied (version int PK, applied_at). The driver writes its SCHEMA_VERSION on every open, and schema.sql inserts the same number.

Related

• Persistence: the modes, the compose stack, what survives.
• Encryption at rest: the key-encryption key behind every $aesgcm$1$ value.
• LDAP schema: what is inside sts_ldap_entries.attrs.
• A cluster in AWS: the same schema on RDS.
• postgres/CLAUDE.md and persistence/CLAUDE.md, for maintainers.