Missing Authentication in Adonisjs with Cockroachdb

When an AdonisJS application connects directly to CockroachDB without enforcing authentication on its API endpoints, it can expose data and configuration paths that should be restricted to authenticated users. This typically occurs when route guards or middleware are omitted or misconfigured, allowing unauthenticated HTTP requests to reach controllers that query CockroachDB.

AdonisJS relies on its routing and middleware system to gate access to controller methods. If a route such as GET /users/:id does not invoke an authentication check, an attacker can supply arbitrary numeric or UUID identifiers and directly trigger database queries via the controller. Because CockroachDB is strongly consistent and supports role-based access control at the database level, the risk is not limited to data exposure at the application layer: a missing check can lead to unauthorized SQL execution under the database user configured in the connection string.

In a black-box scan, middleBrick tests unauthenticated paths that interact with CockroachDB by sending requests without credentials and observing whether data is returned or whether the endpoint reveals identifiers, schema details, or error messages tied to database permissions. Findings are mapped to the BOLA/IDOR and Authentication checks, and where API specs are available, definitions are cross-referenced with runtime responses to confirm whether authentication requirements are declared and enforced.

Real-world attack patterns include enumeration of user IDs (BOLA) and privilege escalation when a low-privilege database user is inadvertently granted broader read access due to missing row-level security or application-level checks. OWASP API Top 10 A01:2023 Broken Object Level Authorization and A07:2021 Identification and Authentication Failures are common references for these findings, and scans may map observed behavior to related compliance frameworks such as PCI-DSS and SOC2.

Secure AdonisJS applications interacting with CockroachDB by combining route-level middleware, consistent connection configuration, and parameterized queries. The following patterns demonstrate how to enforce authentication and reduce exposure when your app connects to CockroachDB.

1. Enforce authentication on routes

Apply the auth middleware to routes that access CockroachDB. This ensures only authenticated requests can invoke the controller methods.

// start/routes.ts
import Route from '@ioc:Adonis/Core/Route'
import AuthMiddleware from 'App/Middleware/Auth'

Route.get('/users/:id', 'UsersController.show').middleware([AuthMiddleware])

2. Use authenticated database connections

Configure your CockroachDB connection in database.ts with a dedicated user that follows the principle of least privilege. Avoid using a highly privileged user for routine application queries.

// config/database.ts
import { defineConfig } from '@ioc:Adonis/Addons/Lucid'

export default defineConfig({
  connection: 'cockroachdb',
  connections: {
    cockroachdb: {
      client: 'postgres',
      host: 'cockroachdb-host.example.com',
      port: 26257,
      user: 'app_reader',
      password: process.env.COCKROACH_PASSWORD,
      database: 'app_db',
      ssl: {
        rejectUnauthorized: true,
      },
      connectionString: process.env.DATABASE_URL, // optional, ensure it includes sslmode=require
    },
  },
})

3. Parameterized queries in controllers

Always bind parameters when querying CockroachDB to prevent SQL injection and ensure proper handling of user input.

// app/Controllers/Http/UsersController.ts
import { HttpContextContract } from '@ioc:Adonis/Core/HttpContext'
import Database from '@ioc:Adonis/Lucid/Database'

export default class UsersController {
  public async show({ params, auth }: HttpContextContract) {
    const viewerId = auth.user?.id
    const targetId = params.id

    if (!viewerId || viewerId !== targetId) {
      // Enforce ownership or role checks as appropriate
      throw new Error('Unauthorized')
    }

    const result = await Database.query()
      .from('profiles')
      .where('user_id', '=', targetId)
      .limit(1)

    return result[0]
  }
}

4. Row-level security and application-level checks

Even when database permissions are restrictive, enforce checks in application code. For CockroachDB, combine role-based database permissions with AdonisJS policies to ensure users can only access their own data.

// Example policy check (pseudo-code style; adapt to your policy loader)
if (auth.user.id !== resource.userId && !auth.user.isAdmin) {
  throw new ForbiddenException('You can only access your own data')
}

5. Validate and sanitize input

Treat path and query parameters as untrusted. Validate IDs and sanitize output to avoid leaking database details in errors.

import schema from '@ioc:Adonis/Core/Validator'

const userSchema = schema.create({
  id: schema.string({}, [rules.exists({ table: 'profiles', column: 'user_id' })]),
})

export async function validateId(ctx: HttpContextContract) {
  const payload = await ctx.validate({
    schema: userSchema,
  })
  return payload.id
}

6. Monitoring and scans

Use middleBrick to detect missing authentication and BOLA patterns against endpoints that query CockroachDB. With the Pro plan, you can enable continuous monitoring so new routes interacting with CockroachDB are automatically tested for authentication requirements. The GitHub Action can fail builds if a scan against a staging environment reveals unauthenticated data exposure, and the MCP Server allows you to run checks directly from supported AI coding assistants during development.