Mass Assignment in Fastapi with Cockroachdb

Mass assignment occurs when a Fastapi endpoint binds user-supplied data directly to a Pydantic model and then uses that model to construct database operations against Cockroachdb without explicit field filtering. In this stack, the risk is not only over-permissive models but also the assumption that Cockroachdb will enforce safe field mapping. Cockroachdb is a PostgreSQL-compatible database, so it does not inherently reject columns that are not present in an INSERT or UPDATE; instead, it accepts the provided column set. If the Fastapi layer does not explicitly whitelist fields, an attacker can include extra keys (e.g., is_admin, role, permissions) that map to columns in the Cockroachdb table, and the database will persist them.

For example, consider a typical Fastapi route that creates a user. A developer might define a Pydantic model for input that only includes username and email, but if they reuse the same model for database insertion without excluding read-only or privileged fields, and the database table contains columns like is_admin or reset_token, an attacker can supply those fields in the JSON payload. Because Cockroachdb accepts the statement, the privileged columns are written. This becomes more dangerous when the same table is later queried by other endpoints that trust the database values, enabling privilege escalation or account takeover. The API schema (OpenAPI 3.0/3.1) may also reflect these extended fields if models are generated from the database or if $ref definitions are too permissive, which can mislead developers about the intended surface.

Another common pattern is using SQLAlchemy or an ORM with dynamic models where field lists are derived from request bodies. If the code builds an insert or update query by iterating over request dict keys and mapping them to Cockroachdb columns, any extra key becomes a target. Compounded by the speed of scans (5–15 seconds), middleBrick can detect whether endpoints accept unexpected fields and whether the underlying table contains sensitive columns, highlighting the mismatch between declared input models and actual database schema. Findings here align with OWASP API Top 10 A1: Broken Object Level Authorization when the exposure enables tampering with permissions or identifiers.

Remediation guidance centers on explicit allow-listing. In Fastapi, define a base Pydantic model with only safe fields for input, and a separate model for database output. Use selective insertion, ensuring that only whitelisted keys are passed to Cockroachdb statements. Avoid reusing models across input and database layers without strict filtering. With the CLI tool (middlebrick scan ), you can quickly validate whether your endpoints reflect unsafe schemas and whether runtime submissions can reach the database with unexpected fields.

Secure remediation requires strict separation between input validation and database mapping. Define input models that exclude internal or privileged fields, and use explicit column lists or ORM select/deselect patterns when interacting with Cockroachdb. Below are concrete, working examples.

Example 1: Explicit allow-list with asyncpg

This approach bypasss ORM dynamic mapping entirely and sends only intended columns to Cockroachdb.

import asyncio
import asyncpg
from fastapi import FastAPI, HTTPException
from pydantic import BaseModel, EmailStr

app = FastAPI()

class UserCreateInput(BaseModel):
    username: str
    email: EmailStr

async def get_pool():
    return await asyncpg.create_pool(
        user="appuser",
        password="**",
        database="appdb",
        host="cockroachdb-host",
        port26257
    )

@app.post("/users")
async def create_user(data: UserCreateInput):
    pool = await get_pool()
    async with pool.acquire() as conn:
        # Explicit column list ensures only intended fields are inserted
        query = """
            INSERT INTO users (username, email, created_at)
            VALUES ($1, $2, now())
            RETURNING id, username, email, created_at
        """
        try:
            row = await conn.fetchrow(query, data.username, data.email)
            return dict(row)
        except Exception as e:
            raise HTTPException(status_code=400, detail=str(e))

Example 2: SQLAlchemy with select and exclude read-only columns

When using SQLAlchemy, avoid passing request dict directly to model constructors; instead, filter to permitted fields.

from fastapi import Fastapi, HTTPException
from sqlalchemy import select, insert
from sqlalchemy.ext.asyncio import AsyncSession, create_async_engine
from sqlalchemy.orm import sessionmaker, DeclarativeBase
from pydantic import BaseModel, EmailStr

app = FastAPI()
engine = create_async_engine("cockroachdb+psycopg2://appuser:**@cockroachdb-host:26257/appdb?sslmode=require")
AsyncSessionLocal = sessionmaker(engine, class_=AsyncSession, expire_on_commit=False)

class BaseModelORM(DeclarativeBase):
    pass

class UserORM(BaseModelORM):
    __tablename__ = "users"
    id = Column(Integer, primary_key=True)
    username = Column(String)
    email = Column(String)
    is_admin = Column(Boolean, default=False)

class UserCreateInput(BaseModel):
    username: str
    email: EmailStr

@app.post("/users-safe")
async def create_user_safe(input_data: UserCreateInput, session: AsyncSession = Depends(get_session)):
    # Filter to only allowed fields
    allowed = {"username", "email"}
    filtered = {k: v for k, v in input_data.dict().items() if k in allowed}
    stmt = insert(UserORM).values(**filtered).returning(UserORM.id, UserORM.username, UserORM.email)
    try:
        result = await session.execute(stmt)
        await session.commit()
        return result.mappings().first()
    except Exception as e:
        await session.rollback()
        raise HTTPException(status_code=400, detail=str(e))

    # Always close session appropriately in real code

Schema and validation checks

Ensure your OpenAPI spec does not expose internal fields in request bodies. Use x-exclude-from-body or similar vendor extensions if generating code, and validate that request examples align with the allow-list. middleBrick’s OpenAPI/Swagger analysis (supporting 2.0, 3.0, 3.1 with full $ref resolution) can surface mismatches between declared schemas and runtime behavior, helping you confirm that sensitive columns are not inadvertently writable.

Combine these practices with continuous monitoring. The Pro plan includes continuous monitoring for APIs on a configurable schedule, with alerts delivered via Slack or Teams, so changes that reintroduce mass assignment risks are caught early. The GitHub Action can fail builds if a new endpoint or modification introduces broader input acceptance than intended.