Show and tell: End-to-End Walkthrough — Hardening Database MCP Connectors Against Hallucinated LLM Mutations

[A7mdMe]

Disclosure: Maintainer of MCP Production Kit (listed in docs/frameworks.md via PR #4192). Posting in Show and tell to share a practical implementation walkthrough for safely connecting AI coding agents (Claude Desktop, Cursor, Cline) to relational databases without exposing production systems to destructive mutations.

---

The Problem: Why Toy Database Tools Are Risky

Most basic MCP database tools pass raw SQL directly from the LLM prompt to the database driver. If the connection possesses write permissions, a prompt injection or hallucination can execute DROP TABLE, DELETE, or chained SQL statements with zero application-layer defense.

Relying solely on database credentials can also fail if the connection string uses a service-role or shared credential. A defense-in-depth architecture requires both least-privilege database roles and application-level query validation.

---

End-to-End Practical Walkthrough

We created a runnable verification script (examples/secure_database_agent_walkthrough.ts) demonstrating four critical guardrails:

import { validateReadOnlyQuery, SCHEMA_INTROSPECTION_SQL } from './pro_pack/postgres_connector.js';

// 1. Safe Schema Grounding
console.log('Inspecting metadata via information_schema...');

// 2. Safe Analytical Queries (SELECT + WITH CTEs)
const safeQuery = `
  SELECT u.id, u.email, COUNT(o.id) as total_orders
  FROM users u
  LEFT JOIN orders o ON u.id = o.user_id
  WHERE u.created_at >= $1
  GROUP BY u.id, u.email
`;
const safeRes = validateReadOnlyQuery(safeQuery);
// safeRes.safe === true

// 3. String Literal False-Positive Immunity
// Naive regexes break if a text value contains words like 'DELETED' or 'UPDATE'.
// Our normalizer temporarily blanks string literals ('...' -> '') during token analysis.
const literalQuery = "SELECT id FROM logs WHERE status = 'DELETED' AND tag = 'UPDATE'";
const literalRes = validateReadOnlyQuery(literalQuery);
// literalRes.safe === true

// 4. Intercepting Attack Vectors
validateReadOnlyQuery('DELETE FROM users WHERE id = 1'); 
// => BLOCKED (Must begin with SELECT or WITH)

validateReadOnlyQuery('SELECT * FROM accounts; DROP TABLE users;'); 
// => BLOCKED (Multiple SQL statements rejected)

validateReadOnlyQuery('-- bypass\nDROP TABLE audit_logs;'); 
// => BLOCKED (Comments stripped prior to inspection)

validateReadOnlyQuery('WITH d AS (DELETE FROM sessions RETURNING *) SELECT * FROM d;'); 
// => BLOCKED (Prohibited keyword 'DELETE' detected)

(Note: This is an application-level token & regex sanitization guardrail, not a full SQL AST compiler, and is designed to work alongside PostgreSQL GRANT SELECT and read-only roles).

---

Free Open-Source Core vs. Turnkey Commercial Add-on

Capability	Free Open-Source Core (MIT)	Commercial Pro Pack ($19 Single License)
Protocol Compliance	Full (v2024-11-05 JSON-RPC 2.0)	Full (v2024-11-05 JSON-RPC 2.0)
Transports	Local stdio + Node.js HTTP/SSE	Local stdio + Node.js HTTP/SSE
Core Utilities	System metrics, SSRF guard, memory store	All core utilities included
Glama Registry Score	Grade B Verified	Grade B Verified
PostgreSQL / Supabase Tool	DIY implementation	Turnkey postgres_connector.ts with 12 security unit tests
Edge Serverless Hosting	-	Cloudflare Workers global edge deployment (cloudflare_worker.ts)
Container Orchestration	Standard Dockerfile	Production Docker Compose (Fly.io / Railway / AWS ECS)

---

Questions & Objections for Discussion

We would love to gather real-world developer perspectives:

1. Database sandboxing: What is your team's biggest concern when connecting an AI agent to a live database? Do you rely solely on database user permissions, or do you enforce application-level query checks?
2. Edge vs. Container: When deploying remote MCP servers for your team, do you prefer serverless edge workers (Cloudflare) or container runtimes (Docker / ECS / Fly.io)?

• Repository: github.com/A7mdMe/mcp-production-kit
• Turnkey Pro Pack: Whop Checkout

[wilsonccccc]

The connector allows SELECT and WITH statements after token-level validation. How does it handle valid-looking SELECT queries that invoke side-effecting PostgreSQL or user-defined functions? Does the connector also enforce a read-only transaction at runtime, in addition to relying on database grants?

[A7mdMe]

@wilsonccccc Excellent and critical question — this hits the exact boundary where application-level inspection ends and database engine enforcement must take over.

1. The Side-Effecting Function / UDF Threat

You are completely right: in PostgreSQL, a query like SELECT trigger_payout(); or SELECT pg_terminate_backend(pid); syntactically begins with SELECT, contains no prohibited mutation keywords in the outer statement, but invokes mutating procedural PL/pgSQL logic under the hood.

Because an application-level token/regex scanner only inspects query tokens and does not have access to PostgreSQL's system catalogs (to know whether a function's volatility is VOLATILE vs STABLE / IMMUTABLE), relying solely on application-level filtering for UDFs is fundamentally insufficient.

2. Runtime Read-Only Transaction Enforcement

To address this, our layered architecture pairs token inspection with runtime engine enforcement:

1. Transaction-level block (BEGIN TRANSACTION READ ONLY):
   Wrapping execution in a read-only transaction:

   const client = await pool.connect();
   try {
     await client.query('BEGIN TRANSACTION READ ONLY');
     const res = await client.query({ text: validatedSql, values: parameters });
     await client.query('COMMIT');
     return res.rows;
   } catch (err) {
     await client.query('ROLLBACK');
     throw err;
   } finally {
     client.release();
   }

2. Connection-level pool parameter:
   Alternatively, setting options: '-c default_transaction_read_only=on' in the pg.Pool configuration.

When default_transaction_read_only = on or inside BEGIN TRANSACTION READ ONLY, PostgreSQL's execution engine strictly enforces read-only semantics at the storage layer. If any function called by SELECT attempts an INSERT, UPDATE, DELETE, or DDL, PostgreSQL aborts the query immediately with:
ERROR: cannot execute <OPERATION> in a read-only transaction (SQLSTATE 25006)

3. Why Keep Application-Level Token Inspection?

If BEGIN READ ONLY and database GRANT SELECT handle engine execution, why inspect at the MCP application layer?

• Immediate fast rejection & clear error feedback to the LLM: If an agent hallucinates a DROP TABLE or semicolon-stacked injection, the MCP tool returns a structured error immediately without tying up connection pool slots or hitting the database.
• Defense against privilege escalation / config drift: If a developer accidentally points the connection string at a superuser or shared service-role credential without read-only restrictions, the token/regex guardrail acts as an emergency tripwire against obvious destructive commands.

I've also updated our setup guide and walkthrough example (examples/SAFE_POSTGRES_MCP_WALKTHROUGH.md) to make BEGIN TRANSACTION READ ONLY an explicit code pattern in the connector documentation. Thank you for the sharp feedback!