db-access-mcp

MCP (Model Context Protocol) stdio server that gives AI agents (Claude Code, Claude Desktop, any MCP client) access to configured databases:

• PostgreSQL (≥ 10)
• MySQL (≥ 5; read-only sessions require ≥ 5.6)
• Amazon Redshift
• Microsoft SQL Server (via tedious)

with SSH / AWS SSM tunnels, pluggable secret providers (env vars, HashiCorp Vault, AWS Secrets Manager) and strict per-instance isolation — many MCP instances can run concurrently on one machine without sharing connections or tunnels, and crashed instances never leave orphaned tunnel processes behind.

Highlights

Tools: connection_list / connection_find / connection_test, query, query_plan (EXPLAIN), query_to_file (streamed CSV/JSONL export that bypasses the model context), up_tunnel / down_tunnel / tunnel_list, config_reload (pick up new connections/tunnels without a restart).

Security-first design:

• Verified SSH tunnels — the bastion host key is checked against a host_key_sha256 pin or known_hosts, failing closed on mismatch (MITM defence), not blindly trusted.
• read_only seatbelt + single-statement-by-default, which also closes the SET session read-only off; INSERT … bypass.
• Confined file exports — query_to_file writes only under the export dir or an allow_export_paths root; it cannot clobber ~/.ssh, dotfiles or the config dir.
• No secrets leak out — tools never return stacks or credentials; logs redact by key name and scrub inline user:password@host URIs.
• Pluggable secret providers — env vars, HashiCorp Vault (dynamic leases with auto-refresh and atomic pool swaps), AWS Secrets Manager, RDS IAM auth tokens and temporary Redshift credentials.

Tunnels & crash-safety: in-process SSH (dies with the process — no orphaned ports) and AWS SSM under a watchdog (killed even on SIGKILL), with AWS SSO bootstrap. Every instance's pools and tunnels are isolated; a crashed instance's tunnels are reaped by the next start.

Quick start

// Claude Code / Claude Desktop MCP config
{
  "mcpServers": {
    "db-access-mcp": {
      "command": "npx",
      "args": ["-y", "@rheopyrin/db-access-mcp"]
    }
  }
}

On first start the server creates the working directory ~/.db_acess_mcp (intentional spelling — it is the product contract) with an empty config.json, an empty conf.d/ directory and a full config.example.json covering every dialect, secret provider and tunnel type. The export directory (default /tmp/db-access-mcp/exports) is not created up front — query_to_file makes it on demand on the first export. Edit ~/.db_acess_mcp/config.json, restart the MCP server, done.

Integrating the MCP

The server speaks MCP over stdio: any client that can spawn npx -y @rheopyrin/db-access-mcp (or node <path>/dist/cli.js for a local build) can use it. Every spawned instance is fully isolated — its own pools, tunnels and idle timers — so it is safe to register it in several clients/sessions at once.

Claude Code

# current project only (writes .mcp.json in the project root)
claude mcp add db-access-mcp -- npx -y @rheopyrin/db-access-mcp

# for all your projects
claude mcp add --scope user db-access-mcp -- npx -y @rheopyrin/db-access-mcp

# with options (custom config dir, verbose logs, extra env file)
claude mcp add db-access-mcp -- npx -y @rheopyrin/db-access-mcp --workdir ~/.db_acess_mcp --log-level debug --env-file ~/.db_acess_mcp/secrets.env

Check with /mcp inside a session (server status, reconnect). Server stderr logs land in ~/Library/Caches/claude-cli-nodejs/<project-slug>/mcp-logs-db-access-mcp/ (macOS). After editing config.json, reconnect the server (/mcp) — the config is read at startup only.

Or declare it in the project's .mcp.json directly:

{
  "mcpServers": {
    "db-access-mcp": { "command": "npx", "args": ["-y", "@rheopyrin/db-access-mcp"] }
  }
}

Claude Desktop

