Ekklesia/docs/architecture/decisions/0018-identity-session-resolution-and-database-transport.md

0018: Identity session resolution, canonical public origins, and PostgreSQL transport policy

• Status: Accepted
• Date: 2026-07-26
• Amends: 0010, 0011
• Extends: 0017

Context

Phase 5D pass 1 (ADR 0017, commits d3c6bd73, 1161f5a076b66548767d67b58853bf83ab8f24b4, 3d6c85b0) closed the public request perimeter's default-open exposures. Pass 2 addresses the layer immediately behind it: how a request's principal is actually resolved, what a "public origin" is allowed to be, and how Core reaches PostgreSQL at all. Five defects were reproduced against the pass-1 tree before anything was changed:

1. Any cookie triggered identity database work. resolvePrincipal's ambiguity check ran whenever the request carried a Cookie header. Cookie: x=y on an otherwise anonymous request caused a database round trip on every request — an unauthenticated caller could drive identity database load with a header they chose themselves.
2. The readiness probe proved almost nothing. It issued select 1. A dropped session table, a drifted or removed column, or a revoked SELECT privilege on the session relation all still answered "reachable", so Better Auth's ambiguous null was reported as a genuinely anonymous request. A deployment whose session store was broken in any of those ways served every request as anonymous instead of failing.
3. The base URL / trusted-origin boundary accepted non-origins. Validation accepted any absolute http/https URL, so a value carrying a path, a trailing slash, credentials, a default port written explicitly, or non-canonical case became configuration that no longer compared equal to the origin a browser would send. The loopback rejection was also a short literal list (localhost, 127.0.0.1, ::1), missing 127.0.0.2, the rest of 127.0.0.0/8, 0.0.0.0, ::, the expanded 0:0:0:0:0:0:0:1 spelling, IPv4-mapped forms such as ::ffff:127.0.0.1, and the RFC 6761 .localhost namespace.
4. Every PostgreSQL connection was plaintext, in every environment. No connection site set ssl at all. Credentials, session tokens, and row data crossed the network in the clear, and nothing in configuration, startup, or pnpm doctor said so.
5. A connection string could silently downgrade an explicit TLS decision. The pinned pg 8.22.0 builds its parameters as Object.assign({}, config, parse(config.connectionString)), so a ?sslmode=disable in DATABASE_URL overrides an explicit ssl object passed alongside it. A policy that only set ssl would have been silently defeated by the URL it was given.

Decision

Only the configured session cookie makes null ambiguous

core/src/identity/runtime.ts exports hasConfiguredSessionCookie(headers, cookieName). It parses the Cookie header the way RFC 6265's cookie-string grammar (and Better Auth's own parser) requires — split on ;, take each pair's name as the text before its first =, trim surrounding whitespace — and compares the whole name with an exact string comparison. Never includes, never a substring test, never an unanchored regular expression: better-auth.session_token_x, x_better-auth.session_token, a differently-cased spelling, and a percent-encoded lookalike are all correctly not the session cookie (cookie names are case-sensitive per RFC 6265 §4.1.1 and are never percent-decoded). Ambiguity fails safe towards checking: a duplicated session-cookie name still reports true, because reporting false is what would let a broken session store be served as an anonymous request. Reporting true never grants access on its own — it only means the readiness check runs, and Better Auth's own result still governs the principal.

The cookie name is not a second, hard-coded copy. One betterAuthOptions object feeds both betterAuth() and Better Auth's own supported getCookies helper (better-auth/cookies), so the name this runtime detects — including the __Secure- prefix an https baseURL implies — is derived from the exact configuration Better Auth itself uses. Drift is structurally impossible rather than merely tested.

The practical effect: a request with no cookies, or with only unrelated cookies, performs zero identity database work. Better Auth never attempted to resolve an Ekklesia session for it, so its null is unambiguous and the anonymous principal is returned directly.

The readiness check reads the real session relation

probeSessionStore replaces select 1 with a planned read of the exact session relation and the exact columns the Better Auth adapter depends on (id, token, userId, expiresAt), under a where false predicate. PostgreSQL still resolves the relation, every named column, and the caller's SELECT privilege at plan time, while guaranteeing zero rows are matched, read, or returned — so no session row, token, or user id can be enumerated, logged, or exposed by the check itself. It uses the injected Identity Drizzle view (the API's one shared pool) and never constructs a second pool, client, connection string, or Drizzle instance, and it stays bounded by the existing 3-second timeout whose timer is always cleared.

