= MCP PostgreSQL Rust Bridge
v0.1.0
:description: A high-performance, network-ready MCP server bridge for PostgreSQL with read-only access and financial analytics tools.
:icons: font
:toc: macro
:toc-title: Table of Contents
:imagesdir: images
:sectnums:
[cols="2,10",frame=none]
|===
|image:logo-128.png[logo]
|An implementation of the Model Context Protocol (MCP) that gives AI agents read-only access to a PostgreSQL database, with built-in financial analytics tools. Built with Rust for speed, reliability & minimal footprint.
Unlike standard MCP servers using stdio, this bridge operates over the network:
SSE (Server-Sent Events) with a Hybrid Response mode, making it compatible with clients like LM Studio.
Standard HTTP (Synchronous): The modern MCP standard using a single POST /mcp endpoint. Open WebUI needs this
|===
toc::[]
NOTE: This project is a gateway only and does not include https://www.postgresql.org/[PostgreSQL]. You must deploy and configure your own PostgreSQL instance separately.
WARNING: Read-only enforcement relies on both an application-level prefix check
and a dedicated read-only PostgreSQL role. The application guard alone is
insufficient — see the <<security,Security>> section.
Read-Only by Design: Application-level prefix guard (SELECT/WITH only) plus
PostgreSQL role with no DML/DDL privileges. Defence in depth per ADR-001.
Network-Ready: Connect via SSE/HTTP; no local child-process management required.
LM Studio Optimized: Specialized "Hybrid Handshake" to prevent connection timeouts.
Modern Rust: Leveraging Edition 2024 and the Axum/Tokio/sqlx ecosystem.
Docker Optimized: Multi-stage builds with FROM scratch for a tiny footprint (~6MB).
== Tools
[cols="1,1,3"]
|===
| Tool | Type | Description
| sql_read_query
| Generic
| xecute a SELECT query. Queries not starting with SELECT are rejected
with a clear error. WITH prefix is accepted but see security notes below.
Trailing semicolons (;) are automatically stripped to prevent nested
sub-query errors (row_to_json). Returns a JSON array (max 500 rows).
| list_tables
| Generic
| List all tables in the public schema with their type.
| describe_table
| Generic
| Get column definitions (name, type, nullable, default) for a given table.
| portfolio_performance
| Financial
| Returns all holdings with unrealized P&L, current value, and cost basis per account,
using the latest available quote.
| at_risk_positions
| Financial
| Returns positions flagged as at-risk: drawdown below a configurable threshold
(default 10%) OR 7-day average news sentiment < -0.5.
Includes RSI and moving averages.
| sector_exposure
| Financial
| Returns portfolio allocation by sector: number of positions, total value,
and percentage. Useful for concentration and rebalancing analysis.
|===
== Database Schema Conventions
To ensure the LLM generates reliable SQL queries without hallucinations, the database schema follows strict rules enforced in the tools' system prompts:
ISIN Only for Relations: All data tables (quotes, holdings, signals, news, corporate_events, analyst_ratings) use only the isin column as the foreign key.
Ticker Semantics: The ticker column in the assets table is the natural entry point for the user and LLM (e.g. AAPL, ACA).
One Ticker, Multiple ISINs: A single ticker can be tied to multiple ISINs (e.g., direct shares vs employee stock plans). The LLM is instructed to use the name column to differentiate them.
Views over Joins: The LLM is instructed to query view_latest_quotes and view_portfolio_summary rather than building complex JOIN logic from scratch.
[[security]]
== Security
Read-only protection is implemented at two levels — both are required:
=== Level 1 — Application guard (this bridge)
sql_read_query rejects any query not starting with SELECT.
Queries starting with WITH are accepted (needed for complex analytics),
but PostgreSQL's writable CTEs mean a WITH ... DELETE/INSERT ... SELECT
could pass this check.
This guard provides user-friendly error messages, not a security boundary.
=== Level 2 — PostgreSQL read-only role (required)
Create a dedicated role with no DML or DDL privileges:
[source,sql]
CREATE ROLE mcp_ro LOGIN PASSWORD 'your_password';
GRANT CONNECT ON DATABASE paitrimony TO mcp_ro;
GRANT USAGE ON SCHEMA public TO mcp_ro;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO mcp_ro;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO mcp_ro;
-- Belt and suspenders: block writes even if privileges are misconfigured
ALTER ROLE mcp_ro SET default_transaction_read_only = on;
Set MCP_PG_DATABASE_URL to use mcp_ro. At that point, any write attempt
— including writable CTEs — will be rejected by PostgreSQL itself with
ERROR: cannot execute DELETE in a read-only transaction.
== Deployment Options
=== Using the DockerHub Image (Fastest)
[source,bash]
TIP: This bridge uses a Hybrid Initialize response. It returns the handshake
directly via HTTP POST to avoid the 60s timeout often encountered with LM Studio's
SSE implementation.
== Known Limitations
Broadcast Channel: Responses are broadcasted to all active SSE subscribers.
Ideal for private/single-user use.
No auth on the bridge: Keep port 3005 behind a trusted LAN or VPN.
Never expose it to the internet (ADR-001).
WITH prefix accepted: Writable CTEs (WITH ... DELETE ... SELECT) pass
the application-level guard. Mitigated entirely by the mcp_ro read-only role.
pgvector / RAG: Vector similarity search is handled by the dedicated mcp-rag
bridge, not this one.
500 row limit: All queries are capped at 500 rows. Use sql_read_query with
explicit LIMIT and OFFSET for pagination.
== License
MIT License. See LICENSE for details. Note: Mentioning the author in credits
is required for any redistribution.
== AI assistance disclosure
This project includes AI-assisted code and documentation.
The maintainer reviews and tests changes, however the software is provided "as is" and may contain errors.
Use at your own risk; validate in your environment before production use.