MCP Read-Only SQL Server

A secure MCP (Model Context Protocol) server that provides read-only SQL access to PostgreSQL and ClickHouse databases with built-in safety features.

Default layout:

• Config: ~/.config/lukleh/mcp-read-only-sql/connections.yaml
• Credentials: stored in connections.yaml
• State: ~/.local/state/lukleh/mcp-read-only-sql/
• Query results: ~/.local/state/lukleh/mcp-read-only-sql/results/
• Cache: ~/.cache/lukleh/mcp-read-only-sql/

Security

The server implements a three-layer security model:

1. Database-level read-only - Sessions forced to read-only mode
2. Timeout protection - Connection and query timeouts are configurable per connection
3. Managed result files - Successful query results are written to state_dir/results with 0600 permissions

All write operations (INSERT, UPDATE, DELETE, etc.) are blocked at the database level.

How Read-Only Is Enforced

• PostgreSQL (both implementations) – Every query is parsed with PostgreSQL's own grammar (pglast) before it leaves the server. Only SELECT, EXPLAIN and SHOW shapes are accepted, and every function call must be on an allow-list of pg_catalog functions PostgreSQL declares side-effect free. This refuses the things a read-only transaction alone does not stop, such as COPY ... TO PROGRAM, DO blocks, and calls like pg_terminate_backend() or pg_read_file(). The list can be extended per connection with allowed_functions.
• PostgreSQL (Python) – Connections are opened with default_transaction_read_only=on, sessions are set to read-only, and every statement runs with a configurable statement_timeout.
• PostgreSQL (CLI) – Queries are wrapped in a transaction that issues SET TRANSACTION READ ONLY; before execution. Only a single statement (plus optional trailing semicolon) is forwarded, and all psql invocations include --single-transaction, -v ON_ERROR_STOP=1, and PGOPTIONS=-c default_transaction_read_only=on for defence in depth.
• ClickHouse (Python) – The driver sets readonly=1 plus connection/query timeouts, forcing the server to reject any write or DDL attempt.
• ClickHouse (CLI) – clickhouse-client is invoked with --readonly=1, --max_execution_time, and connection timeouts, turning the session into a read-only context.

The shared connector base also applies hard timeouts, giving the MCP server deterministic behaviour even if the database misbehaves.

Because bare names resolve through search_path, the guard also asks the server, right before the query, whether any bare function or operator it uses has a definition outside pg_catalog in a schema the session can see, and refuses the query if so. This matches on name only, so a visible public.length(integer) refuses length('abc') even though PostgreSQL would pick the catalog function; the error says to qualify the call as pg_catalog.length(...) or list the function. Resolving overloads client-side would mean reimplementing PostgreSQL's function resolution, so the guard stays conservative. The guard is still a filter, not a privilege boundary. It cannot see inside views, user-defined types or casts that already exist in the database, and it does not reduce what the configured login is allowed to do. Log in with a role that can only read (for PostgreSQL 14+, pg_read_all_data); a superuser login stays a superuser login.

See READ_ONLY_ENFORCEMENT_MATRIX.md for a statement-by-statement view of every write-capable command and the tests that enforce it.

Key Features

• Read-only enforcement - Multiple layers of protection against writes
• Multi-database support - PostgreSQL and ClickHouse
• Dual implementations - Choose between Python (pure Python, no dependencies) or CLI (uses psql/clickhouse-client)
• SSH tunnel support - Both implementations support key authentication, password authentication (Paramiko in Python, sshpass in CLI), and falling back to agent-loaded identities when no credentials are provided
• Security built-in - Timeouts, managed result files, session controls
• DBeaver import - Import existing connections easily

Prerequisites

• uv for package installs and ephemeral uvx runs
• psql if you want PostgreSQL connections with implementation: cli
• clickhouse-client if you want ClickHouse connections with implementation: cli
• sshpass only if you want CLI-based SSH tunnels with password authentication
• just is optional and only needed for repo-local contributor workflows

Install the optional CLI binaries with your operating system's package manager or the official PostgreSQL / ClickHouse packages for your environment.

The CLI binaries are located via the override environment variable (MCP_READ_ONLY_SQL_PSQL_PATH / MCP_READ_ONLY_SQL_CLICKHOUSE_CLIENT_PATH) if set, then PATH, then OS-specific fallback locations (e.g. Homebrew keg-only libpq on macOS, packaged PostgreSQL directories on Linux). If a binary is installed somewhere not on PATH, set the matching variable to its full path.

The SQL package keeps both execution models first-class:

