Privilege Escalation in Adonisjs with Cockroachdb

AdonisJS is a Node.js web framework that encourages a structured, policy-based approach to authorization. When paired with CockroachDB, a distributed SQL database, privilege escalation risks arise primarily from mismatches between application-level policies and database permissions, or from insecure direct object references (IDOR) in routes that interact with CockroachDB.

Consider a typical AdonisJS controller that fetches a user profile using a route parameter id and queries CockroachDB via an ORM or query builder:

const User = use('App/Models/User')

async show ({ params, auth }) {
  const user = await User.query().where('id', params.id).first()
  return user
}

If the application does not enforce that the authenticated user can only view their own record (or an allowed subset), an attacker can modify the id parameter to access other users’ data. This is an IDOR/BOLA issue. Even when row-level security (RLS) is not explicitly configured in CockroachDB, the application must enforce authorization; missing checks enable privilege escalation.

Another scenario involves role checks performed only in AdonisJS before issuing SQL to CockroachDB. For example, an admin-only endpoint might check user.isAdmin in JavaScript but then execute a sensitive CockroachDB operation without re-verifying privileges on the database side. If the application logic is bypassed (e.g., through an API parameter manipulation or a misconfigured route), the attacker can execute privileged SQL statements directly against CockroachDB, leading to privilege escalation.

CockroachDB’s permission model supports roles and grants at the SQL level. If the AdonisJS application connects to CockroachDB with a highly privileged account (for convenience), a compromised endpoint or misconfigured query can perform operations beyond what the application intends. For instance, an attacker might exploit an unsafe dynamic query or an unvalidated filter to run statements that modify or read data reserved for higher-privilege roles.

Additionally, inconsistent handling of ownership and tenant identifiers can enable vertical or horizontal privilege escalation across users or organizations. Without explicit checks that align the authenticated identity with the data ownership in CockroachDB, users may traverse relationships or manipulate parameters to reach records they should not access.

To mitigate privilege escalation when using AdonisJS with CockroachDB, enforce authorization both in application logic and at the database level, and validate all inputs that influence SQL queries.

1. Enforce ownership checks in the controller

Ensure every data access verifies that the authenticated user owns or is allowed to access the resource. For example:

const User = use('App/Models/User')

async show ({ params, auth }) {
  const user = await User.query()
    .where('id', params.id)
    .where('user_id', auth.user.id) // enforce ownership
    .firstOrFail()
  return user
}

This pattern prevents IDOR by ensuring users only retrieve their own records unless explicitly authorized otherwise.

2. Use parameterized queries and avoid dynamic SQL

When constructing queries for CockroachDB, prefer parameterized conditions to prevent injection and unintended privilege escalation:

const result = await Database.from('accounts')
  .where('user_id', auth.user.id)
  .andWhere('role', 'in', ['user', 'editor'])
  .select('id', 'name')

Avoid interpolating untrusted input into SQL strings. If you must build dynamic queries, use query builder methods or a safe helper that validates inputs.

3. Implement role-based checks before sensitive operations

Verify roles server-side before executing privileged queries against CockroachDB:

if (!auth.user.isAdmin) {
  throw new Error('Unauthorized: admin access required')
}
await Database.from('system_configs').update({ value: 'new' }).where('id', configId)

Do not rely solely on client-side flags or route guards; re-validate on the server before touching CockroachDB.

4. Use CockroachDB grants and roles appropriately

Connect AdonisJS to CockroachDB with a dedicated account that has least privilege. For example, create roles in CockroachDB and map them to your application’s needs:

-- CockroachDB setup (run separately)
CREATE ROLE app_reader;
GRANT SELECT ON TABLE users TO app_reader;
CREATE ROLE app_writer;
GRANT SELECT, INSERT, UPDATE ON TABLE users TO app_writer;
-- Then in AdonisJS config, use credentials tied to app_writer as needed
module.exports = {
  connection: {
    cockroach: {
      client: 'cockroachdb',
      connection: {
        host: process.env.DB_HOST,
        port: process.env.DB_PORT,
        user: process.env.DB_USER,
        password: process.env.DB_PASSWORD,
        database: process.env.DB_NAME,
      },
      options: {
        ssl: process.env.NODE_ENV === 'production'
      }
    }
  }
}

By aligning database roles with application permissions, you reduce the impact of a compromised endpoint.

5. Validate and sanitize inputs that affect joins or filters

Ensure that values used in joins, where clauses, or ORDER BY are validated. For example, if sorting is user-controlled, restrict to known columns:

const allowedSortColumns = ['id', 'name', 'created_at']
const sortBy = allowedSortColumns.includes(request.input('sort')) ? request.input('sort') : 'id'
const users = await User.query().orderBy(sortBy, 'asc')

This prevents attackers from manipulating queries to access or escalate privileges indirectly through CockroachDB.