max / makenotwork

1.2 KB · 26 lines History Blame Raw

1	-- Single-use, DB-backed password reset tokens.
2	--
3	-- The reset flow was a stateless HMAC bound to the user's current password
4	-- hash: valid for the whole expiry window and replayable any number of times
5	-- until the password actually changed. Anyone who obtained the URL before the
6	-- user submitted it (browser history, referer, proxy/CDN logs) could reuse it.
7	--
8	-- This mirrors `login_tokens` exactly: the emailed token is random, only its
9	-- SHA-256 hash is stored, and consumption is a single `UPDATE ... WHERE
10	-- used_at IS NULL` so a replay (or a race) can never succeed twice.
11	CREATE TABLE IF NOT EXISTS password_reset_tokens (
12	id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
13	user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
14	token_hash VARCHAR(64) NOT NULL,
15	expires_at TIMESTAMPTZ NOT NULL,
16	used_at TIMESTAMPTZ,
17	created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
18	);
19
20	CREATE UNIQUE INDEX IF NOT EXISTS idx_password_reset_tokens_hash
21	ON password_reset_tokens(token_hash);
22	CREATE INDEX IF NOT EXISTS idx_password_reset_tokens_user
23	ON password_reset_tokens(user_id);
24	CREATE INDEX IF NOT EXISTS idx_password_reset_tokens_expires
25	ON password_reset_tokens(expires_at);
26