mirror of
https://github.com/truespar/sentio.git
synced 2026-11-17 08:16:11 +00:00
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.
64 lines
2.4 KiB
SQL
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 $$;
|