• implementation: cli uses the official database client binaries you already trust in operations.
• implementation: python stays fully supported when you want a pure-Python setup with no external database client binaries.

You can verify optional CLI dependencies with:

psql --version
clickhouse-client --version
sshpass -V

Quick Start

1. Install or Run the Server

For the published package, prefer @latest with uvx:

uvx mcp-read-only-sql@latest --write-sample-config

Or install it once and reuse the command directly:

uv tool install mcp-read-only-sql
mcp-read-only-sql --write-sample-config

When using uvx with the published package, prefer mcp-read-only-sql@latest in user-facing docs and MCP client configs. This avoids reusing a stale cached tool environment after a new release is published.

For one-off runs from this checkout, use uvx --from .:

uvx --from . mcp-read-only-sql --write-sample-config

For a persistent local install from this checkout:

uv tool install .
mcp-read-only-sql --write-sample-config

For checkout-based commands below, you can replace uvx --from . mcp-read-only-sql with uvx mcp-read-only-sql@latest once you want to use the published package instead.

That creates:

• ~/.config/lukleh/mcp-read-only-sql/connections.yaml
• ~/.local/state/lukleh/mcp-read-only-sql/
• ~/.local/state/lukleh/mcp-read-only-sql/results/
• ~/.cache/lukleh/mcp-read-only-sql/

2. Choose an Implementation Per Connection

connections.yaml supports both implementations side by side:

- connection_name: postgres_cli
  type: postgresql
  implementation: cli
  servers:
    - "db.example.com:5432"
  db: analytics
  username: analyst
  password: change_me

- connection_name: clickhouse_python
  type: clickhouse
  implementation: python
  servers:
    - "analytics.example.com:8123"
  db: default
  username: analyst
  password: change_me

Use CLI mode when you want the behavior of psql or clickhouse-client, or when those tools are already part of your operational setup. Use Python mode when you want a package-only setup with no extra system binaries.

3. Import or Edit connections.yaml

You can edit the generated sample directly, or import a DBeaver workspace:

uvx --from . mcp-read-only-sql import-dbeaver \
  ~/Library/DBeaverData/workspace6/General/.dbeaver

That writes connections.yaml with any decrypted passwords stored directly in the file. The importer writes user-only permissions and keeps timestamped backups when it overwrites an existing file.

connections.yaml contains credentials. Keep it private, do not commit it, and restart the MCP process after editing it so changes take effect.

To allow a connection to access multiple databases, add an explicit allowlist:

- connection_name: analytics_multi
  type: postgresql
  servers:
    - "analytics.example.com:5432"
  allowed_databases:
    - analytics
    - reporting
  default_database: analytics
  username: analyst
  password: change_me

If you only set db, that single database is implicitly the allowlist.

PostgreSQL connections refuse function calls outside the built-in allow-list. If a query legitimately needs another function, list it under allowed_functions, spelled as the catalog spells it. A schema.name entry permits both schema.name(...) and the bare name(...), and pins the bare call to that schema: if another visible schema also defines the name, the query is refused. A bare entry permits only the bare call and trusts whatever it resolves to:

- connection_name: analytics
  type: postgresql
  servers:
    - "analytics.example.com:5432"
  db: analytics
  username: analyst
  password: change_me
  allowed_functions:
    - public.customer_segment
    - st_distance

4. Validate and Test Connections

The package includes management subcommands for connection validation and dry-run testing:

uvx --from . mcp-read-only-sql validate-config
uvx --from . mcp-read-only-sql test-connection
uvx --from . mcp-read-only-sql test-connection my_postgres
uvx --from . mcp-read-only-sql test-ssh-tunnel
uvx --from . mcp-read-only-sql --print-paths

If you are working from a clone, the same helpers are available through just:

just validate
just test-connection
just test-connection my_postgres
just print-paths

5. Add the MCP Server to Your Client

For Claude Code:

claude mcp add mcp-read-only-sql -- uvx mcp-read-only-sql@latest

For Codex:

codex mcp add mcp-read-only-sql -- uvx mcp-read-only-sql@latest

For manual testing with a different config root:

uvx mcp-read-only-sql@latest --config-dir /path/to/config-dir --print-paths

MCP Tools

run_query_read_only

Execute read-only SQL queries on configured databases.

{
  "connection_name": "my_postgres",
  "query": "SELECT * FROM users LIMIT 10",
  "database": "analytics",
  "server": "db2.example.com"
}

Parameters:

