DuckDB temporary files are not encrypted by default #645

#!/usr/bin/env python3
# /// script
# requires-python = ">=3.13"
# dependencies = ["duckdb==1.5.6"]
# ///
"""Minimal reproduction: DuckDB does not encrypt temp spill files for an attached
encrypted database.

The documentation (https://duckdb.org/2025/11/19/encryption-in-duckdb) states:

    "Temporary files are encrypted (1) automatically when you attach an encrypted
     database or (2) when you use the setting SET temp_file_encryption = true."

As of duckdb 1.5.6, clause (1) does not hold. This was also reproduced on 1.5.1, 1.5.5 and 1.5.6.
Only the explicit setting takes effect; without it, data read from an encrypted database
spills to disk in plaintext.

How it works: each trial builds a table whose every row contains a known marker
string, then runs an aggregation under a memory limit low enough to force a spill
to a dedicated temp directory. DuckDB deletes spill files as soon as the query
finishes, so the temp directory is polled for the marker while the query is still
running, on a background thread.

Trials:
    unencrypted                 positive control -- MUST leak, else no spill happened
    attach+use                  the documented case that should be encrypted
    attach+use+DETACH memory    rules out the in-memory catalog keeping encryption off
    attach+use+explicit setting  control -- the documented workaround, should not leak
    attach+use +httpfs          rules out the crypto backend (OpenSSL instead of mbedtls)

Usage:
    uv run duckdb_temp_encryption_repro.py

Exit code 0 if DuckDB behaves as documented, 1 if the plaintext leak is present.
"""

import glob
import os
import platform
import shutil
import sys
import tempfile
import threading
import time
from pathlib import Path

import duckdb

MARKER = "ZZSECRETMARKERZZ"
ROWS = 4_000_000
MEMORY_LIMIT = "200MB"

# Big enough to spill under MEMORY_LIMIT; the marker rides along in every row.
SPILL_QUERY = "SELECT s, count(*) FROM t GROUP BY s ORDER BY s DESC"

POLL_INTERVAL = 0.003


def _files_in(directory: Path) -> list[Path]:
    return [
        Path(p)
        for p in glob.glob(str(directory / "**"), recursive=True)
        if os.path.isfile(p)
    ]


def run_trial(name: str, setup: list[str], work_dir: Path) -> int:
    """Force a spill and poll the temp dir for the marker while the query runs.

    Returns the number of spill files found containing plaintext.
    """
    temp_dir = work_dir / f"tmp_{name}"
    temp_dir.mkdir(parents=True, exist_ok=True)

    con = duckdb.connect(":memory:")
    for statement in setup:
        con.execute(statement)
    con.execute(f"SET temp_directory='{temp_dir}'")
    con.execute(f"SET memory_limit='{MEMORY_LIMIT}'")
    con.execute("SET threads=2")
    con.execute("SET preserve_insertion_order=false")
    con.execute(
        f"CREATE TABLE t AS SELECT i, '{MARKER}' || i::VARCHAR AS s FROM range({ROWS}) tbl(i)"
    )

    setting = bool(
        con.execute("SELECT current_setting('temp_file_encryption')").fetchone()[0]
    )
    catalog = con.execute("SELECT current_database()").fetchone()[0]

    seen: set[str] = set()
    leaking: set[str] = set()
    done = threading.Event()

    def query() -> None:
        try:
            con.execute(SPILL_QUERY).fetchall()
        finally:
            done.set()

    thread = threading.Thread(target=query)
    thread.start()
    # DuckDB deletes spill files when the query finishes, so sample while it runs.
    while not done.is_set():
        for path in _files_in(temp_dir):
            seen.add(path.name)
            try:
                blob = path.read_bytes()
            except OSError:
                continue  # file vanished mid-read
            if MARKER.encode() in blob:
                leaking.add(path.name)
        time.sleep(POLL_INTERVAL)
    thread.join()
    con.close()

    print(
        f"  {name:34} catalog={catalog:8} temp_file_encryption={str(setting):5} "
        f"spill_files={len(seen):3} leaking={len(leaking):3} {sorted(leaking)[:2]}"
    )
    return len(leaking)


