Missing Tls in Fastapi with Cockroachdb

A Fastapi application that connects to Cockroachdb without TLS exposes connection parameters and data in transit. Cockroachdb accepts SQL connections over standard PostgreSQL wire protocol, and without TLS the client-to-node channel is unencrypted. When Fastapi builds a connection string dynamically (for example using environment variables or a settings module) and omits sslmode=require or a CA certificate, the ORM or driver sends queries and results in plaintext. An attacker on the same network or positioned at a relay point can observe authentication credentials, session tokens, or application-level data that might be reused elsewhere.

In a typical Fastapi stack, a developer might use asyncpg, sqlalchemy with asyncpg dialect, or Tortoise ORM to reach Cockroachdb. If the DSN is constructed without ssl mode enforcement, the initial authentication handshake—including username and potentially a password—travels unencrypted. This is especially risky in distributed environments where services communicate across internal networks that are assumed to be safe. middleBrick’s unauthenticated scans detect missing transport-layer protections by probing endpoint metadata and configuration endpoints; for API security, the equivalent is verifying that all external connections enforce encryption in transit.

Additionally, missing TLS can lead to opportunistic tampering. An attacker capable of man-in-the-middle position could alter DNS records or route traffic to a malicious node that presents a self-signed certificate. If the Fastapi app does not validate the server certificate (by setting sslmode=verify-ca or verify-full and providing a trusted CA bundle), the client will accept the forged identity. This enables credential interception or injection of malicious data into application queries. The combination of Fastapi’s async request handling and Cockroachdb’s distributed nature amplifies the exposure, because connections may traverse multiple network segments and rely on consistent client-side enforcement.

Remediation requires both connection string configuration and certificate validation. Cockroachdb recommends using sslmode=verify-ca with a trusted CA certificate, and preferrably verify-full when host identity is also validated. Below are concrete examples using asyncpg through sqlalchemy and direct asyncpg usage, demonstrating how to enforce TLS in Fastapi.

Example 1: SQLAlchemy with asyncpg and TLS

from sqlalchemy.ext.asyncio import create_async_engine, AsyncSession
from sqlalchemy.orm import sessionmaker
import os

# Ensure environment provides these or load from a secure vault
DB_HOST = os.getenv("DB_HOST", "localhost")
DB_PORT = os.getenv("DB_PORT", "26257")
DB_NAME = os.getenv("DB_NAME", "defaultdb")
DB_USER = os.getenv("DB_USER", "root")
DB_CERT_PATH = os.getenv("DB_CERT_PATH", "/secrets/client.crt")
DB_KEY_PATH = os.getenv("DB_KEY_PATH", "/secrets/client.key")
DB_CA_PATH = os.getenv("DB_CA_PATH", "/secrets/ca.crt")

# Build connection string with full TLS enforcement
dsn = (
    f"postgresql+asyncpg://{DB_USER}@{DB_HOST}:{DB_PORT}/{DB_NAME}"
    f"?sslmode=verify-ca"
    f"&sslcert={DB_CERT_PATH}"
    f"&sslkey={DB_KEY_PATH}"
    f"&sslrootcert={DB_CA_PATH}"
)

engine = create_async_engine(dsn, echo=False)
AsyncLocalSession = sessionmaker(engine, class_=AsyncSession, expire_on_commit=False)

async def get_session():
    async with AsyncLocalSession() as session:
        yield session

Example 2: Direct asyncpg with TLS

import asyncio
import asyncpg
import os

async def fetch_users():
    conn = await asyncpg.connect(
        host=os.getenv("DB_HOST", "localhost"),
        port=int(os.getenv("DB_PORT", 26257)),
        database=os.getenv("DB_NAME", "defaultdb"),
        user=os.getenv("DB_USER", "root"),
        ssl={  # Enforce TLS with certificate validation
            "ssl_certfile": os.getenv("DB_CERT_PATH", "/secrets/client.crt"),
            "ssl_keyfile": os.getenv("DB_KEY_PATH", "/secrets/client.key"),
            "ssl_root_cert": os.getenv("DB_CA_PATH", "/secrets/ca.crt"),
            "ssl_mode": "verify-ca",  # or "verify-full" for hostname checks
        }
    )
    try:
        rows = await conn.fetch("SELECT username, email FROM users WHERE active = $1", True)
        return rows
    finally:
        await conn.close()

if __name__ == "__main__":
    result = asyncio.run(fetch_users())
    for r in result:
        print(r)

Operational practices

• Store certificates and keys in a secure secret manager; reference them via paths or environment variables as shown.
• Rotate CA and client certificates according to your organization’s policy; ensure Fastapi reloads configuration safely if certificates change at runtime.
• Use mTLS when your deployment model requires client authentication; Cockroachdb supports this by requiring client certificates signed by the same CA.
• Validate that your scanning or monitoring tools also enforce TLS; middleBrick checks for missing TLS on API endpoints, and equivalent checks should apply to database connections in your CI/CD pipeline.