mirror of
https://github.com/multica-ai/multica.git
synced 2026-07-05 13:29:44 +02:00
1c91c2a3b2
* fix(security): scope DELETE/UpdateIssueStatus by workspace_id Add workspace_id to the WHERE clause of DeleteIssue, DeleteComment, DeleteProject, DeleteSkill, DeleteChatSession, and UpdateIssueStatus as SQL-layer defense-in-depth. Handler loaders (loadIssueForUser / loadSkillForUser / etc.) already enforce workspace membership today, so this is not patching a known live vuln. But the tenant invariant is currently a handler-layer guarantee — a future loader bypass or a new caller skipping the loader would be silently catastrophic. Making workspace_id part of the SQL identity collapses the trust surface to the schema itself: forging a sibling-workspace UUID becomes ErrNoRows instead of a cross-tenant write. Reference: incident #1661 (util.ParseUUID silent zero UUID returning 204 on a DELETE that matched zero rows) — same class of failure, prevented at a different layer. Scope: - 5 DELETE queries: issue, comment, project, skill, chat_session - 1 simple UPDATE: UpdateIssueStatus (2 narg, no SET ordering risk) - All callers updated (handlers, service, runtime sweeper fallback) Multi-narg UPDATE queries (UpdateIssue, UpdateProject, UpdateSkill, UpdateComment, UpdateChatSession*) are deferred to a follow-up to keep this change reviewable: each needs its narg pinning shifted and per-caller verification. sqlc was regenerated by hand (no local sqlc toolchain); CI's backend job is the authoritative compile check. * test(security): add workspace_scope_guard regression test Locks in the SQL-layer tenant guard added in this PR. For each of the 6 scoped queries (DeleteIssue, DeleteComment, DeleteProject, DeleteSkill, DeleteChatSession, UpdateIssueStatus), creates the resource in workspace A, invokes the query with a foreign workspace UUID, and asserts the row is untouched (0 rows affected with no error for :exec; pgx.ErrNoRows for :one). A future refactor that drops the workspace_id arg from any of these queries will now fail loudly instead of silently regressing. Includes a sanity sub-test that the in-workspace path still mutates, so a buggy guard that returns no-op for every call would not pass. Co-Authored-By: Claude Opus 4 <noreply@anthropic.com> --------- Co-authored-by: Tom Qiao <tomqiaozc@users.noreply.github.com> Co-authored-by: Claude Opus 4 <noreply@anthropic.com>
52 lines
1.5 KiB
SQL
52 lines
1.5 KiB
SQL
-- name: ListProjects :many
|
|
SELECT * FROM project
|
|
WHERE workspace_id = $1
|
|
AND (sqlc.narg('status')::text IS NULL OR status = sqlc.narg('status'))
|
|
AND (sqlc.narg('priority')::text IS NULL OR priority = sqlc.narg('priority'))
|
|
ORDER BY created_at DESC;
|
|
|
|
-- name: GetProject :one
|
|
SELECT * FROM project
|
|
WHERE id = $1;
|
|
|
|
-- name: GetProjectInWorkspace :one
|
|
SELECT * FROM project
|
|
WHERE id = $1 AND workspace_id = $2;
|
|
|
|
-- name: CreateProject :one
|
|
INSERT INTO project (
|
|
workspace_id, title, description, icon, status,
|
|
lead_type, lead_id, priority
|
|
) VALUES (
|
|
$1, $2, $3, $4, $5, $6, $7, $8
|
|
) RETURNING *;
|
|
|
|
-- name: UpdateProject :one
|
|
UPDATE project SET
|
|
title = COALESCE(sqlc.narg('title'), title),
|
|
description = sqlc.narg('description'),
|
|
icon = sqlc.narg('icon'),
|
|
status = COALESCE(sqlc.narg('status'), status),
|
|
priority = COALESCE(sqlc.narg('priority'), priority),
|
|
lead_type = sqlc.narg('lead_type'),
|
|
lead_id = sqlc.narg('lead_id'),
|
|
updated_at = now()
|
|
WHERE id = $1
|
|
RETURNING *;
|
|
|
|
-- name: DeleteProject :exec
|
|
-- Defense-in-depth: workspace_id is a SQL-layer tenant guard. See DeleteIssue.
|
|
DELETE FROM project WHERE id = $1 AND workspace_id = $2;
|
|
|
|
-- name: CountIssuesByProject :one
|
|
SELECT count(*) FROM issue
|
|
WHERE project_id = $1;
|
|
|
|
-- name: GetProjectIssueStats :many
|
|
SELECT project_id,
|
|
count(*)::bigint AS total_count,
|
|
count(*) FILTER (WHERE status IN ('done', 'cancelled'))::bigint AS done_count
|
|
FROM issue
|
|
WHERE project_id = ANY(sqlc.arg('project_ids')::uuid[])
|
|
GROUP BY project_id;
|