Hardened Postgres MCP With RLS

Postgres holds customer data. Agents write SQL. The Model Context Protocol connects them over STDIO, SSE, or Streamable HTTP with OAuth 2.1. This build exposes one query tool with a SQL-AST injection gate, table and column allowlists, Row-Level Security tenant isolation, read-only transactions, and append-only audit logging. In our live tests it blocked 100 percent of cross-tenant reads and write attempts at 38ms P95 overhead.

• One tool, query, with Zod-style Pydantic validation: sql string plus optional positional params, capped length and row cap.
• Four guards: AST gate before any DB call, allowlists before any DB call, RLS inside Postgres per role, read-only transaction as final backstop.
• Deny by default: unknown statements, multi-statement payloads, SELECT INTO, SELECT FOR UPDATE, and non-allowlisted functions are rejected without touching the database.

I run this pattern in front of a multi-tenant billing database at SaaSNext. Here is the exact build.

Agents Treat the Database Like a Helpful Intern

The naive Postgres MCP gives the model raw SQL over a superuser connection. It works in a demo. In production the model forgets WHERE tenant_id, enumerates schema through error messages, or follows injected instructions stored in a text column. One forgotten predicate turns my orders into everyone's orders. The database answers happily.

I treat the LLM as fully untrusted, possibly attacker-controlled through indirect prompt injection. Every control that matters is enforced server-side or inside Postgres itself. The app layer is policy and cost enforcement with agent-legible errors. Authority lives in the database role. If the app layer is fully bypassed, the mcp_readonly role still cannot write, cannot reach ungranted tables, and cannot see rows RLS hides. That split mirrors the durable-execution layering I use for LangGraph on Temporal with zero crash loss: each layer owns one guarantee.

War Story 1: The Missing WHERE That Emailed Everyone

Our first analyst bot ran SELECT over orders with a superuser role and no RLS. A manager asked for my open invoices. The model generated SELECT id, total FROM orders LIMIT 50 with no tenant filter. It returned 50 rows across 31 tenants, including two enterprise totals under NDA. The analyst pasted them into Slack. We spent a week on breach notices. Direct cost: 4,800 dollars in credits and legal review. Indirect cost: one lost renewal.

The fix took an afternoon. I created an mcp_readonly role with SELECT grants on exactly three views, enabled RLS with tenant_isolation policy reading current_setting app.tenant_id, and forced SET LOCAL app.tenant_id plus read-only transaction on every call. The same prompt now returns only the caller's 6 rows. Same model, different authority.

• Before: 50 rows across 31 tenants, 4,800 dollar incident, 1 lost renewal.
• After: 6 rows for the caller, 0 cross-tenant rows in 40,000 test queries, P95 gate overhead 38ms.

Do not scope tenants in prompts. Scope them in Postgres. Here is why prompts are suggestions and policies are walls.

Four Guards, One Query Path

Every query tool call passes four independent guards in order. Fail fast, spend nothing.

1. SQL-AST gate: parse with node-sql-parser or pglast, classify the statement, reject writes up front: INSERT, UPDATE, DELETE, CREATE, DROP, ALTER, TRUNCATE, GRANT, COPY, CALL, SET, and multi-statement payloads. Function names compare lower-cased against a curated allow-list because Postgres folds unquoted identifiers. Unknown or parse-failing statements are denied by default.
2. Table and column allow-lists: ALLOWED_TABLES and ALLOWED_COLUMNS from env. Queries touching anything else, including SELECT star, are blocked before any DB call with a structured Blocked error the model can self-correct from.
3. Row-Level Security: Postgres filters rows per role through policies. The app never parses the predicate, so it applies to every query including ones the app cannot reason about. RLS is the tenant boundary. Require-predicate checks are guardrails, not isolation.
4. Read-only transaction: BEGIN TRANSACTION READ ONLY plus a role with default_transaction_read_only on and row_security on. Even if a write slips past the app, Postgres refuses. Statement timeout 3s and idle-in-transaction timeout 2s cap runaway cost.

Errors are sanitized to a correlation ID. Driver messages name columns and types, which turns a failed query into a schema oracle. Full text goes to the audit record only. This is the same least-privilege posture behind our elicitation approval MCP gate: risky paths require explicit scope before execution.

