| 1 | -- SPDX-License-Identifier: AGPL-3.0-or-later |
| 2 | -- |
| 3 | -- RFC 8628 (OAuth 2.0 Device Authorization Grant) state. Each row is a |
| 4 | -- single in-flight authorization request from a CLI / non-browser |
| 5 | -- client; the row's lifetime is bounded by `expires_at` (default 15 |
| 6 | -- minutes from issue). |
| 7 | -- |
| 8 | -- device_code_hash holds sha256(raw_device_code); the raw value is |
| 9 | -- returned to the client exactly once and never stored. user_code is |
| 10 | -- the short, human-typeable identifier shown on the CLI ("ABCD-EFGH") |
| 11 | -- and entered by the user on the verification page; we store it |
| 12 | -- plaintext because it's intentionally low-entropy and the row is |
| 13 | -- garbage-collected on expiry. |
| 14 | -- |
| 15 | -- A row's terminal state is one of: |
| 16 | -- * approved_at IS NOT NULL → exchange yields an access token (a row |
| 17 | -- in user_tokens, joined via issued_token_id). |
| 18 | -- * denied_at IS NOT NULL → /login/oauth/access_token returns |
| 19 | -- access_denied; row stays until expires_at for forensics. |
| 20 | -- * expires_at < now() with no approval/denial → invalid_grant on |
| 21 | -- exchange; same forensics window. |
| 22 | -- |
| 23 | -- last_polled_at + interval_seconds back the `slow_down` enforcement |
| 24 | -- that RFC 8628 §3.5 mandates so misbehaving clients can't busy-poll. |
| 25 | |
| 26 | -- +goose Up |
| 27 | CREATE TABLE device_authorizations ( |
| 28 | id bigserial PRIMARY KEY, |
| 29 | device_code_hash bytea NOT NULL UNIQUE, |
| 30 | user_code text NOT NULL UNIQUE, |
| 31 | client_id text NOT NULL, |
| 32 | scopes text[] NOT NULL DEFAULT ARRAY[]::text[], |
| 33 | user_id bigint REFERENCES users(id) ON DELETE CASCADE, |
| 34 | approved_at timestamptz, |
| 35 | denied_at timestamptz, |
| 36 | issued_token_id bigint REFERENCES user_tokens(id) ON DELETE SET NULL, |
| 37 | interval_seconds integer NOT NULL DEFAULT 5, |
| 38 | expires_at timestamptz NOT NULL, |
| 39 | last_polled_at timestamptz, |
| 40 | created_at timestamptz NOT NULL DEFAULT now(), |
| 41 | |
| 42 | CONSTRAINT device_authorizations_hash_size CHECK (octet_length(device_code_hash) = 32), |
| 43 | CONSTRAINT device_authorizations_user_code_length CHECK (char_length(user_code) BETWEEN 4 AND 32) |
| 44 | ); |
| 45 | |
| 46 | CREATE INDEX device_authorizations_expires_at_idx |
| 47 | ON device_authorizations (expires_at); |
| 48 | |
| 49 | -- Pending-only lookup index — user_code is meaningful only while the row |
| 50 | -- is awaiting approval; once approved/denied/expired the index can be |
| 51 | -- skipped on the user-entry path. |
| 52 | CREATE INDEX device_authorizations_pending_user_code_idx |
| 53 | ON device_authorizations (user_code) |
| 54 | WHERE approved_at IS NULL AND denied_at IS NULL; |
| 55 | |
| 56 | -- +goose Down |
| 57 | DROP TABLE IF EXISTS device_authorizations; |
| 58 |