def main() -> int:
    work_dir = Path(tempfile.mkdtemp(prefix="duckdb_tempenc_"))
    print(f"duckdb {duckdb.__version__}")
    print(f"python {platform.python_version()} on {platform.platform()}")
    print(f"scratch dir {work_dir}\n")

    def attach(name: str, *, key: bool) -> list[str]:
        db = work_dir / f"{name}.db"
        opts = " (ENCRYPTION_KEY 'k')" if key else ""
        return [f"ATTACH '{db}' AS d{opts}", "USE d"]

    try:
        # Positive control: nothing is encrypted, so plaintext MUST appear. If it
        # does not, the harness is not producing spills and the run proves nothing.
        control_leak = run_trial(
            "unencrypted (control)", attach("plain", key=False), work_dir
        )

        attach_leak = run_trial("attach+use", attach("enc1", key=True), work_dir)
        detach_leak = run_trial(
            "attach+use+DETACH memory",
            [*attach("enc2", key=True), "DETACH memory"],
            work_dir,
        )
        explicit_leak = run_trial(
            "attach+use+explicit setting",
            [*attach("enc3", key=True), "SET temp_file_encryption=true"],
            work_dir,
        )
        # httpfs swaps mbedtls for OpenSSL, ruling out the crypto backend as the
        # variable. It may not be installed on a fresh machine, so treat it as optional.
        try:
            duckdb.connect(":memory:").execute("LOAD httpfs")
        except duckdb.Error as exc:
            print(f"  {'attach+use +httpfs (OpenSSL)':34} SKIPPED ({exc})")
            httpfs_leak = 0
        else:
            httpfs_leak = run_trial(
                "attach+use +httpfs (OpenSSL)",
                ["LOAD httpfs", *attach("enc4", key=True)],
                work_dir,
            )
    finally:
        shutil.rmtree(work_dir, ignore_errors=True)

    print()
    if not control_leak:
        print(
            "INCONCLUSIVE: control did not leak, so no spill was produced. Raise ROWS."
        )
        return 1
    if explicit_leak:
        print("UNEXPECTED: explicit temp_file_encryption=true still leaked plaintext.")
        return 1
    if attach_leak or detach_leak or httpfs_leak:
        print(
            "BUG PRESENT: attaching an encrypted database did not encrypt spill files.\n"
            "Plaintext from the encrypted database was readable in the temp directory.\n"
            "Workaround: SET temp_file_encryption=true explicitly."
        )
        return 1
    print("AS DOCUMENTED: attaching an encrypted database encrypted the spill files.")
    return 0


if __name__ == "__main__":
    sys.exit(main())

duckdb 1.5.6
python 3.14.5 on macOS-27.0.1-arm64-arm-64bit-Mach-O
scratch dir /var/folders/62/rj1rzcwj3339x8wm28kz9t7w0000gn/T/duckdb_tempenc_614cpb9u

  unencrypted (control)              catalog=d        temp_file_encryption=False spill_files=  7 leaking=  6 ['duckdb_temp_storage_DEFAULT-0.tmp', 'duckdb_temp_storage_S128K-0.tmp']
  attach+use                         catalog=d        temp_file_encryption=False spill_files=  7 leaking=  7 ['duckdb_temp_storage_DEFAULT-0.tmp', 'duckdb_temp_storage_S128K-0.tmp']
  attach+use+DETACH memory           catalog=d        temp_file_encryption=False spill_files=  6 leaking=  6 ['duckdb_temp_storage_DEFAULT-0.tmp', 'duckdb_temp_storage_S128K-0.tmp']
  attach+use+explicit setting        catalog=d        temp_file_encryption=True  spill_files=  8 leaking=  0 []
  attach+use +httpfs (OpenSSL)       catalog=d        temp_file_encryption=False spill_files=  8 leaking=  7 ['duckdb_temp_storage_DEFAULT-0.tmp', 'duckdb_temp_storage_S128K-0.tmp']