Files
sentio/migrations/003_tenant_isolation.sql
Gustav Weslien e76194d52e security: close review pass-1 findings — 409 on username conflicts, fail-closed bootstrap gate, CI, docs
Review pass 1 (architectural) follow-ups on top of the previously
addressed CRITICAL create/inbound-route IDORs:

- #9 smtp-credential create: unique-violation (23505) now maps to
  SentioError::Conflict -> ApiError::Conflict -> HTTP 409 instead of an
  opaque 500; message names the taken username.
- #6 bootstrap-key gate now fails CLOSED: a database error during the
  startup check refuses to start (was: debug! + continue, letting a
  transient outage defeat the gate). SENTIO_ALLOW_BOOTSTRAP_KEY=1
  remains the explicit recovery hatch.
- #7 README documents the refuse-to-start behavior, the recovery hatch,
  and the manual SQL rotation path (API rotation is impossible when the
  server won't bind).
- #8 migration 003 raises a NOTICE per renamed credential so operators
  see which SMTP usernames stop authenticating (breaking change).
- #5 new GitHub Actions workflow (ci.yml): postgres service container,
  migrations + full workspace tests + explicit tenant-isolation suite —
  the regression tests previously skipped silently without DATABASE_URL
  and nothing ran them.
- #11 tests clean up their tenant rows (cleanup_tenant) and a unit test
  pins the handler-level guard contract: mismatched path tenant must
  serialize as 404 (concrete assert on the response status).
- migration 004 extends error_events.error_type CHECK with 'conflict'.

cargo test --workspace --all-targets: 744 passed, 0 failed.
2026-10-15 18:09:27 +02:00

64 lines
2.4 KiB
SQL

-- 003: Cross-tenant isolation hardening
--
-- Bug: `smtp_credentials.username` was only unique per tenant
-- (UNIQUE(tenant_id, username)), while the SMTP AUTH path looks credentials
-- up by username alone (there is no tenant context on an unauthenticated
-- SMTP connection — the lookup is what establishes the session tenant).
-- Two tenants could therefore both create a credential named "agent", and
-- AUTH would match whichever row the database returned first, granting one
-- tenant's privileges to the other.
--
-- Fix: make the username globally unique. Credential creation APIs should
-- use tenant-namespaced usernames (e.g. "tenant-slug.bot@domain") when the
-- same human-readable name is wanted in two tenants.
--
-- The migration is idempotent: safe to re-run after a partial or manual
-- intervention (duplicates no longer exist, constraints already in place).
-- Resolve cross-tenant duplicate usernames by prefixing each duplicate
-- with its tenant id: globally unique, stable, and reversible. The
-- `migration_` prefix keeps the renamed row distinguishable from a literal
-- credential someone actually named "<uuid>.<name>" and makes accidental
-- re-prefixing impossible on re-run (already-prefixed names are unique).
DO $$
DECLARE
dup RECORD;
BEGIN
IF EXISTS (
SELECT 1 FROM pg_constraint
WHERE conname = 'smtp_credentials_username_key'
) THEN
-- Already applied; nothing to do.
RETURN;
END IF;
FOR dup IN
SELECT username
FROM smtp_credentials
GROUP BY username
HAVING COUNT(DISTINCT tenant_id) > 1
LOOP
-- BREAKING (documented in README): renamed usernames stop
-- authenticating until SMTP clients are updated to the new value.
RAISE NOTICE 'migration 003: renaming duplicate smtp credential username % (prev name will no longer authenticate)', dup.username;
UPDATE smtp_credentials
SET username = 'migration_003_' || tenant_id::text || '.' || username
WHERE username = dup.username;
END LOOP;
END $$;
ALTER TABLE smtp_credentials
DROP CONSTRAINT IF EXISTS smtp_credentials_tenant_id_username_key;
-- Idempotent unique constraint on username.
DO $$
BEGIN
IF NOT EXISTS (
SELECT 1 FROM pg_constraint
WHERE conname = 'smtp_credentials_username_key'
) THEN
ALTER TABLE smtp_credentials
ADD CONSTRAINT smtp_credentials_username_key UNIQUE (username);
END IF;
END $$;