Cross Site Request Forgery in Fastapi with Cockroachdb

Cross Site Request Forgery (CSRF) in a FastAPI application backed by CockroachDB can arise when state-changing endpoints rely solely on cookies for session identification without anti-CSRF tokens. FastAPI does not enable CSRF protection by default, and if developers implement session management using cookie-based authentication (e.g., signed cookies or session identifiers stored in cookies) without additional safeguards, an attacker can trick a victim’s browser into submitting unauthorized requests to those endpoints. Because CockroachDB is often used as the backend data store, malicious requests can result in unauthorized writes or changes in the distributed SQL database, such as updating user email addresses, modifying permissions, or performing transactions on behalf of the authenticated user.

The risk is compounded when endpoints accept JSON payloads but also maintain cookie-based session context, creating a mismatch between authentication and request validation. For example, an authenticated user who visits a malicious site may trigger a DELETE or POST to /api/account/change-email. If the endpoint only checks the presence of a valid session cookie stored in the browser and does not validate a CSRF token or require a same-site cookie attribute plus explicit origin checks, the request will be executed in the context of the victim’s CockroachDB-backed session. CockroachDB’s transactional semantics do not prevent these unauthorized operations; they will commit if the application’s permissions and validation logic permit them.

In practice, this often maps to OWASP API Top 10 A01:2023 — Broken Object Level Authorization when combined with missing CSRF considerations, and can intersect with authentication flaws. An attacker does not need to compromise CockroachDB directly; they leverage the web application’s trust in cookie-based sessions. Therefore, securing the API requires explicit CSRF mitigation strategies in FastAPI, including anti-CSRF tokens, strict same-site cookie policies, and origin/referrer checks, rather than relying on the database layer to enforce authorization.

To mitigate CSRF in FastAPI with CockroachDB, implement defense-in-depth: use anti-CSRF tokens for state-changing operations, enforce strict cookie attributes, and validate request origins. Below are concrete, working examples that integrate with CockroachDB using an async driver such as asyncpg (CockroachDB wire-compatible).

1. Set secure, same-site session cookies

from fastapi import FastAPI, Request, Response
from fastapi.responses import JSONResponse
import secrets

app = FastAPI()

@app.post("/login")
async def login(response: Response):
    token = secrets.token_urlsafe(32)
    # Set a secure, HttpOnly, SameSite=Strict cookie
    response.set_cookie(
        key="session_token",
        value=token,
        httponly=True,
        secure=True,
        samesite="strict",
        max_age=3600
    )
    return {"status": "ok"}

2. Anti-CSRF token generation and validation

from fastapi import FastAPI, Request, Depends, HTTPException, status
from fastapi.responses import JSONResponse
import secrets
import asyncpg

app = FastAPI()

# Acquire a CockroachDB connection (asyncpg-compatible)
async def get_db():
    pool = asyncpg.create_pool(
        host="your-cockroachdb-host",
        port=26257,
        user="your_user",
        password="your_password",
        database="your_db",
        command_timeout=10
    )
    return pool

def get_csrf_token(request: Request):
    token = request.cookies.get("csrf_token")
    if not token:
        raise HTTPException(status_code=status.HTTP_403_FORBIDDEN, detail="Missing CSRF token")
    return token

@app.post("/api/account/change-email")
async def change_email(
    request: Request,
    response: Response,
    form_data: dict,
    db=Depends(get_db)
):
    session_token = request.cookies.get("session_token")
    csrf_token = get_csrf_token(request)

    # Validate session and CSRF token binding (simplified)
    async with db.acquire() as conn:
        user = await conn.fetchrow("SELECT id FROM users WHERE session_token=$1", session_token)
        if not user:
            raise HTTPException(status_code=401, detail="Invalid session")
        # In practice, store CSRF token per session in CockroachDB and compare
        stored_csrf = await conn.fetchval("SELECT csrf_token FROM sessions WHERE user_id=$1", user["id"])
        if not secrets.compare_digest(csrf_token, stored_csrf):
            raise HTTPException(status_code=403, detail="CSRF token mismatch")

        # Proceed with the transaction on CockroachDB
        await conn.execute(
            "UPDATE users SET email=$1 WHERE id=$2",
            form_data["email"],
            user["id"]
        )

    response.set_cookie(
        key="csrf_token",
        value=secrets.token_urlsafe(32),
        httponly=False,
        secure=True,
        samesite="strict",
        max_age=3600
    )
    return JSONResponse({"status": "email updated"})

3. Enforce SameSite and Origin checks for state-changing methods

from fastapi.middleware.cors import CORSMiddleware

app.add_middleware(
    CORSMiddleware,
    allow_origins=["https://trusted.example.com"],
    allow_credentials=True,
    allow_methods=["POST", "PUT", "DELETE"],
    allow_headers=["Content-Type", "Authorization"],
)

These measures ensure that even if a malicious site triggers a request from the victim’s browser, the browser’s same-site cookie policies and missing anti-CSRF token will block the request or cause it to fail. Because CockroachDB transactions will only execute when the application validates both session integrity and CSRF tokens, unauthorized state changes are prevented.