g1t/services/identity/migrations/0032_two_factor.sql
| 1 | -- Two-factor authentication for accounts (src/two_factor.rs): an |
| 2 | -- authenticator app's TOTP secret (RFC 6238), recovery codes, and the |
| 3 | -- sign-ins waiting for a code. Every timestamp is RFC 3339 UTC. |
| 4 | |
| 5 | -- One per account. `secret` is sealed with IDENTITY_KEY, bound to the |
| 6 | -- account's id (g1t_secrets::Sealer). Until `enabled_at` is set it is an |
| 7 | -- enrolment in progress, which a code from the app confirms. |
| 8 | CREATE TABLE IF NOT EXISTS two_factor ( |
| 9 | user_id TEXT PRIMARY KEY REFERENCES users (id) ON DELETE CASCADE, |
| 10 | secret TEXT NOT NULL, |
| 11 | enabled_at TEXT, |
| 12 | created_at TEXT NOT NULL, |
| 13 | -- The last 30-second step a code was accepted for: a code is never |
| 14 | -- accepted twice, nor one older than the last used. |
| 15 | last_step INTEGER NOT NULL DEFAULT 0 |
| 16 | ); |
| 17 | |
| 18 | -- Ten single-use codes for when the app is lost, each kept as its |
| 19 | -- SHA-256. Made again, all ten are replaced. |
| 20 | CREATE TABLE IF NOT EXISTS two_factor_recovery ( |
| 21 | user_id TEXT NOT NULL REFERENCES users (id) ON DELETE CASCADE, |
| 22 | code_hash TEXT NOT NULL, |
| 23 | used_at TEXT, |
| 24 | PRIMARY KEY (user_id, code_hash) |
| 25 | ); |
| 26 | |
| 27 | -- A sign-in that gave the right password and waits for a code. Its id is |
| 28 | -- the SHA-256 of the token the site holds for it; it lasts ten minutes |
| 29 | -- and a few wrong codes. |
| 30 | CREATE TABLE IF NOT EXISTS two_factor_challenges ( |
| 31 | id TEXT PRIMARY KEY, |
| 32 | user_id TEXT NOT NULL REFERENCES users (id) ON DELETE CASCADE, |
| 33 | expires_at TEXT NOT NULL, |
| 34 | attempts INTEGER NOT NULL DEFAULT 0 |
| 35 | ); |
| 36 | CREATE INDEX IF NOT EXISTS two_factor_challenges_user ON two_factor_challenges (user_id); |