Pii Leakage in Fastapi with Cockroachdb

Pii Leakage occurs when personally identifiable information is returned to an unauthenticated caller or logged in plaintext. A Fastapi service using Cockroachdb can unintentionally expose PII through several realistic patterns. For example, an endpoint that queries a Cockroachdb SQL instance without verifying identity and returns rows directly may expose email, phone, or government ID fields. If the API relies on unauthenticated endpoints or misconfigured route permissions, middleBrick’s Authentication and BOLA/IDOR checks flag this as a high-severity finding because any client can request sensitive records by guessing identifiers.

Another common cause is verbose error messages or debug responses that include stack traces containing connection strings or query details. Cockroachdb error payloads sometimes surface internal table or column names, and when combined with Fastapi’s default exception handlers, this can disclose whether a specific PII field exists. Insecure logging practices in Fastapi middleware that print request parameters or database responses to stdout also create a leakage channel. middleBrick’s Data Output and Encryption checks detect when responses contain API keys, PII, or code fragments, and the Inventory Management and Unsafe Consumption checks highlight missing data minimization in query design.

Schema design plays a role. A Cockroachdb table with columns such as email, phone, and ssn that are not restricted by row-level security or field-level filtering can be over-fetched. If the Fastapi route uses an ORM or raw SQL and returns the full model without redaction, middleBrick’s Property Authorization and Input Validation checks highlight that consumers receive more data than necessary. Cross-referencing the OpenAPI spec with runtime findings is valuable here: the spec may declare a minimal response schema while the actual Cockroachdb query returns additional sensitive columns, creating a mismatch that indicates a leakage risk.

Rate Limiting and Data Exposure checks reveal whether endpoints that return PII are adequately protected against enumeration and scraping. Without proper controls, an attacker can iterate through user IDs and collect datasets that violate privacy regulations. middleBrick’s LLM/AI Security checks ensure that no prompts or responses inadvertently leak system instructions or model context when AI features are layered on top of the API. By mapping findings to frameworks like OWASP API Top 10 and GDPR, the report underscores the impact and remediation direction for each PII exposure scenario.

Apply targeted remediation to reduce PII exposure when Fastapi interacts with Cockroachdb. Start with strict query design: select only the fields required for the operation and omit sensitive columns unless explicitly authorized. Use parameterized queries to prevent injection and ensure proper handling of identifiers. Below is a concrete Fastapi example that connects to Cockroachdb, retrieves only non-sensitive fields for public endpoints, and uses role-based checks before returning PII.

from fastapi import Fastapi, Depends, HTTPException, status
from pydantic import BaseModel
import psycopg2
from contextlib import closing

app = Fastapi()

class UserPublic(BaseModel):
    user_id: int
    username: str

class UserPrivate(UserPublic):
    email: str
    phone: str

def get_db_connection():
    # In production, use a connection pool and secure credential management
    return psycopg2.connect(
        host="localhost",
        port="26257",
        user="app_user",
        password="strong_password",
        dbname="app_db",
        sslmode="require"
    )

def require_role(required: str):
    def role_checker(user_role: str = Depends(get_current_role)):
        if user_role != required:
            raise HTTPException(status_code=status.HTTP_403_FORBIDDEN, detail="Insufficient permissions")
        return user_role
    return role_checker

def get_current_role() -> str:
    # Placeholder: derive role from request context or token
    return "user"

@app.get("/users/{user_id}/public", response_model=UserPublic)
def get_public_user(user_id: int, conn = Depends(get_db_connection)):
    with closing(conn.cursor()) as cur:
        cur.execute("SELECT user_id, username FROM users WHERE user_id = %s", (user_id,))
        row = cur.fetchone()
    if row is None:
        raise HTTPException(status_code=404, detail="User not found")
    return UserPublic(user_id=row[0], username=row[1])

@app.get("/users/{user_id}/private", response_model=UserPrivate)
def get_private_user(user_id: int, role: str = Depends(require_role("admin")), conn = Depends(get_db_connection)):
    with closing(conn.cursor()) as cur:
        cur.execute("SELECT user_id, username, email, phone FROM users WHERE user_id = %s", (user_id,))
        row = cur.fetchone()
    if row is None:
        raise HTTPException(status_code=404, detail="User not found")
    return UserPrivate(user_id=row[0], username=row[1], email=row[2], phone=row[3])

This pattern limits data exposure by segregating public and private endpoints and selecting only necessary columns. Ensure that Cockroachdb connection parameters are injected securely and not logged. middleBrick’s CLI can be run as middlebrick scan <url> to validate that these endpoints do not leak PII to unauthenticated roles. The dashboard and GitHub Action integrations help enforce that future changes do not reintroduce leakage, while the MCP Server allows you to scan API contracts directly from your AI coding assistant.

Additionally, apply schema-level protections in Cockroachdb: use row-level security where supported, restrict column access based on roles, and avoid storing highly sensitive PII unless necessary. Configure Fastapi to suppress detailed errors in production and ensure responses exclude stack traces. By combining precise query selection, role-based access, and continuous scanning with middleBrick’s Pro plan for ongoing monitoring and CI/CD integration, you reduce the likelihood of PII leakage and maintain alignment with compliance requirements.