6a675565e1
TASK-SECADV-001: WebAuthn/Passkeys (F022) - WebAuthn credential model, service, handler - Registration/authentication ceremony endpoints - CRUD operations (list, rename, delete passkeys) - Routes: GET/POST/PUT/DELETE /auth/passkeys/* TASK-SECADV-002: Configurable password policy (F015) - PasswordPolicyConfig with MinLength, MaxLength, RequireUpper/Lower/Number/Special - NewPasswordValidatorWithPolicy constructor - PasswordPolicyFromEnv() reads env vars (PASSWORD_MIN_LENGTH, etc.) - All character class checks now respect policy configuration TASK-SECADV-003: Géolocalisation connexions (F025) - GeoIPResolver interface + GeoIPService implementation - Country/city columns added to login_history table - LoginHistoryService.Record() performs GeoIP lookup - GetUserHistory returns geolocation data - GET /auth/login-history endpoint TASK-SECADV-004: Password expiration (F016) - password_changed_at column on users table - CheckPasswordExpiration() method on PasswordService - All password change/reset methods now set password_changed_at - NewPasswordServiceWithPolicy() supports expiration days config Migration: 971_security_advanced_v0133.sql Co-Authored-By: Claude Opus 4.6 <noreply@anthropic.com>
27 lines
1.4 KiB
SQL
27 lines
1.4 KiB
SQL
-- v0.13.3: F022 WebAuthn Credentials + F025 GeoIP on login_history + F016 Password expiration
|
|
-- Up migration
|
|
|
|
-- F022: WebAuthn credentials — stores FIDO2 passkeys per user
|
|
CREATE TABLE IF NOT EXISTS webauthn_credentials (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
credential_id BYTEA NOT NULL UNIQUE,
|
|
public_key BYTEA NOT NULL,
|
|
attestation_type VARCHAR(50) NOT NULL DEFAULT 'none',
|
|
aaguid BYTEA,
|
|
sign_count BIGINT NOT NULL DEFAULT 0,
|
|
name VARCHAR(100) NOT NULL DEFAULT 'My Passkey',
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
last_used_at TIMESTAMPTZ
|
|
);
|
|
CREATE INDEX IF NOT EXISTS idx_webauthn_user_id ON webauthn_credentials(user_id);
|
|
CREATE INDEX IF NOT EXISTS idx_webauthn_credential_id ON webauthn_credentials(credential_id);
|
|
|
|
-- F025: Add geolocation columns to login_history
|
|
ALTER TABLE login_history ADD COLUMN IF NOT EXISTS country VARCHAR(2) DEFAULT '';
|
|
ALTER TABLE login_history ADD COLUMN IF NOT EXISTS city VARCHAR(100) DEFAULT '';
|
|
|
|
-- F016: Add password_changed_at to users for expiration tracking
|
|
ALTER TABLE users ADD COLUMN IF NOT EXISTS password_changed_at TIMESTAMPTZ;
|
|
-- Backfill: set password_changed_at = updated_at for existing users with passwords
|
|
UPDATE users SET password_changed_at = updated_at WHERE password_hash != '' AND password_changed_at IS NULL;
|