Add the same mcpServers block to the config file and restart the app:

• macOS: ~/Library/Application Support/Claude/claude_desktop_config.json
• Windows: %APPDATA%\Claude\claude_desktop_config.json

Cursor / other MCP clients

Any stdio-capable client works with the same shape — command npx, args ["-y", "@rheopyrin/db-access-mcp"] (Cursor: ~/.cursor/mcp.json, same mcpServers format). Two things to know:

• stdout is the protocol — if your client shows a JSON-RPC parse error, make sure nothing wraps the command with extra output; all server logs go to stderr.
• Pass CLI options via args, e.g. ["-y", "@rheopyrin/db-access-mcp", "--workdir", "/opt/mcp-db", "--log-level", "warn"].

Local build (development)

git clone <repo> && cd db_access_mcp && npm ci && npm run build
claude mcp add db-access-mcp-dev -- node /abs/path/db_access_mcp/dist/cli.js --log-level debug

Trying it without a client

npx -y @modelcontextprotocol/inspector npx -y @rheopyrin/db-access-mcp

opens a web UI listing all tools with call forms and live stderr. A sensible first-session sequence: dialect_list → connection_list → connection_test on one connection → query.

Requirements on the host

• Node.js ≥ 20.19.
• For ssm tunnels: AWS CLI + session-manager-plugin on PATH; for the SSO bootstrap a browser (login opens interactively).
• For ssh tunnels: nothing extra (in-process ssh2 client).

CLI options

db-access-mcp [workdir] [exportdir] [--workdir <dir>] [--exportdir <dir>] [--config <file>] [--env-file <file>]... [--log-level <level>]

The workdir holds config.json, conf.d/, config.example.json and the runtime instances/ and sso/ state (unchanged from earlier releases). The exportdir is where query_to_file writes exports (created on demand, not at startup); allow_export_paths adds extra writable roots.

All logs are JSON lines on stderr (stdout belongs to the MCP protocol). Values of keys matching password, token, secret, privateKey etc. are redacted.

MCP tools

config_reload

Edit config.json / conf.d/*.json (or the --config file), call config_reload, and the next connection_list, query, up_tunnel, … sees the new definitions. The files are re-read, re-validated (schema + semantics + dialect check) and the config env_files are re-applied before anything is swapped in — an invalid config changes nothing: the server keeps running on the previous configuration and the tool returns the validation error.

The reload is deliberately non-disruptive: open pools and running tunnels are never closed, drained, recreated or reconfigured, even when their definition changed or was removed from the file. They keep the definition they were created with until they are closed the normal way (idle timeout, down_tunnel, server exit); the next pool/tunnel created after that uses the new definition. Every such case is listed in the response's warnings.

Response: files (what was read), connections and tunnels as {added, removed, changed, unchanged}, the pool_defaults_changed / limits_changed / env_files_changed flags, and warnings. Per-call and per-connection limits are read at call time, so they take effect immediately; pool settings only apply to pools created after the reload.

Security note for query_to_file: writes are confined to the export dir (default /tmp/db-access-mcp/exports) plus any roots listed in allow_export_paths (e.g. ["/tmp", "~/data_files"]) — every subpath below a listed root is allowed, anything else is rejected, so the tool cannot clobber ~/.ssh, dotfiles or the workdir. It only ever creates files (no reads, no appends) and refuses to overwrite without an explicit overwrite: true. Cells are written verbatim; be mindful of CSV-injection when opening exports in Excel.

Configuration reference

The schema is strict — unknown keys are rejected at startup with a readable error (typo protection). Everything inside a connection's options is passed through to the database driver as-is.

{
  "vault":               { /* named Vault servers */ },
  "aws_secret_profiles": { /* named AWS Secrets Manager profiles */ },
  "env_files":           [ /* extra .env files applied at startup */ ],
  "pool":                { /* global pool defaults */ },
  "limits":              { /* global query limits */ },
  "tunnels":             { /* named tunnel definitions */ },
  "connections":         { /* named connections */ }
}

