mirror of
https://github.com/btouchard/ackify-ce.git
synced 2026-02-28 10:48:47 -06:00
Benjamin
68426bc882
- Implement PKCE (Proof Key for Code Exchange) with S256 method - Add crypto/pkce module with code verifier and challenge generation - Modify OAuth flow to include code_challenge in authorization requests - Update HandleCallback to validate code_verifier during token exchange - Extend session lifetime from 7 to 30 days - Add comprehensive unit tests for PKCE functions - Maintain backward compatibility with fallback for non-PKCE sessions - Add detailed logging for OAuth flow with PKCE tracking PKCE enhances security by preventing authorization code interception attacks, as recommended by OAuth 2.1 and OIDC standards. feat: add encrypted refresh token storage with automatic cleanup - Add oauth_sessions table for storing encrypted refresh tokens - Implement AES-256-GCM encryption for refresh tokens using cookie secret - Create OAuth session repository with full CRUD operations - Add SessionWorker for automatic cleanup of expired sessions - Configure cleanup to run every 24h for sessions older than 37 days - Modify OAuth flow to store refresh tokens after successful authentication - Track client IP and user agent for session security validation - Link OAuth sessions to user sessions via session ID - Add comprehensive encryption tests with security validations - Integrate SessionWorker into server lifecycle with graceful shutdown This enables persistent OAuth sessions with secure token storage, reducing the need for frequent re-authentication from 7 to 30 days.
30 lines
1.6 KiB
SQL
30 lines
1.6 KiB
SQL
-- SPDX-License-Identifier: AGPL-3.0-or-later
|
|
|
|
-- Table for storing OAuth refresh tokens securely
|
|
CREATE TABLE IF NOT EXISTS oauth_sessions (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
session_id TEXT NOT NULL UNIQUE, -- Gorilla session ID
|
|
user_sub TEXT NOT NULL, -- OAuth user ID (sub claim)
|
|
refresh_token_encrypted BYTEA NOT NULL, -- AES-256-GCM encrypted refresh token
|
|
access_token_expires_at TIMESTAMPTZ, -- When the access token expires
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
last_refreshed_at TIMESTAMPTZ, -- Last time token was refreshed
|
|
|
|
-- Security metadata for session validation
|
|
user_agent TEXT, -- User agent for session binding
|
|
ip_address INET -- IP address for session binding
|
|
);
|
|
|
|
-- Indexes for fast lookups
|
|
CREATE INDEX IF NOT EXISTS idx_oauth_sessions_session_id ON oauth_sessions(session_id);
|
|
CREATE INDEX IF NOT EXISTS idx_oauth_sessions_user_sub ON oauth_sessions(user_sub);
|
|
CREATE INDEX IF NOT EXISTS idx_oauth_sessions_expires_at ON oauth_sessions(access_token_expires_at);
|
|
|
|
-- Comment for documentation
|
|
COMMENT ON TABLE oauth_sessions IS 'Stores encrypted OAuth refresh tokens for session management';
|
|
COMMENT ON COLUMN oauth_sessions.refresh_token_encrypted IS 'Refresh token encrypted with AES-256-GCM';
|
|
COMMENT ON COLUMN oauth_sessions.session_id IS 'Links to the gorilla session cookie';
|
|
COMMENT ON COLUMN oauth_sessions.user_agent IS 'Used to detect session hijacking';
|
|
COMMENT ON COLUMN oauth_sessions.ip_address IS 'Used to detect session hijacking';
|