Data Exposure in Strapi with Cockroachdb

Data exposure in Strapi when backed by CockroachDB typically occurs due to misconfigured permissions, overly broad role-based access control (RBAC), or unguarded API endpoints that return sensitive fields such as password hashes, email addresses, or internal metadata. Strapi’s default behavior can expose all attributes of a content type unless explicitly filtered, and CockroachDB’s relational model does not inherently restrict which columns are returned once SQL permissions allow the query.

In a typical Strapi setup with CockroachDB as the data layer, the database user configured in database.js often has broad SELECT access to simplify development. This broad access becomes a risk if Strapi’s API responses are not scoped to the minimum required fields. For example, a content type user-profile might include sensitive fields like ssn, apiKey, or internalNotes. Without explicit attribute filtering in Strapi’s response policies or GraphQL resolvers, these fields can be returned to unauthenticated or low-privilege consumers via REST or GraphQL endpoints, leading to data exposure.

Another common vector is dynamic query parameters that are not validated or sanitized. If a Strapi controller accepts user-supplied field lists (e.g., ?fields=name,email,ssn) and passes them directly to the CockroachDB layer without a denylist or allowlist, an attacker can enumerate sensitive columns. CockroachDB will return the requested columns, and Strapi will serialize them into the HTTP response, resulting in unintended data exposure. This pattern is especially risky when combined with misconfigured CORS or missing rate limiting, which can facilitate automated enumeration.

Strapi’s middleware and policies provide a place to enforce field-level security, but if developers rely solely on UI-based role permissions without corresponding database or query safeguards, gaps remain. CockroachDB’s role-based access control can be configured at the SQL level to restrict column access, but if Strapi connects with a role that has broad privileges, the database will honor the query and expose data. Therefore, securing the combination requires coordinated controls: least-privilege database roles, strict field allowlisting in Strapi responses, and validation of all input-driven field selection.

To mitigate data exposure, apply least-privilege SQL roles for the Strapi CockroachDB user and enforce field allowlists in Strapi controllers and policies. Below are concrete, syntactically correct examples tailored to Strapi’s configuration and query patterns.

1. Configure a least-privilege CockroachDB user for Strapi

Create a dedicated CockroachDB user that has SELECT access only on required columns and tables. Avoid granting blanket SELECT on sensitive columns.

-- Create a role for Strapi with restricted column access
CREATE ROLE strapi_app WITH LOGIN PASSWORD 'STRONG_PASSWORD';

-- Grant USAGE on schema and limited SELECT on specific columns
GRANT USAGE ON SCHEMA public TO strapi_app;
GRANT SELECT (id, name, email, published_at) ON TABLE public.users TO strapi_app;
GRANT SELECT (id, title, author_id, published_at) ON TABLE public.articles TO strapi_app;

-- Explicitly deny access to sensitive columns (defense in depth)
REVOKE ALL ON TABLE public.users(ssn, api_key, internal_notes) FROM PUBLIC;
REVOKE ALL ON TABLE public.articles(api_key, draft_content) FROM PUBLIC;

2. Define an allowlist policy in Strapi controller

In your Strapi controller, validate incoming field requests against an allowlist and avoid passing raw field parameters directly to the database layer.

// src/api/user/controllers/user.js
'use strict';

const allowedFields = new Set(['id', 'username', 'email', 'displayName', 'avatar']);

module.exports = {
  async find(ctx) {
    const { fields } = ctx.query;
    let queryParams = {};

    if (fields) {
      const selected = fields.split(',').map(f => f.trim());
      const invalid = selected.filter(f => !allowedFields.has(f));
      if (invalid.length > 0) {
        return ctx.badRequest(null, [{ messages: [{ id: 'invalid.fields', message: 'Requested fields are not allowed' }] }]);
      }
      queryParams.select = selected;
    }

    const users = await strapi.entityService.findMany('api::user.user', queryParams);
    return users;
  },
};

3. Use a policy to sanitize GraphQL responses

If using GraphQL, define a resolver or a schema-based guard to exclude sensitive fields from payloads.

// src/api/user/resolvers.js
'use strict';

module.exports = {
  async user(parent, { id }, { dataSources }) {
    const user = await dataSources.usersAPI.getUserById(id);
    // Explicitly remove sensitive fields before returning
    const { ssn, apiKey, internalNotes, ...safeUser } = user;
    return safeUser;
  },
};

4. Configure environment-specific database credentials in Strapi

Use separate CockroachDB credentials for development, staging, and production with progressively restricted permissions. Never use a superuser for routine API operations.

// config/database.js
module.exports = ({ env }) => ({
  defaultConnection: 'default',
  connections: {
    default: {
      connector: 'bookshelf',
      settings: {
        client: 'cockroachdb',
        host: env('DB_HOST', 'localhost'),
        port: env.int('DB_PORT', 26257),
        database: env('DB_NAME', 'strapi'),
        username: env('DB_USERNAME', 'strapi_app'),
        password: env('DB_PASSWORD', ''),
        ssl: env.bool('DB_SSL', true),
      },
      options: {
        schemaName: 'public',
      },
    },
  },
});

By combining CockroachDB’s column-level permissions with Strapi’s controller and resolver safeguards, you reduce the risk of data exposure. This approach ensures that even if an endpoint is misconfigured, the underlying database will not return columns the application role is not explicitly permitted to read.