Insecure Direct Object Reference in Cockroachdb

Insecure Direct Object Reference (IDOR) in Cockroachdb typically occurs when applications expose database object identifiers (like primary keys, row IDs, or unique identifiers) and trust client-provided values without proper authorization checks. Cockroachdb's distributed architecture and SQL interface create several specific attack vectors.

The most common pattern involves REST APIs that accept row identifiers and construct SQL queries using these values directly. For example:

SELECT * FROM users WHERE id = $1;

When the application receives an HTTP request like /api/users/123, it passes the ID directly to Cockroachdb without verifying whether the authenticated user owns that record. An attacker can simply increment the ID to access other users' data.

Cockroachdb's SERIAL data type and auto-incrementing primary keys make this particularly dangerous. Consider this schema:

CREATE TABLE accounts (id UUID PRIMARY KEY DEFAULT gen_random_uuid(), user_id UUID, balance DECIMAL);

An attacker who knows one account ID can iterate through the UUID space or exploit predictable UUID generation patterns to access other accounts.

Cross-tenant data exposure is another Cockroachdb-specific IDOR pattern. Cockroachdb's multi-tenant architecture allows logical isolation through row-level security policies, but improperly configured applications can bypass these protections. For instance:

SELECT * FROM orders WHERE tenant_id = $1 AND order_id = $2;

If the application trusts the client-provided tenant_id instead of using the authenticated user's tenant context, attackers can access data from other tenants.

Cockroachdb's JSONB support creates additional IDOR risks. Applications often store user-specific data in JSONB columns and retrieve it using path expressions:

SELECT user_data->'preferences' FROM profiles WHERE user_id = $1;

Without proper authorization, an attacker can manipulate the JSON path to access other users' preferences or sensitive data stored in the same column.

Stored procedures in Cockroachdb can also introduce IDOR vulnerabilities. Consider this procedure:

CREATE PROCEDURE get_user_data(IN user_id UUID, IN target_id UUID)
AS $$
BEGIN
  SELECT * FROM users WHERE id = target_id;
END;
$$ LANGUAGE plpgsql;

The procedure accepts a target_id parameter without validating that the calling user has permission to access that specific user's data.

Detecting IDOR in Cockroachdb applications requires both static analysis and dynamic testing. The most effective approach combines code review with runtime scanning.

Static analysis should focus on SQL query patterns that accept user input as identifiers. Look for:

SELECT * FROM table WHERE id = $1;
SELECT * FROM table WHERE uuid = $1;
SELECT * FROM table WHERE row_id = $1;

Pay special attention to Cockroachdb-specific patterns like:

SELECT * FROM table WHERE crdb_internal.range_id = $1;

Dynamic testing with middleBrick specifically targets these Cockroachdb IDOR patterns. The scanner tests unauthenticated endpoints by systematically varying identifier parameters:

GET /api/users/1
GET /api/users/2
GET /api/users/999999

middleBrick's BOLA (Broken Object Level Authorization) module specifically checks for:

• Predictable identifier enumeration
• Cross-tenant data access
• Privilege escalation through ID manipulation
• Missing authorization checks in stored procedures

The scanner also tests Cockroachdb's unique features like range IDs and partition keys. For example, it attempts to access data using:

SELECT * FROM table@partition_key = 'value';

middleBrick's LLM/AI Security module adds another layer of detection for Cockroachdb applications that use AI features. It tests for system prompt leakage in AI endpoints and checks if AI models can be manipulated to generate SQL queries that expose data through IDOR vulnerabilities.

Database-level detection involves querying Cockroachdb's system catalogs to identify potentially vulnerable patterns:

SELECT schemaname, tablename, column_name
FROM information_schema.columns
WHERE column_name IN ('id', 'uuid', 'user_id', 'tenant_id')
AND table_schema NOT IN ('crdb_internal', 'information_schema');

This helps identify which tables contain identifiers that might be exposed through APIs.

Remediating IDOR in Cockroachdb applications requires a defense-in-depth approach combining application-level authorization with database-level controls.

The first layer is application-level authorization. Instead of accepting raw identifiers from clients, applications should use the authenticated user's context:

// BAD: Trusts client-provided ID
const userId = req.params.id;
const result = await db.query('SELECT * FROM users WHERE id = $1', [userId]);

GOOD: Uses authenticated user context

// GOOD: Uses authenticated user's ID from session
const authenticatedUserId = req.user.id;
const result = await db.query('SELECT * FROM users WHERE id = $1', [authenticatedUserId]);

Cockroachdb's row-level security (RLS) provides the second layer of defense. Enable RLS and create policies that enforce access control at the database level:

ALTER TABLE users ENABLE ROW LEVEL SECURITY;

Create policies that restrict access based on user identity:

CREATE POLICY user_access ON users
FOR SELECT USING (id = current_user);

For multi-tenant applications, use tenant-aware policies:

CREATE POLICY tenant_access ON accounts
FOR SELECT USING (tenant_id = current_setting('app.current_tenant')::UUID);

Set the current tenant in your application connection:

await db.query(