A missing relation, a dropped column, a revoked privilege, an unreachable connection, and a query timeout all surface at this seam identically — as a rejected query — and all produce the same outcome: a sanitized PRINCIPAL_RESOLUTION_FAILED, which apps/api already maps to a 503 {"code":"PRINCIPAL_RESOLUTION_FAILED"} with no relation name, column name, SQL, driver text, host, or stack in it. A genuinely missing, expired, revoked, or malformed session still resolves to the anonymous principal exactly as ADR 0011 specifies. This is the distinction the pass-1 tree could not make.

The public identity URL boundary is exactly one canonical origin

parseCanonicalHttpOrigin (core/src/identity/config.ts) requires url.origin === value — the WHATWG URL parser's own canonical serialization must equal the supplied string exactly. A path, a trailing slash, a query, a fragment, embedded credentials, an explicitly written default port, or non-canonical case therefore fails with the existing BASE_URL_INVALID / TRUSTED_ORIGIN_INVALID codes rather than being normalized behind the operator's back, and both validateBaseUrl and validateTrustedOrigin return that canonical origin, so what is stored is what Better Auth compares against a browser's Origin header. The base URL is now an origin, not a URL that merely happens to have one.

isLoopbackOrUnspecifiedHostname replaces the literal list. It classifies localhost and the whole reserved .localhost namespace (RFC 6761), all of 127.0.0.0/8, 0.0.0.0, ::1 in both its compressed and fully expanded spellings, ::, and IPv4-mapped forms such as ::ffff:127.0.0.1 and ::ffff:7f00:1 — by expanding the address to its eight numeric groups rather than by comparing strings. Production rejects every one of them for both the base URL and every trusted origin (BASE_URL_LOOPBACK_IN_PRODUCTION / TRUSTED_ORIGIN_LOOPBACK_IN_PRODUCTION), and development composition must still opt in explicitly with environment: "development".

This is a configuration-shape guarantee and nothing more. It does not resolve DNS and does not prove reachability. A public hostname that resolves to a loopback or private address still passes this validation, and a firewall, network policy, or reverse proxy — not URL parsing — is what actually determines who can reach the deployment.

Production PostgreSQL requires TLS with certificate and hostname verification

core/src/database/transport.ts is the one typed transport policy. DatabaseTlsMode has exactly two values:

• verify-full — TLS required, certificate chain verified, and the server's identity verified against the configured host. The default in production.
• disable — no TLS. The default in development/test, and refused outright in production.

There is deliberately no encryption-only mode (require), no chain-only mode (verify-ca), and no rejectUnauthorized: false escape hatch anywhere in the configuration surface: encryption without identity verification is exactly what an interception attack defeats, so offering it as a supported setting would offer a switch whose safe position is the only one worth having. Every loose, aliased, or coercible spelling (VERIFY-FULL, " verify-full", require, prefer, no-verify, true, 1, "", null) is rejected with DATABASE_TLS_MODE_INVALID in both environments, so a typo never quietly becomes a weaker mode.

Production has no plaintext exception at all — not even for a loopback database. assertProductionDatabaseTransport refuses mode: "disable" in production whether the URL points at db.example.test, 127.0.0.1, localhost, or [::1], with the stable DATABASE_TLS_REQUIRED_IN_PRODUCTION code. A "same host, therefore safe" carve-out would be a claim about the deployment's process isolation, container networking, and socket namespace that this repository cannot verify from a URL string.

databaseTlsConnectionOptions builds the actual ssl object: rejectUnauthorized: true, an optional explicit private CA (certificateAuthority, supplied as PEM text — a path, a filesystem read, and a secret store are all out of scope here), and — critically — an explicit checkServerIdentity pinned to the host parsed from the connection string. That last part closes a real gap in the pinned driver rather than restating a default: pg sets TLS servername only for non-IP hosts (connection.js), and Node performs no identity check when servername is unset, so rejectUnauthorized: true alone accepts a certificate from any host the configured CA ever signed when connecting by IP address. Both halves of that claim are proven against a real TLS-enabled PostgreSQL in core/tests/database-tls.integration.ts: the policy rejects the mismatch with ERR_TLS_CERT_ALTNAME_INVALID, and the same connection without the pinned callback succeeds.

A connection string may not carry its own TLS decision

