Как организовать базу данных для сервиса авторизации

Что проверяют

Понимаете ли вы, что база сервиса авторизации хранит в основном секреты и их производные, и какие из них нельзя держать в открытом виде. Следом обычно спрашивают, как отозвать все сессии пользователя после смены пароля.

Как отвечать

Опишите схему таблицами, сразу с причинами:

• users: id (UUID или bigint), email с уникальностью без учёта регистра через citext или индекс по lower(email), password_hash, email_verified_at, is_active, created_at. Пароль может быть NULL, если человек входит только через внешнего провайдера;
• user_identities: связь с OAuth и OIDC провайдерами, уникальная пара (provider, provider_subject), один пользователь может привязать несколько входов;
• refresh_tokens: user_id, хэш токена, expires_at, revoked_at, family_id для ротации, устройство и IP. Хранится SHA-256 от токена, чтобы утёкший дамп не давал готовых сессий;
• roles и permissions со связующими таблицами, если сервис отвечает и за права;
• одноразовые токены сброса пароля и подтверждения почты: хэш, срок жизни в пределах часа, отметка об использовании;
• auth_events: журнал входов и неудачных попыток для расследований и блокировок.

Пароли хэшируют Argon2id (argon2-cffi) или bcrypt. У bcrypt предел 72 байта на пароль, а passlib давно не развивается и плохо уживается со свежим пакетом bcrypt, поэтому в новых проектах берут argon2-cffi или pwdlib. Схему ведут через SQLAlchemy 2.0 и Alembic, счётчики попыток и rate limit держат в Redis, а доступ к базе у сервиса отдельный, с минимальными правами.

Access-токен JWT делают коротким и в базе не хранят, отзыв идёт через refresh-токены: пометили revoked_at у всех токенов пользователя, и новые access-токены он уже не получит.

Где спотыкаются

Хранят refresh-токены и токены сброса как есть. Кто прочитал таблицу, тот вошёл под любым пользователем.

Хэшируют пароли SHA-256 или MD5, пусть даже с солью. Это быстрые функции, на видеокарте их перебирают миллиардами в секунду.

Делают уникальность email чувствительной к регистру и получают двух пользователей Ivan@example.com и ivan@example.com.