Api Key Exposure in Sinatra with Postgresql

When a Sinatra application connects to Postgresql using hard‑coded or improperly managed credentials, the API key or database password can be exposed through multiple vectors. In Sinatra, inline connection strings or environment-dependent configuration often lead to secrets being stored in source code, logs, or error messages. If the application constructs SQL strings by interpolating user input, an attacker may leverage injection to read configuration tables or backup files that contain credentials. The combination means that a compromised endpoint or misconfiguration can reveal the very credentials used to protect Postgresql, turning an authentication bypass or information disclosure into a full connection string leak.

middleBrick scans for this class of exposure as part of Data Exposure and Input Validation checks, flagging insecure patterns such as string interpolation in queries, verbose error messages that include connection details, and missing environment isolation. For example, a Sinatra route that directly interpolates params into SQL can expose the underlying Postgresql authentication method, enabling attackers to infer valid keys or escalate access. The scan also surfaces unauthenticated endpoints that may return configuration or debug data, which is especially relevant when LLM endpoints are involved, as LLM/AI Security checks can detect whether prompts or responses leak sensitive connection metadata.

Additionally, middleware or logging setups in Sinatra that capture full request or response bodies may inadvertently include authorization tokens or session keys used to access Postgresql. Without proper filtering, these artifacts remain in logs and can be retrieved via less-protected administrative routes. The risk is compounded when the application shares hosting environments or uses default Postgresql ports without network-level restrictions. middleBrick’s checks for Unsafe Consumption and Property Authorization help identify whether access controls around these sensitive routes are insufficient, ensuring that exposure points are surfaced with severity and remediation guidance.

To mitigate Api Key Exposure when using Postgresql with Sinatra, store credentials outside the application code and enforce strict separation between application logic and secrets. Use environment variables and a secure configuration loader, and always employ parameterized queries to prevent injection that could expose connection details. Below are concrete, working examples that demonstrate secure patterns.

1. Secure connection setup with environment variables

require 'sinatra'
require 'pg'

configure do
  set :db_host, ENV['PGHOST'] || 'localhost'
  set :db_port, ENV['PGPORT'] || 5432
  set :db_name, ENV['PGDATABASE']
  set :db_user, ENV['PGUSER']
  set :db_password, ENV['PGPASSWORD']
end

def db_connection
  PG.connect(
    host: settings.db_host,
    port: settings.db_port,
    dbname: settings.db_name,
    user: settings.db_user,
    password: settings.db_password
  )
end

2. Parameterized queries to avoid injection and credential leakage

get '/users/:id' do
  user_id = params['id']
  # Safe: parameterized query prevents injection that could expose connection strings
  result = db_connection.exec_params('SELECT id, email FROM users WHERE id = $1', [user_id])
  result.to_a.to_json
end

3. Avoid logging sensitive data and enforce error handling

configure :production do
  configure do
    set :show_exceptions, false
    set :dump_errors, false
  end

  error do
    # Log minimal information; do not include connection details
    env['sinatra.error'].message
    { error: 'Internal server error' }.to_json
  end
end

4. Role-based access control and row-level security in Postgresql

# Postgresql setup: create limited role for the application
-- Run this in psql
CREATE ROLE app_user WITH LOGIN PASSWORD 'strong_password_here';
GRANT CONNECT ON DATABASE myapp_production TO app_user;
GRANT USAGE ON SCHEMA public TO app_user;
GRANT SELECT, INSERT, UPDATE ON TABLE users TO app_user;

-- Optionally enforce row-level security
ALTER TABLE users ENABLE ROW LEVEL SECURITY;
CREATE POLICY user_isolation ON users
  FOR ALL
  TO app_user
  USING (id = current_setting('app.user_id')::int);

5. Connection string validation and runtime checks

before do
  content_type :json
  # Ensure required env vars are present before proceeding
  missing = %w[PGHOST PGDATABASE PGUSER PGPASSWORD].select { |k| ENV[k].to_s.strip.empty? }
  if missing.any?
    status 500
    { error: 'Server configuration error' }.to_json
  end
end