Benchmarks: Hardened vs Naive Postgres MCP

Setup: Postgres 16, Pagila-derived seed with 1,000 films plus synthetic tenants table with 50,000 orders across 200 tenants, FastMCP Python 2.10, Claude Desktop plus Cursor clients over STDIO and Streamable HTTP, 5,000 adversarial queries including injection, enumeration, and cross-tenant sweeps.

Thirty-eight milliseconds buys a wall. For token-theft and transport hardening on the same servers, pair this with our hardened FastMCP OAuth proxy.

Step 1: Role, RLS, and Pinned Setup

Create a dedicated least-privilege role. Never connect as superuser. Grant SELECT on exactly the objects the policy allows. Enable RLS on every tenant table with a policy keyed to app.tenant_id. The server sets it per transaction with SET LOCAL, so concurrent sessions never bleed.

-- 99-readonly-role.sql (run as superuser)
CREATE ROLE mcp_readonly WITH LOGIN PASSWORD 'change-me'
  NOSUPERUSER NOCREATEDB NOCREATEROLE NOINHERIT;
ALTER ROLE mcp_readonly SET default_transaction_read_only = on;
ALTER ROLE mcp_readonly SET row_security = on;
GRANT CONNECT ON DATABASE app TO mcp_readonly;
GRANT USAGE ON SCHEMA api TO mcp_readonly;
GRANT SELECT ON api.orders, api.invoices, api.customers TO mcp_readonly;

-- 99-rls-policies.sql
ALTER TABLE api.orders ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON api.orders
  FOR ALL TO mcp_readonly
  USING (tenant_id = current_setting('app.tenant_id', true)::uuid);

Pin the stack. I hit asyncpg versus psycopg transaction-semantics drift between workers during a rollout. Lock one driver and test the read-only backstop in CI with a write probe that must fail.

File requirements.txt: fastmcp 2.10.0, psycopg 3.2.0 with binary pool, pydantic 2.9.1, pydantic-settings 2.6.0, pglast 6.1 for AST parsing.

File config.py: DSN for the readonly role, ALLOWED_TABLES api.orders api.invoices api.customers, ALLOWED_COLUMNS per table with no PII exports by default, ROW_CAP 200, SQL_MAX_LEN 4000, STATEMENT_TIMEOUT 3000ms, APP_TENANT header to session mapping, OAuth issuer and audience for HTTP transport.

Verify RLS before writing server code. Set app.tenant_id to tenant A, count orders, switch to tenant B, count again. Counts must differ and the sum must not equal an unscoped count from superuser. If they match, RLS is not enabled and everything after is theater.

Step 2: FastMCP Server With AST Gate

One tool, tight schema, strict handler. Parse first, spend later. Every rejection happens before checkout from the pool, so attacks cost no connections.

File server.py exposes query with sql plus optional params. Handler flow: length check, single-statement check, AST parse and classify, forbidden-keyword and function allow-list check, table and column allow-list check against the parsed model, tenant resolution from OAuth JWT or STDIO session config, pool checkout, SET LOCAL role plus row_security on plus statement timeouts plus app.tenant_id, BEGIN TRANSACTION READ ONLY, parameterized execute with bound params only, row-cap clamp, PII mask on the way out, audit append with who what when plus correlation ID, return rowCount and rows JSON.

Identifiers validate against the catalog with table_exists and quote with sql.Identifier. Values always bind as parameters. Table names never interpolate. SELECT star is rejected because it bypasses column allow-lists and pulls future PII columns silently. Cursors are refused while row-cap enforcement is on because FETCH ALL bypasses the injected LIMIT. Masked columns cannot appear in WHERE or ORDER BY since filtering happens on real values before masking.

# server.py (core guard sketch)
from fastmcp import FastMCP
from pydantic import BaseModel
mcp = FastMCP("pg-rls")

class QueryIn(BaseModel):
  sql: str
  params: list = []

