max / makenotwork

2.2 KB · 35 lines History Blame Raw

1	-- OAuth maturation: scopes + rotating refresh tokens (closes MT finding S13).
2	--
3	-- Authorization codes now carry the granted scope so /token can mint a
4	-- correctly-scoped access token. Empty scope = a legacy client (the desktop
5	-- sync apps via synckit-client) that sends no `scope` and expects a full sync
6	-- token; the DEFAULT '' preserves that for codes minted before this migration
7	-- (in-flight during deploy). Only a non-empty, explicitly-requested scope opts
8	-- into the new userinfo-scoped token + refresh-token flow.
9	ALTER TABLE oauth_authorization_codes
10	ADD COLUMN IF NOT EXISTS scope TEXT NOT NULL DEFAULT '';
11
12	-- Rotating, scoped refresh tokens. The RP stores one of these (never an access
13	-- token). Each use rotates: the presented token is marked used and a new token
14	-- is minted with the same chain_id. Presenting an already-used token is a theft
15	-- signal -> the whole chain is revoked. Tokens are stored as SHA-256 hashes,
16	-- never plaintext; lookup is by hash.
17	CREATE TABLE IF NOT EXISTS oauth_refresh_tokens (
18	id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
19	token_hash TEXT NOT NULL UNIQUE, -- SHA-256 hex of the opaque token
20	app_id UUID NOT NULL REFERENCES sync_apps(id) ON DELETE CASCADE,
21	user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
22	key TEXT NOT NULL, -- SDK key carried into refreshed access tokens
23	scope TEXT NOT NULL,
24	chain_id UUID NOT NULL, -- stable across rotations; revoke-by-chain
25	expires_at TIMESTAMPTZ NOT NULL,
26	used_at TIMESTAMPTZ, -- set when rotated/consumed (single-use)
27	revoked_at TIMESTAMPTZ, -- chain-level or per-token revoke
28	issued_after TIMESTAMPTZ NOT NULL DEFAULT NOW(), -- compared to users.jwt_invalidated_at
29	created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
30	);
31
32	CREATE INDEX IF NOT EXISTS idx_oauth_refresh_chain ON oauth_refresh_tokens(chain_id);
33	CREATE INDEX IF NOT EXISTS idx_oauth_refresh_user ON oauth_refresh_tokens(user_id);
34	CREATE INDEX IF NOT EXISTS idx_oauth_refresh_expires ON oauth_refresh_tokens(expires_at);
35