DuckDB-Specific Security

The Risk

DuckDB can read and write files on the server's filesystem by default. A crafted query can read /etc/passwd, write malicious files, or load external data from the network. In a web-facing application, this is equivalent to giving users shell access to your server.

The Solution

Open the DuckDB database in read-only mode so no data can be modified, and disable external access so queries cannot read files from disk or fetch data from the internet. These two settings together ensure that DuckDB can only query the data already inside the database file - nothing else on your server.

The Fix

conn = duckdb.connect(filepath, read_only=True)
conn.execute("SET enable_external_access=false")

The Risk

DuckDB runs in-process - not in a separate server. Without limits, a single query can consume all available RAM and CPU cores, starving your web server and every other process on the machine. Unlike Postgres, there's no separate process to kill.

The Solution

Set explicit limits on how much memory and how many CPU cores DuckDB is allowed to use. For example, cap it at 512MB of RAM and 2 CPU threads. This ensures that even a heavy query leaves enough resources for your web server and other services to keep running normally.

The Fix

conn.execute("SET memory_limit='512MB'")
conn.execute("SET threads=2")

The Risk

In analytical engines like DuckDB, the obvious cancellation controls do NOT reliably terminate a runaway query. Setting an asyncio timeout cancels your Python coroutine, but the C++ engine on the worker thread keeps churning. Calling conn.interrupt() is best-effort: the engine polls the cancel flag only at internal checkpoints, and a deep cartesian materialization may never reach one. Calling conn.close() from a different thread does not kill a cursor that is currently executing. The memory_limit setting does not stop a streaming join that produces rows without holding state. A single crafted query (for example a three-way CROSS JOIN inside a CTE) can pin host CPU for hours.

The Solution

Run each query inside a separate OS process you can kill. Maintain a small pool of worker subprocesses, route each query to one, and on timeout send SIGTERM (graceful) followed by SIGKILL (uninterruptible) to that one worker. SIGKILL is delivered by the kernel and the process dies in milliseconds, no engine cooperation required. Other workers keep serving traffic untouched; the dead worker is respawned in the background. This is the same pattern Postgres uses internally (per-backend processes plus pg_terminate_backend), applied one layer up by the application.

The Fix

# Conceptual pattern: run each query in a worker
# subprocess you can kill on timeout. Pseudocode.
import multiprocessing, time

def run_in_worker(worker, sql, timeout):
    worker.send(sql)
    if worker.poll(timeout):
        return worker.recv()
    worker.terminate()       # SIGTERM (graceful)
    time.sleep(1)
    if worker.is_alive():
        worker.kill()        # SIGKILL (uninterruptible)
    raise TimeoutError

The Risk

Even with DuckDB's memory_limit set, a bug or edge case can cause the process to exceed it. Without Docker container limits, one runaway DuckDB container can starve every other service on the same server - databases, web servers, monitoring.

The Solution

Set memory and CPU limits at the Docker container level as a second safety net. Even if DuckDB's own memory limit is bypassed due to a bug, the container will be capped. This protects every other service running on the same server from being starved by one runaway container.

The Fix

# Docker / Coolify container settings
memory: 1GB
swap: 2GB
cpus: 2

The Risk

DuckDB uses file-level locking. If a process crashes or is killed while holding a write lock, it can leave behind a stale .wal (write-ahead log) file that blocks all subsequent connections. In read-only mode, multiple connections work fine - but if ANY process opens the file in write mode (even briefly, like a data import script), it blocks all other connections until it releases the lock.

The Solution

Always open DuckDB in read-only mode for web-facing applications (see 4.1). Keep write access limited to offline processes (data imports, migrations) that run one at a time. If a stale .wal file blocks connections after a crash, you may need to open the database in write mode once to recover it, then switch back to read-only. Monitor for .wal files as part of your health checks.

The Fix

# Web-facing app: always read-only (no lock contention)
conn = duckdb.connect(filepath, read_only=True)

# Data import script: write mode, one process at a time
conn = duckdb.connect(filepath, read_only=False)
# ... import data ...
conn.close()  # releases lock

# Recovery: if stale .wal blocks read-only connections
# Option 1: open write mode briefly to flush WAL
conn = duckdb.connect(filepath, read_only=False)
conn.close()
# Option 2: delete .wal file (LAST RESORT - may lose data)

The Risk

Unlike Postgres (separate server process), DuckDB runs inside your FastAPI process. A heavy DuckDB query blocks the Python asyncio event loop, freezing ALL concurrent handling - health checks stop responding, rate limit checks can't execute, and timeout logic can't fire. Your entire API becomes unresponsive for the duration of the query, not just the endpoint that triggered it.

The Solution

Always run DuckDB queries in a ThreadPoolExecutor so they execute in a separate thread, leaving the asyncio event loop free to handle other requests. Without this, your timeout and interrupt logic (item 4.3) can't even fire - the event loop is blocked, so the timeout coroutine never gets a chance to run. This is also why concurrent request limits (item 2.3) are critical for DuckDB backends.

The Fix

import asyncio

# WRONG - blocks the event loop
@app.get("/api/query")
async def query(request: Request):
    result = conn.execute(sql).fetchall()  # blocks everything

# RIGHT - run in thread, event loop stays responsive
@app.get("/api/query")
async def query(request: Request):
    loop = asyncio.get_event_loop()
    result = await loop.run_in_executor(
        None, execute_duckdb_query, sql
    )
    return result