Broken Access Control in Cockroachdb

Broken Access Control in CockroachDB often emerges through improper role-based access control (RBAC) configuration and insecure query patterns. The most common manifestation occurs when developers rely on application-layer authorization checks while CockroachDB's database-level permissions remain overly permissive.

Consider a multi-tenant application where users should only access their own organization's data. A typical vulnerable pattern looks like this:

CREATE TABLE organizations (id UUID PRIMARY KEY, name STRING);
CREATE TABLE users (id UUID PRIMARY KEY, org_id UUID, email STRING, role STRING);
CREATE TABLE documents (id UUID PRIMARY KEY, org_id UUID, content STRING, owner_id UUID);

The application enforces authorization by checking user.org_id === requestedOrgId in code, but the database user has SELECT permissions on the entire documents table. This creates a critical gap: if an attacker bypasses the application layer (through SQL injection, direct database access, or compromised credentials), they can retrieve any organization's documents.

CockroachDB's role system can compound this issue. Developers often create overly broad roles like:

CREATE ROLE app_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE documents TO app_user;

Instead of implementing row-level security (RLS) policies that restrict access based on org_id or user roles. Without RLS, every application user with database access can manipulate any row in these tables.

Another CockroachDB-specific vulnerability involves improper handling of crdb_internal and system tables. Applications that construct queries using user input without proper sanitization might inadvertently expose metadata through queries like:

SELECT * FROM crdb_internal.node_metrics WHERE time > $1 LIMIT 10;

If $1 isn't properly validated, attackers could extract system information useful for further attacks.

Stored procedures and user-defined functions in CockroachDB present additional attack surfaces. If these functions don't properly validate input parameters or check the caller's permissions, they can become privilege escalation vectors. For example:

CREATE OR REPLACE FUNCTION transfer_funds(from_id UUID, to_id UUID, amount DECIMAL)
RETURNS VOID AS $$
BEGIN
  UPDATE accounts SET balance = balance - amount WHERE id = from_id;
  UPDATE accounts SET balance = balance + amount WHERE id = to_id;
END;
$$ LANGUAGE plpgsql;

This function lacks any authorization checks, allowing any caller to transfer funds between arbitrary accounts.

Detecting Broken Access Control in CockroachDB requires examining both configuration and runtime behavior. Start with database-level analysis using CockroachDB's built-in tools.

Examine role privileges with:

SELECT 
  role_name,
  array_agg(privilege_type) as privileges,
  array_agg(table_name) as tables
FROM information_schema.role_table_grants
WHERE table_schema = 'public'
GROUP BY role_name;

This reveals overly broad permissions. Look for roles with ALL PRIVILEGES or SELECT on tables they shouldn't access.

Check for missing RLS policies:

SELECT schemaname, tablename, is_rls_enabled
FROM pg_tables
WHERE schemaname = 'public' AND is_rls_enabled = false;

Tables without RLS enabled in multi-tenant applications are immediate red flags.

middleBrick's API security scanner can detect Broken Access Control patterns in CockroachDB-backed applications through black-box testing. The scanner examines:

1. Authentication bypass attempts on API endpoints that query CockroachDB
2. Parameter manipulation to access other users' data
3. Rate limiting bypass that could enable enumeration attacks
4. Response analysis for data leakage across tenant boundaries
5. OpenAPI spec analysis to identify missing authorization parameters

For example, middleBrick would flag an endpoint like:

GET /api/documents?orgId=123

If the scanner can modify orgId to access another organization's documents without proper authorization checks.

The scanner also tests for CockroachDB-specific vulnerabilities like:

• SQL injection in query parameters that could expose crdb_internal tables
• Time-based blind injection to extract data
• Union-based attacks to combine results from different tables
• Error-based attacks that reveal schema information

middleBrick's continuous monitoring (Pro plan) would regularly scan your staging and production APIs, alerting you if new Broken Access Control vulnerabilities appear after deployments.

Remediating Broken Access Control in CockroachDB requires a defense-in-depth approach combining database-level controls with application-layer validation.

First, implement Row-Level Security (RLS) policies. For the multi-tenant example:

CREATE POLICY org_isolation ON documents
FOR ALL TO app_user
USING (org_id = current_setting('app.current_org_id')::UUID);

This policy ensures users can only access documents from their current organization. The application sets app.current_org_id in the database session after authentication.

For more granular control, use user-specific policies:

CREATE POLICY document_ownership ON documents
FOR ALL TO app_user
USING (org_id = current_setting('app.current_org_id')::UUID AND (owner_id = current_user OR role = 'admin'));

This allows document owners and admins to access documents, while regular users are restricted to their organization.

Implement least privilege at the role level:

CREATE ROLE readonly_app_user;
GRANT CONNECT ON DATABASE mydb TO readonly_app_user;
GRANT USAGE ON SCHEMA public TO readonly_app_user;
GRANT SELECT ON documents TO readonly_app_user;

Then create specific roles for different application functions rather than using a single broad role.

Secure stored procedures with proper authorization:

CREATE OR REPLACE FUNCTION transfer_funds(from_id UUID, to_id UUID, amount DECIMAL)
RETURNS VOID AS $$
DECLARE
  from_user UUID;
  to_user UUID;
BEGIN
  -- Verify caller is the from_account owner or admin
  SELECT owner_id INTO from_user FROM accounts WHERE id = from_id;
  IF from_user <> current_user AND current_user NOT IN (SELECT unnest(admin_users) FROM accounts WHERE id = from_id) THEN
    RAISE EXCEPTION 'Insufficient permissions';
  END IF;
  -- Verify to_account exists and is accessible
  SELECT owner_id INTO to_user FROM accounts WHERE id = to_id;
    RAISE EXCEPTION 'Destination account not found';
  END IF;
  -- Perform transfer
  UPDATE accounts SET balance = balance - amount WHERE id = from_id;
  UPDATE accounts SET balance = balance + amount WHERE id = to_id;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

The SECURITY DEFINER ensures the function runs with the definer's privileges, while the internal checks enforce authorization.

Implement query time limitations to prevent abuse:

SET statement_timeout = '30s';
SET max_row_limit = 1000;

These settings prevent long-running queries and large result sets that could be used for data exfiltration.

For API security, use middleBrick's CLI to scan your endpoints before deployment:

npx middlebrick scan https://api.example.com --output json --fail-on-severity high

This integrates into your CI/CD pipeline, ensuring Broken Access Control issues are caught early. The GitHub Action can automatically fail builds if the security score drops below your threshold.

Finally, audit your CockroachDB configuration regularly:

SELECT 
  rolname,
  rolconnlimit,
  rolcreatedb,
  rolcanlogin,
  rolpassword
FROM pg_roles
WHERE rolname NOT IN ('admin', 'root', 'cockroach');

This identifies unexpected superuser roles or overly permissive configurations that could enable access control bypasses.