g1t/services/identity/migrations/0019_user_emails.sql
| 1 | -- Many email addresses per account, one of them primary. Every timestamp |
| 2 | -- is RFC 3339 UTC. See src/emails.rs and src/security.rs. |
| 3 | -- |
| 4 | -- users.email stays, as a copy of the primary address that identity keeps |
| 5 | -- in step, and users.email_verified_at as the primary's verified_at: other |
| 6 | -- services and older queries read them, and "the account is confirmed" |
| 7 | -- still means exactly that the primary is. user_emails is the source of |
| 8 | -- truth for every address. |
| 9 | |
| 10 | CREATE TABLE IF NOT EXISTS user_emails ( |
| 11 | id TEXT PRIMARY KEY, |
| 12 | user_id TEXT NOT NULL REFERENCES users (id) ON DELETE CASCADE, |
| 13 | -- Trimmed and lowercased: what uniqueness and lookups use. |
| 14 | email TEXT NOT NULL, |
| 15 | -- As the person typed it, for showing. |
| 16 | display TEXT NOT NULL, |
| 17 | -- Null until a link sent to the address is followed. |
| 18 | verified_at TEXT, |
| 19 | -- When a confirmation link was last sent, to space out resends. |
| 20 | sent_at TEXT, |
| 21 | created_at TEXT NOT NULL |
| 22 | ); |
| 23 | -- A confirmed address belongs to one account. Anyone may add an address |
| 24 | -- they have not confirmed; the first account to confirm it keeps it, and |
| 25 | -- everyone else's unconfirmed claim to it is dropped (src/emails.rs). |
| 26 | CREATE UNIQUE INDEX IF NOT EXISTS user_emails_verified ON user_emails (email) WHERE verified_at IS NOT NULL; |
| 27 | CREATE UNIQUE INDEX IF NOT EXISTS user_emails_per_user ON user_emails (user_id, email); |
| 28 | CREATE INDEX IF NOT EXISTS user_emails_email ON user_emails (email); |
| 29 | |
| 30 | -- The primary address: where account mail and password resets go. |
| 31 | ALTER TABLE users ADD COLUMN primary_email_id TEXT; |
| 32 | -- A second confirmed address that also gets security notices. Null: the |
| 33 | -- primary only. |
| 34 | ALTER TABLE users ADD COLUMN backup_email_id TEXT; |
| 35 | -- Commits g1t makes for the person on the web use their noreply address |
| 36 | -- (<id suffix>+<username>@users.noreply.g1t.sh) instead of the primary. |
| 37 | -- On by default, so no address is ever published without asking. |
| 38 | ALTER TABLE users ADD COLUMN private_email INTEGER NOT NULL DEFAULT 1; |
| 39 | -- Refuse pushes whose commits carry one of the person's private addresses. |
| 40 | -- Stored now; enforced by repos once it asks (see docs). |
| 41 | ALTER TABLE users ADD COLUMN block_private_pushes INTEGER NOT NULL DEFAULT 0; |
| 42 | |
| 43 | -- Which address a confirmation link is for. Null on links sent before |
| 44 | -- this migration: those confirm the primary. |
| 45 | ALTER TABLE email_tokens ADD COLUMN email_id TEXT; |
| 46 | |
| 47 | -- When the session's owner last proved who they are (signed in, or typed |
| 48 | -- their password again). Sensitive changes need it to be recent |
| 49 | -- (src/security.rs). Null on older sessions: never recent. |
| 50 | ALTER TABLE sessions ADD COLUMN authenticated_at TEXT; |
| 51 | |
| 52 | -- The addresses accounts have now. users.email has been unique, so no two |
| 53 | -- rows collide on the confirmed index. |
| 54 | INSERT OR IGNORE INTO user_emails (id, user_id, email, display, verified_at, created_at) |
| 55 | SELECT 'eml_' || lower(hex(randomblob(13))), id, lower(trim(email)), trim(email), email_verified_at, created_at |
| 56 | FROM users |
| 57 | WHERE email IS NOT NULL AND trim(email) <> ''; |
| 58 | |
| 59 | UPDATE users SET primary_email_id = ( |
| 60 | SELECT user_emails.id FROM user_emails |
| 61 | WHERE user_emails.user_id = users.id AND user_emails.email = lower(trim(users.email)) |
| 62 | ) |
| 63 | WHERE email IS NOT NULL AND trim(email) <> ''; |
| 64 | |
| 65 | -- Uniqueness moves to confirmed addresses (above): two accounts may both |
| 66 | -- have an unconfirmed address, and then neither has it yet. |
| 67 | DROP INDEX IF EXISTS users_email; |
| 68 | |
| 69 | -- Every place an account is made (password, invite, GitHub) inserts into |
| 70 | -- users with an email; this gives that address its row and makes it the |
| 71 | -- primary, so none of them has to know about user_emails. An account made |
| 72 | -- with a confirmed address (GitHub's) also wins it from anyone's |
| 73 | -- unconfirmed claim. An address another account has confirmed fails the |
| 74 | -- insert with a UNIQUE error, which those places report as taken. |
| 75 | CREATE TRIGGER IF NOT EXISTS users_primary_email AFTER INSERT ON users |
| 76 | WHEN NEW.email IS NOT NULL AND trim(NEW.email) <> '' |
| 77 | BEGIN |
| 78 | SELECT RAISE(ABORT, 'UNIQUE constraint failed: user_emails.email') |
| 79 | WHERE EXISTS ( |
| 80 | SELECT 1 FROM user_emails |
| 81 | WHERE email = lower(trim(NEW.email)) AND verified_at IS NOT NULL AND user_id <> NEW.id |
| 82 | ); |
| 83 | UPDATE users SET email = NULL, email_verified_at = NULL, primary_email_id = NULL |
| 84 | WHERE NEW.email_verified_at IS NOT NULL AND id <> NEW.id |
| 85 | AND primary_email_id IN ( |
| 86 | SELECT id FROM user_emails WHERE email = lower(trim(NEW.email)) AND verified_at IS NULL |
| 87 | ); |
| 88 | DELETE FROM user_emails |
| 89 | WHERE NEW.email_verified_at IS NOT NULL AND user_id <> NEW.id |
| 90 | AND email = lower(trim(NEW.email)) AND verified_at IS NULL; |
| 91 | INSERT INTO user_emails (id, user_id, email, display, verified_at, created_at) |
| 92 | VALUES ('eml_' || lower(hex(randomblob(13))), NEW.id, lower(trim(NEW.email)), trim(NEW.email), |
| 93 | NEW.email_verified_at, NEW.created_at); |
| 94 | UPDATE users SET email = lower(trim(NEW.email)), primary_email_id = ( |
| 95 | SELECT id FROM user_emails WHERE user_id = NEW.id AND email = lower(trim(NEW.email)) |
| 96 | ) WHERE id = NEW.id; |
| 97 | END; |
| 98 | |
| 99 | -- What happened to an account's security: addresses added, confirmed, |
| 100 | -- removed or made primary, passwords changed. Shown to the person as their |
| 101 | -- security log, and to staff. Never another person's address in `detail`. |
| 102 | CREATE TABLE IF NOT EXISTS security_events ( |
| 103 | id TEXT PRIMARY KEY, |
| 104 | user_id TEXT NOT NULL REFERENCES users (id) ON DELETE CASCADE, |
| 105 | -- email_added, email_verified, email_removed, primary_email_changed, |
| 106 | -- backup_email_changed, email_privacy_changed, password_changed. |
| 107 | kind TEXT NOT NULL, |
| 108 | -- The address concerned, or what changed, in words. |
| 109 | detail TEXT, |
| 110 | -- Who did it: the person (null), or staff, by email. |
| 111 | staff TEXT, |
| 112 | -- Why staff did it. |
| 113 | reason TEXT, |
| 114 | created_at TEXT NOT NULL |
| 115 | ); |
| 116 | CREATE INDEX IF NOT EXISTS security_events_user ON security_events (user_id, created_at); |
| 117 | |
| 118 | -- Guessing passwords, and asking for mail, slowed down (src/throttle.rs). |
| 119 | -- `key` such as `signin.account:usr_…`, `signin.client:<sha256 of the IP>` |
| 120 | -- or `reset.email:<sha256 of the address>`. |
| 121 | CREATE TABLE IF NOT EXISTS auth_throttle ( |
| 122 | key TEXT PRIMARY KEY, |
| 123 | -- Failures (or requests) since window_start. |
| 124 | hits INTEGER NOT NULL, |
| 125 | window_start TEXT NOT NULL, |
| 126 | -- Nothing more is tried for this key until then. Null: not locked. |
| 127 | locked_until TEXT, |
| 128 | -- When the account's owner was last told it was locked. |
| 129 | notified_at TEXT |
| 130 | ); |
| 131 | CREATE INDEX IF NOT EXISTS auth_throttle_window ON auth_throttle (window_start); |