Memory Leak in Fastapi with Cockroachdb

A memory leak in a Fastapi service that uses Cockroachdb typically arises from how application-level code manages database sessions, cursors, and result sets rather than from Cockroachdb itself. When a Fastapi endpoint opens a Cockroachdb connection or session and fails to explicitly release resources, Python objects (such as cursor results and connection handles) can remain referenced beyond their intended lifetime. This prevents Python’s garbage collector from reclaiming memory, and under sustained traffic the process grows in memory usage.

In a black-box scan, middleBrick’s checks for Data Exposure and Unsafe Consumption can surface indicators such as unusually large response payloads or patterns consistent with unbounded in-memory accumulation. A common pattern is performing long-running queries without streaming or pagination, causing full result sets to be materialized in memory. For example, an endpoint that executes a complex SQL query and returns the raw list of rows can retain large objects in memory if the caller does not iterate and discard results promptly.

SSRF and Inventory Management checks may further highlight that services connecting to Cockroachdb without proper resource hygiene can expose metadata or connections that amplify leak impacts. If an endpoint opens many sessions (for example, creating a new Cockroachdb connection per request instead of using a bounded pool), the cumulative effect can be file descriptor pressure alongside memory growth. middleBrick’s checks related to Rate Limiting and Authentication can help identify whether traffic spikes correlate with memory growth, supporting prioritization of findings by severity.

Because middleBrick scans the unauthenticated attack surface and runs checks in parallel, it can detect risky response patterns and surface a high Data Exposure or Unsafe Consumption finding with remediation guidance, without requiring credentials or agent deployment. The 5–15 second scan window helps teams quickly correlate API behavior with resource anomalies while following OWASP API Top 10 guidance relevant to server-side memory risks.

Remediation focuses on deterministic resource cleanup, bounded concurrency, and streaming where feasible. Use async context managers for Cockroachdb connections and sessions, ensure cursors are closed, and prefer limit/offset or keyset pagination to avoid materializing large result sets. Below are concrete, working examples for Fastapi with asyncpg-backed Cockroachdb connectivity.

from fastapi import FastAPI, Depends, HTTPException
import asyncpg
import os

app = FastAPI()

async def get_pool():
    # Create a single connection pool at startup; reuse across requests
    if not hasattr(get_pool, "_pool"):
        get_pool._pool = asyncpg.create_pool(
            host=os.getenv("COCKROACH_HOST", "localhost"),
            port=int(os.getenv("COCKROACH_PORT", 26257)),
            user=os.getenv("COCKROACH_USER", "root"),
            password=os.getenv("COCKROACH_PASSWORD", ""),
            database=os.getenv("COCKROACH_DATABASE", "defaultdb"),
            min_size=1,
            max_size=10  # bound the pool to limit concurrent connections and memory
        )
    return get_pool._pool

@app.get("/widgets/{widget_id}")
async def read_widget(widget_id: str, pool: asyncpg.Pool = Depends(get_pool)):
    async with pool.acquire() as conn:
        # Use parameterized query to avoid injection and ensure clean cursor lifecycle
        row = await conn.fetchrow("SELECT id, name, metadata FROM widgets WHERE id = $1", widget_id)
        if row is None:
            raise HTTPException(status_code=404, detail="Widget not found")
        # row is a dict-like object; ensure no large nested fields are retained unnecessarily
        return {"id": row["id"], "name": row["name"]}

@app.get("/reports/")
async def list_reports(limit: int = 100, pool: asyncpg.Pool = Depends(get_pool)):
    bounded_limit = min(limit, 1000)  # enforce a reasonable ceiling
    async with pool.acquire() as conn:
        # Stream results via cursor to avoid loading full result into memory
        async with conn.cursor("DECLARE cur CURSOR FOR SELECT id, summary FROM reports") as cursor:
            await cursor.execute(f"FETCH FORWARD {bounded_limit} FROM cur")
            rows = []
            async for record in cursor:
                rows.append({"id": record[0], "summary": record[1]})
            # cursor and connection released automatically via context managers
            return {"reports": rows}

Key remediation practices demonstrated:

• Create a single connection pool at startup with bounded max_size to cap memory and file descriptors.
• Use async context managers (async with) for connections, sessions, and cursors to guarantee cleanup.
• Avoid returning raw ORM/model instances that may carry lazy-loaded relations; project only needed fields.
• Apply limit bounding and keyset pagination instead of fetching all rows into a list.
• Set reasonable timeouts and avoid long-lived transactions that can hold server-side state and memory.

In production, monitor memory and file descriptor usage; correlate findings from middleBrick’s Data Exposure and Unsafe Consumption checks with runtime metrics to validate that remediation reduces in-memory pressure. The dashboard and CLI outputs from middleBrick can be integrated into your GitHub Action to fail builds if risk scores exceed your chosen threshold, ensuring ongoing posture control without relying on unauthenticated scanning alone.