Proposal: isolated-DB multi-tenant Auth runtime with bounded tenant routing

[xmh1011]

Summary

This proposes adding an isolated-database multi-tenant runtime to Supabase Auth. The goal is to let one Auth process resolve a tenant per request, synthesize tenant-specific runtime configuration, route data access to a bounded tenant database pool, and support hot tenant configuration updates without process restart.

This proposal intentionally keeps shared_instance_id blocked. Many Auth tables and state flows still lack full tenant-scoped predicates or instance_id columns, so shared-DB mode should not be enabled until table-level scoping is complete and audited end to end.

Proposed design

1. Control-plane tenant metadata

   • Add an auth_tenants control-plane table containing tenant ID, instance_id, tenant DB URL/namespace, JWT secret/service key, external URL, site URL, redirect allow-list, JSON runtime config, and disabled_at.
   • Tenant metadata cache is bounded by item count and byte budget, has TTL, uses per-tenant singleflight for cold loads, and supports PostgreSQL LISTEN/NOTIFY targeted invalidation and wildcard cache clearing.
   • Control-plane SQL pool has its own max-open/max-idle limits.

2. Tenant resolution

   • Resolve tenant from a configured header, forwarded-host regexp, or default tenant ID.
   • Missing, unknown, disabled, host-confused, and conflicting tenant resolvers fail closed before user auth.
   • Invalid forwarded-host regexp fails startup validation.

3. Request-scoped config

   • Add conf.WithRequestConfig / conf.FromContext and update tenant-sensitive paths to read request config instead of process config.
   • Tenant JSON can override JWT issuer/audience/expiration/default group, URL/redirect policy, provider feature gates, OAuth server, custom OAuth, passkey/WebAuthn, SAML/SSO, SMTP, SMS, hooks, MFA, password/session/flow-state policy, request rate limits, and email/SMS send-rate limits.
   • Tenant metadata URL/glob/config validation fails closed during tenant resolution.
   • A bounded last-known-good runtime config fallback is only allowed when tenant ID, instance ID, DB URL/namespace, JWT secret, and service key are unchanged. Disabled/missing tenants, first-load failures, and DB/JWT/service-key boundary changes fail closed.

4. Isolated tenant DB routing

   • Add a request-scoped TenantDatabase and bounded storage.TenantRouter.
   • Tenant DB pools are opened lazily and capped separately from metadata cache size.
   • Routing keys include driver, URL, and namespace.
   • In-use or closing pools continue counting against capacity until released; new tenant routes wait or fail with request context timeout instead of opening over budget.
   • Startup config requires DatabasePoolConnectionBudget covering tenant request pools, control-plane SQL pool, optional notify listener, and enabled tenant cleanup/index worker concurrency.

5. Hot updates

   • Tenant config changes, JWT secret rotation, DB metadata changes, tenant disable/restore, and URL/redirect policy changes are applied through cache invalidation plus request-time reload.
   • Multi-node behavior relies on each Auth process maintaining its own cache and listening for invalidation notifications.
   • Control-plane short outage behavior is fail-closed for cold misses and for cached entries after TTL expiry.

6. Background workers and global object lifecycle

   • Index and cleanup workers page enabled tenants from the control plane and process isolated DBs with configurable concurrency.
   • One tenant worker failure is recorded but does not cancel the whole tenant fleet scan.
   • Tenant cleanup connections use the tenant dial path and skip per-pool DB stats callbacks / DB advisor goroutines.
   • Per-tenant cleanup runners skip long-lived cleanup metric callbacks.
   • OIDC provider cache and mailer template cache are bounded in multi-tenant mode.
   • PostgreSQL function hooks use the supplied transaction directly and avoid an extra process-level DB route when already inside a transaction.

Validation performed locally

The local branch includes a PoC under poc/multitenant and focused Go tests. On July 10, 2026, I ran:

• bash -n over the multi-tenant PoC scripts
• node --check poc/multitenant/browser-passkey-host.mjs
• git diff --check
• make auth
• DATABASE_URL=postgres://supabase_auth_admin:root@localhost:5541/postgres GOTRUE_DB_DATABASE_URL=postgres://supabase_auth_admin:root@localhost:5541/postgres go test -p=1 -count=1 ./...
• host-mode PoC: 42/42 scenarios, ending with PoC baseline completed
• browser passkey + MFA WebAuthn E2E using Chrome CDP virtual authenticator
• multi-node hot-update validation: 9/9 scenarios covering independent caches, config update notification, disabled tenant notification, listener reconnect, short control-plane outage, and TTL-expired fail-closed behavior
• low-budget churn validation with 256 distinct tenant DB routing keys, DatabasePoolMaxPools=8, DatabasePoolMaxOpen=5, DatabasePoolConnectionBudget=59: 256 public anonymous signup/user/refresh requests, 0 errors, p99 about 2499 ms, and tenant DB pg_stat_activity showing 8 churn tenant idle connections plus 1 active psql sampling query

