darko
fcafe1ae15
API аккаунтов на чистой стандартной библиотеке Python (server/splitapi): - регистрация по почте: пароль задаётся по ссылке из письма, аккаунт создаётся только после подтверждения - вход, выход, сброс пароля по почте (токены одноразовые, в базе только SHA-256), удаление аккаунта - пароли scrypt, лимиты попыток по паре «адрес + IP», IP и аккаунту, защита от CSRF - хранение прогресса пользователя с оптимистичной блокировкой версии - SMTP через переменные окружения; без SMTP на публичном адресе API не стартует Клиент: public/auth.js (вход, регистрация, сброс, синхронизация), public/sync.js (чистое слияние прогресса). Без API сайт работает как раньше. Развёртывание: Dockerfile.api, сервис splitapi в docker-compose, проксирование /api/ в nginx с ограничением частоты, deploy/install-api-debian.sh (systemd), .env.example. Тесты: 216 для бэкенда (unittest, покрытие около 97%), 16 для клиентского слияния. Документация: раздел «Аккаунты и почта» в docs/DEPLOY.md, раздел 9 в docs/ИНСТРУКЦИЯ.md.
246 lines
12 KiB
Python
246 lines
12 KiB
Python
"""Хранилище SQLite. Все запросы параметризованы; одноразовые токены гасятся атомарно."""
|
||
from __future__ import annotations
|
||
|
||
import os
|
||
import sqlite3
|
||
from contextlib import contextmanager
|
||
from typing import Iterator
|
||
|
||
SCHEMA_VERSION = 2 # 2: регистрация больше не хранит пароль (его задают после подтверждения почты)
|
||
|
||
_SCHEMA = """
|
||
CREATE TABLE IF NOT EXISTS users (
|
||
id INTEGER PRIMARY KEY,
|
||
email TEXT NOT NULL UNIQUE,
|
||
pw_hash TEXT NOT NULL,
|
||
created_at INTEGER NOT NULL
|
||
);
|
||
-- Ожидание подтверждения почты: пользователь появляется только после перехода по ссылке и выбора пароля.
|
||
CREATE TABLE IF NOT EXISTS registrations (
|
||
email TEXT PRIMARY KEY,
|
||
token_hash TEXT NOT NULL UNIQUE,
|
||
expires_at INTEGER NOT NULL,
|
||
created_at INTEGER NOT NULL
|
||
);
|
||
CREATE TABLE IF NOT EXISTS reset_tokens (
|
||
token_hash TEXT PRIMARY KEY,
|
||
user_id INTEGER NOT NULL UNIQUE REFERENCES users(id) ON DELETE CASCADE,
|
||
expires_at INTEGER NOT NULL
|
||
);
|
||
CREATE TABLE IF NOT EXISTS sessions (
|
||
token_hash TEXT PRIMARY KEY,
|
||
user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
||
expires_at INTEGER NOT NULL,
|
||
created_at INTEGER NOT NULL
|
||
);
|
||
CREATE INDEX IF NOT EXISTS sessions_user ON sessions(user_id);
|
||
CREATE INDEX IF NOT EXISTS registrations_expires ON registrations(expires_at);
|
||
CREATE INDEX IF NOT EXISTS reset_tokens_expires ON reset_tokens(expires_at);
|
||
CREATE INDEX IF NOT EXISTS sessions_expires ON sessions(expires_at);
|
||
CREATE TABLE IF NOT EXISTS user_state (
|
||
user_id INTEGER PRIMARY KEY REFERENCES users(id) ON DELETE CASCADE,
|
||
data TEXT NOT NULL,
|
||
version INTEGER NOT NULL,
|
||
updated_at INTEGER NOT NULL
|
||
);
|
||
"""
|
||
|
||
_COUNTABLE = frozenset({"users", "registrations", "reset_tokens", "sessions", "user_state"})
|
||
|
||
|
||
class Store:
|
||
def __init__(self, path: str) -> None:
|
||
self._path = path
|
||
os.makedirs(os.path.dirname(os.path.abspath(path)), exist_ok=True)
|
||
self._init_schema()
|
||
try:
|
||
os.chmod(path, 0o600)
|
||
except OSError:
|
||
pass # Windows и некоторые файловые системы не поддерживают права
|
||
|
||
@contextmanager
|
||
def _tx(self, immediate: bool = False) -> Iterator[sqlite3.Connection]:
|
||
conn = sqlite3.connect(self._path, timeout=5, isolation_level=None)
|
||
conn.row_factory = sqlite3.Row
|
||
try:
|
||
conn.execute("PRAGMA foreign_keys = ON")
|
||
conn.execute("BEGIN IMMEDIATE" if immediate else "BEGIN")
|
||
try:
|
||
yield conn
|
||
except BaseException:
|
||
conn.execute("ROLLBACK")
|
||
raise
|
||
conn.execute("COMMIT")
|
||
finally:
|
||
conn.close()
|
||
|
||
def _init_schema(self) -> None:
|
||
conn = sqlite3.connect(self._path, timeout=5, isolation_level=None)
|
||
try:
|
||
conn.execute("PRAGMA journal_mode = WAL")
|
||
version = conn.execute("PRAGMA user_version").fetchone()[0]
|
||
if version < 2:
|
||
# Ожидающие регистрации одноразовы и недолговечны: старые (с паролем) просто отбрасываем
|
||
conn.execute("DROP TABLE IF EXISTS registrations")
|
||
conn.executescript(_SCHEMA)
|
||
conn.execute(f"PRAGMA user_version = {SCHEMA_VERSION}")
|
||
finally:
|
||
conn.close()
|
||
|
||
# --- пользователи ---
|
||
def create_user(self, email: str, pw_hash: str, now: float) -> int | None:
|
||
try:
|
||
with self._tx(immediate=True) as conn:
|
||
return conn.execute(
|
||
"INSERT INTO users (email, pw_hash, created_at) VALUES (?, ?, ?)", (email, pw_hash, int(now))
|
||
).lastrowid
|
||
except sqlite3.IntegrityError:
|
||
return None
|
||
|
||
def user_by_email(self, email: str) -> sqlite3.Row | None:
|
||
with self._tx() as conn:
|
||
return conn.execute("SELECT id, email, pw_hash FROM users WHERE email = ?", (email,)).fetchone()
|
||
|
||
def set_password(self, user_id: int, pw_hash: str, expect_pw_hash: str | None = None) -> bool:
|
||
"""С expect_pw_hash меняет пароль, только если он всё ещё прежний (перехеширование не затирает сброс пароля)."""
|
||
with self._tx(immediate=True) as conn:
|
||
if expect_pw_hash is None:
|
||
cursor = conn.execute("UPDATE users SET pw_hash = ? WHERE id = ?", (pw_hash, user_id))
|
||
else:
|
||
cursor = conn.execute("UPDATE users SET pw_hash = ? WHERE id = ? AND pw_hash = ?", (pw_hash, user_id, expect_pw_hash))
|
||
return cursor.rowcount > 0
|
||
|
||
def delete_user(self, user_id: int) -> None:
|
||
"""Удаляет пользователя вместе с сессиями, токенами и прогрессом (каскад внешних ключей)."""
|
||
with self._tx(immediate=True) as conn:
|
||
conn.execute("DELETE FROM users WHERE id = ?", (user_id,))
|
||
|
||
# --- регистрация ---
|
||
def save_registration(self, email: str, token_hash: str, expires_at: float, now: float) -> None:
|
||
with self._tx(immediate=True) as conn:
|
||
conn.execute(
|
||
"INSERT OR REPLACE INTO registrations (email, token_hash, expires_at, created_at) VALUES (?, ?, ?, ?)",
|
||
(email, token_hash, int(expires_at), int(now)),
|
||
)
|
||
|
||
def peek_registration(self, token_hash: str, now: float) -> str | None:
|
||
with self._tx() as conn:
|
||
row = conn.execute(
|
||
"SELECT email FROM registrations WHERE token_hash = ? AND expires_at > ?", (token_hash, int(now))
|
||
).fetchone()
|
||
return row["email"] if row else None
|
||
|
||
def activate_registration(self, token_hash: str, pw_hash: str, now: float) -> bool:
|
||
"""Гасит ссылку и создаёт пользователя одной транзакцией. False: ссылка недействительна или адрес уже занят."""
|
||
with self._tx(immediate=True) as conn:
|
||
row = conn.execute(
|
||
"DELETE FROM registrations WHERE token_hash = ? AND expires_at > ? RETURNING email", (token_hash, int(now))
|
||
).fetchone()
|
||
if row is None:
|
||
return False
|
||
try:
|
||
conn.execute(
|
||
"INSERT INTO users (email, pw_hash, created_at) VALUES (?, ?, ?)", (row["email"], pw_hash, int(now))
|
||
)
|
||
except sqlite3.IntegrityError:
|
||
return False # неудавшийся INSERT не отменяет транзакцию: ссылка всё равно погашена
|
||
return True
|
||
|
||
# --- сброс пароля ---
|
||
def save_reset_token(self, user_id: int, token_hash: str, expires_at: float) -> None:
|
||
with self._tx(immediate=True) as conn:
|
||
conn.execute("DELETE FROM reset_tokens WHERE user_id = ?", (user_id,))
|
||
conn.execute(
|
||
"INSERT INTO reset_tokens (token_hash, user_id, expires_at) VALUES (?, ?, ?)",
|
||
(token_hash, user_id, int(expires_at)),
|
||
)
|
||
|
||
def peek_reset_token(self, token_hash: str, now: float) -> sqlite3.Row | None:
|
||
with self._tx() as conn:
|
||
return conn.execute(
|
||
"SELECT u.id AS id, u.email AS email FROM reset_tokens t JOIN users u ON u.id = t.user_id "
|
||
"WHERE t.token_hash = ? AND t.expires_at > ?",
|
||
(token_hash, int(now)),
|
||
).fetchone()
|
||
|
||
def reset_password(self, token_hash: str, pw_hash: str, now: float) -> sqlite3.Row | None:
|
||
"""Гасит токен, меняет пароль и завершает все сессии одной транзакцией. None: токен недействителен."""
|
||
with self._tx(immediate=True) as conn:
|
||
row = conn.execute(
|
||
"DELETE FROM reset_tokens WHERE token_hash = ? AND expires_at > ? RETURNING user_id", (token_hash, int(now))
|
||
).fetchone()
|
||
if row is None:
|
||
return None
|
||
conn.execute("UPDATE users SET pw_hash = ? WHERE id = ?", (pw_hash, row["user_id"]))
|
||
conn.execute("DELETE FROM sessions WHERE user_id = ?", (row["user_id"],))
|
||
return conn.execute("SELECT id, email FROM users WHERE id = ?", (row["user_id"],)).fetchone()
|
||
|
||
# --- сессии ---
|
||
def create_session(self, user_id: int, token_hash: str, expires_at: float, now: float, keep: int = 10,
|
||
expect_pw_hash: str | None = None) -> bool:
|
||
"""expect_pw_hash защищает от гонки: если пароль сменили после проверки, сессия не создаётся."""
|
||
with self._tx(immediate=True) as conn:
|
||
if expect_pw_hash is not None:
|
||
current = conn.execute("SELECT pw_hash FROM users WHERE id = ?", (user_id,)).fetchone()
|
||
if current is None or current["pw_hash"] != expect_pw_hash:
|
||
return False
|
||
conn.execute(
|
||
"INSERT INTO sessions (token_hash, user_id, expires_at, created_at) VALUES (?, ?, ?, ?)",
|
||
(token_hash, user_id, int(expires_at), int(now)),
|
||
)
|
||
conn.execute(
|
||
"DELETE FROM sessions WHERE user_id = ? AND token_hash NOT IN "
|
||
"(SELECT token_hash FROM sessions WHERE user_id = ? ORDER BY created_at DESC, rowid DESC LIMIT ?)",
|
||
(user_id, user_id, keep),
|
||
)
|
||
return True
|
||
|
||
def session_user(self, token_hash: str, now: float) -> sqlite3.Row | None:
|
||
with self._tx() as conn:
|
||
return conn.execute(
|
||
"SELECT u.id AS id, u.email AS email FROM sessions s JOIN users u ON u.id = s.user_id "
|
||
"WHERE s.token_hash = ? AND s.expires_at > ?",
|
||
(token_hash, int(now)),
|
||
).fetchone()
|
||
|
||
def delete_session(self, token_hash: str) -> None:
|
||
with self._tx(immediate=True) as conn:
|
||
conn.execute("DELETE FROM sessions WHERE token_hash = ?", (token_hash,))
|
||
|
||
def delete_user_sessions(self, user_id: int) -> None:
|
||
with self._tx(immediate=True) as conn:
|
||
conn.execute("DELETE FROM sessions WHERE user_id = ?", (user_id,))
|
||
|
||
# --- состояние прогресса ---
|
||
def get_state(self, user_id: int) -> tuple[int, str] | None:
|
||
with self._tx() as conn:
|
||
row = conn.execute("SELECT version, data FROM user_state WHERE user_id = ?", (user_id,)).fetchone()
|
||
return (row["version"], row["data"]) if row else None
|
||
|
||
def put_state(self, user_id: int, expected_version: int, data: str, now: float) -> int | None:
|
||
"""Записывает состояние, если версия совпала (оптимистичная блокировка). None = конфликт."""
|
||
with self._tx(immediate=True) as conn:
|
||
row = conn.execute("SELECT version FROM user_state WHERE user_id = ?", (user_id,)).fetchone()
|
||
current = row["version"] if row else 0
|
||
if current != expected_version:
|
||
return None
|
||
new_version = current + 1
|
||
conn.execute(
|
||
"INSERT INTO user_state (user_id, data, version, updated_at) VALUES (?, ?, ?, ?) "
|
||
"ON CONFLICT(user_id) DO UPDATE SET data = excluded.data, version = excluded.version, updated_at = excluded.updated_at",
|
||
(user_id, data, new_version, int(now)),
|
||
)
|
||
return new_version
|
||
|
||
# --- обслуживание ---
|
||
def purge_expired(self, now: float) -> None:
|
||
with self._tx(immediate=True) as conn:
|
||
for table in ("registrations", "reset_tokens", "sessions"):
|
||
conn.execute(f"DELETE FROM {table} WHERE expires_at <= ?", (int(now),)) # имена таблиц - константы выше
|
||
|
||
def count(self, table: str) -> int:
|
||
if table not in _COUNTABLE:
|
||
raise ValueError("неизвестная таблица")
|
||
with self._tx() as conn:
|
||
return conn.execute(f"SELECT COUNT(*) FROM {table}").fetchone()[0]
|