max / makenotwork

561 B · 11 lines History Blame Raw

1	-- Mark a session row's role so the "log out everywhere" sweep
2	-- (`delete_all_sessions_for_user`) can also catch sessions stuck in the
3	-- 2FA-pending intermediate state — a phisher who has the password but not
4	-- the TOTP code holds an authenticated-pending session that previously was
5	-- session-storage-only and invisible to user_sessions sweeps.
6	ALTER TABLE user_sessions
7	ADD COLUMN kind TEXT NOT NULL DEFAULT 'active'
8	CHECK (kind IN ('active', 'pending_2fa'));
9
10	CREATE INDEX idx_user_sessions_kind ON user_sessions (kind) WHERE kind <> 'active';
11