Excessive Data Exposure in Grape with Cockroachdb

Excessive Data Exposure occurs when an API returns more information than necessary, such as internal identifiers, sensitive attributes, or related records that should remain restricted. In a Grape API backed by Cockroachdb, this often arises from overly broad SELECT statements or from serializing entire database rows without filtering. Cockroachdb, like PostgreSQL, returns column values in row results; if a Grape endpoint maps query results directly to JSON without pruning fields, sensitive data can leak to clients.

Consider an endpoint that retrieves user profiles. If the SQL query selects all columns including password_hash, internal_id, or email, and the serializer does not exclude these, the response contains data that should be private. In a distributed Cockroachdb cluster, data may be replicated across nodes; an incomplete ORM configuration that does not limit returned columns can inadvertently expose replicated data in responses. Additionally, Cockroachdb system tables and default row metadata are not meant for application exposure; incorrect query construction can surface these details.

Another scenario involves related resources. A route like /organizations/:id might join organizations with users or billing records. Without explicit field selection, the response may include nested user lists or payment details. This is Excessive Data Exposure because the API reveals information about other users or sensitive billing data to a caller who only requested organization information. The risk is compounded when authorization checks are applied at the route level but not at the field level, allowing an authenticated user to receive data they are not permitted to see.

middleBrick scans for this class of issue by correlating OpenAPI/Swagger definitions with runtime responses. When a spec declares a subset of fields but the actual response includes additional properties, a finding is generated with severity and remediation guidance. The scanner also checks for missing filtering on sensitive fields such as keys or PII, highlighting where responses violate the principle of minimal disclosure.

To remediate Excessive Data Exposure in a Grape API using Cockroachdb, explicitly select only required columns in SQL and ensure serialization omits sensitive attributes. Below are concrete examples using the pg gem, which is commonly used with Cockroachdb in Ruby services.

1. Explicit column selection in SQL

Instead of selecting all columns, list only the fields needed by the client. This prevents sensitive columns from being returned even if table schemas change.

require 'pg'

conn = PG.connect(host: 'cockroachdb-host', port: 26257, dbname: 'mydb', user: 'appuser')

# Safe: explicit columns
result = conn.exec_params('SELECT id, name, created_at FROM organizations WHERE id = $1', [org_id])
organizations = result.map { |row| { id: row['id'], name: row['name'], created_at: row['created_at'] } }

2. Using an ORM with select

If using an ORM like Sequel or DataMapper, restrict selected fields and avoid automatic row-to-hash mapping that includes all columns.

require 'sequel'

DB = Sequel.connect('cockroachdb://appuser@cockroachdb-host:26257/mydb')

# Safe: select only necessary fields
organization = DB[:organizations].where(id: org_id).select(:id, :name, :created_at).first
# Returns a hash with only the specified keys

3. Filtering sensitive fields in Grape entity or serializer

Even if the database returns extra columns, use an entity to expose only intended fields. This adds a clear layer of control.

class OrganizationEntity < Grape::Entity
  expose :id
  expose :name
  expose :created_at
  # Do not expose :password_hash, :internal_id, or :billing_details
end

# In your resource class
get '/organizations/:id' do
  org = DB[:organizations].where(id: params[:id]).first
  OrganizationEntity.new(org).with_options(request.env).represent(org)
end

4. Avoid SELECT * in dynamic queries

Never construct queries that use SELECT * when the shape of the response is client-facing. This is especially important in dynamic search or filter endpoints where column sets may vary.

# Unsafe pattern to avoid
# query = "SELECT * FROM organizations WHERE name LIKE '%#{search_term}%'"

# Safe alternative
query = "SELECT id, name, created_at FROM organizations WHERE name LIKE $1"
result = conn.exec_params(query, ["%#{search_term}%"])

5. Verify row-level security and authorization at the field level

While Cockroachdb supports row-level security, complement it with application-level field filtering to ensure no sensitive column reaches the response layer when the route-level authorization passes.

# Example: ensure sensitive columns are excluded even after authorization checks
allowed_columns = %w[id name created_at]
selected_columns = params[:fields] ? (params[:fields] & allowed_columns) : allowed_columns
column_list = selected_columns.join(', ')
result = conn.exec_params("SELECT #{column_list} FROM organizations WHERE id = $1", [org_id])