• connection_name (required): Identifier returned by list_connections
• query (required): SQL text that must remain read-only
• database (optional): Database to use (must be listed in the connection's allowlist).
• server (optional): Hostname to target a specific server. If not provided, uses the first server in the connection's list.

Returns: Absolute path to a TSV file created under the server's managed state directory, typically ~/.local/state/lukleh/mcp-read-only-sql/results/. Successful query results are persisted with 0600 permissions and are no longer returned inline on success.

Result files accumulate under state_dir/results/ until you remove them. If you do not want to retain old query output, periodically clean ~/.local/state/lukleh/mcp-read-only-sql/results/.

list_connections

List all available database connections.

Returns: Tab-separated text with columns name, type, description, servers, database, databases, and user. database is the default database, while databases lists the allowlisted databases (comma-separated). The servers column lists comma-separated hostnames after resolving SSH/VPN tunnels, so entries reflect the endpoints the agent should reference.

Implementation Matrix

Database Support by Implementation

ClickHouse Port Compatibility

Important Notes:

• ClickHouse CLI (clickhouse-client) uses native protocol ports (9000, 9440)
• ClickHouse Python (using clickhouse-connect) uses HTTP/HTTPS ports (8123, 8443)
• Port mismatches are automatically handled - see below

Automatic Port Handling (Bidirectional):

ClickHouse Python Implementation:

• Direct connections: Port 9000 → automatically uses port 8123 on the same host
• SSH tunnels: Port 9000 → automatically tunnels to remote port 8123
• SSH tunnels: Port 9440 → automatically tunnels to remote port 8443

ClickHouse CLI Implementation:

• Direct connections: Port 8123 → automatically uses port 9000 on the same host
• SSH tunnels: Port 8123 → automatically tunnels to remote port 9000
• SSH tunnels: Port 8443 → automatically tunnels to remote port 9440

✨ This means you can use the same configuration for both CLI and Python implementations, regardless of which port you specify (8123 or 9000) - each implementation will automatically convert to the correct protocol port it needs!

Choosing an Implementation

Use CLI implementation when:

• You have the database CLI tools installed (psql, clickhouse-client)
• You prefer not to install Python database drivers
• You're connecting to ClickHouse on native ports (9000, 9440)
• You want the exact behavior of the official CLI tools

Use Python implementation when:

• You want a pure Python solution with no external dependencies
• You're connecting to ClickHouse HTTP interface (port 8123, 8443)
• You need SSH password authentication without installing sshpass
• You want more programmatic control over connections

Configuration Notes

HAProxy and Custom Ports

When using HAProxy or other proxy servers with ClickHouse:

• HAProxy typically provides HTTP interface on custom ports (e.g., 2650, 8000, etc.)
• Custom ports are NOT auto-converted - the system only converts standard ports (8123, 8443, 9000, 9440)
• For HAProxy connections: Use implementation: python since HAProxy usually proxies HTTP traffic
• If you get "Unexpected packet" errors with CLI on custom ports, switch to Python implementation

Example HAProxy configuration:

- connection_name: clickhouse_haproxy
  type: clickhouse
  servers:
  - haproxy-server:2650  # Custom HAProxy port
  implementation: python  # Use Python for HTTP protocol
  # ... other settings

Multiple Servers

When multiple servers are specified in a connection's configuration, the system currently uses only the first server in the list. Load balancing across servers is not implemented.

SSH Authentication

• Python implementation: Supports both ssh_tunnel.password and ssh_tunnel.private_key
• CLI implementation: Supports key-based authentication and can use passwords when sshpass is installed
• SSH agent / identity fallback: Omit both private_key and password to use agent-loaded identities and identity-related SSH configuration. The Python implementation lets paramiko discover keys via look_for_keys/allow_agent; the CLI implementation invokes system ssh without -i, so agent identities and matching identity options can be used. The configured ssh_tunnel.host, user, and port are still passed explicitly; full OpenSSH Host alias fallback for those fields is future work.
• Timeout behavior: CLI SSH tunnel startup defaults to 30 seconds to allow system ssh interactive approval flows such as hardware tokens or short-lived certificate prompts. Python/Paramiko SSH tunnel startup keeps the 5 second default because it does not use the system ssh interactive prompt path. Set ssh_tunnel.ssh_timeout to a lower value when fail-fast behavior is preferred for unreachable bastions.
• Host-key trust: SSH tunnel helpers currently trust newly seen bastion host keys automatically (StrictHostKeyChecking=no for CLI, Paramiko AutoAddPolicy for Python). Use these tunnels only on trusted networks until configurable host-key verification is added.