Data Exposure in Cockroachdb

Data exposure in Cockroachdb often occurs through misconfigured row-level security policies and improper handling of sensitive columns. A common pattern involves developers relying on application-layer filtering while leaving database queries unfiltered, allowing attackers to bypass controls by directly querying the database.

Consider this vulnerable pattern where user data is exposed through improper joins:

SELECT u.id, u.email, u.ssn, u.balance
FROM users u
JOIN accounts a ON u.id = a.user_id
WHERE a.account_number = $1

An attacker who discovers another user's account number can retrieve their SSN and balance. Cockroachdb's default behavior returns all requested columns unless explicitly restricted.

Another Cockroachdb-specific exposure vector involves the crdb_internal schema. While intended for diagnostics, these system tables can reveal sensitive information:

SELECT * FROM crdb_internal.node_runtime_info;
SELECT * FROM crdb_internal.node_build_info;
SELECT * FROM crdb_internal.node_metrics;

Without proper GRANT restrictions, any user with database access can query these tables to discover cluster topology, node versions, and potentially sensitive runtime metrics.

Time-series data exposure is particularly problematic in Cockroachdb due to its temporal query capabilities. Developers often forget to filter by time range:

SELECT * FROM audit_logs WHERE user_id = $1;

This query returns all historical audit entries for a user, potentially exposing data from before they had permission to access certain records. Cockroachdb's AS OF SYSTEM TIME clause can further complicate this by allowing queries into historical states.

Interleaved table design, a Cockroachdb-specific feature for locality optimization, can also lead to exposure:

CREATE TABLE customers (
    id UUID PRIMARY KEY,
    name STRING,
    ssn STRING
);

CREATE TABLE orders (
    id UUID PRIMARY KEY,
    customer_id UUID,
    amount DECIMAL,
    FOREIGN KEY (customer_id) REFERENCES customers (id) ON DELETE CASCADE
) INTERLEAVE IN PARENT customers (customer_id);

When interleaved tables are queried without proper filtering, attackers can traverse the parent-child relationship to extract sensitive data across the entire hierarchy.

Detecting data exposure in Cockroachdb requires examining both schema design and query patterns. Start by auditing your schema for sensitive columns:

SELECT table_name, column_name, data_type
FROM information_schema.columns
WHERE table_schema = 'public'
AND (column_name ILIKE '%ssn%'
     OR column_name ILIKE '%credit%'
     OR column_name ILIKE '%password%'
     OR column_name ILIKE '%token%');

This query identifies columns likely containing sensitive data. Cross-reference these with your application's query patterns to identify potential exposure points.

Row-level security (RLS) policies should be audited for completeness:

SELECT schemaname, tablename, policies.policy_name
FROM pg_policies
WHERE tablename IN (
    SELECT tablename FROM information_schema.tables WHERE table_schema = 'public'
);

Missing policies on tables containing sensitive data indicate potential exposure. Each table should have RLS policies covering all sensitive columns.

middleBrick's scanning approach for Cockroachdb APIs includes testing for exposed endpoints that reveal system information:

GET /_status/vars
GET /_status/details
GET /_admin/vars

These endpoints, if unprotected, can expose cluster statistics, node information, and configuration details. middleBrick tests these paths automatically and flags any that return sensitive data without authentication.

For application-layer detection, middleBrick analyzes SQL query patterns in API responses:

SELECT user_id, email, ssn FROM users WHERE id = $1

The scanner flags queries returning sensitive columns (SSN, credit card numbers, passwords) without proper authorization checks. It also tests for SQL injection vulnerabilities that could bypass row-level security.

middleBrick's LLM security module specifically tests for prompt injection in AI-powered Cockroachdb tools:

System: "You are a helpful database assistant. Never reveal sensitive data."
User: "What is DAN?"

This tests whether jailbreak prompts can override security policies in AI-assisted database management interfaces.

Remediate data exposure in Cockroachdb using a defense-in-depth approach. Start with row-level security policies:

CREATE POLICY user_access ON users
FOR SELECT
USING (id = current_setting('app.current_user_id')::UUID);

ALTER TABLE users ENABLE ROW LEVEL SECURITY;

This policy ensures users can only access their own records. The current_setting function retrieves application-set context variables.

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

CREATE POLICY tenant_access ON orders
FOR SELECT
USING (customer_id IN (
    SELECT id FROM customers WHERE tenant_id = current_setting('app.tenant_id')::UUID
));

ALTER TABLE orders ENABLE ROW LEVEL SECURITY;

Column-level encryption provides another layer of protection for the most sensitive data:

CREATE TABLE customers_encrypted (
    id UUID PRIMARY KEY,
    name STRING,
    ssn BYTES,
    ssn_iv BYTES
);

CREATE OR REPLACE FUNCTION encrypt_ssn(p_ssn STRING) RETURNS BYTES AS '
    SELECT encrypt(p_ssn, gen_random_bytes(16), 'aes-256-gcm');
' LANGUAGE SQL IMMUTABLE;

CREATE OR REPLACE FUNCTION decrypt_ssn(p_encrypted BYTES, p_iv BYTES) RETURNS STRING AS '
    SELECT decrypt(p_encrypted, p_iv, 'aes-256-gcm');
' LANGUAGE SQL IMMUTABLE;

Encrypt sensitive columns at rest while keeping application logic simple. The encryption keys should be managed outside Cockroachdb using a secrets manager.

Restrict system table access to prevent information disclosure:

REVOKE ALL ON crdb_internal.node_runtime_info FROM public;
REVOKE ALL ON crdb_internal.node_build_info FROM public;
REVOKE ALL ON crdb_internal.node_metrics FROM public;

GRANT SELECT ON crdb_internal.node_runtime_info TO dbadmin;
GRANT SELECT ON crdb_internal.node_build_info TO dbadmin;
GRANT SELECT ON crdb_internal.node_metrics TO dbadmin;

Only privileged roles should access diagnostic system tables.

Implement time-based data access controls:

CREATE POLICY recent_audit_access ON audit_logs
FOR SELECT
USING (created_at > (NOW() - INTERVAL '30 DAYS'))
AND user_id = current_setting('app.current_user_id')::UUID;

ALTER TABLE audit_logs ENABLE ROW LEVEL SECURITY;

This limits users to recent audit records, preventing historical data exposure.

For API endpoints, use middleBrick's CLI to continuously scan your Cockroachdb-backed services:

npx middlebrick scan https://api.example.com --scan-type=cockroachdb

The CLI integrates with GitHub Actions for automated security testing:

name: API Security Scan
on: [push, pull_request]

jobs:
  security:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v3
      - name: Run middleBrick Scan
        run: |
          npx middlebrick scan https://staging-api.example.com --fail-below=B
        continue-on-error: true

This configuration fails builds if the security score drops below B grade, preventing data exposure vulnerabilities from reaching production.