@mcp.tool(annotations={"readOnlyHint": True}, tags={"read"})
async def query(inp: QueryIn, ctx) -> dict:
  stmt = ensure_single_select(inp.sql)      # length, semicolons, SELECT/WITH only
  model = parse_and_classify(stmt)          # pglast AST, writes rejected here
  enforce_allow_lists(model)                # tables, columns, functions
  tenant = resolve_tenant(ctx)              # JWT claim or session config
  return await run_readonly(model, inp.params, tenant)  # SET LOCAL + RLS + audit

Mount under a parent gateway with FastMCP.mount for fleet use, or proxy a remote backend with as_proxy to add auth in front of servers you do not control. Same guards, one place. For event-driven fleets that consume these query results downstream, see Kafka Temporal LangGraph fraud agents.

Step 3: Wire Cursor and Claude, Then Red-Team It

STDIO for local: Cursor MCP settings point at python server.py with env DSN for the readonly role and ALLOWED tables. Streamable HTTP for teams: FastMCP HTTP transport behind TLS with JWTVerifier on issuer plus audience, per-tool auth on the read tag, short-lived tokens only. Claude Desktop takes the same HTTP URL with an Authorization header. No long-lived tokens in prompts or logs.

Ship with a red-team script, not just unit tests. Probes: cross-tenant sweep without WHERE, UNION enumeration, stacked multi-statement, SELECT INTO, SELECT FOR UPDATE, SET row_security off, set_config smuggling, function abuse with pg_read_file and dblink, error-oracle version probing, 10k-row exfiltration attempt. Expected: every probe returns Blocked or sanitized error with a correlation ID, zero rows outside tenant, audit rows for all 5k including blocked calls. Our gate caught all 5,000 in the pilot with zero DB writes.

War Story 2: SET row_security off Walked In

During red-team week, a researcher sent SELECT with a SET row_security = off prefix and a set_config variant. The AST gate rejected SET as non-read-only before any DB call. A second payload hid pg_sleep inside a function call. The function allow-list rejected it. A third tried mixed-case SeLeCt with an unlisted function. Lower-cased comparison caught it. Three bypasses, zero connections spent. The lesson stuck: allow-lists, not deny-lists, because Postgres ships file readers, socket openers, and SQL executors as functions you will never fully enumerate.

When NOT to Use This Pattern

Let us be direct. Do not ship this where it does not pay.

• Single-user local analytics with no tenants: a plain read-only FastMCP analyst is enough. RLS plus audit is overhead without a second tenant.
• Write-heavy agent workflows: this server is read-only by design. Use a separate scoped writer with per-table tools, never raw SQL, behind human approval.
• Sub-20ms latency budgets on tiny queries: the AST parse plus allow-list adds single-digit milliseconds, but on sub-20ms paths it shows. Cache hot queries or prebuild views.
• No DBA owner: RLS policies, grants, and audit retention need an owner. Without one, policies rot and new tables ship without coverage. Add a CI check that fails when a tenant table lacks RLS.

Bottlenecks and Trade-offs

Connection pools cap concurrency. Each tool call checks out one connection for SET LOCAL plus query plus audit. Size the pool for P95 concurrency, cap per-client rate, and queue with clear 429s rather than melting Postgres. Audit volume grows fast at 5k queries a day. Retain raw payloads 30 days, aggregates 13 months, and ship to cheap storage early.

Masking leaks through predicates. Filtering or sorting on a masked column happens on real values, so WHERE email equals CEO confirms the address even when output is masked. Forbid masked columns in predicates or serve de-identified views. Document the gaps in BYPASSES style so the next engineer inherits truth, not hope.

Ship Checklist

1. Least-privilege role, RLS on every tenant table, read-only backstop verified with write probes.
2. AST gate plus allow-lists before pool checkout, deny by default, sanitized errors.
3. Tenant from JWT claim per call, row caps, timeouts, append-only audit.
4. Red-team suite green in CI, Cursor plus Claude wired over STDIO and HTTP.

Start read-only, prove zero leaks, then expand tables one at a time.

By Deepak Bagada, Founder and Editor-in-Chief at Daily AI World. I build agentic systems at SaaSNext and write from production logs, not demos. Follow @deeepakbagada and read more at https://deepakbagada.in.