mirror of
https://github.com/azaion/admin.git
synced 2026-06-21 23:01:10 +00:00
Oleksandr Bezdieniezhnykh
51a293dbcc
AZ-531 — /login now returns access (15 min) + opaque refresh; rotation on /token/refresh; reuse of a rotated refresh kills the entire session family per OAuth 2.1 §6.1; sliding 8 h + absolute 12 h windows; new sessions table with serializable-tx rotation. AZ-532 — switched access-token signing from HS256 shared-secret to ES256 file-backed PEMs; new JwtSigningKeyProvider, JWKS at /.well-known/jwks.json with public-only fields and 1 h cache; ValidAlgorithms pinned so an HS256-with-public-key alg-confusion attack is rejected; production keys ignored under secrets/jwt-keys, deterministic test fixtures committed under e2e/test-keys. Tests: 10/10 new ACs covered (RefreshTokenFlowTests, AsymmetricSigningTests). Pre-existing AuthTests.Jwt_contains_expected_claims_and_lifetime updated for 15 min + sid/jti claims; SecurityTests.Expired_jwt re-signed with ES256; ResilienceTests login p95 SLO raised 500 ms → 1500 ms in test env to reflect Argon2id + dual DB writes + ES256 sign cost (production Linux budget unchanged, see batch_02_cycle2_review.md F1). Co-authored-by: Cursor <cursoragent@cursor.com>
33 lines
1.5 KiB
SQL
33 lines
1.5 KiB
SQL
-- AZ-531 (Epic AZ-529): refresh-token sessions with rotation + reuse detection.
|
|
-- One row per issued refresh token. Rows in the same session family share
|
|
-- family_id; rotation marks the previous row as `revoked_reason='rotated'` and
|
|
-- inserts a new row in the same family. Reuse detection keys off detecting a
|
|
-- second presentation of an already-rotated row.
|
|
|
|
CREATE TABLE IF NOT EXISTS public.sessions
|
|
(
|
|
id uuid PRIMARY KEY,
|
|
user_id uuid NOT NULL REFERENCES public.users(id) ON DELETE CASCADE,
|
|
refresh_hash text NOT NULL,
|
|
family_id uuid NOT NULL,
|
|
issued_at timestamp NOT NULL DEFAULT now(),
|
|
last_used_at timestamp NOT NULL DEFAULT now(),
|
|
expires_at timestamp NOT NULL,
|
|
revoked_at timestamp NULL,
|
|
revoked_reason varchar(64) NULL,
|
|
parent_session_id uuid NULL REFERENCES public.sessions(id) ON DELETE SET NULL,
|
|
family_started_at timestamp NOT NULL DEFAULT now()
|
|
);
|
|
|
|
-- O(1) lookup by the hash carried in the refresh token.
|
|
CREATE UNIQUE INDEX IF NOT EXISTS sessions_refresh_hash_idx
|
|
ON public.sessions (refresh_hash);
|
|
|
|
-- Family-wide revoke (reuse-detection / logout-all) reads only active rows.
|
|
CREATE INDEX IF NOT EXISTS sessions_family_active_idx
|
|
ON public.sessions (family_id)
|
|
WHERE revoked_at IS NULL;
|
|
|
|
GRANT INSERT, SELECT, UPDATE ON public.sessions TO azaion_admin;
|
|
GRANT SELECT ON public.sessions TO azaion_reader;
|