rfc(data): pilot PostgreSQL RLS with Prisma and Hyperdrive

[tiankaima]

Decision context

Prisma ORM 7.8 can query RLS-protected PostgreSQL tables, but its current schema/migration DSL does not model policies. Prisma Next's new @@rls support is Early Access and is not a reason to migrate this production app immediately. Policies therefore require reviewed SQL migrations.

Cloudflare Hyperdrive uses transaction-mode pooling. Request identity cannot be stored with session-global SET; it must be set with transaction-local set_config(..., true) and every protected query must run on that same transaction/connection.

Applying RLS to every table at once would break the current shared Prisma-client/service pattern and would conflate user-owned, collaborative, public catalog, auth, importer, and audit data.

RFC and pilot

1. Classify every model by owner and access class: user-owned, collaborative/public, auth-provider, importer/catalog, admin/audit.
2. Define distinct migration-owner, runtime-app, auth-service, loader, and admin roles. Runtime roles must not own tables or have BYPASSRLS; use FORCE ROW LEVEL SECURITY where appropriate.
3. Add withUserDbContext(userId, tx => ...) that starts a transaction, sets transaction-local context, and passes only the transaction client to protected services.
4. Pilot on one clear user-owned domain such as Todo with raw SQL policies, service plumbing, and production-like Prisma/Hyperdrive tests.
5. Roll out by table family only after correctness and latency are measured. Auth tables, append-only audit, and public importer/catalog data need explicit separate decisions rather than policy-count targets.

Acceptance criteria

• With no user context, protected access is default-deny.
• User A cannot read/write User B rows; WITH CHECK rejects forged ownership.
• Runtime role is non-owner/no-BYPASSRLS; service/loader/admin roles have documented least privilege.
• All protected queries, including nested operations, run inside the same context transaction and pool reuse does not leak identity.
• SQL migrations replay in shadow/CI databases and introspection drift is understood.
• Pilot records query count, transaction duration, and Hyperdrive latency before broader rollout.
• The final model matrix explicitly explains any table not using RLS.

[tiankaima]

2026-07-20 repository audit: do not enable RLS globally yet

I audited the current Prisma/Hyperdrive/runtime shape and validated the core PostgreSQL primitive in a disposable database. The safe decision is a staged pilot, not a one-release all-table rollout.

Model classification

Current schema: 63 Prisma models plus four implicit join tables.

• Importer/catalog (34): academic metadata, schedules/exams, teachers, bus catalog, and StaticImportState. These are predominantly public-read + loader/admin-write; table/column grants are usually a better baseline than USING (true) RLS.
• Clearly user-owned (7): DashboardLinkClick, DashboardLinkPin, Upload, UploadPending, Todo, BusUserPreference, HomeworkCompletion. These are candidates for phased owner RLS, with cross-table visibility exceptions for uploads.
• Collaborative/public (7): Description, Homework, HomeworkAuditLog, DescriptionEdit, Comment, CommentAttachment, CommentReaction. These require separate anonymous/authenticated/softban/admin/shared-write policies; owner = userId is not sufficient.
• Auth-provider (13): User, VerifiedEmail, Account, Session, VerificationToken, Passkey, Authenticator, OAuthClient, DeviceCode, OAuthRefreshToken, OAuthAccessToken, OAuthConsent, Jwks. Better Auth queries several of these before an end-user principal exists, so they need a separate auth-service database role/client before any end-user RLS.
• Admin/audit (2): AuditLog, UserSuspension. Audit should be runtime insert-only + admin read; suspension has both enforcement-read and admin-write semantics.
• Implicit tables: _ScheduleTeachers, _SectionAdminClasses, _SectionTeachers are catalog joins; _UserCalendarSections is user-owned and would require an explicit raw-SQL policy.

Current blockers

1. src/lib/db/prisma.ts and src/lib/db/prisma-adapter.ts expose one shared Prisma/base connection. There is no migrator/runtime/auth/loader role boundary.
2. Authentication queries the database before authUser exists. Injecting a request user into the global client would lock Better Auth out of its own tables.
3. CI uses the PostgreSQL superuser for migration, seed, app, and tests. Superusers bypass RLS, so the current green suite could not prove isolation.
4. Production migration, static sync, auth cleanup, and app runtime reuse DATABASE_URL; the repository defines no CREATE ROLE, GRANT, non-owner, or NO BYPASSRLS contract.
5. Hyperdrive transaction pooling makes transaction-local context mandatory. Protected queries must execute inside the same $transaction after set_config(..., true); a session-global SET is unsafe. A cache-disabled protected binding/connection also needs an explicit decision.
6. Enabling a policy and first deploying context plumbing in the same release is not atomic. The context wrapper must deploy first; policy activation must be a later migration.
7. Existing nested/long-lived transactions in loader, comments, homework, subscriptions, uploads, and admin flows prevent a mechanical global wrapper because Prisma TransactionClient cannot start another $transaction.

Primitive validation

In a disposable PostgreSQL database I created a migration-owner role plus a non-owner/no-BYPASSRLS app role, enabled and forced RLS on a Todo-shaped table, and used repository Prisma 7.8 with $transaction + transaction-local set_config.

Verified:

• no context returns no rows;
• user A sees only A and user B sees only B;
• forged-owner insert is rejected by WITH CHECK;
• concurrent A/B contexts do not cross;
• the context disappears after the transaction.

This proves the direct PostgreSQL/Prisma primitive, not the still-missing production role/Hyperdrive deployment contract. Prisma 7.8 can query protected tables but policies still belong in reviewed raw SQL migrations; Prisma Next @@rls is Early Access and is not a production migration reason.

