Security: Cross-workspace IDOR in warehouse, survey, website, monitor, feed, and AI endpoints

[lighthousekeeper1212]

Summary

Multiple cross-workspace Insecure Direct Object Reference (IDOR) vulnerabilities exist across tRPC routers. The workspaceProcedure and workspaceAdminProcedure verify workspace membership but individual handlers query Prisma by sub-resource ID without including workspaceId in the WHERE clause.

Root Cause

When handlers fetch sub-resources (databases, surveys, websites, monitors, etc.), they query by the sub-resource's own ID without verifying it belongs to the caller's workspace.

Affected Areas (~31 endpoints)

CRITICAL: Warehouse Database Credential Exposure + SQL Execution

In src/server/trpc/routers/insights/warehouse.ts:

• warehouse.database.sync (line 63): findUnique({ where: { id: input.id } }) - exposes connectionUri
• warehouse.database.upsert update path (line 110): update({ where: { id: input.id } })
• warehouse.database.delete (line 142): delete({ where: { id: input.id } })
• warehouse.query.execute (line 253): Fetches ANY workspace's database by ID, then executes user-supplied SQL on it
• warehouse.table.upsert update path (line 172): update({ where: { id: input.id } })
• warehouse.table.delete (line 200): delete({ where: { id: input.id } })

Secure comparison - warehouse.database.list (line 41) correctly uses where: { workspaceId: input.workspaceId }

HIGH: Survey Data Leakage

• survey.allResultCount: Uses groupBy with NO where clause (all workspaces)
• survey.resultList/survey.count: Query by surveyId only

HIGH: Website Analytics Cross-Workspace

• website.onlineCount/stats/geoStats/pageviews/metrics: Query by websiteId only

MEDIUM: Monitor/Feed/AI/Cohorts

• monitor.dataMetrics/getStatus/testNotifyScript: Query by monitorId only
• feed.fetchEventsByCursor/archiveEvent/unarchiveEvent/clearAllArchivedEvents: Query by channelId only
• ai.generateSurvey: Updates survey by surveyId only
• insights.cohorts.upsert/delete: Query by ID only

Impact

• Any workspace member can read database connection URIs (credentials) of other workspaces
• Any workspace member can execute SQL queries against other workspaces' external databases
• Cross-workspace survey data exposure, website analytics leakage, monitor manipulation

Suggested Fix

Add workspaceId to all Prisma WHERE clauses:

// Before (vulnerable):
const db = await prisma.warehouseDatabase.findUnique({
  where: { id: input.id },
});

// After (fixed):
const db = await prisma.warehouseDatabase.findFirst({
  where: { id: input.id, workspaceId: input.workspaceId },
});
if (!db) throw new Error('Not found');

Apply this pattern to all affected routers.

CWE-639: Authorization Bypass Through User-Controlled Key

---

Found during security research by Lighthouse. Please consider enabling GitHub Private Vulnerability Reporting for responsible disclosure.