Insecure Design in Hanami with Cockroachdb

Insecure design in a Hanami application using Cockroachdb often stems from schema decisions and access patterns that weaken authorization, enable unsafe data exposure, and simplify injection or privilege escalation. Unlike monolithic SQL setups, Cockroachdb’s distributed SQL behavior can inadvertently encourage designs that do not enforce least-privilege access or strict ownership checks at the application layer.

One common pattern is defining database roles in migrations that are too permissive (e.g., granting broad SELECT/INSERT/UPDATE on tables) and then relying solely on Hanami’s in-app checks without row-level security. Because Cockroachdb supports secondary indexes and distributed joins, queries that appear safe in a single-node dev environment can expose more data in production when combined with inefficient or missing filters in Hanami operations.

Authorization flaws arise when object-level permissions are not encoded in queries. For example, a Hanami action that loads Post.where(id: params[:id]) without verifying that the current account owns the post can lead to IDOR across tenants, especially when Cockroachdb’s global consistency makes cross-shard reads return data that a single-node setup might not. An attacker can iterate through numeric IDs and access records belonging to other users or organizations.

Input validation and type mismatches are also design risks. Hanami’s validations may reject malformed input, but if Cockroachdb receives queries with unexpected types (for example, strings passed where UUIDs are expected), the distributed planner may still execute the query and return unintended results, bypassing intended constraints. Similarly, missing uniqueness constraints or weak default values in schema design can enable collision attacks or account takeover.

Data exposure is another insecure design outcome. Storing sensitive fields (API keys, PII) without encryption at rest or in transit, and then querying them in Hanami without field-level filtering, can leak information through logs, error messages, or bulk exports. Cockroachdb’s distributed logs and trace facilities may inadvertently surface these values if instrumentation is not carefully controlled.

Finally, combining Hanami’s convention-based routing with Cockroachdb’s SQL extensibility can encourage unsafe dynamic SQL or ORM chaining that bypasses prepared statement benefits. This increases exposure to injection and SSRF-like paths when hostnames or paths are concatenated into queries instead of being parameterized, even when using an ORM.

Remediation focuses on schema design, tenant-aware queries, and strict input handling. Below are Cockroachdb-specific code examples for Hanami that address insecure design patterns.

• Use tenant-scoped indexes and enforce ownership in queries:

require "hanami/entity"
require "pg" # Cockroachdb wire protocol

# app/entities/account_post.rb
class AccountPost
  include Hanami::Entity

  def self.for_account(account_id)
    repository(:db)["SELECT id, title, content, account_id FROM posts WHERE account_id = $1 ORDER BY created_at DESC", account_id].to_a
  end
end

# db/migrations/20240101000000_create_posts.cr
Sequel.migration do
  up do
    create_table :posts do
      primary_key :id, type: :uuid, default: Sequel.function(:gen_random_uuid)
      foreign_key :account_id, :accounts, on_delete: :cascade, type: :uuid
      String :title, null: false
      String :content, null: false
      DateTime :created_at, null: false, default: Sequel.function(:now)
      index [:account_id, :created_at], unique: false, where: "(deleted_at IS NULL)"
    end
  end
end

• Apply row-level patterns in Hanami operations and use parameterized inputs:

# app/actions/posts/show.rb
class Posts::Show
  include Hanami::Action

  def call(params)
    post = AccountPost.for_account(current_account.id).find { |p| p.id == params[:id] }
    if post.nil?
      self.status = 404
      self.body = { error: "Not found" }.to_json
    else
      self.body = PostEntity.new(post).serializable_hash.to_json
    end
  end
end

# Enforce type-safe queries with Hanami::Repository
class PostRepository
  include Hanami::Repository

  def find_by_id_for_account(id, account_id)
    posts = DB[
      "SELECT id, title, content FROM posts WHERE id = $1 AND account_id = $2",
      id, account_id
    ].to_a
    posts.first
  end
end

• Use Cockroachdb’s secure defaults and avoid overprivileged roles in migrations:

# db/migrations/20240101000001_create_accounts.cr
Sequel.migration do
  up do
    create_table :accounts do
      primary_key :id, type: :uuid, default: Sequel.function(:gen_random_uuid)
      String :public_key, null: false
      DateTime :created_at, null: false, default: Sequel.function(:now)
    end
    # Avoid granting blanket update/delete; use application-level checks
    run "REVOKE ALL ON DATABASE mycockroach FROM PUBLIC"
  end
end

• Prevent data exposure by selecting only needed fields and avoiding SELECT *:

# Instead of:
# DB["SELECT * FROM posts WHERE account_id = $1", account_id]
# Use:
DB[
  "SELECT id, title, updated_at FROM posts WHERE account_id = $1 AND visible = $2",
  account_id, true
].map { |r| { id: r[:id], title: r[:title], updated_at: r[:updated_at] } }

• Validate and normalize inputs before query construction to avoid type confusion:

# app/validators/post_params.rb
class PostParams
  include Hanami::Validations::Schema

  validations do
    required(:id).filled(:str?, format?: /^[0-9a-f]{8}-[0-9a-f]{4}-4[0-9a-f]{3}-[89ab][0-9a-f]{3}-[0-9a-f]{12}$/i)
    required(:account_id).filled(:str?, format?: /^[0-9a-f]{8}-[0-9a-f]{4}-4[0-9a-f]{3}-[89ab][0-9a-f]{3}-[0-9a-f]{12}$/i)
  end
end