Broken Access Control in Express with Cockroachdb

Broken Access Control occurs when an API fails to enforce proper authorization checks, allowing one user to access or modify another user’s resources. In Express applications that use CockroachDB as the backend datastore, this risk is heightened when application logic conflates identity (who you are) with permissions (what you are allowed to do) and relies on the database or ORM alone to enforce boundaries.

Consider an Express route that retrieves a user profile by ID directly from URL parameters without verifying ownership or role:

app.get('/api/users/:id', async (req, res) => {
  const userId = req.params.id;
  const result = await pool.query('SELECT id, email, role FROM users WHERE id = $1', [userId]);
  res.json(result.rows[0]);
});

If the caller provides /api/users/other-user-id, the database returns that row without confirming that the authenticated requestor has any right to view it. CockroachDB faithfully executes the query, but the Express layer never enforces authorization. This is a classic BOLA/IDOR pattern (Broken Level of Authorization / Insecure Direct Object Reference), one of the 12 security checks run by middleBrick.

Another common pattern amplifies risk: coarse role checks placed after data retrieval instead of parameterized into the query:

app.get('/api/admin/settings', async (req, res) => {
  const result = await pool.query('SELECT * FROM settings');
  if (req.user && req.user.role === 'admin') {
    return res.json(result.rows);
  }
  res.status(403).send('Forbidden');
});

Here, the data is fetched before authorization is verified. An attacker who is not an admin can still cause the database to do the work and potentially infer data existence or cause heavier load. Because CockroachDB supports complex SQL and distributed execution, failing to push authorization predicates into the query means sensitive rows are materialized and sent over the network unnecessarily.

middleBrick’s checks for BOLA/IDOR, Property Authorization, and Authentication are particularly relevant in this context. The scanner evaluates whether endpoints validate identity, scope, and tenant boundaries before touching CockroachDB, and whether outputs are inadvertently exposing identifiers or roles that aid horizontal privilege escalation.

Insecure default configurations or missing row-level security (RLS) equivalents in application code—combined with expressive SQL capabilities in CockroachDB—mean that unchecked query construction becomes a direct path to data exposure. Even when RLS-like patterns are emulated in the app, inconsistent enforcement across endpoints yields a fragmented authorization model that is difficult to audit manually.

To remediate Broken Access Control when using CockroachDB with Express, always enforce authorization at the query layer and avoid retrieving data for later filtering. Use parameterized queries that incorporate the requester’s identity and role, and prefer denying by default.

First, ensure authentication middleware attaches a verified user to req.user with stable identifiers:

// auth.middleware.js
function authenticate(req, res, next) {
  const token = req.headers.authorization?.split(' ')[1];
  if (!token) return res.status(401).send('Unauthorized');
  // verify token and fetch user from CockroachDB
  const user = await pool.query('SELECT id, email, role, tenant_id FROM users WHERE id = $1', [token]);
  if (user.rows.length === 0) return res.status(401).send('Unauthorized');
  req.user = user.rows[0];
  next();
}

Second, scope every data query by tenant and user ID, embedding them directly in the WHERE clause:

app.get('/api/users/me', authenticate, async (req, res) => {
  const result = await pool.query(
    'SELECT id, email, role FROM users WHERE id = $1 AND tenant_id = $2',
    [req.user.id, req.user.tenant_id]
  );
  if (result.rows.length === 0) return res.status(404).send('Not found');
  res.json(result.rows[0]);
});

Third, enforce authorization for administrative actions by checking roles before executing sensitive operations:

app.delete('/api/users/:id', authenticate, async (req, res) => {
  if (req.user.role !== 'admin') return res.status(403).send('Forbidden');
  const result = await pool.query(
    'DELETE FROM users WHERE id = $1 AND tenant_id = $2 RETURNING id',
    [req.params.id, req.user.tenant_id]
  );
  if (result.rowCount === 0) return res.status(404).send('Not found');
  res.status(204).end();
});

Fourth, avoid fetching data and then filtering in JavaScript. Instead, push predicates into SQL so CockroachDB returns only rows the caller is allowed to see:

app.get('/api/records', authenticate, async (req, res) => {
  const result = await pool.query(
    'SELECT id, name, visibility FROM records WHERE tenant_id = $1 AND ($2 = ''admin'' OR owner_id = $1)',
    [req.user.tenant_id, req.user.role]
  );
  res.json(result.rows);
});

Finally, centralize authorization logic where feasible and validate inputs strictly to prevent injection that could bypass checks. middleBrick’s checks for Input Validation, Authentication, and Property Authorization help ensure these patterns are consistently applied across endpoints.