Skip to content

postgres

The database adapter. Package @unset/infrastructure-postgres. Driver choice is ADR 0014. pg in this package’s package.json is 8.23.1.

Owns

Migrations, the role roster, grants, pools, transactions, and the stores whose SQL lives here.

ModuleWhat it holds
infrastructure/postgres/migrate.tsmigrate, migrateThenSyncPasswords. Applies migrations/*.sql in order as the connected role, under advisory lock MIGRATE_LOCK_ID. Checksums live in public.schema_migrations, created by the runner, not by a migration file.
infrastructure/postgres/migrate-cli.tsunset-migrate. ROLE_PASSWORDS_DIR is /run/secrets.
infrastructure/postgres/sqlLint.tsText lint of a migration before it runs. An expand file is refused when its text matches drop, rename, alter_type, set_not_null or truncate.
infrastructure/postgres/roles.jsonThe roster below.
infrastructure/postgres/roles.tssyncRolePasswords. Skips a row whose passwordFrom is null. ALTER ROLE gets the string scramVerifier returns.
infrastructure/postgres/grant-matrix.jsonGrants the integration test diffs.
infrastructure/postgres/pool.tscreatePool, acquire, withClient, readUntilAbort, ping, checkConnectionBudget. readUntilAbort ends a client whose query outlives its signal, rejecting with http.deadline. ping is one SELECT 1; the signal bounds the wait for a client and the query. The file comment and ADR 0014 say pool.connect and pool.query live only here. migrate.ts and roles.ts open a pg.Client and call client.connect().
infrastructure/postgres/tx.tsinTransaction, withTransaction. The file comment says BEGIN, COMMIT and ROLLBACK in TypeScript live only here. docs/human/architecture.md row DM-2 names Semgrep transactions-only-in-tx.
infrastructure/postgres/config.tspostgresFields, poolFields, lockPoolFields, connectionOf.
infrastructure/postgres/lock.tswithAdvisoryLock, createRequestLock. LockNamespace.oauth is 1. The wait calls pg_advisory_xact_lock.
infrastructure/postgres/oauthStores.tscreateOAuthStateStore, createOAuthSessionStore, oauthSessionExists. SQL on app.oauth_state and app.oauth_session. The stores seal values. oauthSessionExists is SELECT 1 on app.oauth_session and does not unseal. createOAuthStateStore’s sweep is run by interfaces/web/src/lib/server/sweeps.ts (sweep.oauth_state). interfaces/web/src/lib/server/compose.ts passes oauthSessionExists to createPdsCall as tokenSetRemains.
infrastructure/postgres/accountRepository.tscreateAccountRepository, AccountRepositoryOptions, BlockTransaction. SQL on app.account. Implements AccountRepository from domains/identity/account.ts. The audit writer (recordUnderageBlock) comes from the caller and gets only BlockTransaction, a query-only view of the block’s transaction.
infrastructure/postgres/singleUse/store.tscreateSingleUseStore. SQL on app.single_use. PURPOSES is the closed list.
infrastructure/postgres/didColumns.tsdidColumns. One catalog query for columns typed types.did or types.at_uri, including arrays.
infrastructure/postgres/sealedColumns.tssealedColumns, sealedContext. Rows from sealed-columns.json.
infrastructure/postgres/rewrapAll.tsrewrapAll, kidCounts.
infrastructure/postgres/rewrap-cli.tsunset-rewrap.
infrastructure/postgres/erasure-registry.jsonDID columns and an erasure strategy per column.

createPool sets application_name from its service argument and connectionTimeoutMillis from connectTimeoutMs. connectionOf maps PG_SSLMODE=disable to ssl: false and verify-full to ssl: true or { ca }. The disable rule calls classifyAddress from @unset/infrastructure-net-guard.

tests/integration/postgres/grants.test.ts reads roles.json and grant-matrix.json and diffs them with a database migrated from scratch. tests/integration/postgres/did-columns.test.ts and sealed-columns.test.ts cover the two registries.

May import

The infrastructure row. This package imports net-guard and seal. accountRepository.ts imports types from @unset/domains-identity.

Declared in infrastructure/postgres/package.json:

DependencyVersion
@unset/domains-identityworkspace
@unset/infrastructure-net-guardworkspace
@unset/infrastructure-sealworkspace
@unset/shared-configworkspace
@unset/shared-errorsworkspace
@unset/shared-logworkspace
pg8.23.1

vendor-sdk-one-adapter confines pg to this folder. Dev dependency @types/pg is 8.23.1.

Entry points

RolePath
Package export .infrastructure/postgres/index.ts
Bin unset-migrateinfrastructure/postgres/migrate-cli.ts
Bin unset-rewrapinfrastructure/postgres/rewrap-cli.ts
ImageENTRYPOINT ["node", "infrastructure/postgres/migrate-cli.ts"] in deployment/images/migrate.Dockerfile
ComposeService migrate in deployment/compose.dev.yaml and deployment/compose.local.yaml. PG_USER is migrator.
Poolsinterfaces/web/src/lib/server/compose.ts calls createPool and createLockPool

The Postgres server is Compose service postgres, image deployment/images/postgres.Dockerfile (FROM docker.io/library/postgres:18-trixie@sha256:74935e72241653ca55e0414067e6d8763aceb8a810eb51b452253ec3dcfc4336). deployment/postgres/init/00-bootstrap.sh runs at initdb and creates roles migrator and tap, databases unset and tap. tap is not in roles.json.

unset-rewrap is a bin in package.json. No Dockerfile ENTRYPOINT names rewrap-cli.ts.

Data

0002_schemas.sql creates schemas app, idx, audit and types. grant-matrix.json owners: app, idx and types are migrator; audit is audit_owner. 0003_roles_and_grants.sql runs ALTER SCHEMA audit OWNER TO audit_owner and GRANT audit_owner TO migrator WITH SET TRUE, INHERIT FALSE.

Roster in roles.json. statement_timeout is the settings value; two rows omit it.

RoleClassLoginconnectionLimitstatement_timeoutpasswordFrom
webserviceyes402spg_web_password
apiserviceyes202spg_api_password
indexerserviceyes1010spg_indexer_password
adminserviceyes105snull
retentionjobyes260snull
mediaserviceyes102snull
reviewserviceyes410snull
review_egressserviceyes25snull
auditorjobyes260snull
backupjobyes20null
legal_hold_readergroupnonullabsentnull
audit_ownerownernonullabsentnull

0003_roles_and_grants.sql creates those twelve roles when missing. It sets search_path to empty, idle_in_transaction_session_timeout 10s and lock_timeout 3s on each. migrator is not in the file.

Schema USAGE in grant-matrix.json:

SchemaRoles
appweb, admin, retention
auditweb, indexer, admin, retention, auditor
idxweb, api, indexer, admin
typesweb, api, indexer, admin, audit_owner

idx has no table in migrations/. grant-matrix.json gives media, review, review_egress and backup database CONNECT and no schema or table privilege. legal_hold_reader and audit_owner have an empty database list there, and no table privilege. audit_owner is the audit schema owner in that file. 0003_roles_and_grants.sql grants CONNECT to web, api, indexer, admin, retention, media, review, review_egress, auditor and backup.

Table and routine grants in grant-matrix.json (column lists are in that file):

ObjectRoles
app.single_useweb (column list); retention (SELECT on expires_at, DELETE)
app.sessionweb
app.end_sessions_for_didweb (EXECUTE)
app.oauth_stateweb
app.oauth_sessionweb
app.accountweb
audit.appendweb, indexer, admin (EXECUTE)
audit.chainauditor (SELECT, wholeTable)
audit.segmentauditor (SELECT, wholeTable)
audit.redaction_logauditor (SELECT, wholeTable)

Migrations and the objects they add:

FileObjects
0001_init.sqlNo statement. The runner records version 1.
0002_schemas.sqlSchemas app, idx, audit, types.
0003_roles_and_grants.sqlThe twelve roles, CONNECT, schema USAGE, audit owner.
0004_types.sqlDomains types.did and types.at_uri. Function types.at_uri_did.
0005_retention_app_usage.sqlUSAGE on app for retention.
0006_single_use.sqlapp.single_use.
0007_sealed_type.sqlDomain types.sealed. Check ^s1\.[a-z0-9]{1,16}\..
0008_audit.sqlaudit.lp, audit.row_hash, audit.append, audit.refuse_change.
0009_audit_tables.sqlaudit.retention_classes, audit.actions, audit.reasons, audit.chain, audit.event_body.
0010_audit_append_grant.sqlEXECUTE on audit.append for web, indexer, admin.
0011_app_session.sqlapp.session. The comment names infrastructure/postgres/session/. The store is infrastructure/session/store.ts. migrate.ts checksums an applied file, so the comment stays.
0012_app_session_did_index.sqlIndex session_did. Marked -- unset: no-transaction.
0013_app_oauth.sqlapp.oauth_state, app.oauth_session.
0014_app_end_sessions.sqlapp.end_sessions_for_did (SECURITY DEFINER).
0015_app_account.sqlapp.account.
0016_audit_actor.sqlaudit.event_body.actor; audit.append replaced to fill it and to refuse a person’s action with no actor.
0017_audit_retention.sqlaudit.segment, audit.redaction_log.
0018_audit_retention_functions.sqlaudit.redact, audit.erase_subject, audit.seal_segment, audit.mark_anchored, audit.drop_segment, audit.segment_transition. Replaces audit.refuse_change. No GRANT.
0019_app_session_issuer.sqlapp.session.issuer_origin, nullable, no default, https-origin check. No grant in this file.
0020_app_session_issuer_grants.sqlSELECT (issuer_origin) and INSERT (issuer_origin) on app.session for web.

sealed-columns.json: app.oauth_session.sealed row key did; app.oauth_state.sealed row key key.

erasure-registry.json: app.single_use.bind_did, app.session.did, app.oauth_session.did and app.account.did (delete_row); audit.event_body.subject and audit.event_body.actor (audit_redact).

singleUse/store.ts PURPOSES: login.nonce 600, module.assertion 120, service_auth.jti 120, invite.claim 600, email.interstitial 600, chat.openid 3600. The numbers are seconds.