Pii Leakage in Grape with Cockroachdb

Grape is a Ruby API micro-framework commonly used to build REST-like endpoints. When a Grape API uses CockroachDB as its backend, PII leakage can arise from a mismatch between schema design, query patterns, and the API’s serialization logic. CockroachDB, while wire-compatible with PostgreSQL, has specific behaviors around distributed SQL, secondary indexes, and transaction isolation that can inadvertently expose sensitive data if queries are not carefully constructed.

One common pattern is defining a users table with columns such as email, phone, and ssn, and then creating an index to support efficient lookups. If a Grape resource inadvertently returns the full record—including sensitive columns—without explicit field filtering, PII can be exposed over HTTP. For example, a GET /users/:id endpoint might map to a CockroachDB query like SELECT * FROM users WHERE id = $1. In a distributed CockroachDB cluster, this query might read from a non-local replica; if the connection is not properly secured or if logging captures results, sensitive fields can be exposed in logs or error traces.

Serialization is another critical area. Grape often uses representer libraries or custom to_json calls. If the representation layer does not explicitly whitelist fields, developers might rely on models that include all attributes. Consider a CockroachDB schema with a table profiles that stores user_id, address, and date_of_birth. A Grape endpoint that does not restrict fields can return these PII attributes unintentionally. Moreover, CockroachDB’s changefeed and audit logging features, when enabled for observability, can stream sensitive changes if access controls are not strict, leading to secondary exposure through log aggregation systems.

Indexing strategies can also contribute to leakage. Secondary indexes in CockroachDB store indexed column values alongside row data. If an index includes a PII column such as email, and the query planner uses that index, the data may be read and transmitted in ways that bypass application-level safeguards. A Grape endpoint that performs partial matches or case-insensitive searches on email might leverage an index like CREATE INDEX idx_users_email ON users (lower(email)). While this improves performance, it also means that PII is resident in the index and exposed to anyone who can observe index usage or extract index metadata through adjacent queries.

Finally, transaction isolation and retry logic in CockroachDB can interact with Grape middleware in subtle ways. Long-running requests that hold read transactions may keep older snapshots alive, increasing the window during which sensitive data remains accessible in memory. If error handling in Grape exposes stack traces or database messages, detailed CockroachDB errors might reveal query internals or data patterns, aiding an attacker in inferring PII locations or values.

To mitigate PII leakage when using Grape with CockroachDB, apply strict schema, query, and serialization controls. Use column whitelisting in SQL, enforce field-level permissions, and ensure serialization layers do not expose sensitive attributes.

1. Explicit column selection and schema design

Avoid SELECT * in Grape endpoints. Instead, explicitly list required columns and exclude PII at the database level. Define your CockroachDB table with sensitive columns segregated when possible:

CREATE TABLE users (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  public_name TEXT NOT NULL,
  email TEXT NOT NULL,
  phone TEXT,
  ssn TEXT
);

CREATE INDEX idx_users_email ON users (email); -- Be mindful: index contains PII

When querying, select only non-sensitive fields for general endpoints:

-- Safe: explicit columns
SELECT id, public_name FROM users WHERE id = $1;

2. Parameterized queries in Grape routes

Use placeholders to prevent injection and ensure CockroachDB handles types safely. In a Grape resource:

class UserResource < Grape::API
  format :json

  desc 'Get public user fields by ID'
  params do
    requires :id, type: String, desc: 'User UUID'
  end
  get '/users/:id' do
    user = DB[%(SELECT id, public_name FROM users WHERE id = $1), params[:id]].first
    error!('Not found', 404) unless user
    user

3. Serialization with field whitelisting

Control the JSON output to omit PII. Using representer or manual hashes:

class UserResource < Grape::API
  format :json

  helpers do
    def user_serializer(user)
      { id: user['id'], public_name: user['public_name'] }
    end
  end

  get '/users/:id' do
    user = DB[%(SELECT id, public_name, email FROM users WHERE id = $1), params[:id]].first
    error!('Not found', 404) unless user
    user_serializer(user)
  end
end

4. Limit index usage for PII columns

If feasible, avoid including PII in frequently used indexes or create partial indexes that exclude sensitive rows:

-- Example: index only for active, non-sensitive users
CREATE INDEX idx_users_email_active ON users (lower(email)) WHERE deleted_at IS NULL AND ssn IS NULL;

5. Secure connection and logging practices

Ensure TLS for CockroachDB connections and suppress sensitive data in logs. In your database configuration, avoid logging full query results:

# config/database.yml (example settings)
development:
  adapter: cockroachdb
  database: myapp
  sslmode: require
  log_statement: 'none' # or 'mod' to avoid result logging

6. Middleware and error handling

Prevent exposure of CockroachDB internals in error responses. Rescue exceptions and return generic messages:

class API < Grape::API
  rescue_from :all do |e|
    Rack::Response.new({ error: 'Internal server error' }.to_json, 500)
  end
end

By combining explicit queries, careful serialization, and secure operational settings, you reduce the risk of PII leakage while maintaining compatibility with CockroachDB’s distributed architecture.