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

Read-only access is enforced in three independent layers:

1. Client-side statement policy - PostgreSQL queries are parsed with PostgreSQL's own grammar and checked against an allow-list before anything is sent; every connector forwards a single statement only
2. Database-level read-only - PostgreSQL sessions run in a read-only transaction, ClickHouse sessions run with readonly=1
3. Timeout protection - Connection and query timeouts are configurable per connection, with a hard timeout around the whole call

All write operations (INSERT, UPDATE, DELETE, etc.) are blocked at the database level. Successful query results are written to state_dir/results with 0600 permissions instead of being returned inline.

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.
• ClickHouse (both) – A login whose profile already sets readonly refuses the client-side readonly and max_execution_time settings by name. Each connector finds out once which settings the login accepts, with a probe that does not involve the caller's statement (SELECT 1 for clickhouse-client, the client construction for clickhouse-connect), drops only the refused ones, and remembers the answer. A statement is never re-run with weaker settings than it was first sent with.

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 (database drivers in the package) or CLI (uses psql/clickhouse-client)
• SSH tunnel support - Tunnels go through the system ssh for both implementations: key authentication, password authentication with sshpass, agent-loaded identities and host certificates, and ssh's own host-key verification
• Security built-in - Timeouts, managed result files, session controls
• DBeaver import - Import existing connections easily

Prerequisites

• uv for package installs and ephemeral uvx runs
• ssh (OpenSSH 7.6 or newer) if any connection uses an SSH tunnel; both implementations tunnel through it
• psql 12 or newer if you want PostgreSQL connections with implementation: cli (its CSV output mode is used)
• clickhouse-client if you want ClickHouse connections with implementation: cli
• sshpass only if you want 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 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.

The file holds a header line and one line per row, tab-separated. For PostgreSQL both implementations follow the quoting rules of psql --csv: NULL and the empty string are empty, and a value containing a tab, a double quote or a line break is enclosed in double quotes with inner quotes doubled, so such a row may span several lines. ClickHouse results use ClickHouse's own TabSeparatedWithNames escaping (\N for NULL, backslash escapes).

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 no database client binaries
• You're connecting to ClickHouse HTTP interface (port 8123, 8443)
• 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

• One tunnel: Both implementations start the system ssh for the tunnel, so everything ssh supports applies: keys, agents, certificates, ~/.ssh/config. ssh_tunnel.private_key is passed with -i; ssh_tunnel.password needs sshpass
• SSH agent / identity fallback: Omit both private_key and password and ssh runs without -i, so agent identities and matching identity options are 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: Tunnel startup defaults to 30 seconds to allow ssh interactive approval flows such as hardware tokens or short-lived certificate prompts. Set ssh_tunnel.ssh_timeout to a lower value when fail-fast behavior is preferred for unreachable bastions.
• Host-key verification: ssh verifies the bastion's host key, and by default the key must already be known: ssh_tunnel.host_key_checking takes the OpenSSH StrictHostKeyChecking values and defaults to yes. A bastion you have connected to with ssh before is already known. To provision one you have not, fetch its key with ssh-keyscan -p <port> <host>, compare the fingerprint (ssh-keygen -lf on the output) with one you got from the bastion's operator or console, and only then append the line to ~/.ssh/known_hosts; ssh-keyscan itself does not verify anything, so appending it unseen is trust on first use, which is what accept-new does with a safety check. ssh_tunnel.known_hosts_file can point at another file; /etc/ssh/ssh_known_hosts is read as well. accept-new is the explicit trust-on-first-use mode: the bastion is recorded on first use and refused if its key changes, and because ssh only warns when it cannot write the file, the tunnel passes known_hosts files explicitly and checks that a first-use key was saved. It uses ssh_tunnel.known_hosts_file when set, otherwise the standard user and global files. This mode overrides UserKnownHostsFile and GlobalKnownHostsFile from ~/.ssh/config; set ssh_tunnel.known_hosts_file to use a custom file. HostKeyAlias and HostName still apply. A bastion covered only by an @cert-authority entry is always connected to strictly, so a valid host certificate connects and a raw key is refused rather than recorded. no restores the previous behaviour: every key is trusted and none is recorded. A changed key surfaces as an SSH: error naming host key verification; remove the stale line from the file once you have confirmed the new key.