assertNoConnectionStringTlsParameters rejects a DATABASE_URL carrying any TLS parameter the pinned pg-connection-string 2.14.0 recognizes — ssl, sslmode, sslcert, sslkey, sslrootcert, sslpassword, sslnegotiation, uselibpqcompat, case-insensitively — with DATABASE_TLS_CONNECTION_STRING_CONFLICT. This is not stylistic. The pinned driver's Object.assign({}, config, parse(config.connectionString)) gives the connection string the last word, so a ?sslmode=disable in the URL would silently replace the policy's verified ssl object; sslmode=require is additionally aliased to verify-full behavior with a deprecation warning in this release, so even the "stricter-looking" spellings do not mean what an operator would assume. There is exactly one place TLS is decided, and it is the policy — never the URL. databaseTlsConnectionOptions performs this assertion itself, so no connection site can forget it.

One policy, every executable, and Doctor

core/src/cli/database-transport-environment.ts is the single environment reader (EKKLESIA_DATABASE_TLS_MODE, EKKLESIA_DATABASE_CA), keeping process.env out of Core's reusable constructors: createValidatedPostgresPool, createIdentityDrizzleDatabase, createAuthorizationDrizzleDatabase, and createPostgresMigrationDatabase all take an explicit optional transport policy instead. Every executable that opens a PostgreSQL connection resolves the policy the same way — apps/api (via ApiRuntimeConfig.databaseTransport, validated in createApiRuntimeConfig before any pool, Better Auth instance, module runtime, or listener exists), pnpm migrations:plan, pnpm migrations:apply, the platform-owner bootstrap CLI, and pnpm doctor's own connection.

pnpm doctor gains a database.transport check reported in its PostgreSQL section. A verified production policy passes; an unsafe or invalid one is a blocking NOT READY failure, never a warning, and its output names the effective mode only — never the URL, host, port, credentials, or CA material.

Alternatives

• Keeping select 1 and adding a separate table-existence query was rejected: it would be a second, drifting description of what the adapter actually needs. Planning the real read against the real relation and the real columns is the only check that fails when any of them changes.
• Probing the session store on every request was rejected: it makes an unauthenticated caller able to drive database load, which is the defect being fixed, not a stricter version of it.
• Normalizing a non-canonical base URL instead of rejecting it was rejected: silently rewriting an operator's origin is how a deployment ends up with a trusted-origin list that does not match what browsers actually send, and the failure then appears as an unexplained CSRF rejection.
• Resolving DNS during configuration validation to catch a public hostname pointing at a private address was rejected: it makes startup depend on resolver state, is trivially defeated by a later DNS change, and would invite exactly the false confidence this ADR explicitly disclaims.
• Supporting sslmode=require (encryption without identity verification) was rejected: it is the mode that looks secure and is not, and having it available makes it the one an operator picks when verify-full is inconvenient.
• A production plaintext exception for a loopback database was rejected: the URL cannot prove the database is actually process-local, and the exception would be the first thing a containerized deployment mistakenly claimed.
• Trusting rejectUnauthorized: true alone was rejected once the reproduction showed the pinned driver leaves servername unset for IP hosts and Node then performs no identity check at all.
• Reading EKKLESIA_DATABASE_* inside Core's database constructors was rejected: it would put process.env inside reusable library code and give every test and future embedder an implicit, invisible input.

Consequences

A request without the configured session cookie performs no identity database work at all; a request that presents it, and that Better Auth resolved to null, is disambiguated by a real session-store read, so a broken store produces a sanitized 503 instead of a silent anonymous downgrade. The identity base URL and every trusted origin are now exactly one canonical origin each, with a complete loopback/unspecified classifier enforced in production — a shape guarantee only, which does not resolve DNS, does not prove reachability, and does not replace a firewall or reverse proxy. Production PostgreSQL connections require TLS with both certificate and hostname verification, from every executable, with no plaintext exception and no way to express a weaker mode; a connection string that tries to decide TLS for itself is refused rather than honored.

None of this makes an internet-exposed database safe: TLS protects the connection, not the exposure, and the network boundary remains the deployment's own responsibility. This pass adds no secret store, no CA file loading, no certificate rotation, no client-certificate authentication, no connection-level authorization change, and no distributed rate limiting. Phase 5D passes 3–5 (authorization integrity, module/runtime/migration correctness, and deployment/supply-chain closure) remain outstanding — Phase 5D is not complete.