"""Exercise SQL-backed registration and ownership without a MariaDB daemon.""" from __future__ import annotations import sqlite3 import uuid import pytest from virsh_panel.auth import verify_password from virsh_panel.users import DatabaseUserStore, UserError, normalize_email class _Cursor: def __init__(self, connection: sqlite3.Connection) -> None: self._cursor = connection.cursor() def __enter__(self): return self def __exit__(self, *_exc): self._cursor.close() def execute(self, statement, parameters=()): sql = statement.replace("%s", "?").replace(" FOR UPDATE", "") try: self._cursor.execute(sql, parameters) except sqlite3.IntegrityError as exc: import pymysql raise pymysql.err.IntegrityError(1062, str(exc)) from exc return self def fetchone(self): row = self._cursor.fetchone() return None if row is None else dict(row) def fetchall(self): return [dict(row) for row in self._cursor.fetchall()] @property def lastrowid(self): return self._cursor.lastrowid @property def rowcount(self): return self._cursor.rowcount class _Connection: def __init__(self): self.raw = sqlite3.connect(":memory:") self.raw.row_factory = sqlite3.Row self.raw.execute("PRAGMA foreign_keys=ON") self.raw.executescript(""" CREATE TABLE users ( id INTEGER PRIMARY KEY AUTOINCREMENT, username TEXT NOT NULL UNIQUE, email TEXT UNIQUE, password_hash TEXT NOT NULL, admin INTEGER NOT NULL DEFAULT 0, approved INTEGER NOT NULL DEFAULT 0, email_verified INTEGER NOT NULL DEFAULT 0, created_at INTEGER NOT NULL ); CREATE TABLE registration_codes ( email TEXT PRIMARY KEY, code_hash TEXT NOT NULL, sent_at INTEGER NOT NULL, expires_at INTEGER NOT NULL, failed_attempts INTEGER NOT NULL DEFAULT 0 ); CREATE TABLE vm_ownership ( host_scope TEXT NOT NULL, vm_uuid TEXT NOT NULL, user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE, created_at INTEGER NOT NULL, PRIMARY KEY (host_scope, vm_uuid) ); """) def cursor(self): return _Cursor(self.raw) def commit(self): self.raw.commit() def rollback(self): self.raw.rollback() def close(self): # DatabaseUserStore opens a new wrapper per method. This one fake # keeps a shared in-memory database alive across the test. pass class _Store: def __init__(self): self.connection = _Connection() def _connect(self): return self.connection @pytest.fixture def users(): store = _Store() yield DatabaseUserStore(store, b"x" * 32), store.connection.raw store.connection.raw.close() def test_initial_user_is_not_admin_but_is_approved(users): repository, _database = users assert repository.has_users() is False first = repository.create_initial_user("first", "initial-long-password", now=100) assert first["admin"] is False assert first["approved"] is True assert first["email_verified"] is True assert first["email"] is None assert verify_password("initial-long-password", first["password_hash"]) assert repository.has_users() is True assert repository.get_by_username("first") == first assert repository.get_by_id(first["id"]) == first assert repository.get_by_email("first@example.test") is None assert repository.get_by_email("first") is None _database.execute("UPDATE users SET email=? WHERE id=?", ("first@example.test", first["id"])) _database.commit() assert repository.get_by_email("FIRST@EXAMPLE.TEST")["id"] == first["id"] with pytest.raises(UserError) as repeated: repository.create_initial_user("second", "another-long-password", now=101) assert repeated.value.status == 409 def test_initial_user_rejects_invalid_email_when_provided(users): repository, _database = users for email in ("", "not-an-email"): with pytest.raises(UserError): repository.create_initial_user("first", "initial-long-password", email) assert repository.has_users() is False def test_registration_code_is_keyed_rate_limited_and_one_time(users): repository, database = users assert repository.issue_registration_code("User@Example.COM", "123456", now=100) code_hash = database.execute( "SELECT code_hash FROM registration_codes WHERE email=?", ("user@example.com",) ).fetchone()[0] assert len(code_hash) == 64 assert code_hash != "123456" with pytest.raises(UserError) as cooldown: repository.issue_registration_code("user@example.com", "654321", now=120) assert cooldown.value.status == 429 with pytest.raises(UserError): repository.register_with_code("registered", "user@example.com", "long-user-password", "000000", now=130) assert database.execute( "SELECT failed_attempts FROM registration_codes WHERE email=?", ("user@example.com",) ).fetchone()[0] == 1 created = repository.register_with_code( "registered", "USER@EXAMPLE.COM", "long-user-password", "123456", now=140, ) assert created["email"] == "user@example.com" assert created["email_verified"] is True assert created["admin"] is False assert created["approved"] is False assert verify_password("long-user-password", created["password_hash"]) assert repository.issue_registration_code("user@example.com", "222222", now=300) is False with pytest.raises(UserError): repository.register_with_code( "duplicate", "user@example.com", "long-user-password", "123456", now=145, ) def test_code_expires_and_five_failures_consume_challenge(users): repository, database = users repository.issue_registration_code("fail@example.com", "999999", now=100) for attempt in range(5): with pytest.raises(UserError): repository.register_with_code( "wrong", "fail@example.com", "long-user-password", "000000", now=110 + attempt, ) assert database.execute("SELECT COUNT(*) FROM registration_codes").fetchone()[0] == 0 with pytest.raises(UserError): repository.register_with_code( "wrong", "fail@example.com", "long-user-password", "999999", now=116, ) repository.issue_registration_code("expired@example.com", "111111", now=200) with pytest.raises(UserError): repository.register_with_code( "expired", "expired@example.com", "long-user-password", "111111", now=800, ) assert database.execute( "SELECT COUNT(*) FROM registration_codes WHERE email='expired@example.com'" ).fetchone()[0] == 0 def test_failed_smtp_delivery_can_revoke_only_matching_code(users): repository, database = users repository.issue_registration_code("retry@example.com", "111111", now=100) assert repository.revoke_registration_code("retry@example.com", "222222") is False assert repository.revoke_registration_code("retry@example.com", "111111") is True assert database.execute("SELECT COUNT(*) FROM registration_codes").fetchone()[0] == 0 assert repository.issue_registration_code("retry@example.com", "333333", now=101) def test_approval_and_manual_admin_flag_are_distinct(users): repository, database = users repository.issue_registration_code("awaiting@example.com", "123456", now=100) account = repository.register_with_code( "awaiting", "awaiting@example.com", "long-user-password", "123456", now=120, ) pending = repository.list_pending_users() assert len(pending) == 1 and pending[0]["username"] == "awaiting" assert "password_hash" not in pending[0] approved = repository.approve_user(account["id"]) assert approved["approved"] is True and approved["admin"] is False assert repository.list_pending_users() == [] database.execute("UPDATE users SET admin=1 WHERE id=?", (account["id"],)) database.commit() assert repository.get_by_username("awaiting")["admin"] is True assert repository.approve_user(999999) is None def test_vm_ownership_is_scoped_to_host_and_user(users): repository, _database = users first = repository.create_initial_user("first", "initial-long-password", now=100) repository.issue_registration_code("other@example.com", "123456", now=101) other = repository.register_with_code( "other", "other@example.com", "long-user-password", "123456", now=102, ) host = "a" * 64 second_host = "b" * 64 vm_id = str(uuid.uuid4()) assert repository.owner_of_vm(host, vm_id) is None repository.assign_vm(host, vm_id, first["id"], now=103) repository.assign_vm(host, vm_id, first["id"], now=103) assert repository.owns_vm(host, vm_id, first["id"]) is True assert repository.owns_vm(host, vm_id, other["id"]) is False assert repository.list_owned_vm_ids(host, first["id"]) == {vm_id} assert repository.list_owned_vm_ids(second_host, first["id"]) == set() with pytest.raises(UserError) as conflict: repository.assign_vm(host, vm_id, other["id"], now=104) assert conflict.value.status == 409 repository.delete_vm_owner(host, vm_id) assert repository.owner_of_vm(host, vm_id) is None @pytest.mark.parametrize("address", [ "evil@example.com\r\nBcc: victim@example.com", "not-an-email", "x@localhost", "a..b@example.com", "x@example..com", "ñ@example.com", ]) def test_email_validation_rejects_unsafe_addresses(address): with pytest.raises(UserError): normalize_email(address)