Janady13/missionmatch / db/migrations/0009_auth.sql

1-- +goose Up 2-- Phase 2 Authentication and Accounts. Run as the BYPASSRLS migrator role. 3-- 4-- Posture (see docs/STACK_DECISIONS.md section 7): the secret bearing tables (auth_credentials, 5-- auth_sessions, mfa_totp, mfa_recovery_codes, user_invites) live OUTSIDE RLS and the app role gets 6-- NO direct table grants on them. Every app access goes through a SECURITY DEFINER function owned by 7-- the migrator with a locked search_path, EXECUTE granted narrowly. That restores DB side default 8-- deny for the most sensitive data: the app role literally cannot SELECT a credential or session row, 9-- so a forgotten predicate cannot leak another org's secrets. provision_org is migrator only. 10-- 11-- Every per user table uses a COMPOSITE foreign key (organization_id, user_id) to users so the org on 12-- the row is pinned to the user's real org by the database, closing cross org credential binding. 13 14-- --------------------------------------------------------------------------- 15-- users: gain the MFA enrollment flag and a GLOBALLY unique email. Identity is one human, one 16-- account, one org, so login can resolve a user by email before the org context is known. The pre 17-- existing per org unique index is kept (it is implied by the global one but other code may name it). 18-- --------------------------------------------------------------------------- 19ALTER TABLE users ADD COLUMN mfa_enrolled boolean NOT NULL DEFAULT false; 20CREATE UNIQUE INDEX uq_users_global_email ON users (lower(email)); 21 22-- --------------------------------------------------------------------------- 23-- auth_credentials: one password credential per user. OUTSIDE RLS, no app table grants. The composite 24-- FK pins the org to the user's org; the global unique email reconciles with users so a second org 25-- cannot shadow the first. 26-- --------------------------------------------------------------------------- 27CREATE TABLE auth_credentials ( 28 user_id uuid PRIMARY KEY, 29 organization_id uuid NOT NULL, 30 email_normalized text NOT NULL, 31 password_hash text NOT NULL, 32 pepper_version int NOT NULL, 33 failed_streak int NOT NULL DEFAULT 0, 34 locked_until timestamptz, 35 password_changed_at timestamptz NOT NULL DEFAULT now(), 36 created_at timestamptz NOT NULL DEFAULT now(), 37 updated_at timestamptz NOT NULL DEFAULT now(), 38 CONSTRAINT uq_cred_email_global UNIQUE (email_normalized), 39 CONSTRAINT fk_cred_user_org FOREIGN KEY (organization_id, user_id) 40 REFERENCES users (organization_id, id) ON DELETE CASCADE 41); 42 43-- --------------------------------------------------------------------------- 44-- auth_sessions: opaque server side sessions, stored only as a token hash. OUTSIDE RLS, no app table 45-- grants. organization_id is a convenience copy; auth_validate_session JOINs users and returns the 46-- authoritative org, failing closed on a mismatch. 47-- --------------------------------------------------------------------------- 48CREATE TABLE auth_sessions ( 49 id uuid PRIMARY KEY DEFAULT gen_random_uuid(), 50 token_hash bytea NOT NULL UNIQUE, 51 user_id uuid NOT NULL, 52 organization_id uuid NOT NULL, 53 state text NOT NULL CHECK (state IN ('auth_pending', 'active')), 54 mfa_satisfied boolean NOT NULL DEFAULT false, 55 created_at timestamptz NOT NULL DEFAULT now(), 56 last_seen_at timestamptz NOT NULL DEFAULT now(), 57 idle_expires_at timestamptz NOT NULL, 58 absolute_expires_at timestamptz NOT NULL, 59 revoked_at timestamptz, 60 rotated_from uuid, 61 ip_address text, 62 user_agent text, 63 CONSTRAINT fk_session_user_org FOREIGN KEY (organization_id, user_id) 64 REFERENCES users (organization_id, id) ON DELETE CASCADE 65); 66CREATE INDEX idx_auth_sessions_user_live ON auth_sessions (user_id) WHERE revoked_at IS NULL; 67CREATE INDEX idx_auth_sessions_sweep ON auth_sessions (absolute_expires_at); 68 69-- --------------------------------------------------------------------------- 70-- mfa_totp: one TOTP secret per user, AES 256 GCM encrypted at rest with a key_version for rotation. 71-- last_used_step is the strictly monotonic replay marker. OUTSIDE RLS, no app table grants, reached 72-- only by the auth definers (MFA enroll and verify run pre org on an auth_pending session). 73-- --------------------------------------------------------------------------- 74CREATE TABLE mfa_totp ( 75 user_id uuid PRIMARY KEY, 76 organization_id uuid NOT NULL, 77 secret_ciphertext bytea NOT NULL, 78 secret_nonce bytea NOT NULL, 79 key_version int NOT NULL, 80 status text NOT NULL CHECK (status IN ('pending', 'active')), 81 last_used_step bigint NOT NULL DEFAULT 0, 82 created_at timestamptz NOT NULL DEFAULT now(), 83 activated_at timestamptz, 84 CONSTRAINT fk_mfa_user_org FOREIGN KEY (organization_id, user_id) 85 REFERENCES users (organization_id, id) ON DELETE CASCADE 86); 87 88-- --------------------------------------------------------------------------- 89-- mfa_recovery_codes: single use backup codes. code_hash is PBKDF2, lookup_tag is a non secret HMAC 90-- so a recovery attempt runs at most one PBKDF2 verify. OUTSIDE RLS, no app table grants. 91-- --------------------------------------------------------------------------- 92CREATE TABLE mfa_recovery_codes ( 93 id uuid PRIMARY KEY DEFAULT gen_random_uuid(), 94 user_id uuid NOT NULL, 95 organization_id uuid NOT NULL, 96 code_hash text NOT NULL, 97 pepper_version int NOT NULL, 98 lookup_tag bytea NOT NULL, 99 used_at timestamptz, 100 created_at timestamptz NOT NULL DEFAULT now(), 101 CONSTRAINT fk_recovery_user_org FOREIGN KEY (organization_id, user_id) 102 REFERENCES users (organization_id, id) ON DELETE CASCADE 103); 104CREATE INDEX idx_recovery_lookup_live ON mfa_recovery_codes (user_id, lookup_tag) WHERE used_at IS NULL; 105 106-- --------------------------------------------------------------------------- 107-- user_invites: single use, time boxed invite tokens (stored hashed) redeemed before the invitee has 108-- a session. OUTSIDE RLS, no app table grants. role bounds are enforced in the API and re asserted at 109-- accept; the CHECK permits owner only because the migrator provisioning path issues the owner's 110-- activation through this same table. 111-- --------------------------------------------------------------------------- 112CREATE TABLE user_invites ( 113 id uuid PRIMARY KEY DEFAULT gen_random_uuid(), 114 organization_id uuid NOT NULL, 115 email_normalized text NOT NULL, 116 invited_user_id uuid NOT NULL, 117 token_hash bytea NOT NULL UNIQUE, 118 role text NOT NULL CHECK (role IN ('owner', 'admin', 'recruiter', 'member', 'viewer')), 119 expires_at timestamptz NOT NULL, 120 accepted_at timestamptz, 121 created_by uuid NOT NULL, 122 created_at timestamptz NOT NULL DEFAULT now(), 123 CONSTRAINT fk_invite_user_org FOREIGN KEY (organization_id, invited_user_id) 124 REFERENCES users (organization_id, id) ON DELETE CASCADE 125); 126 127-- --------------------------------------------------------------------------- 128-- login_attempts: brute force counter. OUTSIDE RLS but the ONE auth table with a narrow app grant 129-- (no secrets). A row is written for known AND unknown emails so lock behavior is not an enumeration 130-- oracle. 131-- --------------------------------------------------------------------------- 132CREATE TABLE login_attempts ( 133 id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, 134 email_normalized text NOT NULL, 135 organization_id uuid, 136 ip_address text, 137 succeeded boolean NOT NULL, 138 reason text, 139 created_at timestamptz NOT NULL DEFAULT now() 140); 141CREATE INDEX idx_login_attempts_email ON login_attempts (email_normalized, created_at DESC); 142CREATE INDEX idx_login_attempts_ip ON login_attempts (ip_address, created_at DESC); 143 144-- =========================================================================== 145-- SECURITY DEFINER functions. Owned by the migrator (this script runs as migrator), search_path 146-- locked, EXECUTE revoked from PUBLIC below and granted narrowly. 147-- =========================================================================== 148 149-- +goose StatementBegin 150CREATE FUNCTION auth_lookup_login(p_email text) 151RETURNS TABLE ( 152 user_id uuid, organization_id uuid, role text, user_status text, 153 password_hash text, pepper_version int, mfa_enrolled boolean, 154 mfa_status text, mfa_secret_ciphertext bytea, mfa_secret_nonce bytea, 155 mfa_key_version int, last_used_step bigint 156) 157LANGUAGE sql SECURITY DEFINER SET search_path = pg_catalog, public AS $$ 158 SELECT u.id, u.organization_id, u.role, u.status, 159 c.password_hash, c.pepper_version, u.mfa_enrolled, 160 m.status, m.secret_ciphertext, m.secret_nonce, m.key_version, m.last_used_step 161 FROM users u 162 JOIN auth_credentials c ON c.user_id = u.id 163 LEFT JOIN mfa_totp m ON m.user_id = u.id 164 WHERE c.email_normalized = lower(p_email) 165 LIMIT 1; 166$$; 167-- +goose StatementEnd 168 169-- +goose StatementBegin 170CREATE FUNCTION auth_create_session( 171 p_token_hash bytea, p_user_id uuid, p_org_id uuid, p_state text, 172 p_mfa_satisfied boolean, p_idle_seconds int, p_absolute_seconds int, p_ip text, p_ua text) 173RETURNS TABLE (session_id uuid, idle_expires_at timestamptz, absolute_expires_at timestamptz) 174LANGUAGE sql SECURITY DEFINER SET search_path = pg_catalog, public AS $$ 175 INSERT INTO auth_sessions ( 176 token_hash, user_id, organization_id, state, mfa_satisfied, 177 idle_expires_at, absolute_expires_at, ip_address, user_agent) 178 VALUES ( 179 p_token_hash, p_user_id, p_org_id, p_state, p_mfa_satisfied, 180 now() + make_interval(secs => p_idle_seconds), 181 now() + make_interval(secs => p_absolute_seconds), p_ip, p_ua) 182 RETURNING id, idle_expires_at, absolute_expires_at; 183$$; 184-- +goose StatementEnd 185 186-- +goose StatementBegin 187-- The single atomic validate, slide, and revocation check. Returns the AUTHORITATIVE users.org (the 188-- JOIN, never the stored session copy) and fails closed on a session.org to users.org mismatch, so a 189-- moved org user's stale session is rejected, not silently re scoped. A revoked or expired row can 190-- never be slid because the guard and the update are one statement. The org is returned for both 191-- auth_pending and active sessions because the pre MFA enroll and verify flows need the user's own 192-- org for their definer writes; the request resolver grants tenant context ONLY when state is active 193-- and mfa_satisfied is true, and RunInTenantTx plus RLS still fail closed if that gate is ever missed. 194CREATE FUNCTION auth_validate_session(p_token_hash bytea, p_idle_seconds int) 195RETURNS TABLE ( 196 session_id uuid, user_id uuid, organization_id uuid, role text, 197 state text, mfa_satisfied boolean, user_status text) 198LANGUAGE plpgsql SECURITY DEFINER SET search_path = pg_catalog, public AS $$ 199#variable_conflict use_column 200BEGIN 201 RETURN QUERY 202 UPDATE auth_sessions s 203 SET last_seen_at = now(), 204 idle_expires_at = LEAST(now() + make_interval(secs => p_idle_seconds), s.absolute_expires_at) 205 FROM users u 206 WHERE s.token_hash = p_token_hash 207 AND s.user_id = u.id 208 AND s.revoked_at IS NULL 209 AND now() < s.absolute_expires_at 210 AND now() < s.idle_expires_at 211 AND u.status = 'active' 212 AND s.organization_id = u.organization_id 213 RETURNING s.id, s.user_id, u.organization_id, u.role, s.state, s.mfa_satisfied, u.status; 214END; 215$$; 216-- +goose StatementEnd 217 218-- +goose StatementBegin 219-- Privilege transition: revoke the old token and mint a new one carrying the same user and org, in 220-- one statement pair, so the pre transition token is invalid in the same request cycle. 221CREATE FUNCTION auth_rotate_session( 222 p_old_token_hash bytea, p_new_token_hash bytea, p_new_state text, p_mfa_satisfied boolean, 223 p_idle_seconds int, p_absolute_seconds int, p_ip text, p_ua text) 224RETURNS TABLE (session_id uuid, idle_expires_at timestamptz, absolute_expires_at timestamptz) 225LANGUAGE plpgsql SECURITY DEFINER SET search_path = pg_catalog, public AS $$ 226#variable_conflict use_column 227DECLARE v_old_id uuid; v_user uuid; v_org uuid; 228BEGIN 229 UPDATE auth_sessions SET revoked_at = now() 230 WHERE token_hash = p_old_token_hash AND revoked_at IS NULL 231 RETURNING id, user_id, organization_id INTO v_old_id, v_user, v_org; 232 IF v_user IS NULL THEN 233 RETURN; 234 END IF; 235 RETURN QUERY 236 INSERT INTO auth_sessions ( 237 token_hash, user_id, organization_id, state, mfa_satisfied, 238 idle_expires_at, absolute_expires_at, rotated_from, ip_address, user_agent) 239 VALUES ( 240 p_new_token_hash, v_user, v_org, p_new_state, p_mfa_satisfied, 241 now() + make_interval(secs => p_idle_seconds), 242 now() + make_interval(secs => p_absolute_seconds), v_old_id, p_ip, p_ua) 243 RETURNING id, idle_expires_at, absolute_expires_at; 244END; 245$$; 246-- +goose StatementEnd 247 248-- +goose StatementBegin 249CREATE FUNCTION auth_revoke_session(p_token_hash bytea) 250RETURNS integer 251LANGUAGE plpgsql SECURITY DEFINER SET search_path = pg_catalog, public AS $$ 252DECLARE n integer; 253BEGIN 254 UPDATE auth_sessions SET revoked_at = now() 255 WHERE token_hash = p_token_hash AND revoked_at IS NULL; 256 GET DIAGNOSTICS n = ROW_COUNT; 257 RETURN n; 258END; 259$$; 260-- +goose StatementEnd 261 262-- +goose StatementBegin 263-- Cascade revoke. p_except_token_hash keeps the caller's current session alive on a password change. 264CREATE FUNCTION auth_revoke_user_sessions(p_user_id uuid, p_except_token_hash bytea) 265RETURNS integer 266LANGUAGE plpgsql SECURITY DEFINER SET search_path = pg_catalog, public AS $$ 267DECLARE n integer; 268BEGIN 269 UPDATE auth_sessions SET revoked_at = now() 270 WHERE user_id = p_user_id AND revoked_at IS NULL 271 AND (p_except_token_hash IS NULL OR token_hash <> p_except_token_hash); 272 GET DIAGNOSTICS n = ROW_COUNT; 273 RETURN n; 274END; 275$$; 276-- +goose StatementEnd 277 278-- +goose StatementBegin 279CREATE FUNCTION auth_set_credential(p_user_id uuid, p_org_id uuid, p_email text, p_hash text, p_pepper_version int) 280RETURNS void 281LANGUAGE sql SECURITY DEFINER SET search_path = pg_catalog, public AS $$ 282 INSERT INTO auth_credentials (user_id, organization_id, email_normalized, password_hash, pepper_version) 283 VALUES (p_user_id, p_org_id, lower(p_email), p_hash, p_pepper_version) 284 ON CONFLICT (user_id) DO UPDATE 285 SET password_hash = EXCLUDED.password_hash, 286 pepper_version = EXCLUDED.pepper_version, 287 password_changed_at = now(), 288 updated_at = now(); 289$$; 290-- +goose StatementEnd 291 292-- +goose StatementBegin 293-- Read a credential by user id (for the authenticated password change path, where email is unknown 294-- but the validated session gives the user id). 295CREATE FUNCTION auth_get_credential(p_user_id uuid) 296RETURNS TABLE (organization_id uuid, email_normalized text, password_hash text, pepper_version int) 297LANGUAGE sql SECURITY DEFINER SET search_path = pg_catalog, public AS $$ 298 SELECT organization_id, email_normalized, password_hash, pepper_version 299 FROM auth_credentials WHERE user_id = p_user_id; 300$$; 301-- +goose StatementEnd 302 303-- +goose StatementBegin 304-- Enroll writes a pending secret. The ON CONFLICT update is scoped to rows that are still pending, so 305-- it can NEVER overwrite an already active authenticator. That closes the account takeover where an 306-- attacker holding the password re enrolls their own secret from a pre MFA session; a genuine reset 307-- must go through the authenticated admin auth_mfa_reset path. Returns true only when a pending row 308-- was written. 309CREATE FUNCTION auth_mfa_enroll(p_user_id uuid, p_org_id uuid, p_ciphertext bytea, p_nonce bytea, p_key_version int) 310RETURNS boolean 311LANGUAGE plpgsql SECURITY DEFINER SET search_path = pg_catalog, public AS $$ 312DECLARE n integer; 313BEGIN 314 INSERT INTO mfa_totp (user_id, organization_id, secret_ciphertext, secret_nonce, key_version, status) 315 VALUES (p_user_id, p_org_id, p_ciphertext, p_nonce, p_key_version, 'pending') 316 ON CONFLICT (user_id) DO UPDATE 317 SET secret_ciphertext = EXCLUDED.secret_ciphertext, 318 secret_nonce = EXCLUDED.secret_nonce, 319 key_version = EXCLUDED.key_version, 320 status = 'pending', 321 last_used_step = 0, 322 created_at = now(), 323 activated_at = NULL 324 WHERE mfa_totp.status = 'pending'; 325 GET DIAGNOSTICS n = ROW_COUNT; 326 RETURN n > 0; 327END; 328$$; 329-- +goose StatementEnd 330 331-- +goose StatementBegin 332CREATE FUNCTION auth_mfa_activate(p_user_id uuid) 333RETURNS boolean 334LANGUAGE plpgsql SECURITY DEFINER SET search_path = pg_catalog, public AS $$ 335DECLARE n integer; 336BEGIN 337 UPDATE mfa_totp SET status = 'active', activated_at = now() 338 WHERE user_id = p_user_id AND status = 'pending'; 339 GET DIAGNOSTICS n = ROW_COUNT; 340 IF n = 0 THEN 341 RETURN false; 342 END IF; 343 UPDATE users SET mfa_enrolled = true, updated_at = now() WHERE id = p_user_id; 344 RETURN true; 345END; 346$$; 347-- +goose StatementEnd 348 349-- +goose StatementBegin 350CREATE FUNCTION auth_mfa_get(p_user_id uuid) 351RETURNS TABLE (secret_ciphertext bytea, secret_nonce bytea, key_version int, status text, last_used_step bigint) 352LANGUAGE sql SECURITY DEFINER SET search_path = pg_catalog, public AS $$ 353 SELECT secret_ciphertext, secret_nonce, key_version, status, last_used_step 354 FROM mfa_totp WHERE user_id = p_user_id; 355$$; 356-- +goose StatementEnd 357 358-- +goose StatementBegin 359-- Monotonic advance: only moves the marker forward, so a consumed or earlier step cannot be reused. 360CREATE FUNCTION auth_mfa_advance_step(p_user_id uuid, p_step bigint) 361RETURNS integer 362LANGUAGE plpgsql SECURITY DEFINER SET search_path = pg_catalog, public AS $$ 363DECLARE n integer; 364BEGIN 365 UPDATE mfa_totp SET last_used_step = p_step 366 WHERE user_id = p_user_id AND last_used_step < p_step; 367 GET DIAGNOSTICS n = ROW_COUNT; 368 RETURN n; 369END; 370$$; 371-- +goose StatementEnd 372 373-- +goose StatementBegin 374CREATE FUNCTION auth_mfa_reset(p_user_id uuid) 375RETURNS void 376LANGUAGE plpgsql SECURITY DEFINER SET search_path = pg_catalog, public AS $$ 377BEGIN 378 DELETE FROM mfa_totp WHERE user_id = p_user_id; 379 DELETE FROM mfa_recovery_codes WHERE user_id = p_user_id; 380 UPDATE users SET mfa_enrolled = false, updated_at = now() WHERE id = p_user_id; 381END; 382$$; 383-- +goose StatementEnd 384 385-- +goose StatementBegin 386CREATE FUNCTION auth_recovery_store(p_user_id uuid, p_org_id uuid, p_hashes text[], p_versions int[], p_tags bytea[]) 387RETURNS void 388LANGUAGE plpgsql SECURITY DEFINER SET search_path = pg_catalog, public AS $$ 389BEGIN 390 DELETE FROM mfa_recovery_codes WHERE user_id = p_user_id; 391 INSERT INTO mfa_recovery_codes (user_id, organization_id, code_hash, pepper_version, lookup_tag) 392 SELECT p_user_id, p_org_id, h, v, t 393 FROM unnest(p_hashes, p_versions, p_tags) AS x(h, v, t); 394END; 395$$; 396-- +goose StatementEnd 397 398-- +goose StatementBegin 399CREATE FUNCTION auth_recovery_lookup(p_user_id uuid, p_tag bytea) 400RETURNS TABLE (id uuid, code_hash text, pepper_version int) 401LANGUAGE sql SECURITY DEFINER SET search_path = pg_catalog, public AS $$ 402 SELECT id, code_hash, pepper_version 403 FROM mfa_recovery_codes 404 WHERE user_id = p_user_id AND lookup_tag = p_tag AND used_at IS NULL 405 LIMIT 1; 406$$; 407-- +goose StatementEnd 408 409-- +goose StatementBegin 410-- Atomic single use claim: a second concurrent claim of the same row affects zero rows. 411CREATE FUNCTION auth_recovery_claim(p_id uuid) 412RETURNS integer 413LANGUAGE plpgsql SECURITY DEFINER SET search_path = pg_catalog, public AS $$ 414DECLARE n integer; 415BEGIN 416 UPDATE mfa_recovery_codes SET used_at = now() WHERE id = p_id AND used_at IS NULL; 417 GET DIAGNOSTICS n = ROW_COUNT; 418 RETURN n; 419END; 420$$; 421-- +goose StatementEnd 422 423-- +goose StatementBegin 424CREATE FUNCTION auth_create_invite( 425 p_org_id uuid, p_email text, p_invited_user_id uuid, p_token_hash bytea, 426 p_role text, p_expires_seconds int, p_created_by uuid) 427RETURNS uuid 428LANGUAGE sql SECURITY DEFINER SET search_path = pg_catalog, public AS $$ 429 INSERT INTO user_invites ( 430 organization_id, email_normalized, invited_user_id, token_hash, role, expires_at, created_by) 431 VALUES ( 432 p_org_id, lower(p_email), p_invited_user_id, p_token_hash, p_role, 433 now() + make_interval(secs => p_expires_seconds), p_created_by) 434 RETURNING id; 435$$; 436-- +goose StatementEnd 437 438-- +goose StatementBegin 439-- Accept asserts token.org == users.org AND the invite email matches the users row, takes the org 440-- from the authoritative users row, creates the credential, and activates the user, all in one tx. 441-- Any mismatch returns no rows so a tampered or replayed invite cannot plant a cross org login. 442CREATE FUNCTION auth_accept_invite(p_token_hash bytea, p_password_hash text, p_pepper_version int) 443RETURNS TABLE (user_id uuid, organization_id uuid) 444LANGUAGE plpgsql SECURITY DEFINER SET search_path = pg_catalog, public AS $$ 445#variable_conflict use_column 446DECLARE v_inv user_invites%ROWTYPE; v_user users%ROWTYPE; 447BEGIN 448 SELECT * INTO v_inv FROM user_invites 449 WHERE token_hash = p_token_hash AND accepted_at IS NULL AND now() < expires_at; 450 IF v_inv.id IS NULL THEN 451 RETURN; 452 END IF; 453 SELECT * INTO v_user FROM users WHERE id = v_inv.invited_user_id; 454 IF v_user.id IS NULL 455 OR v_user.organization_id <> v_inv.organization_id 456 OR lower(v_user.email) <> v_inv.email_normalized THEN 457 RETURN; 458 END IF; 459 INSERT INTO auth_credentials (user_id, organization_id, email_normalized, password_hash, pepper_version) 460 VALUES (v_user.id, v_user.organization_id, lower(v_user.email), p_password_hash, p_pepper_version) 461 ON CONFLICT (user_id) DO UPDATE 462 SET password_hash = EXCLUDED.password_hash, pepper_version = EXCLUDED.pepper_version, updated_at = now(); 463 UPDATE user_invites SET accepted_at = now() WHERE id = v_inv.id; 464 UPDATE users SET status = 'active', updated_at = now() WHERE id = v_user.id; 465 RETURN QUERY SELECT v_user.id, v_user.organization_id; 466END; 467$$; 468-- +goose StatementEnd 469 470-- +goose StatementBegin 471-- Genesis: migrator only. Creates the org, the owner user (invited, MFA not yet enrolled), and the 472-- owner's one time activation invite, in one tx. EXECUTE is revoked from app and PUBLIC below. 473CREATE FUNCTION provision_org( 474 p_org_name text, p_org_slug text, p_owner_email text, p_owner_name text, 475 p_token_hash bytea, p_invite_expires_seconds int) 476RETURNS TABLE (organization_id uuid, owner_user_id uuid, invite_id uuid) 477LANGUAGE plpgsql SECURITY DEFINER SET search_path = pg_catalog, public AS $$ 478#variable_conflict use_column 479DECLARE v_org uuid; v_user uuid; v_inv uuid; 480BEGIN 481 INSERT INTO organizations (name, slug) VALUES (p_org_name, p_org_slug) RETURNING id INTO v_org; 482 INSERT INTO users (organization_id, email, full_name, role, status, mfa_enrolled) 483 VALUES (v_org, p_owner_email, p_owner_name, 'owner', 'invited', false) 484 RETURNING id INTO v_user; 485 INSERT INTO user_invites (organization_id, email_normalized, invited_user_id, token_hash, role, expires_at, created_by) 486 VALUES (v_org, lower(p_owner_email), v_user, p_token_hash, 'owner', 487 now() + make_interval(secs => p_invite_expires_seconds), v_user) 488 RETURNING id INTO v_inv; 489 RETURN QUERY SELECT v_org, v_user, v_inv; 490END; 491$$; 492-- +goose StatementEnd 493 494-- =========================================================================== 495-- Grants. The app role gets ONLY EXECUTE on the auth definers plus the login_attempts counter. By 496-- default CREATE FUNCTION grants EXECUTE to PUBLIC, so each function is revoked from PUBLIC first. 497-- =========================================================================== 498GRANT SELECT, INSERT ON login_attempts TO missionmatch_app; 499 500REVOKE EXECUTE ON FUNCTION auth_lookup_login(text) FROM PUBLIC; 501REVOKE EXECUTE ON FUNCTION auth_create_session(bytea, uuid, uuid, text, boolean, int, int, text, text) FROM PUBLIC; 502REVOKE EXECUTE ON FUNCTION auth_validate_session(bytea, int) FROM PUBLIC; 503REVOKE EXECUTE ON FUNCTION auth_rotate_session(bytea, bytea, text, boolean, int, int, text, text) FROM PUBLIC; 504REVOKE EXECUTE ON FUNCTION auth_revoke_session(bytea) FROM PUBLIC; 505REVOKE EXECUTE ON FUNCTION auth_revoke_user_sessions(uuid, bytea) FROM PUBLIC; 506REVOKE EXECUTE ON FUNCTION auth_set_credential(uuid, uuid, text, text, int) FROM PUBLIC; 507REVOKE EXECUTE ON FUNCTION auth_get_credential(uuid) FROM PUBLIC; 508REVOKE EXECUTE ON FUNCTION auth_mfa_enroll(uuid, uuid, bytea, bytea, int) FROM PUBLIC; 509REVOKE EXECUTE ON FUNCTION auth_mfa_activate(uuid) FROM PUBLIC; 510REVOKE EXECUTE ON FUNCTION auth_mfa_get(uuid) FROM PUBLIC; 511REVOKE EXECUTE ON FUNCTION auth_mfa_advance_step(uuid, bigint) FROM PUBLIC; 512REVOKE EXECUTE ON FUNCTION auth_mfa_reset(uuid) FROM PUBLIC; 513REVOKE EXECUTE ON FUNCTION auth_recovery_store(uuid, uuid, text[], int[], bytea[]) FROM PUBLIC; 514REVOKE EXECUTE ON FUNCTION auth_recovery_lookup(uuid, bytea) FROM PUBLIC; 515REVOKE EXECUTE ON FUNCTION auth_recovery_claim(uuid) FROM PUBLIC; 516REVOKE EXECUTE ON FUNCTION auth_create_invite(uuid, text, uuid, bytea, text, int, uuid) FROM PUBLIC; 517REVOKE EXECUTE ON FUNCTION auth_accept_invite(bytea, text, int) FROM PUBLIC; 518REVOKE EXECUTE ON FUNCTION provision_org(text, text, text, text, bytea, int) FROM PUBLIC; 519 520GRANT EXECUTE ON FUNCTION auth_lookup_login(text) TO missionmatch_app; 521GRANT EXECUTE ON FUNCTION auth_create_session(bytea, uuid, uuid, text, boolean, int, int, text, text) TO missionmatch_app; 522GRANT EXECUTE ON FUNCTION auth_validate_session(bytea, int) TO missionmatch_app; 523GRANT EXECUTE ON FUNCTION auth_rotate_session(bytea, bytea, text, boolean, int, int, text, text) TO missionmatch_app; 524GRANT EXECUTE ON FUNCTION auth_revoke_session(bytea) TO missionmatch_app; 525GRANT EXECUTE ON FUNCTION auth_revoke_user_sessions(uuid, bytea) TO missionmatch_app; 526GRANT EXECUTE ON FUNCTION auth_set_credential(uuid, uuid, text, text, int) TO missionmatch_app; 527GRANT EXECUTE ON FUNCTION auth_get_credential(uuid) TO missionmatch_app; 528GRANT EXECUTE ON FUNCTION auth_mfa_enroll(uuid, uuid, bytea, bytea, int) TO missionmatch_app; 529GRANT EXECUTE ON FUNCTION auth_mfa_activate(uuid) TO missionmatch_app; 530GRANT EXECUTE ON FUNCTION auth_mfa_get(uuid) TO missionmatch_app; 531GRANT EXECUTE ON FUNCTION auth_mfa_advance_step(uuid, bigint) TO missionmatch_app; 532GRANT EXECUTE ON FUNCTION auth_mfa_reset(uuid) TO missionmatch_app; 533GRANT EXECUTE ON FUNCTION auth_recovery_store(uuid, uuid, text[], int[], bytea[]) TO missionmatch_app; 534GRANT EXECUTE ON FUNCTION auth_recovery_lookup(uuid, bytea) TO missionmatch_app; 535GRANT EXECUTE ON FUNCTION auth_recovery_claim(uuid) TO missionmatch_app; 536GRANT EXECUTE ON FUNCTION auth_create_invite(uuid, text, uuid, bytea, text, int, uuid) TO missionmatch_app; 537GRANT EXECUTE ON FUNCTION auth_accept_invite(bytea, text, int) TO missionmatch_app; 538-- provision_org is intentionally NOT granted to missionmatch_app; the migrator executes it as owner. 539 540-- +goose Down 541DROP FUNCTION IF EXISTS provision_org(text, text, text, text, bytea, int); 542DROP FUNCTION IF EXISTS auth_accept_invite(bytea, text, int); 543DROP FUNCTION IF EXISTS auth_create_invite(uuid, text, uuid, bytea, text, int, uuid); 544DROP FUNCTION IF EXISTS auth_recovery_claim(uuid); 545DROP FUNCTION IF EXISTS auth_recovery_lookup(uuid, bytea); 546DROP FUNCTION IF EXISTS auth_recovery_store(uuid, uuid, text[], int[], bytea[]); 547DROP FUNCTION IF EXISTS auth_mfa_reset(uuid); 548DROP FUNCTION IF EXISTS auth_mfa_advance_step(uuid, bigint); 549DROP FUNCTION IF EXISTS auth_mfa_get(uuid); 550DROP FUNCTION IF EXISTS auth_mfa_activate(uuid); 551DROP FUNCTION IF EXISTS auth_mfa_enroll(uuid, uuid, bytea, bytea, int); 552DROP FUNCTION IF EXISTS auth_get_credential(uuid); 553DROP FUNCTION IF EXISTS auth_set_credential(uuid, uuid, text, text, int); 554DROP FUNCTION IF EXISTS auth_revoke_user_sessions(uuid, bytea); 555DROP FUNCTION IF EXISTS auth_revoke_session(bytea); 556DROP FUNCTION IF EXISTS auth_rotate_session(bytea, bytea, text, boolean, int, int, text, text); 557DROP FUNCTION IF EXISTS auth_validate_session(bytea, int); 558DROP FUNCTION IF EXISTS auth_create_session(bytea, uuid, uuid, text, boolean, int, int, text, text); 559DROP FUNCTION IF EXISTS auth_lookup_login(text); 560DROP TABLE IF EXISTS login_attempts; 561DROP TABLE IF EXISTS user_invites; 562DROP TABLE IF EXISTS mfa_recovery_codes; 563DROP TABLE IF EXISTS mfa_totp; 564DROP TABLE IF EXISTS auth_sessions; 565DROP TABLE IF EXISTS auth_credentials; 566DROP INDEX IF EXISTS uq_users_global_email; 567ALTER TABLE users DROP COLUMN IF EXISTS mfa_enrolled; 568