Insecure Direct Object Reference in Fastapi with Cockroachdb

Insecure Direct Object Reference (IDOR) occurs when an API exposes a reference to a resource—such as a numeric ID or UUID—in a predictable way and fails to enforce that the authenticated context has permission to access that specific resource. In Fastapi with Cockroachdb, this risk arises when route parameters like user_id or document_id are taken directly from the URL and used to build Cockroachdb SQL queries without verifying that the requesting user owns or is authorized to view that object.

Consider a Fastapi endpoint designed to retrieve a user profile by ID:

from fastapi import FastAPI, Depends, HTTPException
from sqlalchemy import text
from database import get_session  # provides an async session to Cockroachdb

app = FastAPI()

@app.get("/users/{user_id}")
async def get_user(user_id: int, session=Depends(get_session)):
    result = session.execute(text("SELECT id, email, role FROM users WHERE id = :uid"), {"uid": user_id})
    row = result.fetchone()
    if row is None:
        raise HTTPException(status_code=404, detail="Not found")
    return {"id": row[0], "email": row[1], "role": row[2]}

If the API trusts the incoming user_id and uses it verbatim in a Cockroachdb SQL statement without confirming the requester’s authorization to that user ID, an attacker can change the ID (e.g., /users/123 to /users/124) and enumerate other users’ data. Cockroachdb returns the requested row if it exists, and the API leaks information across user boundaries. This becomes more impactful when sensitive fields like roles, permissions, or internal identifiers are exposed.

In a distributed SQL environment like Cockroachdb, developers sometimes assume that strong consistency and transactional guarantees reduce application-level authorization risks. However, the database enforces data constraints and isolation, not application-level authorization. An attacker can exploit predictable integer or sequential IDs to traverse relationships—for example, by probing /documents/1, /documents/2, etc.—and discover documents they should not see. If those documents contain regulated data, the exposure also increases compliance risk under frameworks such as OWASP API Top 10 (2023) A1: Broken Object Level Authorization, PCI-DSS, and SOC2.

Another common pattern is binding path parameters directly to object references used in Cockroachdb queries without considering multi-tenancy or tenant isolation. For instance, a workspace-based API might accept workspace_id and document_id but only validate the document existence in Cockroachdb, not whether the document belongs to the provided workspace or whether the caller has workspace access. Because Cockroachdb does not inherently enforce tenant boundaries at the SQL layer, the application must explicitly encode these checks. Missing tenant and ownership checks turn predictable object references into an IDOR vector.

IDOR also intersects with other checks performed by middleBrick, such as BOLA/IDOR and Property Authorization. Even if authentication is present, improper authorization logic allows one user to manipulate or view another user’s resources by tweaking object references. In Cockroachdb-driven Fastapi services, this often maps to missing row-level security policies or incomplete application-level authorization checks before constructing SQL statements.

Remediation centers on ensuring that every data access decision considers the requester’s identity and permissions, not just the existence of the requested object. Below are concrete, Cockroachdb-oriented fixes for Fastapi that you can apply immediately.

1. Include the requester’s ID in the query predicate

Always filter by the authenticated subject rather than trusting the client-supplied identifier alone. Combine the resource ID with the requester’s ID or tenant to form a compound lookup key.

from fastapi import FastAPI, Depends, HTTPException
from sqlalchemy import text
from database import get_session, get_current_user  # get_current_user returns {user_id, role, tenant_id}

app = FastAPI()

@app.get("/users/{user_id}")
async def get_user(user_id: int, user=Depends(get_current_user), session=Depends(get_session)):
    result = session.execute(
        text("""
            SELECT id, email, role FROM users
            WHERE id = :uid AND id = :requester_id
            LIMIT 1
        """),
        {"uid": user_id, "requester_id": user.user_id}
    )
    row = result.fetchone()
    if row is None:
        raise HTTPException(status_code=403, detail="Forbidden or not found")
    return {"id": row[0], "email": row[1], "role": row[2]}

This ensures that even if an attacker changes user_id, the query will return no row unless the ID matches the authenticated user’s ID.

2. Enforce tenant or workspace ownership explicitly

For multi-tenant applications, include the tenant or workspace identifier in both the route or query parameters and the SQL predicate.

from fastapi import FastAPI, Depends, HTTPException
from sqlalchemy import text
from database import get_session, get_current_user

app = FastAPI()

@app.get("/workspaces/{workspace_id}/documents/{document_id}")
async def get_document(
    workspace_id: int,
    document_id: int,
    user=Depends(get_current_user),
    session=Depends(get_session)
):
    result = session.execute(
        text("""
            SELECT id, title, content FROM documents
            WHERE id = :doc_id
              AND workspace_id = :workspace_id
              AND workspace_id IN (
                  SELECT workspace_id FROM workspace_members
                  WHERE user_id = :user_id
              )
            LIMIT 1
        """),
        {
            "doc_id": document_id,
            "workspace_id": workspace_id,
            "user_id": user.user_id
        }
    )
    row = result.fetchone()
    if row is None:
        raise HTTPException(status_code=403, detail="Document not accessible")
    return {"id": row[0], "title": row[1], "content": row[2]}

This pattern binds the document to a workspace and checks workspace membership on each request, preventing IDOR across tenants even when object IDs are guessable.

3. Use parameterized queries and avoid string interpolation

Never construct SQL by concatenating user input. Use bound parameters with Cockroachdb to prevent injection and ensure type safety. The examples above use text() with named placeholders, which is the recommended approach in Fastapi with SQLAlchemy.

4. Apply principle of least privilege to the Cockroachdb role used by Fastapi

Ensure the database user your Fastapi service connects with has only the permissions it needs—typically SELECT/INSERT/UPDATE/DELETE on specific tables, not superuser privileges. This limits the impact of any residual authorization bug.

5. Add logging and monitoring for suspicious access patterns

Log failed authorization attempts where a requested ID does not belong to the requester. Correlate these events across your Fastapi service and Cockroachdb audit logs to detect enumeration attacks early.

By combining route design discipline, explicit ownership checks in SQL, and least-privilege database credentials, you mitigate IDOR in Fastapi applications backed by Cockroachdb without relying on the database alone to enforce application-level permissions.