README.md

sqlstore

Durable stores for a FAPIgo Authorization Server's server.Dependencies, and for a relying party's client.Dependencies.Sessions, in PostgreSQL or SQLite through database/sql. It's a Go module of its own, github.com/idfoundry/fapigo/sqlstore, so FAPIgo itself takes no database driver: bring the one you use.

The package documentation (doc.go) is the reference: setup, migrations, atomicity, sealing, capabilities, the clients table and sweeping. This page is the short version. For a running deployment, examples/linked-accounts keeps an Authorization Server's, a resource server's and two relying parties' stores in one SQLite file, each under its own table prefix.

Setup

import (
	"database/sql"

	_ "github.com/jackc/pgx/v5/stdlib" // or _ "modernc.org/sqlite"

	"github.com/idfoundry/fapigo/pairwise"
	"github.com/idfoundry/fapigo/server"
	"github.com/idfoundry/fapigo/sqlstore"
)

sqlDB, err := sql.Open("pgx", os.Getenv("DATABASE_URL"))
db, err := sqlstore.New(sqlDB, sqlstore.Postgres, sqlstore.Options{})
if err := db.Migrate(ctx); err != nil { … } // or CheckSchema, if a deployment step migrates

deps := server.Dependencies{
	Clients:      db.Clients(),
	Transactions: db.Transactions(),
	Grants:       db.Grants(),
	Replay:       db.Replay(),
	Nonces:       db.Nonces(),
	Revocation:   db.Revocation(),
	// …
}

// For the features that need one:
deps.AccessTokens = server.OpaqueAccessTokens{Store: db.AccessTokens()} // opaque access tokens (JWTs need none)
deps.Backchannel = db.Backchannel()                                    // CIBA
deps.PairwiseSubjects, err = pairwise.NewMapping(db.PairwiseSubjects()) // random, mapped pairwise subs

// A relying party (package client):
clientDeps := client.Dependencies{
	Sessions: db.Sessions(),
	// …
}

SQLite (for example modernc.org/sqlite, pure Go, no cgo) needs WAL mode and a busy timeout:

sql.Open("sqlite", "file:fapigo.db?_pragma=busy_timeout(10000)&_pragma=journal_mode(WAL)")

On a new file, the first connection switches it to WAL mode as it opens, and a connection another process opens at that moment fails with SQLITE_BUSY. Migrate waits that out, so run it first in each process (or once, from a deployment step, before starting them) before anything else uses the file.

Sealing records

Set Options.Sealer to encrypt every record blob the stores write — pushed requests, grants behind codes and refresh tokens, opaque access tokens, CIBA requests and decisions, a relying party's sessions — with an AEAD bound to its row:

sealer, err := sqlstore.AESGCMSealer(key) // 32 random bytes, from a secrets manager or KMS, never the database
db, err := sqlstore.New(sqlDB, sqlstore.Postgres, sqlstore.Options{Sealer: sealer})

A sealed blob moved to another row, client, table or table prefix, altered, or opened under another key is refused (ErrRecordFormat, as an outage), as is one whose row's expiry (or an interaction's pushed request, or a CIBA request's handle, delivery mode or interval) was changed. Lookup keys and digests, client IDs, times, CIBA status, replay digests, nonces, revocations, pairwise subjects and the clients table stay readable: the stores look rows up by them. The Sealer interface is OID4VCgo's verifier.TransactionSealer, so one sealer serves both.

To start sealing a database in use: deploy with Sealer and ReadUnsealed: true, run db.SealRecords(ctx), then deploy without ReadUnsealed. SealRecords trusts what it finds, so run it on a database whose contents you trust. Losing the key makes sealed records unreadable. AESGCMSealer allows 2^32 seals per key (random nonces, NIST SP 800-38D), which a busy server reaches in weeks: rotate before then with sqlstore.RotatingSealer(newSealer, oldSealer), which seals with the new key and opens with either until the old one's records expire. See the package documentation's Sealing section.

Upgrading from v0.2

Blobs sealed by v0.2 and earlier (format byte 2) didn't bind their row's expiry and the other columns above. They still open, so an upgraded database keeps working; run db.SealRecords(ctx) once after upgrading every instance to re-seal them in the current format (byte 3) and bind them too. Upgrade every instance sharing a database at once: a v0.2 instance can't open what an upgraded one seals, and rolling back to v0.2 leaves the records sealed since unreadable.

What each database declares

	Durable	AtomicConsume	CrossInstanceConsistent	SerializableRedemption	EncryptedAtRest
PostgreSQL	yes	yes	yes	no (READ COMMITTED, row locks per key)	with a Sealer*
SQLite	yes	yes	no: one host's file	yes	with a Sealer*

* Transactions, Grants, AccessTokens, Backchannel and Sessions only, and not while ReadUnsealed is set; Clients, Replay, Nonces, Revocation and PairwiseSubjects never declare it.

So server.New accepts the PostgreSQL stores under AssuranceProduction with either Deployment, and the SQLite stores with DeploymentSingleInstance only. client.New accepts Sessions on either under AssuranceProduction, except that a native app's CallbackBindingDeviceLocalStore refuses PostgreSQL's, a store shared between instances: use SQLite there.

Running it

• Migrations are embedded and versioned. Migrate applies the missing ones and is safe to run from several processes at once; nothing migrates implicitly. Options.TablePrefix (default fapigo_) prefixes every table, so these tables can share a database, and a *sql.DB, with OID4VCgo's sqlstore (oid4vc_).
• Sweeping: stores never delete expired rows. Call db.Sweep(ctx, time.Now()) every few minutes. CIBA requests are kept an hour past their expiry, so a late poll gets expired_token; sessions until they're consumed or expire; pairwise subjects are never swept.
• Resource servers reading opaque access tokens or revocations use the server's tables (AccessTokens(), Revocation(), and Replay(), whose digests each role namespaces), through a DB with the same TablePrefix and Sealer. Their DPoP nonce store is the exception: Nonces() from a DB with a TablePrefix of its own, never the server's, whose nonces it would then accept. Give a resource server a database role of its own: SELECT on the server's access_tokens, revocations and schema_migrations, SELECT/INSERT on replay, and its own prefix's tables; and let only the server Sweep the tables they share.
• Clients are rows the operator manages: a client_id and a versioned JSON configuration, written with Clients().Put or MarshalClientConfig. See the package documentation's Clients section for the document's members.

Tests

The tests run each store's storage contract suite against SQLite, and against PostgreSQL too when FAPIGO_SQLSTORE_POSTGRES_DSN is set (in CI, where the CI environment variable is set, it must be):

docker run --rm -d --name sqlstore-pg -p 127.0.0.1:55432:5432 -e POSTGRES_PASSWORD=x postgres:17-alpine
FAPIGO_SQLSTORE_POSTGRES_DSN='postgres://postgres:x@127.0.0.1:55432/postgres?sslmode=disable' go test -race ./...
docker stop sqlstore-pg

Each run works in a schema of its own, dropped when it ends, and each test in tables of its own.

Versioning

This module is released and tagged on its own (sqlstore/vX.Y.Z). Its go.mod requires the FAPIgo release it's built against; the replace there serves development in this repository only, and CI also tests the module against the required release with it dropped.