🔐 MCP Server — secure AI access to Postgres & Google Sheets

A Model Context Protocol server that lets Claude, ChatGPT, Cursor or any MCP client answer questions about company data — sales, customers, orders, spreadsheets — without exporting files and without write access. Read-only by design (SQL guard + read-only DB role), with row limits, pagination, PII masking, bearer-token auth for remote use and a full audit log.

The problem

Managers want to ask "What were last month's top customers?" or "Which orders are late?" in plain English. Today that means CSV exports or giving an AI tool a production connection string. This server gives AI assistants a narrow, audited, read-only window into the data instead.

Related MCP server: db-connector

Tools, resources, prompts

All tools carry typed JSON input/output schemas and readOnlyHint annotations.

Security model (defense in depth)

1. SQL guard (sqlglot AST): exactly one statement; only SELECT/set operations; rejects INSERT/UPDATE/DELETE/MERGE/DDL, SELECT … INTO, FOR UPDATE, COPY, SET, PRAGMA, ATTACH, transactions, dangerous functions (pg_sleep, pg_read_file, load_extension, dblink…), system catalogs, and tables outside ALLOWED_TABLES.

2. Read-only database: Postgres role mcp_readonly with SELECT grants only (not on staff_salaries) and default_transaction_read_only; every connection also runs BEGIN READ ONLY + statement_timeout. SQLite is opened with mode=ro + PRAGMA query_only. Tests bypass the guard on purpose to prove the DB still refuses.

3. Result limits: MAX_ROWS (500), MAX_RESULT_BYTES, pagination, query timeout.

4. PII masking: configurable columns (PII_COLUMNS=email,phone) are masked in every result (m***@example.com, ***67); PII columns can't be wrapped in expressions/aliases to dodge the mask. PII_MASKING=off for trusted users.

5. Auth: Streamable HTTP requires Authorization: Bearer <token> (per-user tokens, constant-time compare); optional DNS-rebinding protection via MCP_ALLOWED_HOSTS.

6. Audit log: every tool call → JSONL with timestamp, user (token owner / local-stdio), tool, arguments, status (ok / rejected / error), row count, duration.

Architecture

flowchart LR
    C1[Claude Desktop / Claude Code] -- stdio --> S
    C2[Cursor] -- stdio --> S
    C3[ChatGPT connectors / remote agents] -- Streamable HTTP + Bearer --> AUTH[Bearer auth] --> S
    subgraph S[MCP server · Python MCP SDK]
      T[Tools · Resources · Prompts] --> G[SQL guard<br/>AST allow-list]
      G --> LIM[Row/byte caps<br/>pagination · timeout]
      LIM --> PII[PII masking]
      T --> AUD[(Audit log JSONL)]
    end
    LIM -->|read-only role<br/>BEGIN READ ONLY| PG[(PostgreSQL / SQLite<br/>shop data)]
    T -->|spreadsheets.readonly| GS[(Google Sheets<br/>SalesTargets)]

Screenshots

Produced by scripts/demo_screenshots.py. Agent screenshots 05–07 use a real LLM (Kimi K3 on Amazon Bedrock) via scripts/ask_agent.py; 11 uses the offline scripted plan (the provider was rate-limiting at capture time) — the MCP calls, auth and audit entries are identical either way.

Quick start (local, SQLite, 2 minutes)

python -m venv .venv && . .venv/bin/activate && pip install -r requirements.txt
python scripts/seed.py                         # data/shop.db with ~8k rows of fake data + data/sheets/SalesTargets.csv
npx @modelcontextprotocol/inspector --web $PWD/scripts/run_stdio.sh     # poke at it in MCP Inspector
python scripts/ask_agent.py "Top 5 customers by revenue last month and who hasn't ordered in 60 days?"

ask_agent.py is a tiny tool-calling agent (any OpenAI-compatible LLM via LLM_BASE_URL/LLM_API_KEY/LLM_MODEL; scripted offline plan if unset) that prints each MCP tool call — useful to demo the server without a desktop client.

Docker (Postgres + server)

docker compose up        # Postgres 17 seeded + read-only role, MCP over HTTP on http://localhost:8000/mcp

Remote (Streamable HTTP)

MCP_API_TOKENS="alice@acme:$(openssl rand -hex 24)" python -m bizdata_mcp.server --transport http --host 0.0.0.0 --port 8000

Put it behind HTTPS (Caddy/nginx/Cloudflare Tunnel) and set MCP_ALLOWED_HOSTS.

Client configuration

Claude Desktop (claude_desktop_config.json) — see docs/client-configs/claude_desktop_config.json:

{ "mcpServers": { "business-data": {
    "command": "/ABSOLUTE/PATH/mcp-business-data-server/scripts/run_stdio.sh",
    "env": { "DATABASE_URL": "postgresql://mcp_readonly:CHANGE_ME@localhost:5432/shop" } } } }

Claude Code

claude mcp add business-data -e DATABASE_URL=sqlite:///$PWD/data/shop.db -- $PWD/scripts/run_stdio.sh
claude mcp add --transport http business-data https://mcp.example.com/mcp --header "Authorization: Bearer $MCP_TOKEN"

Cursor (.cursor/mcp.json) — see docs/client-configs/cursor_mcp.json. ChatGPT — add the HTTPS /mcp URL as a custom connector (developer mode) with the bearer token.

Google Sheets

Create a service account, download its JSON key, share the sheet with the service-account email as Viewer, then set GOOGLE_APPLICATION_CREDENTIALS and SHEETS_SPREADSHEET_ID. Scope is spreadsheets.readonly. Without them the tool reads data/sheets/<name>.csv (same shape) so the demo runs offline.

Acceptance criteria → evidence

Tests

pytest -v                                                             # 49 tests (SQLite)
TEST_PG_ADMIN_URL=postgresql://postgres@localhost:5432/shop pytest -v   # + Postgres read-only-role test
python scripts/demo_screenshots.py                                    # scripted demo → docs/screenshots/*.png

Portfolio write-up & demo video script

See PORTFOLIO.md.

License

MIT — all data is fake.