Broken Access Control in Chi with Cockroachdb

Broken Access Control in a Chi application that uses Cockroachdb typically occurs when route or handler authorization is missing or inconsistent, and database permissions do not enforce the same boundaries. Chi is a lightweight router, so it does not provide built-in authorization; developers must explicitly enforce role- or scope-based checks on each route. If those checks are omitted or applied inconsistently, an authenticated user can reach endpoints that should be restricted.

When Cockroachdb is the backend, the risk is compounded if queries are built by naively interpolating user input without verifying that the requesting user owns the targeted resource. For example, a route like /api/tenants/:tenant_id/data might extract tenant_id from the URL and use it in a SQL query such as SELECT * FROM data WHERE tenant_id = $1. Without a preceding authorization check that confirms the requesting user belongs to that tenant, any authenticated user can change the tenant_id parameter to access another tenant’s data, resulting in Insecure Direct Object References (IDOR) or Broken Level Authorization (BOLA).

Cockroachdb’s SQL semantics support this pattern, but they do not by default enforce row-level security based on the application identity. If the application relies only on Chi route guards and omits per-request ownership checks, or if the database user used by the app has broader privileges than necessary, a compromised or malicious actor can traverse IDs to access or modify data across tenants. Real-world findings often map this to OWASP API Top 10 A01:2023 – Broken Access Control and can intersect with compliance frameworks such as PCI-DSS and SOC2 when multi-tenant isolation is required.

In practice, this vulnerability surfaces when:

• Chi middleware or route-level authorization is missing or bypassed via direct URL manipulation.
• SQL queries in handlers use raw user input (e.g., URL params or query strings) to construct tenant or ownership filters without verifying the caller’s rights.
• Database permissions allow the application user to read or write rows belonging to other tenants because row-level policies are not defined or are not enforced at query time.

An attacker who discovers this can escalate from viewing to modifying data, potentially violating tenant isolation. For example, changing a numeric or UUID identifier in the path or crafting requests that exploit predictable IDs can lead to unauthorized data exposure or manipulation. middleBrick scans detect these patterns by correlating unauthentized endpoint behavior with OpenAPI path definitions and by checking whether authorization checks are consistently applied across routes.

Remediation centers on enforcing ownership or tenant checks in every handler and ensuring database queries respect those checks with parameterized SQL. Below are concrete, realistic examples using Chi and Cockroachdb in Go.

1. Enforce tenant ownership in Chi middleware

Use Chi middleware to validate tenant access before reaching the handler. This ensures every request confirms the user’s relationship to the tenant.

// tenant_middleware.go
package main

import (
	"context"
	"net/http"

	"github.com/go-chi/chi/v5"
)

// TenantClaims can be added by an auth middleware earlier in the chain.
type TenantClaims struct {
	UserID   string
	TenantID string
}

func requireTenant(next http.Handler) http.Handler {
	return http.HandlerFunc(func(w http.ResponseWriter, r *http.Request) {
		// Assume authMiddleware populated context with TenantClaims
		claims, ok := r.Context().Value("tenantClaims").(TenantClaims)
		if !ok {
			http.Error(w, "Unauthorized", http.StatusUnauthorized)
			return
		}
		// Expose tenantID for downstream use
		ctx := context.WithValue(r.Context(), "tenantID", claims.TenantID)
		next.ServeHTTP(w, r.WithContext(ctx))
	})
}

func routes() http.Handler {
	r := chi.NewRouter()
	r.Use(requireTenant)
	r.Get("/api/tenants/{tenant_id}/data", getDataHandler)
	return r
}

2. Parameterized query with tenant_id binding

In the handler, always bind the tenant_id from the validated context rather than trusting the URL parameter. Use Cockroachdb placeholders ($1, $2) to avoid injection and ensure tenant scoping.

// data_handler.go
package main

import (
	"context"
	"database/sql"
	"net/http"

	_ "github.com/lib/pq"
)

func getDataHandler(w http.ResponseWriter, r *http.Request) {
	ctx := r.Context()
	requestedTenant := chi.URLParam(r, "tenant_id")
	// Use the tenant ID from auth context, not the URL parameter directly
	currentTenant := ctx.Value("tenantID").(string)
	if requestedTenant != currentTenant {
		http.Error(w, "Forbidden", http.StatusForbidden)
		return
	}

	var result string
	row := db.QueryRowContext(ctx, "SELECT content FROM tenant_data WHERE tenant_id = $1 AND id = $2", currentTenant, requestedTenant)
	err := row.Scan(&result)
	if err != nil {
		if err == sql.ErrNoRows {
			http.Error(w, "Not found", http.StatusNotFound)
		} else {
			http.Error(w, "Internal error", http.StatusInternalServerError)
		}
		return
	}
	w.Write([]byte(result))
}

3. Enforce row-level security in Cockroachdb

Define a simple row-level policy so that even if an application-level check is misapplied, Cockroachdb prevents cross-tenant reads. This example creates a table with a tenant_id column and a policy that allows rows only when the session variable matches.

-- tenant_setup.sql
CREATE TABLE tenant_data (
    id UUID PRIMARY KEY,
    tenant_id STRING NOT NULL,
    content STRING
);

-- Enable tenant isolation via session variable and a policy
SET app tenant_id = 'placeholder';

CREATE POLICY tenant_isolation_policy ON tenant_data
USING (tenant_id = current_setting('app.tenant_id'));

In your Go code, set the session variable per connection or per request before querying:

// db_utils.go
package main

import (
	"context"
	"database/sql"
)

func withTenant(ctx context.Context, db *sql.DB, tenantID string) (*sql.DB, context.Context) {
	ctx = context.WithValue(ctx, "tenantID", tenantID)
	// Example: use a derived connection or set session variable
	// This pattern depends on your driver/connection pool specifics.
	return db, ctx
}

4. Least-privilege database user

Ensure the Cockroachdb user used by the application does not have broad admin rights. Grant only the necessary permissions on the tenant_data table and avoid wildcard grants that could allow privilege escalation across tenants.

-- Minimal privileges for the app user
GRANT SELECT, INSERT, UPDATE ON tenant_data TO app_user;
REVOKE ALL ON DATABASE yourdb FROM app_user;

These steps align with middleBrick findings by ensuring that authorization checks exist in Chi handlers, that queries consistently enforce tenant scoping, and that database permissions follow least privilege. The scanner will highlight missing route guards and risky query patterns, providing remediation guidance tied to real frameworks and compliance mappings.