Identification Failures in Cockroachdb
How Identification Failures Manifest in CockroachDB
Identification failures occur when an API cannot reliably determine who is making a request, allowing an attacker to bypass authorization checks and access data they should not see. In a CockroachDB‑backed service this often shows up as broken object‑level authorization (BOLA) or broken object‑property level authorization, where the application trusts user‑supplied identifiers without verifying that the caller is entitled to that specific row or column.
A typical vulnerable pattern in Go (using the pgx driver) looks like this:
func getAccountHandler(w http.ResponseWriter, r *http.Request) {
accountID := r.URL.Query().Get("id") // user‑supplied, no validation
var acct Account
err := db.QueryRow(context.Background(),
fmt.Sprintf("SELECT id, owner_id, balance FROM accounts WHERE id = %s", accountID)).
Scan(&acct.ID, &acct.OwnerID, &acct.Balance)
if err != nil {
http.Error(w, err.Error(), http.StatusInternalServerError)
return
}
json.NewEncoder(w).Encode(acct)
}Because the accountID is concatenated directly into the SQL string, an attacker can supply 1 OR 1=1 or a specific ID that belongs to another user, causing the query to return rows they are not authorized to see. CockroachDB treats the statement as any other PostgreSQL‑compatible SQL, so the injection works exactly as it would on vanilla Postgres.
Another manifestation is missing or weak authentication middleware that fails to set a reliable user identifier (e.g., a JWT sub claim) before the handler runs. If the handler then relies on a column like owner_id = current_user_id() but the application never populates current_user_id(), the condition always evaluates to true, effectively granting access to all rows.
These flaws map to OWASP API Security Top 10 2023 items API1 (Broken Object Level Authorization) and API3 (Broken Object Property Level Authorization), and they are frequently exploited in real‑world incidents such as CVE‑2021‑22986 (Improper authorization in a Kubernetes API server) and CVE‑2020‑13942 (IDOR in a cloud storage service).
CockroachDB‑Specific Detection
Detecting identification failures in a CockroachDB‑powered API does not require source‑code access; a black‑box scanner can observe behavioral differences between authenticated and unauthenticated requests. middleBrick performs unauthenticated black‑box testing and includes the following relevant checks:
- Authentication check – verifies whether the endpoint demands a valid token or credential. If the endpoint returns 200 OK without any auth header, the scanner flags a missing authentication vector.
- BOLA/IDOR check** – attempts to iterate over predictable identifiers (e.g., numeric IDs, UUIDs) and looks for successful data retrieval when the identifier belongs to another user. For CockroachDB, the scanner sends requests like
GET /accounts/1001,GET /accounts/1002, etc., and compares the returned data to baseline responses. - Property Authorization check** – probes for exposure of fields that should be hidden (e.g.,
password_hash,secret_key) by requesting the resource with different Accept‑Headers or query parameters and scanning the response for unexpected fields.
When middleBrick runs against a CockroachDB endpoint, it also analyses any exposed OpenAPI/Swagger spec (if present) to verify that defined security schemes match the actual behavior. A mismatch — such as a spec declaring oauth2 security but the live endpoint accepting requests without a token — triggers a finding.
Example CLI usage:
# Scan a staging API for identification failures
middlebrick scan https://staging.example.com/api/accounts
The output includes a severity rating, a short description, and remediation guidance. If the scanner detects a BOLA issue, it will note something like:
Finding: Potential IDOR via user‑supplied account ID. Severity: High. Remediation: Use parameterized queries and enforce ownership checks at the data layer.
Because the scan is unauthenticated and takes only 5–15 seconds, it can be integrated into CI pipelines (via the middleBrick GitHub Action) to catch regressions before they reach production.
CockroachDB‑Specific Remediation
Fixing identification failures in a CockroachDB‑backed service involves two layers: ensuring the application correctly identifies the caller, and using CockroachDB’s native access‑control features to enforce that identity at the data layer.
1. Use Parameterized Queries and Context‑Aware Identity
The first line of defense is to never concatenate user input into SQL. With the pgx driver, pass parameters separately:
func getAccountHandler(w http.ResponseWriter, r *http.Request) {
accountID := r.URL.Query().Get("id")
// Verify the caller’s identity (e.g., from JWT) before using it
callerID, ok := r.Context().Value("userID").(string)
if !ok {
http.Error(w, "Unauthorized", http.StatusUnauthorized)
return
}
var acct Account
err := db.QueryRow(context.Background(),
"SELECT id, owner_id, balance FROM accounts WHERE id = $1 AND owner_id = $2",
accountID, callerID).Scan(&acct.ID, &acct.OwnerID, &acct.Balance)
if err != nil {
if err == pgx.ErrNoRows {
http.Error(w, "Not found or access denied", http.StatusNotFound)
} else {
http.Error(w, err.Error(), http.StatusInternalServerError)
}
return
}
json.NewEncoder(w).Encode(acct)
}
Here the query uses $1 and $2 placeholders, preventing SQL injection, and adds an explicit ownership check (owner_id = $2) that ties the record to the authenticated user.
2. Leverage CockroachDB Row‑Level Security (RLS)
CockroachDB supports row‑level security via the standard PostgreSQL‑compatible ROW LEVEL SECURITY feature. You can enable it on a table and create a policy that filters rows based on the current user:
-- Enable RSL on the accounts table
ALTER TABLE accounts ENABLE ROW LEVEL SECURITY;
-- Create a policy that lets a user see only their own rows
CREATE POLICY user_accounts ON accounts
USING (owner_id = crdb_internal.role());
-- Ensure the application sets the CockroachDB role to match the application user
-- At the start of each DB session:
SET ROLE = 'app_user_123'; -- where 123 comes from the authenticated JWT sub claim
When RLS is enabled, any query that does not include the owner_id filter will automatically be rewritten by CockroachDB to include the policy expression, so even a flawed query like SELECT * FROM accounts WHERE id = $1 will still be constrained to the caller’s rows.
3. Validate and Normalize Identifiers
Before using an identifier in a query, confirm it matches the expected format (e.g., UUID v4). In Go:
import "github.com/google/uuid"
func isValidUUID(s string) bool {
_, err := uuid.Parse(s)
return err == nil
}
if !isValidUUID(accountID) {
http.Error(w, "Invalid account ID", http.StatusBadRequest)
return
}
Combining these practices eliminates the root causes of identification failures: the application can no longer be tricked into accessing another user’s data, and CockroachDB will enforce the same restriction even if a developer mistake slips through.