Cryptographic Failures in Fastapi with Cockroachdb

Cryptographic failures occur when data is not adequately protected in transit or at rest. The combination of FastAPI and CockroachDB can expose risks if cryptographic controls are misconfigured or omitted. FastAPI applications often interact with CockroachDB over network connections; if TLS is not enforced for the CockroachDB client, credentials, queries, and result sets can traverse the network unencrypted. This is a common failure pattern when developers use a CockroachDB connection string without specifying sslmode=require or equivalent, leaving connections susceptible to eavesdropping and tampering.

Another specific issue arises when application-level encryption is attempted but key management is weak. For example, storing encryption keys in the same environment as the FastAPI app (or in source code) undermines confidentiality. CockroachDB’s native support for secure storage does not automatically protect data that is explicitly stored in columns by the application; if FastAPI encrypts data before insertion using a static key embedded in the service, compromise of the service also compromises the keys. Additionally, improper certificate validation when connecting to CockroachDB can lead to man-in-the-middle attacks, where an attacker presents a fake certificate and decrypts or alters traffic.

Compliance mappings such as OWASP API Top 10 (2023) A02:2023 – Cryptographic Failures and SOC2 CC6.1 highlight the need for strong encryption and key management. Real-world CVEs, such as CVE-2023-29729 (related to improper certificate validation) and issues around weak cipher suites, illustrate the impact when cryptographic controls are not rigorously applied. MiddleBrick’s LLM/AI Security checks can detect system prompt leakage that might expose encryption-related logic, and its unauthenticated endpoint detection can identify insecurely exposed APIs that handle sensitive data without encryption.

Remediation centers on enforcing TLS for all CockroachDB connections, managing keys securely, and validating certificates. Below are concrete, working code examples for a FastAPI application connecting to CockroachDB with SSL mode enabled.

1. Enforce TLS in the database connection

Use a connection string that requires TLS and provide the CA certificate to validate the server. This prevents unencrypted or spoofed connections.

import asyncpg
from fastapi import FastAPI
import os

app = FastAPI()

# Example connection string with SSL mode require and CA certificate
# Store CA certificate as a file or Kubernetes secret in production
DATABASE_URL = os.getenv(
    "DATABASE_URL",
    "postgresql://myuser:mypassword@cockroachdb-host:26257/mydb?sslmode=require&sslrootcert=/app/secrets/ca.pem"
)

@app.on_event("startup")
async def startup():
    app.state.db = await asyncpg.create_pool(DATABASE_URL)

@app.on_event("shutdown")
async def shutdown():
    await app.state.db.close()

2. Use application-level encryption with secure key handling

Encrypt sensitive fields before insertion using a key managed outside the application runtime (e.g., environment variables injected via secrets, or a key management service). Avoid hardcoding keys.

from cryptography.hazmat.primitives.ciphers.aead import AESGCM
from fastapi import FastAPI, HTTPException
import os
import base64

app = FastAPI()

# Key should be injected via secure secret store, not in code
ENCRYPTION_KEY_B64 = os.getenv("ENCRYPTION_KEY_B64")
if not ENCRYPTION_KEY_B64:
    raise RuntimeError("ENCRYPTION_KEY_B64 environment variable is required")

key = base64.b64decode(ENCRYPTION_KEY_B64)
aesgcm = AESGCM(key)

def encrypt_value(plaintext: str, associated_data: bytes = b"") -> str:
    nonce = os.urandom(12)
    ciphertext = aesgcm.encrypt(nonce, plaintext.encode(), associated_data)
    return base64.b64encode(nonce + ciphertext).decode()

def decrypt_value(b64_value: str, associated_data: bytes = b"") -> str:
    data = base64.b64decode(b64_value)
    nonce, ciphertext = data[:12], data[12:]
    return aesgcm.decrypt(nonce, ciphertext, associated_data).decode()

@app.post("/store")
async def store_data(field1: str, ssn: str):
    encrypted_ssn = encrypt_value(ssn)
    # Use the asyncpg pool from previous example, omitted for brevity
    # await app.state.db.execute(
    #     "INSERT INTO users (field1, ssn_encrypted) VALUES ($1, $2)",
    #     field1, encrypted_ssn
    # )
    return {"status": "stored", "field1": field1}

@app.get("/retrieve/{field1}")
async def retrieve_data(field1: str):
    # row = await app.state.db.fetchrow(
    #     "SELECT ssn_encrypted FROM users WHERE field1 = $1", field1
    # )
    # Simulated row for example
    row = type("Row", (), {"ssn_encrypted": encrypt_value("123-45-6789")})()
    if row.ssn_encrypted is None:
        raise HTTPException(status_code=404, detail="Not found")
    return {"field1": field1, "ssn": decrypt_value(row.ssn_encrypted)}

3. Validate server certificates and avoid insecure defaults

Ensure CockroachDB server certificates are validated by providing the CA certificate and avoiding sslmode=disable or sslmode=allow. In FastAPI, this is controlled via the connection string and filesystem access to the CA cert.

# Correct: strict certificate validation
# Place ca.pem in a secure location with restricted permissions
DATABASE_URL = "postgresql://myuser:mypassword@host:26257/mydb?sslmode=verify-full&sslrootcert=/app/secrets/ca.pem"

# Incorrect examples to avoid:
# sslmode=disable — no encryption
# sslmode=require — encryption but no cert validation (vulnerable to MITM)

4. Integrate scanning into your workflow

Use the middlebrick CLI to validate that your endpoints do not leak cryptographic material and that LLM endpoints are not inadvertently exposed. For example:

# Scan your FastAPI service from the terminal
middlebrick scan https://api.example.com/openapi.json

For teams using CI/CD, the GitHub Action can enforce a minimum security score before deployment, and the MCP Server enables scanning directly from AI coding assistants when you iterate on database integration code.