Earlier local load runs also covered 10,000 tenant metadata cold/warm /settings, 10,000 public anonymous signup/user/refresh, 10,000 email/password signup/user/refresh, 10,000 admin create/get/list/update/delete, 10,000 admin generate_link signup links, and 10,000 password/user/refresh flows in the local shared physical Postgres PoC setup.

Known remaining gaps

• This is not yet a production-readiness claim.
• Official CI and target deployment environment validation are still required.
• shared_instance_id remains unsafe and should stay rejected until table-level instance_id scoping is implemented and audited for every state table and query path.
• More 10k+ coverage is still needed for the remaining Auth write-path matrix and real production-like 10k independent DB topology.
• Real third-party OAuth providers, real external SMS/email delivery providers, and hardware or OS platform WebAuthn authenticators still need E2E validation.
• Production runbooks, alerts, auditability, long control-plane partition policy, and deployment-specific connection-budget sizing still need to be designed.

[xmh1011]

Additional local review update (2026-07-10):

• Hardened tenant-scoped API limiter hot reload behavior: when ConfigCacheMaxItems is lowered, existing request/email/SMS tenant limiter caches are now trimmed immediately to the new capacity instead of waiting for future tenant churn.
• Added focused regression coverage for request limiter, email send limiter, and SMS send limiter capacity shrink after config reload.
• Updated the local Chinese and English multi-tenant design/audit docs to capture this bounded limiter behavior.

Latest local verification from the working branch:

• bash -n poc/multitenant/verify.sh poc/multitenant/run-host.sh poc/multitenant/verify-host.sh poc/multitenant/verify-multinode-host.sh poc/multitenant/load-10k-host.sh poc/multitenant/load-auth-paths-host.sh
• node --check poc/multitenant/browser-passkey-host.mjs
• git diff --check
• go test ./internal/api/apilimiter -count=1
• make auth
• DATABASE_URL=postgres://supabase_auth_admin:root@localhost:5541/postgres GOTRUE_DB_DATABASE_URL=postgres://supabase_auth_admin:root@localhost:5541/postgres go test -p=1 -count=1 ./...
• host PoC verify-host.sh: passed through [42/42] and PoC baseline completed
• browser WebAuthn/passkey E2E: passed, including cross-tenant registration/authentication/token rejection
• multi-node host verification: passed through [9/9], including config update notifications, listener reconnect, TTL fail-closed behavior, disabled tenant fail-closed, and restore recovery on both nodes
• low-budget connection churn run: 256 distinct routing keys, active pools <= 8, max open 5, budget 59, public anonymous signup/user/refresh 0 errors; post-run pg_stat_activity sample showed 8 churn tenant idle connections plus 1 active psql sampling query

Still explicitly out of scope/blocked:

• shared_instance_id mode remains rejected/unsafe until every relevant table and query is instance-scoped.
• This is still local validation, not a production soak test across real 10k independent databases or a long-running multi-region control-plane topology.

[xmh1011]

Additional hot-reload and production-risk review update (2026-07-10):

• Found and fixed a stale tenant metadata risk in the isolated-DB multi-tenant path: Postgres LISTEN/NOTIFY notifications can be missed while the listener connection is disconnected, so the local tenant metadata cache is now cleared after a successful listener reconnect. This forces later requests to reload control-plane tenant config instead of serving stale config until TTL.
• Added a focused integration regression for listener reconnect cache clearing after the listener backend is terminated.
• Strengthened the multi-node host PoC: it now runs two Auth processes with independent tenant caches, terminates both notification listener backends, updates tenant config during the disconnect window, and verifies both nodes observe the new config before tenant metadata TTL expiry.
• Updated the English and Chinese local multi-tenant design/audit docs to document this missed-notification behavior and the reconnect cache-clear requirement.

Latest verification from the working branch:

• DATABASE_URL=postgres://supabase_auth_admin:root@localhost:5541/postgres go test ./internal/multitenant -count=1
• DATABASE_URL=postgres://supabase_auth_admin:root@localhost:5541/postgres go test -race ./internal/multitenant -run 'TestStoreNotificationListener|TestStoreGetTenantStopsUsingCachedTenantAfterTTLWhenControlPlaneFails' -count=1
• bash -n poc/multitenant/verify-multinode-host.sh poc/multitenant/verify.sh poc/multitenant/run-host.sh poc/multitenant/verify-host.sh
• git diff --check
• make auth
• DATABASE_URL=postgres://supabase_auth_admin:root@localhost:5541/postgres GOTRUE_DB_DATABASE_URL=postgres://supabase_auth_admin:root@localhost:5541/postgres go test -p=1 -count=1 ./...
• enhanced multi-node host verification: passed through [10/10], including control-plane outage, TTL fail-closed behavior, listener reconnect after DB restart, missed notification during listener disconnect, disabled tenant fail-closed, and restore recovery on both nodes
• host PoC verify-host.sh: passed through [42/42] and PoC baseline completed

Production-readiness caveats remain unchanged:

• shared_instance_id mode remains blocked/rejected until all relevant tables and queries are instance-scoped.
• This is still local validation; it is not a long-running production soak test across real 10k independent tenant databases or multi-region control-plane partitions.