Information Disclosure in Fastapi with Cockroachdb

Information Disclosure occurs when a Fastapi application using Cockroachdb inadvertently exposes sensitive data through error messages, misconfigured serialization, or improper access controls. This combination is notable because Cockroachdb’s wire protocol and SQL dialect behave differently under certain conditions compared to single-node databases, and Fastapi’s reliance on async drivers and Pydantic models can amplify exposure if responses are not carefully shaped.

One common scenario is when an endpoint queries Cockroachdb with an invalid or non-existent identifier, and the application returns the raw database error to the client. Cockroachdb may include schema details, table names, or internal hints in error responses, especially when dealing with distributed SQL constructs like range lookups or leaseholder redirects. If Fastapi does not catch and sanitize these errors, the client receives stack traces or metadata that reveal database topology or object names.

Another vector arises from incomplete ORM or query builder usage. For example, using SQLAlchemy or Tortoise ORM with Cockroachdb without explicitly selecting only required columns can result in additional fields being serialized into JSON responses. If the model includes sensitive fields such as internal IDs, hashed secrets, or audit metadata, those fields may be included in API output unintentionally. Fastapi’s automatic JSON serialization via Pydantic will include all declared fields, so a misconfigured model acts as a disclosure channel.

Additionally, pagination or filtering logic that interacts with Cockroachdb can cause disclosure when misapplied. If a client-supplied offset or limit parameter is not validated, an attacker may use large offset values to trigger extensive result sets or cause the database to return metadata about row counts and distribution across nodes. Fastapi endpoints that mirror these parameters directly into Cockroachdb LIMIT/OFFSET clauses without sanitization can expose timing behavior or data distribution patterns that infer dataset size or structure.

Insecure default configurations in the connection layer also contribute. For instance, if the Cockroachdb connection string or session settings are serialized into logs or exception handlers, Fastapi may inadvertently expose credentials or certificate paths through tracebacks or monitoring integrations. Because Cockroachdb often operates in clustered or multi-region setups, connection metadata can hint at network zones or node identities, aiding reconnaissance for further attacks.

Remediation focuses on strict error handling, explicit field selection, input validation, and secure session management. Below are concrete code examples tailored for Fastapi with Cockroachdb.

1. Sanitize Database Errors

Wrap Cockroachdb queries in try/except blocks and map database exceptions to generic HTTP responses. Avoid exposing raw error strings.

from fastapi import FastAPI, HTTPException, status
import psycopg2
from psycopg2 import sql

app = FastAPI()

def get_db_connection():
    return psycopg2.connect(
        host="your-cockroachdb-host",
        port="26257",
        user="app_user",
        password="secure_password",
        database="app_db",
        sslmode="require",
        sslrootcert="cockroach-ca.pem"
    )

@app.get("/users/{user_id}")
async def read_user(user_id: int):
    conn = None
    try:
        conn = get_db_connection()
        with conn.cursor() as cur:
            query = sql.SQL("SELECT id, username, email FROM users WHERE id = %s")
            cur.execute(query, (user_id,))
            row = cur.fetchone()
            if row is None:
                raise HTTPException(status_code=404, detail="User not found")
            return {"id": row[0], "username": row[1], "email": row[2]}
    except psycopg2.Error as e:
        # Log full error internally, return generic message
        app.logger.error(f"Database error: {e}")
        raise HTTPException(
            status_code=status.HTTP_500_INTERNAL_SERVER_ERROR,
            detail="An error occurred while processing your request"
        )
    finally:
        if conn:
            conn.close()

2. Explicit Field Selection in Models

Define Pydantic models that include only necessary fields. Avoid using ORM models directly for serialization unless they are trimmed.

from fastapi import FastAPI
from pydantic import BaseModel
from typing import List
import psycopg2

app = FastAPI()

class UserPublic(BaseModel):
    id: int
    username: str
    email: str

def get_db_connection():
    return psycopg2.connect(
        host="your-cockroachdb-host",
        port="26257",
        user="app_user",
        password="secure_password",
        database="app_db",
        sslmode="require",
        sslrootcert="cockroach-ca.pem"
    )

@app.get("/users", response_model=List[UserPublic])
async def list_users():
    conn = get_db_connection()
    try:
        with conn.cursor() as cur:
            cur.execute("SELECT id, username, email FROM users")
            rows = cur.fetchall()
            return [UserPublic(id=r[0], username=r[1], email=r[2]) for r in rows]
    finally:
        conn.close()

3. Validate Pagination and Filtering Inputs

Sanitize limit and offset parameters to prevent excessive data retrieval and metadata leakage. Use bounded ranges and reject negative values.

from fastapi import FastAPI, Query, HTTPException
import psycopg2

app = FastAPI()

def get_db_connection():
    return psycopg2.connect(
        host="your-cockroachdb-host",
        port="26257",
        user="app_user",
        password="secure_password",
        database="app_db",
        sslmode="require",
        sslrootcert="cockroach-ca.pem"
    )

@app.get("/items")
async def list_items(
    limit: int = Query(10, ge=1, le=100),
    offset: int = Query(0, ge=0)
):
    conn = get_db_connection()
    try:
        with conn.cursor() as cur:
            cur.execute("SELECT id, name FROM items LIMIT %s OFFSET %s", (limit, offset))
            rows = cur.fetchall()
            return {"data": [{"id": r[0], "name": r[1]} for r in rows]}
    finally:
        conn.close()

4. Secure Connection and Session Handling

Do not log or serialize connection parameters. Use environment variables and ensure SSL is enforced. Avoid exposing stack traces in production.

# Configuration via environment variables
import os
from fastapi import FastAPI

app = FastAPI()

DB_CONFIG = {
    "host": os.getenv("COCKROACH_HOST"),
    "port": os.getenv("COCKRACH_PORT", "26257"),
    "user": os.getenv("COCKROACH_USER"),
    "password": os.getenv("COCKROACH_PASSWORD"),
    "database": os.getenv("COCKROACH_DB"),
    "sslmode": "require",
    "sslrootcert": os.getenv("COCKROACH_CA")
}