Required delivery order

1. Infrastructure/role PR: separate migrator owner, runtime non-owner/no-bypass, auth service, loader, cleanup/admin-maintenance identities; split workflow secrets; make CI app tests use the runtime role and assert role attributes. Decide a protected/cache-disabled Hyperdrive binding.
2. Inactive plumbing PR: deploy withUserDbContext(userId, tx => ...), pass only TransactionClient through the Todo service, and measure query/transaction/Hyperdrive latency. No policy enabled yet.
3. Activation PR: a later raw-SQL migration enables + forces Todo RLS with operation-specific USING/WITH CHECK policies scoped TO the runtime role.
4. Isolation tests: no-context deny, A/B CRUD, forged owner, concurrent/pool reuse, upsert/returning/batch, owner/superuser negative controls, fresh migration replay, production-like Hyperdrive/cache status, rollback and kill-switch runbook.
5. Roll out by the model classes above only after the pilot meets correctness and latency gates.

Accordingly, I am not opening or merging a PR that turns on RLS for all business tables under the current shared-role architecture. The next safe code artifact is the role/CI contract, followed by inactive Todo transaction plumbing; policy activation must remain a separate release.

Primary references: PostgreSQL row security, PostgreSQL CREATE POLICY, Prisma database feature matrix, Hyperdrive architecture.

[tiankaima]

2026-07-22 implementation decision after Context7/primary-doc review

I am moving this from RFC-only to executable staged delivery. The following constraints are now fixed:

• Stay on Prisma 7.8 and use reviewed raw SQL migrations. Prisma Next @@rls / policy_* is Early Access for the future Prisma 8 line, so it is not an acceptable production dependency today.
• Do not implement identity through a Prisma client extension. Prisma documents that extension code which calls client-level methods from an interactive transaction can use a different connection and escape the current transaction.
• Use an explicit interactive $transaction, call transaction-local set_config('app.user_id', ..., true), and route every protected query through that exact TransactionClient.
• Hyperdrive transaction-mode pooling returns/reset connections after the transaction. We must not use session-global SET, and must not hold request-wide transactions merely to preserve identity. Protected feature operations should have short transaction boundaries.
• Use a non-owner, NOBYPASSRLS runtime role in CI and production. Migration/loader/auth-maintenance identities remain separate. CI must prove it is not accidentally testing as PostgreSQL superuser.
• Permission-sensitive traffic should use a cache-disabled Hyperdrive binding when the operational binding is provisioned. Hyperdrive currently only caches queries whose functions are immutable, so current_setting-based policy evaluation should not be cached, but an explicit uncached binding is still the safer authorization/read-after-write contract.

Delivery slices

1. Add the role/CI contract and assertions, plus a same-transaction withUserDbContext primitive.
2. Route the complete Todo service (REST, GraphQL, MCP and dashboard consumers) through that primitive without changing its API authorization semantics.
3. Activate Todo RLS with USING + WITH CHECK; test default-deny, A/B CRUD isolation, forged ownership, pool reuse and context reset using a real non-owner role.
4. Extend the exact pattern to the six remaining clearly user-owned models.
5. Handle collaborative, auth-provider, audit, and importer/catalog families with their own role/policy matrices; public catalog tables should primarily use grants, not meaningless USING (true) policy-count inflation.

This means “全面应用” is an enforceable per-family rollout with real non-bypass tests. It does not mean enabling 63 tables in one migration while the application still has a single owner client.

[tiankaima]

2026-07-22 execution progress and remaining policy exceptions

The staged implementation is now concrete:

• refactor(db): route Todo through transaction-local user context #596 merged the transaction-local user context and routed the complete Todo surface through it.
• feat(db): activate Todo row-level security #598 is the separate activation PR: ENABLE/FORCE ROW LEVEL SECURITY, owner USING + WITH CHECK, and a real non-owner/NOBYPASSRLS CI job. Keeping this separate from refactor(db): route Todo through transaction-local user context #596 preserves deployment ordering.
• refactor(db): route personal preferences through RLS context #599 prepares the next pure-owner family (DashboardLinkClick, DashboardLinkPin, BusUserPreference) without activating policies yet. It also removes the nested dashboard transaction and serializes operations on the exact RLS transaction client.

Two previously classified “clearly user-owned” areas need non-owner-only SELECT matrices before activation:

1. Upload / UploadPending: published files are read through comments/profile/download flows, while pending and owner-management operations are private. A blanket owner policy would break valid public attachment reads. The policy needs separate SELECT predicates for published/referenced uploads and strict owner INSERT/UPDATE/DELETE; pending rows remain owner-only.
2. HomeworkCompletion: writes are owner-only, but completion timestamps currently contribute to public user profile contribution graphs. A blanket owner SELECT policy would silently remove those public aggregates. We must either formalize public aggregate reads (preferably through a narrow security-definer aggregate function/view) or remove completion activity from public profiles before owner-only SELECT.

Therefore “全面应用” continues as enforced family policies, not policy-count inflation. Catalog/importer data will use role grants; auth-provider tables require an auth-service identity because Better Auth queries before an end-user context exists.

[github-actions]

🎉 This issue has been resolved in version 1.76.0 🎉

The release is available on GitHub release

Your semantic-release bot 📦🚀

[github-actions]

🎉 This issue has been resolved in version 1.77.0 🎉

The release is available on GitHub release

Your semantic-release bot 📦🚀