Config files: single or split (conf.d)

Without --config, the server loads <workdir>/config.json (optional) plus every <workdir>/conf.d/*.json (sorted by name, non-recursive, dotfiles ignored) and merges them:

• Named-record sections (vault, aws_secret_profiles, tunnels, connections) are unioned across files. The same name in two files is a startup error naming both files — no silent overrides.
• Scalar sections (pool, limits, env_files) may appear in at most one file.
• --config <file> loads exactly that file; conf.d is not scanned.

Everything can live in a single config.json (that is what the example shows) — conf.d is for splitting per team/project when the config grows.

Env-ref values

Wherever noted below, a config value can be an inline string or a reference to an environment variable, resolved lazily at the moment it is needed:

"token": "hvs.inline"            // inline
"token": { "env": "VAULT_2_TOKEN" }  // read from the environment at use time

A missing variable fails only the connections that actually need it, with an error naming the variable.

connections.<key>

Per-dialect options

• postgres / redshift — anything node-postgres accepts: host, port, database, user, password, ssl, … or a single connectionString (postgres://user:pass@host:5432/db).
• mysql — anything mysql2 accepts: host, port, database, user, password, or uri (mysql://…). multipleStatements follows the shared rule below.
• mssql — anything mssql accepts: server (or host alias), port, database, user, password, options: { encrypt, trustServerCertificate, … }, or connectionString (mssql://… URL or ADO style Server=…;Database=…).

options.multipleStatements (default off)

By default a query call runs a single statement. Set "multipleStatements": true in a connection's options to allow several ;-separated statements in one call. Enforced by the engine, not by parsing SQL:

• mysql — the driver's native multipleStatements flag.
• postgres / redshift — with it off, queries run over the extended protocol, so the server itself rejects a second statement (42601); no SQL splitting.
• mssql — cannot be enforced at the protocol level (T-SQL batches), so multi-statement is always allowed here; rely on a read-only DB user.

Leaving it off also closes the SET session-read-only off; INSERT … bypass of a read_only connection (the write can't ride along in a second statement). Like read_only, this is a seatbelt — the real guarantee is a read-only DB user.

Multi-database connections (options.databases)

One server often hosts many databases. Instead of duplicating the connection, declare them all:

"shared-mysql": {
  "type": "mysql",
  "options": { "host": "...", "port": 3306,
               "databases": ["app", "reporting", "audit"],
               "user": "...", "password": "..." }
}

Rules (query, query_plan, query_to_file, connection_test accept an optional database parameter):

• no database parameter → options.database is used; when the connection declares only a databases list there is no implicit default — the call fails with DATABASE_NOT_FOUND listing the available names;
• a passed database must equal options.database or be a member of options.databases, otherwise DATABASE_NOT_FOUND;
• database and databases may be declared together; every connection must declare at least one of them (config error otherwise);
• each (connection, database) pair gets its own pool; all pools of a connection share one tunnel, released when the last pool closes;
• connection_list/connection_find expose databases, and the database find-filter matches either the single property or any list member;
• databases cannot be combined with connectionString/uri.

pool (global and per-connection)

limits (global, per-connection, per-call)

Resolution order: tool-call argument → connection limits → global limits → defaults.

read_only semantics by dialect

For real guarantees always prefer a read-only database user. read_only is a seatbelt, not a security boundary.

Secrets

One provider per connection; placeholders are namespaced by the provider name and resolved against the parsed secret object: ${env.userName}, ${vault.data.password}, ${aws.password}. A placeholder that is the whole string keeps the raw value type (numbers stay numbers); embedded placeholders are string-substituted. Mismatched namespaces are rejected at config load.

env — environment variables

"options": { "user": "${env.userName}", "password": "${env.password}" },
"secrets": { "env": { "userName": "PG_USER_ENV_VAR", "password": "PG_PASSWORD_ENV_VAR" } }

The spec maps placeholder keys to environment variable names. Missing variables fail with the exact list of what is missing. Static — never reloaded.

vault — HashiCorp Vault (multiple servers)

"vault": {
  "vault-main": { "address": "https://vault.example.com:8200", "token": "hvs...." },
  "vault-dr":   { "address": { "env": "VAULT_DR_ADDR" }, "token": { "env": "VAULT_DR_TOKEN" } }
},
...
"secrets": { "vault": { "target": "vault-dr", "path": "secret/data/databases/db4" } }

• vault is a map of named servers; address/token/namespace accept env-refs, extra keys are passed through to node-vault.
• secrets.vault.target picks the server. Without target the implicit default client is used, built purely from VAULT_ADDR/VAULT_TOKEN (/VAULT_NAMESPACE) — node-vault's standard variables. The implicit default is not part of the named map: an entry you name "default" is just a regular named entry.
• KV v2 responses are unwrapped: placeholders address the secret payload directly.
• Dynamic secrets (e.g. database/creds/<role>) carry a lease: the lease is renewed at ~80% of its TTL (with jitter). When renewal fails (max TTL reached), fresh credentials are requested; if they differ, the connection pool is swapped atomically (new queries use the new pool immediately, in-flight queries finish on the old one, which is then drained).
• If Vault is unreachable past the lease deadline, the secret is marked stale and re-resolved lazily on next use; an auth failure with a stale secret triggers one forced re-resolve + reconnect.

aws — AWS Secrets Manager (named profiles)

"aws_secret_profiles": {
  "aws-prod": { "aws_profile": "prod", "aws_region": "us-east-1", "reload_interval_ms": 3600000 },
  "aws-dev":  { "aws_profile": { "env": "AWS_DEV_PROFILE" }, "aws_region": { "env": "AWS_DEV_REGION" } }
},
...
"secrets": {
  "aws": {
    "secret_id": "prod/erp/mssql",       // name or full ARN
    "target": "aws-prod",                 // optional: aws_secret_profiles entry
    "version_stage": "AWSCURRENT"         // optional
  }
}

aws_profile, aws_region and reload_interval_ms live in named aws_secret_profiles entries (values accept env-refs). Without target the default AWS SDK credential chain is used (env, shared config, SSO, IMDS…) and the secret is static. The secret value must be a JSON object (SecretString); its keys become the ${aws.*} namespace. AWS Secrets Manager has no leases, so reloading is opt-in via the profile's reload_interval_ms (min 10s): the secret is re-fetched on that interval and rotation is picked up with the same atomic pool swap as Vault.

env_files — extra .env files

"env_files": ["~/.db_acess_mcp/secrets.env"]

Applied at startup, before any secret resolution; --env-file <file> (repeatable) appends to the config list. Files use standard dotenv syntax. Rules (security):

aws_iam — passwordless RDS/Aurora (IAM auth tokens)

"options": {
  "host": "pg.abc.us-east-1.rds.amazonaws.com", "port": 5432, "database": "appdb",
  "user": "${aws_iam.username}", "password": "${aws_iam.token}",
  "ssl": { "rejectUnauthorized": false }          // SSL is MANDATORY for IAM auth
},
"secrets": { "aws_iam": { "username": "readonly", "target": "aws-prod" } }

No password is stored anywhere: a 15-minute SigV4 auth token is generated locally and used as the password; the standard refresh pipeline re-signs it before expiry and swaps the pools atomically. Tokens are always signed for the real RDS host/port from options (never the tunnel's 127.0.0.1), so tunnels work unchanged; connection strings are not supported here. Optional host/port in the spec override the endpoint (e.g. a reader endpoint).

AWS-side prerequisites:

• IAM auth enabled on the instance/cluster (IAMDatabaseAuthenticationEnabled);
• the DB user is IAM-bound — postgres: GRANT rds_iam TO readonly; mysql: CREATE USER readonly IDENTIFIED WITH AWSAuthenticationPlugin AS 'RDS';
• the caller has rds-db:connect on arn:aws:rds-db:<region>:<acct>:dbuser:<resource-id>/readonly;
• not supported by RDS for SQL Server. Every connect is auditable in CloudTrail.

aws_redshift_creds — temporary Redshift credentials

"options": { "host": "cluster....redshift.amazonaws.com", "port": 5439, "database": "dwh",
             "user": "${aws_redshift_creds.username}", "password": "${aws_redshift_creds.password}" },
"secrets": { "aws_redshift_creds": { "cluster_id": "my-cluster", "db_user": "readonly",
                                     "target": "aws-prod", "duration_seconds": 3600 } }

redshift:GetClusterCredentials issues a temporary user+password pair (900–3600s); the returned username carries the IAM: prefix and is sent to the server verbatim. TTL comes from the API's expiration and feeds the same auto-refresh + pool-swap pipeline.

SSO bootstrap for AWS profiles

An aws_secret_profiles entry may carry the same sso block as ssm tunnels:

"aws_secret_profiles": {
  "aws-prod": { "aws_profile": "my-profile", "aws_region": "us-east-1",
                "sso": { "session": "my-sso-session", "timeout_ms": 300000 } }
}

Before any provider referencing the profile (aws, aws_iam, aws_redshift_creds) uses credentials, the session is verified with aws sts get-caller-identity; an expired session triggers aws sso login (browser) and the resolution waits up to timeout_ms. sso.profile defaults to the entry's aws_profile. Login dedup is by session name — a tunnel and a secret resolution on the same session share one browser login.

Adding a provider

Implement SecretProvider (src/interfaces/secret-provider.ts) and add one binding line in src/composition/modules/secrets.module.ts. The provider name is both the config key under secrets and the placeholder namespace.

Tunnels

"tunnels": {
  "bastion-ssm": { "type": "ssm", "options": { "target": "i-0123...", "region": "us-east-1", "profile": "default" } },
  "bastion-ssh": { "type": "ssh", "options": { "host": "bastion", "port": 22, "username": "ec2-user", "privateKey": "~/.ssh/id_ed25519" } }
}

• ssh — runs inside the MCP process (via the ssh2 library): a local listener forwards TCP through the SSH channel. Because it is in-process it dies with the process even on SIGKILL — orphaned ports are impossible. Options: host, port (22), username, password, privateKey (file path, ~ ok), passphrase, agent (true = platform default agent, or an explicit socket/pipe path), ready_timeout_ms. The bastion host key is verified (MITM defence): by default against ~/.ssh/known_hosts (plaintext and hashed entries, and [host]:port for non-standard ports). Pin it explicitly with host_key_sha256 (the ssh-keygen -lf fingerprint, with or without the SHA256: prefix), point known_hosts at another file, or set strict_host_key: false to accept any key (insecure — opt-out only). An unknown or changed key is rejected.
• ssm — spawns aws ssm start-session --document-name AWS-StartPortForwardingSessionToRemoteHost under a tiny watchdog process. The watchdog holds a stdin pipe from the MCP process: if the MCP process dies for any reason (including SIGKILL), the OS closes the pipe and the watchdog kills the whole aws/session-manager-plugin tree (taskkill /T /F on Windows, process group kill on POSIX). Requires the AWS CLI and session-manager-plugin on PATH. Options: target (instance id), region, profile, document_name.

AWS SSO bootstrap (ssm tunnels)

"bastion-ssm": {
  "type": "ssm",
  "options": { "target": "i-...", "region": "us-east-1", "profile": "prod" },
  "sso": { "session": "my-sso", "profile": "prod", "timeout_ms": 300000 }   // all fields optional
}

When a tunnel has an sso block, the session is verified with aws sts get-caller-identity --profile <profile> before the tunnel opens. If it is missing or expired, a login is started (browser flow): with sso.session set it runs aws sso login --sso-session <name> (the canonical IAM Identity Center form — one session may back several profiles); otherwise aws sso login --profile <profile>. The tunnel waits, polling every 3s, until the session works or timeout_ms (default 5 minutes) elapses — then TUNNEL_FAILED with a hint containing the exact manual command. sso.profile defaults to the tunnel's options.profile; the dedup/marker key is the session name when present.

• The SSO session is never closed by this server; the login process is not watchdog-wrapped, is never killed and survives the MCP instance.
• Concurrent logins are deduplicated: within an instance by profile; across instances via a <workdir>/sso/<profile>.login.json marker — a second instance waits for the first login instead of opening another browser tab (markers of dead processes are ignored via PID + start-time checks).
• SSO tokens and ~/.aws/sso/cache are never read, parsed or logged — only fixed-argument aws CLI invocations, no shell.

Behavior:

• The tunnel's remote endpoint is taken from the connection's options (host/port as seen from the bastion).
• Tunnels are cached per instance and keyed by (tunnel name, remote host:port) — connections through the same bastion to the same database share one tunnel; query/up_tunnel reuse an already-open healthy tunnel.
• Reference counting: the tunnel closes when the last connection using it is closed (including by the idle timer).
• Every instance writes <workdir>/instances/<pid>-<startTime>.json with its tunnel PIDs. At startup (and every 10 minutes) each instance sweeps files of dead instances: PID liveness check, PID-reuse protection via OS process start time, and a command-line sanity check before killing anything.
• On a connection error during query, the tunnel is health-checked, reopened if needed, the pool is rebuilt, and the query is retried — up to 3 attempts with exponential backoff. Auth errors are never retried.

Isolation model

Every npx -y @rheopyrin/db-access-mcp process is fully isolated: its own config snapshot, connection pools, tunnels and idle timers. Nothing is shared between instances; the per-instance registry files exist only so that later instances can clean up after a crashed one. Two instances talking to the same database simply hold independent pools (mind your database max_connections; the default pool max is 5 per connection per instance).

Error codes

Tools return isError: true with a structured payload — never raw stacks or credentials:

Windows notes

• Paths use os.homedir(); ~ in CLI args and privateKey is expanded manually.
• Tunnel trees are killed with taskkill /T /F; process inspection uses PowerShell (wmic is gone from Windows 11).
• AWS CLI v2 (aws.exe) and v1 (aws.cmd) are both handled.
• Default SSH agent pipe: \\.\pipe\openssh-ssh-agent.

Development

npm ci
npm run lint        # eslint (typescript-eslint, type-checked)
npm run typecheck   # tsc --noEmit
npm test            # unit tests (fast, no docker)
npm run test:integration  # requires Docker: postgres:16, mysql:8, testcontainers/sshd
npm run test:e2e    # builds, then drives dist/cli.js over real stdio MCP
npm run build       # tsup -> dist/cli.js + dist/watchdog.js

Architecture: inversify DI container; every extension point (dialect drivers, secret providers, tunnel providers, MCP tools) is a multi-bound interface collected into a registry — adding an implementation is one class + one binding line. See src/composition/.

Not covered by automated integration tests (unit-tested with mocks; verify manually): Redshift specifics, SSM tunnels (needs real AWS), MSSQL against a live server, Vault/AWS Secrets Manager against live services.

Manual verification checklist

1. npx -y @rheopyrin/db-access-mcp first run → workdir, config.json, config.example.json created.
2. Add a real connection → connection_list, query (SELECT 1), query_plan.
3. Tunneled connection → up_tunnel returns 127.0.0.1:<port>; psql -h 127.0.0.1 -p <port> works.
4. kill -9 <mcp pid> → tunnel process disappears within seconds (watchdog); next start removes the stale instance file.
5. Vault dynamic creds: watch the log for secret refreshed / pool swapped after credential rotation around 80% of the lease TTL.