security(db): replace production Hyperdrive postgres origin with cache-disabled RLS runtime role

[tiankaima]

Severity

P0 deployment blocker for further RLS rollout.

Verified current state (2026-07-22)

wrangler hyperdrive get for the production binding reports:

• configuration: life-ustc-production
• origin user: postgres
• query caching: enabled

The repository only creates life_ustc_runtime LOGIN NOINHERIT NOBYPASSRLS inside CI. It does not provision or bind that role in production. If the production postgres role is superuser/BYPASSRLS, PostgreSQL bypasses every current policy, including FORCE RLS.

Cloudflare documents that Hyperdrive caching is enabled by default, does not invalidate cached reads after writes, and recommends a separate cache-disabled binding/client for authentication, sessions, permissions, admin state, and read-after-write paths. Hyperdrive uses transaction pooling, so RLS identity must remain transaction-local.

References:

• https://developers.cloudflare.com/hyperdrive/concepts/query-caching/
• https://developers.cloudflare.com/hyperdrive/concepts/connection-pooling/
• rfc(data): pilot PostgreSQL RLS with Prisma and Hyperdrive #550 and roadmap(data): complete PostgreSQL RLS rollout by access family #601

Required production design

• migrator: owns schema/migrations only; never used by Worker requests.
• app_runtime: LOGIN NOINHERIT NOBYPASSRLS, least-privilege grants, cache-disabled Hyperdrive binding for auth/RLS/admin/fresh reads.
• auth_service: separate least-privilege identity for Better Auth tables queried before an end-user context exists.
• importer / maintenance: separate identities for static loader and cleanup workflows.
• optional cached catalog binding: public immutable/tolerably stale reads only.

Do not copy the current CI grants for User, AuditLog, or UserSuspension into production: they expose sensitive columns too broadly. Use narrow views/functions or dedicated service roles.

Acceptance criteria

• Run a production-origin audit recording only current_user, rolsuper, rolbypassrls, rolinherit, and whether the role owns protected tables.
• Provision app/auth/importer/migrator identities with reviewed exact grants.
• Create a cache-disabled Hyperdrive config for app/auth/RLS/admin/fresh reads and bind separate Prisma/Better Auth clients.
• Update the existing cached binding so it cannot access user/auth/RLS-sensitive tables.
• Add a production smoke probe that proves missing context default-denies and two user contexts isolate rows without exposing row data.
• Verify revoked sessions/permissions and read-after-write behavior through the cache-disabled binding.
• Rotate the previous postgres Hyperdrive credential after cutover.
• Block further RLS family activation until this issue is complete.