Bola Idor in Restify with Cockroachdb

Broken Object Level Authorization (BOLA) occurs when an API exposes object identifiers (IDs) and fails to enforce that the authenticated subject is authorized to access or operate on a specific resource instance. In a Restify service backed by CockroachDB, this typically arises when route parameters such as :id are used directly in SQL queries without verifying ownership or tenant context. Because CockroachDB uses a PostgreSQL-wire compatible protocol, developers often write SQL with simple primary-key lookups (e.g., SELECT * FROM invoices WHERE id = $1) and assume the request’s authentication layer is sufficient. However, if authorization checks are missing or applied inconsistently, an attacker can increment or guess IDs to access other users’ data.

In a multi-tenant or user-segmented design, BOLA becomes more subtle when tenant identifiers are stored in CockroachDB but not enforced as part of every query. For example, an endpoint like GET /users/:userId/profile might query SELECT * FROM profiles WHERE id = $1 and return data even when the requesting user does not belong to the same tenant. CockroachDB’s strong consistency and distributed nature do not prevent this; the issue is that the application layer does not bind the tenant context (such as an organization ID or subject tenant) to the query. As a result, the database returns records it is capable of serving, but the API incorrectly exposes them.

Another common pattern is when IDs are not monotonic or are UUIDs that do not leak direct user counts, giving a false sense of safety. Attackers may still probe endpoints using known resource types (e.g., /api/v1/organizations/:orgId/settings) and, without proper ownership validation, read or modify settings belonging to other organizations. Inconsistent use of middleware that enforces authorization, combined with ad-hoc SQL in route handlers, means that some routes check permissions while others do not. This inconsistency is precisely what BOLA exploits: the API behaves as if the object is protected, but the backend query does not revalidate the relationship between the subject and the object identifier in the context of CockroachDB rows.

Because Restify is often used to build JSON APIs that map closely to domain models, developers may inadvertently expose internal primary keys or surrogate IDs directly in URLs. If the authorization logic is implemented as a one-off check rather than a consistent rule applied to every data access, BOLA emerges. For instance, an endpoint might first fetch a record by ID from CockroachDB and then perform a loose permission check that only verifies the record exists, not that the requesting user has the right role or tenant association. The database will return the row if the ID exists, and the API will return it, even when it should return 403 or 404.

Finally, BOLA in this stack is exacerbated when error handling leaks information. If a query on a CockroachDB table returns no rows, the API might distinguish between ‘not found’ and ‘forbidden’ based on whether the row exists, allowing attackers to infer the existence of other objects. Proper handling requires returning a generic not-authorized response when the subject cannot prove ownership, regardless of whether the row exists, while ensuring that SQL errors or constraints do not reveal sensitive metadata about the CockroachDB schema.

To remediate BOLA in Restify with CockroachDB, enforce tenant or ownership checks at the SQL level and ensure that every data access path includes the subject context. Avoid relying solely on middleware or route-level guards; instead, bind the user’s identity or organization ID directly into the query. Below are concrete patterns and code examples for Restify handlers that safely interact with CockroachDB.

1. Include tenant or user ownership in WHERE clauses

Always append a condition that ties the resource to the requesting user or tenant. For example, if your users belong to organizations, include organization_id in the filter.

// Good: tenant-aware query in a Restify handler
const getHandler = async (req, res, next) => {
  const { id } = req.params;
  const { user } = req; // { id, organizationId }
  const query = 'SELECT * FROM invoices WHERE id = $1 AND organization_id = $2';
  const { rows } = await pool.query(query, [id, user.organizationId]);
  if (rows.length === 0) {
    return res.send(404, { error: 'Not found' });
  }
  res.send(200, rows[0]);
  return next();
};

2. Use parameterized queries and avoid string concatenation

Never interpolate IDs into SQL strings. Use parameterized placeholders to prevent injection and ensure values are properly typed, which also helps CockroachDB use prepared plans safely.

// Safe parameterized usage
const updateHandler = async (req, res, next) => {
  const { itemId } = req.params;
  const { status } = req.body;
  const { user } = req;
  const sql = 'UPDATE items SET status = $1 WHERE id = $2 AND tenant_id = $3';
  const values = [status, itemId, user.tenantId];
  await pool.query(sql, values);
  res.send(204);
  return next();
};

3. Centralize authorization with a data access layer

Introduce a repository or service that all routes use, so ownership checks are consistent. This reduces the risk of missing a check in one handler.

// data/invoiceRepo.js
class InvoiceRepository {
  constructor(pool) { this.pool = pool; }

  async findByIdForUser(id, userId) {
    const sql = 'SELECT * FROM invoices WHERE id = $1 AND user_id = $2';
    const { rows } = await this.pool.query(sql, [id, userId]);
    return rows[0] || null;
  }
}

// In your Restify handler
const repo = new InvoiceRepository(pool);
const getById = async (req, res, next) => {
  const record = await repo.findByIdForUser(req.params.id, req.user.id);
  if (!record) {
    return res.send(403, { error: 'Forbidden' });
  }
  res.send(200, record);
  return next();
};

4. Apply consistent error handling to avoid ID enumeration

Ensure that responses for missing records and unauthorized access are indistinguishable when the subject lacks permission. This prevents attackers from mapping valid IDs.

const safeNotFoundOrForbidden = (res, userCanSee) => {
  if (!userCanSee) {
    res.send(404, { error: 'Not found' });
  }
};

const getHandler = async (req, res, next) => {
  const { id } = req.params;
  const { user } = req;
  const query = 'SELECT * FROM documents WHERE id = $1 AND (team_id = $2 OR is_public = $3)';
  const { rows } = await pool.query(query, [id, user.teamId, true]);
  safeNotFoundOrForbidden(res, rows.length > 0);
  if (rows.length) res.send(200, rows[0]);
  return next();
};

5. Validate and scope UUIDs or non-sequential IDs

Even with UUIDs, always associate the resource with a tenant or owner column. Do not assume UUIDs prevent enumeration; enforce the relationship in SQL.

const { v4: uuidv4 } = require('uuid');
const createHandler = async (req, res, next) => {
  const id = uuidv4();
  const { name } = req.body;
  const { user } = req;
  const sql = 'INSERT INTO documents (id, name, team_id) VALUES ($1, $2, $3) RETURNING id';
  const values = [id, name, user.teamId];
  const { rows } = await pool.query(sql, values);
  res.send(201, { id: rows[0].id });
  return next();
};

6. Use CockroachDB’s role-based access controls in tandem with application checks

Leverage CockroachDB roles to restrict what the application user can do, but do not rely on this alone for per-request authorization. Combine DB-level permissions with row-level filters for defense in depth.

-- Example role setup (run separately, not in request handlers)
-- CREATE ROLE api_user; GRANT SELECT, INSERT ON invoices TO api_user;
-- The app connects as api_user, but the query still filters by organization_id.

7. Instrument tests that simulate ID manipulation

Verify that endpoints correctly reject access to resources owned by other tenants or users. Use tests that attempt to access /invoices/otherId and assert 403/404 with a generic message.

// Example test outline (not runnable without framework specifics)
// test('/invoices/123', { user: alice }, (res) => {
//   assert.equal(res.status, 404);
// });