Api Rate Abuse in Strapi with Cockroachdb

Rate abuse in Strapi backed by CockroachDB can manifest when unauthenticated or insufficiently throttled endpoints—such as public content API routes or GraphQL queries—are called repeatedly in a short time window. Because Strapi’s default configuration does not enforce global rate limits on many built-in or custom controllers, an attacker can generate a high volume of requests that query the database through CockroachDB. Each request opens a connection, executes SQL, and holds resources until the response is returned. Under sustained load this can lead to connection saturation, elevated latencies, and potential denial of service for legitimate users.

The exposure is amplified by CockroachDB’s distributed nature: while it handles concurrency well, each SQL transaction still consumes memory and compute on nodes. Repeated queries that lack caching, pagination limits, or request deduplication can cause read-heavy workloads to spread across nodes, increasing tail latencies and risking node saturation. In a multi-tenant or shared cluster scenario, noisy neighbors or malicious bursts can degrade performance for other services sharing the same CockroachDB cluster. Strapi’s ORM (or the underlying node drivers) may open many short-lived connections if connection pooling is not tuned, which CockroachDB must schedule and serve, further increasing surface area for abuse.

Certain API patterns are more prone: endpoints that filter or sort on unindexed fields, endpoints returning large payloads without size limits, and endpoints that trigger heavy joins or aggregations. Because Strapi can dynamically generate queries from GraphQL or REST params, an attacker can craft inputs that cause full table scans or cross-node transactions. Without request validation, injection mitigations, or rate limiting at the edge, the combination of Strapi’s flexible API layer and CockroachDB’s strong consistency guarantees creates a scenario where availability risks are realized through simple, low-cost HTTP requests.

Remediation focuses on reducing load on CockroachDB and enforcing strict request governance in Strapi. Use connection pooling, query limits, and explicit indexes to ensure predictable resource usage. Below are concrete, syntactically correct CockroachDB and Strapi examples.

1. Connection and statement timeouts

Set session-level timeouts to prevent long-running or abusive queries from holding resources.

-- Example CockroachDB session settings for Strapi migrations or admin scripts
SET statement_timeout = '30s';
SET idle_in_transaction_session_timeout = '10s';
SET lock_timeout = '5s';

2. Parameterized queries with placeholders

Always use placeholders to avoid injection and ensure plan reuse, which improves stability under load.

-- Strapi (Node.js) with pg driver example against CockroachDB
const { Client } = require('pg');
const client = new Client({
  connectionString: process.env.DATABASE_URL,
  ssl: { rejectUnauthorized: false },
  application_name: 'strapi-api',
  statement_timeout: 30000,
  idle_timeout_ms: 10000,
});

await client.connect();
const result = await client.query(
  'SELECT id, title, author_id FROM articles WHERE author_id = $1 AND published = $2 LIMIT $3',
  [userId, true, 50]
);
await client.end();

3. Indexes to avoid full scans

Create indexes that match common filter and sort patterns used by Strapi controllers.

-- CockroachDB SQL for Strapi content types
CREATE INDEX IF NOT EXISTS idx_articles_author_published ON articles (author_id, published DESC);
CREATE INDEX IF NOT EXISTS idx_categories_slug ON categories (slug) WHERE deleted_at IS NULL;

4. Pagination and strict limits

Enforce max page size and offset validation in Strapi policies to avoid expensive deep scans.

// config/policies/rate-limit-and-pagination.js
module.exports = {
  enforce: async (ctx, next) => {
    const provider = ctx.request.header['x-api-provider'] || 'strapi';
    const maxPageSize = 100;
    const page = Math.max(0, parseInt(ctx.query.page || 1, 10));
    const pageSize = Math.min(maxPageSize, parseInt(ctx.query.pageSize || 20, 10));
    if (pageSize > maxPageSize) {
      ctx.throw(400, 'pageSize exceeds maximum allowed');
    }
    ctx.request.query.page = page;
    ctx.request.query.pageSize = pageSize;
    await next();
  },
};

5. Read-only replica routing for heavy queries

For operations that can tolerate staleness, route read traffic to CockroachDB replicas to protect primary node capacity.

-- Connection string with replica routing (example)
postgresql://primary_user:pass@primary-host:26257/db?sslmode=require
postgresql://replica_user:pass@replica-host:26257/db?sslmode=require&options=--statement_timeout%3D15000

// In Strapi, use multiple connections or a proxy; here is a Node example selecting replica for reads:
const readClient = new Client({
  connectionString: process.env.DATABASE_URL_REPLICA,
  ssl: { rejectUnauthorized: false },
});
const writeClient = new Client({
  connectionString: process.env.DATABASE_URL_PRIMARY,
  ssl: { rejectUnauthorized: false },
});

async function findArticles(readOnly = false) {
  const client = readOnly ? readClient : writeClient;
  const res = await client.query('SELECT id, slug FROM articles WHERE published = $1 LIMIT $2', [true, 20]);
  return res.rows;
}

6. Monitoring and query caps

Use CockroachDB’s built-in statements and logs to detect hot queries and enforce caps at the application layer in Strapi.

-- Identify long-running queries in CockroachDB
SELECT query, max_execution_time, count FROM crdb_internal.node_statement_statistics
  WHERE max_execution_time > interval '1s' ORDER BY max_execution_time DESC LIMIT 10;