Insufficient Logging in Fastapi with Cockroachdb

Insufficient logging in a Fastapi service that uses Cockroachdb can leave critical security events undocumented, reducing the ability to detect, investigate, or respond to incidents. Unlike monolithic databases, Cockroachdb is a distributed SQL database with multi-node transactions, commit timestamps, and range-level operations, which can obscure causal relationships when logs are incomplete.

When Fastapi endpoints perform database actions without structured, context-rich logging, important signals are lost. For example, a missing log line for a failed transaction rollback can prevent analysts from correlating an authentication bypass (BOLA/IDOR) with downstream data access anomalies. middleBrick’s checks for Data Exposure and Property Authorization highlight how weak observability can mask unauthorized data access across distributed nodes.

Common gaps specific to this stack include: not logging request identifiers that tie HTTP requests to Cockroachdb transaction retries; omitting SQL statement metadata (e.g., table name, affected row count) in error paths; failing to record authentication context (scopes, roles) with each query; and not capturing transaction retry details that Cockroachdb emits in its server logs. Without these, incident responders cannot reliably trace a single malicious request across service and database boundaries, which increases dwell time for issues like insecure direct object references or privilege escalation.

Compliance mappings such as OWASP API Top 10 (2023) A03:2023 – Injection and A07:2023 – Identification and Authentication Failures, and SOC 2 CC6.1/CC6.7, emphasize the need for audit trails that include who accessed what and when. middleBrick’s Data Exposure and Authentication checks surface these logging deficiencies by comparing runtime behavior against expected controls, noting when error messages reveal stack traces or when successful queries lack audit trails.

Example of insufficient logging in Fastapi with Cockroachdb:

from fastapi import FastAPI, Depends, HTTPException
import psycopg2
from psycopg2.extras import RealDictCursor

app = FastAPI()

def get_db():
    conn = psycopg2.connect(
        host="localhost",
        port=26257,
        dbname="mydb",
        user="myuser",
        password="secret"
    )
    return conn

@app.get("/users/{user_id}")
def read_user(user_id: int, db=Depends(get_db)):
    try:
        with db.cursor(cursor_factory=RealDictCursor) as cur:
            cur.execute("SELECT id, email, role FROM users WHERE id = %s", (user_id,))
            row = cur.fetchone()
            if row is None:
                # Missing: log warning with request_id and user_id
                raise HTTPException(status_code=404, detail="User not found")
            return row
    except Exception as e:
        # Missing: structured log with query, params, transaction id, error type
        raise HTTPException(status_code=500, detail="Internal error")
    finally:
        db.close()

In this example, there is no correlation ID, no statement-level audit trail, and no differentiation between benign not-found cases and potential probing for user IDs. An attacker can probe IDs without generating actionable logs, and Cockroachdb’s distributed nature means that node-specific errors may not be surfaced if the application layer does not capture them.

Remediation centers on structured, contextual logging at the application and transaction level, with explicit correlation of HTTP requests to Cockroachdb operations. Use a request-scoped identifier that propagates into database transactions so that retries and node-specific messages can be tied back to the original API call.

Implement a logging layer that captures: HTTP method and path, request ID, user identity and scopes, SQL query and parameters (redacted for secrets), transaction ID, retry count, affected rows, and outcome (success/failure). For Cockroachdb, also log transaction timestamps and whether the transaction was committed or rolled back due to contention or errors.

Concrete Fastapi example with improved logging:

import logging
import uuid
from contextlib import contextmanager
from fastapi import FastAPI, Depends, HTTPException, Request
import psycopg2
from psycopg2.extras import RealDictCursor

logger = logging.getLogger("api")
app = FastAPI()

@contextmanager
def db_session(request: Request):
    conn = psycopg2.connect(
        host="localhost",
        port=26257,
        dbname="mydb",
        user="myuser",
        password="secret"
    )
    request_id = request.state.request_id or str(uuid.uuid4())
    request.state.request_id = request_id
    conn.set_session(autocommit=False)
    try:
        yield conn, request_id
        conn.commit()
        logger.info("transaction_commit", extra={"request_id": request_id, "status": "committed"})
    except Exception as e:
        conn.rollback()
        logger.warning("transaction_rollback", extra={"request_id": request_id, "error": str(e)})
        raise
    finally:
        conn.close()

@app.get("/users/{user_id}")
def read_user(user_id: int, request: Request, db=Depends(db_session)):
    conn, req_id = db
    with conn.cursor(cursor_factory=RealDictCursor) as cur:
        try:
            cur.execute("SELECT id, email, role FROM users WHERE id = %s", (user_id,))
            row = cur.fetchone()
            logger.info("sql_query", extra={
                "request_id": req_id,
                "user_id": user_id,
                "query": "SELECT id, email, role FROM users WHERE id = %s",
                "params": [user_id],
                "affected_rows": 1 if row else 0
            })
            if row is None:
                raise HTTPException(status_code=404, detail="User not found")
            return row
        except HTTPException:
            raise
        except Exception as e:
            logger.error("sql_error", extra={
                "request_id": req_id,
                "error": str(e),
                "query": "SELECT id, email, role FROM users WHERE id = %s",
                "params": [user_id]
            })
            raise HTTPException(status_code=500, detail="Internal error")

Key points specific to Cockroachdb:

• Include transaction metadata: Cockroachdb returns transaction IDs and retry counts in exceptions; surface these in logs to aid root cause analysis across nodes.
• Log commit and rollback outcomes explicitly; distributed commits can fail in ways that are opaque without explicit status tracking.
• Use structured logging (e.g., JSON) so that fields like request_id, user_id, and query can be indexed and correlated in SIEM or observability tools.

middleBrick’s Continuous Monitoring and GitHub Action integrations can validate that these logging patterns are present in your CI/CD pipeline, failing builds if risk thresholds are exceeded. This ensures that insufficient logging is caught before deployment, reducing the attack surface exposed by weak observability.