learningcircuit--local-deep-research/tests/database/test_db_file_permissions.py

"""Regression tests for DB-file permission hardening (PR #3135).

``DatabaseManager.create_user_database`` tightens the on-disk database file to
owner-only (0o600). The encrypted (SQLCipher) branch chmods right after
creating the file; the unencrypted fallback — which writes PLAINTEXT data and
is therefore the higher-risk path — chmods after ``initialize_database``
materializes the file. This test pins the unencrypted behavior, mirroring the
salt-file permission test in ``test_encryption_constants.py``.
"""

import os
import stat
from unittest.mock import patch

from tests.test_utils import add_src_to_path

add_src_to_path()


class TestDatabaseFilePermissions:
    def test_unencrypted_db_file_has_restrictive_permissions(
        self, tmp_path, monkeypatch
    ):
        """The unencrypted-fallback DB file must be created 0o600.

        It holds plaintext user data, so leaving it at umask-default perms
        (commonly 0o644) would expose it to other local accounts.
        """
        from local_deep_research.database.encrypted_db import DatabaseManager

        monkeypatch.setenv("LDR_BOOTSTRAP_ALLOW_UNENCRYPTED", "true")

        with patch(
            "local_deep_research.database.encrypted_db.get_sqlcipher_module"
        ) as mock_get_sqlcipher:
            mock_get_sqlcipher.side_effect = ImportError(
                "No module named 'sqlcipher3'"
            )
            with patch(
                "local_deep_research.database.encrypted_db.get_data_directory",
                return_value=tmp_path,
            ):
                manager = DatabaseManager()
                assert manager.has_encryption is False, (
                    "Test requires the unencrypted fallback path"
                )

                engine = manager.create_user_database(
                    "permuser", "test-password-123"
                )
                try:
                    db_path = manager._get_user_db_path("permuser")
                    assert db_path.exists(), "DB file was not created"

                    mode = stat.S_IMODE(os.stat(db_path).st_mode)
                    assert mode == 0o600, (
                        f"Unencrypted DB file should be 0o600, got {oct(mode)}"
                    )

                    # The per-user DB directory is owner-only (0o700), so the
                    # plaintext WAL/SHM sidecars and any temp files SQLite may
                    # create alongside the DB are not sibling-readable either.
                    dir_mode = stat.S_IMODE(os.stat(manager.data_dir).st_mode)
                    assert dir_mode == 0o700, (
                        f"DB directory should be 0o700, got {oct(dir_mode)}"
                    )
                finally:
                    engine.dispose()

    def test_db_creation_survives_chmod_failure(self, tmp_path, monkeypatch):
        """Permission hardening must never break DB creation.

        On filesystems that reject POSIX chmod (some Docker bind mounts,
        network/FUSE volumes — e.g. Docker Desktop on macOS/Windows),
        os.chmod can raise OSError. The chmods are best-effort, so
        create_user_database must still succeed and return a usable engine.
        """
        from sqlalchemy import text

        from local_deep_research.database.encrypted_db import DatabaseManager

        monkeypatch.setenv("LDR_BOOTSTRAP_ALLOW_UNENCRYPTED", "true")

        with patch(
            "local_deep_research.database.encrypted_db.get_sqlcipher_module",
            side_effect=ImportError("No module named 'sqlcipher3'"),
        ):
            with patch(
                "local_deep_research.database.encrypted_db.get_data_directory",
                return_value=tmp_path,
            ):
                # Simulate a volume that rejects chmod for every call.
                with patch(
                    "local_deep_research.database.encrypted_db.os.chmod",
                    side_effect=OSError("Operation not permitted"),
                ):
                    manager = DatabaseManager()
                    engine = manager.create_user_database(
                        "chmoduser", "test-password-123"
                    )
                    try:
                        # DB must be created and queryable despite chmod failing.
                        with engine.connect() as conn:
                            assert conn.execute(text("SELECT 1")).scalar() == 1
                    finally:
                        engine.dispose()