mosaic/stack
1
0
Fork 0
Code Issues 10 Pull Requests Actions Packages Projects Releases 2 Wiki Activity

Add RLS policies to auth tables with FORCE enforcement #350

New Issue
Closed
opened 2026-02-07 17:07:17 +00:00 by jason.woltje · 0 comments
jason.woltje commented 2026-02-07 17:07:17 +00:00
Owner
Copy Link

Phase 1a - Security Foundations

Problem

The accounts, sessions, and verifications tables have NO Row-Level Security policies. Additionally, all 23 existing RLS-enabled tables only use ENABLE ROW LEVEL SECURITY without FORCE, meaning Prisma (connecting as table owner) silently bypasses all policies. Tenant isolation is application-level only.

Requirements

  1. Add RLS policies to accounts and sessions tables scoped by user_id = current_user_id()
  2. Add FORCE ROW LEVEL SECURITY to these tables so the table owner is also subject to policies
  3. Add owner bypass policy (TO mosaic USING (true)) so Prisma migrations and BetterAuth internal operations still work
  4. Verify verifications table doesn't need user-scoped RLS (tokens are ephemeral, no user_id column)

Implementation Notes

  • New Prisma migration: add_auth_rls_policies
  • Reuse existing helper functions: current_user_id(), is_workspace_member(), is_workspace_admin() from migration 20260129221004_add_rls_policies
  • Must be deployed AFTER the RLS context interceptor is wired up, or existing queries will break
  • Test: User A cannot query User B's Account/Session rows

Files

  • apps/api/prisma/migrations/[timestamp]_add_auth_rls/migration.sql (new)

Acceptance Criteria

  • RLS policies on accounts and sessions tables
  • FORCE ROW LEVEL SECURITY on both tables
  • Owner bypass policies for migration compatibility
  • Integration tests verify cross-user isolation
  • Existing auth flows (login, session refresh) still work

Dependencies

  • Blocked by: RLS context interceptor issue
  • Blocks: User credential model (needs RLS pattern established)

Refs #346

## Phase 1a - Security Foundations ### Problem The accounts, sessions, and verifications tables have NO Row-Level Security policies. Additionally, all 23 existing RLS-enabled tables only use ENABLE ROW LEVEL SECURITY without FORCE, meaning Prisma (connecting as table owner) silently bypasses all policies. Tenant isolation is application-level only. ### Requirements 1. Add RLS policies to accounts and sessions tables scoped by user_id = current_user_id() 2. Add FORCE ROW LEVEL SECURITY to these tables so the table owner is also subject to policies 3. Add owner bypass policy (TO mosaic USING (true)) so Prisma migrations and BetterAuth internal operations still work 4. Verify verifications table doesn't need user-scoped RLS (tokens are ephemeral, no user_id column) ### Implementation Notes - New Prisma migration: add_auth_rls_policies - Reuse existing helper functions: current_user_id(), is_workspace_member(), is_workspace_admin() from migration 20260129221004_add_rls_policies - Must be deployed AFTER the RLS context interceptor is wired up, or existing queries will break - Test: User A cannot query User B's Account/Session rows ### Files - apps/api/prisma/migrations/[timestamp]_add_auth_rls/migration.sql (new) ### Acceptance Criteria - [ ] RLS policies on accounts and sessions tables - [ ] FORCE ROW LEVEL SECURITY on both tables - [ ] Owner bypass policies for migration compatibility - [ ] Integration tests verify cross-user isolation - [ ] Existing auth flows (login, session refresh) still work ### Dependencies - Blocked by: RLS context interceptor issue - Blocks: User credential model (needs RLS pattern established) Refs #346
jason.woltje added this to the M9-CredentialSecurity (0.0.9) milestone 2026-02-07 17:07:17 +00:00
jason.woltje added the securitydatabasedatabasep0 labels 2026-02-07 17:07:17 +00:00
Sign in to join this conversation.
No Branch/Tag Specified
Branches Tags
Labels
Clear labels
ai
AI/LLM integration
 api
Backend/API work
 api
auth
Authentication
 database
database
Database/schema work
 devops
Docker/deployment
 docs
Documentation
 frontend
graph
knowledge-module
migration
Data migration
 orchestrator
Orchestrator service (apps/orchestrator/)
 p0
Critical priority
 p1
High priority
 p2
Medium priority
 p3
Low priority
 performance
Performance work
 phase-1
phase-2
phase-3
phase-4
phase-5
plugin
MoltBot plugin work
 search
security
Security-related
 setup
Initial setup
 testing
Testing/QA
 web
Frontend work
No Label database database p0 security
Milestone
No items
No Milestone M9-CredentialSecurity (0.0.9)
Projects
Clear projects
No project
Assignees
Clear assignees
jason.woltje
No Assignees
1 Participants
Notifications
Due Date
No due date set.
Dependencies

No dependencies set.

Reference: mosaic/stack#350
Delete Branch "%!s()"

Deleting a branch is permanent. Although the deleted branch may continue to exist for a short time before it actually gets removed, it CANNOT be undone in most cases. Continue?