Bola Idor in Phoenix with Cockroachdb

Broken Object Level Authorization (BOLA) occurs when an API exposes internal object identifiers (such as a Cockroachdb primary key) without verifying that the requesting user is authorized to access that specific resource. In a Phoenix application using Cockroachdb as the backend, this typically surfaces through endpoints that accept an :id parameter in the URL and fetch a record directly from the database without confirming ownership or access rights.

Consider a typical route defined in a Phoenix controller that retrieves a user document:

get "/api/users/:id", UserController, :show

With Cockroachdb, the query might be written as:

query = from u in "users",
        where: u.id == ^id,
        select: u

If the controller passes the id from the request directly into this query without ensuring the current session or token corresponds to that user’s record, an authenticated attacker can change the :id to reference another user’s row. Because Cockroachdb enforces SQL semantics but does not enforce application-level authorization, the database will return the record if it exists, and Phoenix will render it to the attacker. This is a classic BOLA/IDOR pattern.

The vulnerability is more pronounced when identifiers are predictable (e.g., sequential integers or UUIDs that are not tied to access control). Cockroachdb, being a distributed SQL database, does not provide row-level security features that would automatically restrict rows based on the client; that responsibility lies in the application logic. Without explicit checks such as ensuring the authenticated subject matches the owning tenant or user, the API exposes a horizontal privilege escalation surface.

In a microservices or multi-tenant setup, BOLA in Phoenix can also arise when foreign keys are used across bounded contexts. For example, an endpoint like /api/invoices/:invoice_id might query Cockroachdb for an invoice without confirming that the invoice belongs to the authenticated organization. Because Cockroachdb supports complex joins across tables, a developer might inadvertently expose foreign keys that allow traversal from one tenant’s data to another if authorization checks are omitted at the service boundary.

To summarize, the combination of Phoenix routing, Cockroachdb as the data store, and missing authorization checks on identifiers creates a BOLA/IDOR condition where object-level permissions are bypassed simply by manipulating the :id parameter in the request.

Remediation focuses on ensuring that every data retrieval includes a check that ties the requested record to the current subject (user, tenant, or token). Below are concrete examples for Phoenix using Ecto with Cockroachdb.

1) Enforce ownership via the caller’s identity. If your user schema has a :tenant_id or :user_id, include it in the query:

def show(conn, %{"id" => id}) do
  user_id = conn.assigns.current_user.id
  query = from u in "users",
          where: u.id == ^id and u.id == ^user_id,
          select: u
  case Repo.one(query) do
    nil -> conn |> put_status(:not_found) |> json(%{error: "Not found"})
    user -> json(conn, user)
  end
end

This ensures that even if the attacker changes :id, the row will not be returned unless the ID also matches the authenticated user’s ID.

2) For tenant-based isolation, scope by tenant identifier:

def index(conn, _params) do
  tenant_id = conn.assigns.current_tenant.id
  query = from i in "invoices",
          where: i.tenant_id == ^tenant_id,
          select: i
  invoices = Repo.all(query)
  json(conn, invoices)
end

3) Use Phoenix’s resource scoping with route parameters that are not directly mapped to database keys. Instead of exposing raw Cockroachdb primary keys, use slugs or UUIDs that are namespaced per tenant, and validate mapping on read:

def show(conn, %{"slug" => slug}) do
  tenant_id = conn.assigns.current_tenant.id
  case Repo.one(from r in "resources",
                where: r.slug == ^slug and r.tenant_id == ^tenant_id,
                select: r) do
    nil -> conn |> put_status(:not_found) |> json(%{error: "Not found"})
    resource -> json(conn, resource)
  end
end

4) Apply principle of least privilege at the database user level used by Phoenix. Configure the Cockroachdb connection with a role that has read/write access only to the necessary tables and rows, and avoid using a superuser for application connections. While this does not replace application checks, it limits the impact of a compromised component.

5) Validate and sanitize all inputs that influence queries. Even though Cockroachdb is resilient to many SQL injection forms when using parameterized queries, ensure that any dynamic schema or table names are strictly whitelisted:

allowed_columns = %{"name" => "name", "created_at" => "created_at"}
column = Map.get(allowed_columns, params["sort"], "id")
query = from r in "resources",
        order_by: [{^direction, field(r, ^column)}],
        where: r.tenant_id == ^tenant_id,
        select: r

These patterns reinforce that authorization must be explicit and tied to the requesting context. By combining scoped queries, tenant isolation, and strict input validation, the BOLA surface in a Phoenix + Cockroachdb stack is significantly reduced.