<div align="center">

# 🗄️ Universal Database MCP

### A safe, role-restricted MCP server for querying your databases from Claude

Query PostgreSQL, MySQL, MariaDB, SQL Server, or SQLite — read-only,
role-restricted, and with sensitive data blacked out — directly from a
Claude conversation.

![Read-only](https://img.shields.io/badge/access-read--only-2ea44f?style=flat-square)
![RBAC](https://img.shields.io/badge/security-RBAC%20%2B%20masking-blueviolet?style=flat-square)
![Transport](https://img.shields.io/badge/transport-Streamable%20HTTP-orange?style=flat-square)
![Deploy](https://img.shields.io/badge/deploy-Render%20free%20tier-46E3B7?style=flat-square)
![Auth](https://img.shields.io/badge/auth-JWT%20%7C%20OAuth%202.1-red?style=flat-square)

</div>

---

## 📸 See It In Action

<table>
<tr>
<td width="50%">

**Connected in claude.ai**
<img src="assets/claude-connector.png" alt="Claude chat with the universal-database-mcp connector enabled" width="100%">

</td>
<td width="50%">

**Per-tool approval controls**
<img src="assets/tool-permissions.png" alt="Tool permissions panel showing the four MCP tools" width="100%">

</td>
</tr>
<tr>
<td width="50%">

**Claude asks before it acts**
<img src="assets/approval-prompt.png" alt="Claude approval prompt before calling Get database schema" width="100%">

</td>
<td width="50%">

**Automatic tenant scoping**
<img src="assets/query-schema-scoped.png" alt="Query result automatically filtered to a single tenant" width="100%">

</td>
</tr>
</table>

<details>
<summary><strong>More example queries</strong> (filtering, follow-ups, and data masking)</summary>
<br>

<img src="assets/query-filtered-category.png" alt="Query filtered by category" width="100%">
<br><br>
<img src="assets/query-followup-filter.png" alt="Natural language follow-up query narrowing results further" width="100%">
<br><br>
<img src="assets/query-masked-data.png" alt="Sensitive card_number column automatically redacted" width="100%">

</details>

---

## 🧩 What This Is

A small MCP server that lets an LLM query your databases safely — **4 files,
~650 lines total, no classes/decorators/async.**

| File | Role |
|---|---|
| `config.py` | Plain dictionaries: who can see what, which columns get masked. Edit this to change any rule. |
| `security.py` | 4 plain functions: check it's read-only, check the role is allowed, add tenant filtering, mask sensitive values. |
| `db_engine.py` | 3 plain functions: get schema, run query, explain query. Talks to the actual database. |
| `server.py` | The 4 tools the LLM can call, each just a try/except around the functions above, with a log line either way. |

---

## 🔐 Keeping Database Passwords Out of Chat

`db_uri` doesn't have to be a raw connection string typed into the conversation:

1. Copy `.env.example` to `.env` and fill in real connection strings there.
2. In chat, refer to a database by its short name — e.g. *"query the `prod` database"* — and pass `db_uri="prod"`. The server looks up `ANVAYA_DB_PROD` in the environment and uses that.
3. Leaving `db_uri` empty (`""`) uses `ANVAYA_DEFAULT_DB_URI` instead.

A full connection string (containing `://`) still works directly if you pass one — useful for quick local testing — but the recommended pattern is to **never type a real password into the chat at all.**

---

## 🔑 Authentication (Remote / Multi-Team Deployments)

Since other people connect to this server, `role` can no longer be trusted as something the LLM just tells the server — anyone could type `role="admin"`. Instead:

1. **Generate a secret once** and put it in `.env`:
   ```bash
   python -c "import secrets; print(secrets.token_hex(32))"
   ```
   ```
   ANVAYA_JWT_SECRET=<paste it here>
   ANVAYA_MCP_TRANSPORT=http
   ```
2. **Mint a signed token** whenever someone needs access:
   ```bash
   uv run python issue_token.py --name alice --role analyst --tenant acme
   ```
3. **Give them the token.** Their MCP client connects with it as a Bearer token. The server verifies the signature on every call and uses the role/tenant **from the token** — any `role`/`tenant_id` passed as arguments is ignored.

> If `ANVAYA_JWT_SECRET` isn't set, the server runs with **no auth at all** — fine for local `stdio` testing on your own machine, not for anything reachable by other people.

Transport: this uses **Streamable HTTP** (`transport="http"`), the current MCP standard for remote servers — SSE is deprecated as of the 2025-11-25 MCP spec revision.

---

## ☁️ Deploying for Free (Render) So claude.ai Can Reach It

claude.ai connects to remote MCP servers from **Anthropic's cloud**, not from your browser — so this needs a real public HTTPS URL. Local network / VPN-only hosting won't work for the web client (Claude Desktop's local `stdio` config is different and stays working as-is).

[Render](https://render.com) gives you that for free, with auto-deploy on every `git push`.

### 1. Push this project to a GitHub repo
```bash
git init
git add .
git commit -m "Universal Database MCP"
git remote add origin https://github.com/YOUR_USERNAME/universal-database-mcp.git
git push -u origin main
```

### 2. Create the Render service
1. Go to [render.com](https://render.com) → sign up (no credit card needed for the free tier) → **New → Web Service**
2. Connect your GitHub repo
3. Settings:
   - **Runtime:** Python 3
   - **Build Command:** `pip install -r requirements.txt`
   - **Start Command:** `python server.py`
   - **Instance Type:** Free

### 3. Add environment variables (Render dashboard → Environment)
```
ANVAYA_JWT_SECRET=<your generated secret>
ANVAYA_MCP_TRANSPORT=http
ANVAYA_DEFAULT_DB_URI=sqlite:///test.db
```
`test.db` is committed in this repo as a small demo database — Render's free tier has no persistent disk, so a database that's part of the codebase is what survives redeploys. For real data, point `ANVAYA_DEFAULT_DB_URI` at an externally hosted database instead — e.g. a free Postgres from [Neon](https://neon.tech) — rather than SQLite.

### 4. Deploy
Render builds and deploys automatically. You'll get a URL like `https://your-service.onrender.com`. **Every future `git push` to this repo redeploys automatically** — no extra steps needed.

> ⚠️ **Two honest limitations of the free tier:**
> - The service **sleeps after 15 minutes of no traffic**, and the first request after that takes 30-50 seconds to wake up — the very first tool call after idling may feel slow or briefly time out.
> - **No persistent disk** — anything written to disk at runtime disappears on the next restart or deploy.

### 5. Issue a token and add the connector in claude.ai
```bash
uv run python issue_token.py --name alice --role analyst --tenant acme
```
Then in claude.ai: **Settings → Connectors → Add custom connector**
- URL: `https://your-service.onrender.com/mcp`
- Open **Request headers** *(beta feature — if you don't see it, it may not be rolled out to your account yet)*
- Header name: `authorization`
- Header value: `Bearer <the token you issued>`

---

## 🧪 Testing With Realistic Data Across All Roles

`generate_test_data.py` builds a bigger `test.db` — 4 tables, 30 rows each, spread across 3 tenants (`acme`, `globex`, `initech`) — designed to exercise every rule in `ROLE_PERMISSIONS`:

```bash
uv run python generate_test_data.py
```

Then run the full role test battery:
```bash
uv run python test_all_roles.py
```

This checks things like: admin can reach every table but still can't write; analyst gets auto-scoped to one tenant and can't see `password_hash`; support can't see `payments` at all; `readonly_guest` can only ever see `products`.

> One thing worth knowing: **masking is unconditional** — even `admin` sees `email`/`card_number` redacted, since masking isn't role-aware in this version, only RBAC table/column access is.

If you change `test.db`, remember to `git push` so Render picks up the new version on its next deploy (it has no persistent disk, so the committed file is what it actually serves).

---

## ✅ Before Shipping to a Client

Two things worth doing every time, before a client's database is connected for real:

**1. Error messages no longer leak internals.**
Unexpected errors (a bad connection string, a driver failure, anything not deliberately raised by our own RBAC/validation code) now return only a generic message plus a short reference code — the real detail goes only to the server-side log (stderr), tagged with that same code. Our own deliberate messages (like *"Role 'analyst' is not allowed to access column 'x'"*) are unaffected and still show clearly, since those are safe by design.

**2. Check your RBAC config against the real database before go-live:**
```bash
uv run python validate_config.py <db_uri or connection name>
```
This catches table/column name mismatches between `config.py`'s `ROLE_PERMISSIONS` and whatever database you actually point it at — e.g. a table you renamed but forgot to update in the config, or a table nobody remembered to explicitly allow or deny for a given role. It exits non-zero if it finds a real mismatch, so it's safe to wire into a pre-deploy check if you want.

---

## 🔓 OAuth Mode (for claude.ai Web)

Bearer tokens (above) work great for Claude Desktop and Claude Code, but claude.ai in a browser currently only offers OAuth as a connector auth option (a "Request headers" beta exists but isn't available to every account yet). For that, this project includes a **full, self-hosted OAuth 2.1 authorization server** — a real login page, not just a token check.

> **Read this before using it in production:** building your own OAuth server is something FastMCP's own documentation says most people shouldn't do — it's included here because claude.ai web specifically requires it and no external identity provider was wanted. The security-critical cryptography (PKCE verification, redirect URI validation) is handled by the underlying MCP SDK, not custom code here — but you're still responsible for everything else (the login page, code/token bookkeeping, who's allowed to log in). See the limitations listed at the top of `oauth_provider.py`.

### 1. Turn it on
```
ANVAYA_JWT_SECRET=<your secret>
ANVAYA_AUTH_MODE=oauth
ANVAYA_MCP_TRANSPORT=http
ANVAYA_PUBLIC_BASE_URL=https://your-real-public-url.onrender.com
```

### 2. Add people who are allowed to log in
```bash
uv run python manage_oauth_users.py add --username alice --role analyst --tenant acme
uv run python manage_oauth_users.py list
uv run python manage_oauth_users.py remove --username alice
```
You'll be prompted for a password (not shown in your terminal history). This creates `oauth_users.json` locally — **never commit this file** (it's already in `.gitignore`).

### 3. Add the connector in claude.ai
**Settings → Connectors → Add custom connector** — just the URL:
```
https://your-real-public-url.onrender.com/mcp
```
claude.ai discovers everything else automatically (it registers itself as an OAuth client, then redirects you to your server's login page). When you connect, you'll see the login form built into this project — sign in with a username/password from step 2.

### What you get, and what you don't

| ✅ You get | ❌ You don't get |
|---|---|
| Real login on claude.ai web, with role/tenant decided by who logged in — not a header anyone could paste in | No refresh tokens — when the 24-hour access token expires, the person just logs in again |
| PKCE, redirect URI validation, and authorization-code replay protection all verified working | Registered OAuth clients and issued codes are in-memory — a server restart clears them (claude.ai will just re-register/re-login automatically next time it connects) |

---

## 🚀 Setup

```bash
uv sync
uv run anvaya-mcp
```

---

## 🛠️ The 4 Tools

| Tool | Signature |
|---|---|
| Get database schema | `get_database_schema(db_uri, role)` |
| Execute safe query | `execute_safe_query(db_uri, sql_query, role, tenant_id)` |
| Explain SQL query | `explain_sql_query(db_uri, sql_query)` |
| Export results | `export_results_format(db_uri, sql_query, role, format_type, tenant_id)` |

---

## 👥 Roles

Defined in `config.py` under `ROLE_PERMISSIONS`: `admin`, `analyst`, `support`, `readonly_guest`. Edit that dictionary to add roles, change which tables/columns they can see, or turn tenant filtering on/off.

---

## 🪶 What Got Simplified From the First Version

- Sync database calls instead of async (easier to read top-to-bottom)
- No decorators, dataclasses, or Enums — just dicts and functions
- Row-level tenant filtering only supports a single `tenant_id` column (not multiple candidate column names)
- Masking is column-name-based only (no scanning cell contents for patterns like card numbers)

Connections and schema **are** cached (see `db_engine.py` — `ENGINE_CACHE` and `SCHEMA_CACHE`, two plain dictionaries), so repeated calls reuse the same connection instead of opening a new one every time, and don't re-fetch the schema on every call. The schema cache refreshes itself every 5 minutes, or immediately if you pass `force_refresh=True`.

Everything from the original feature list is still here — schema discovery, relationship mapping, read-only enforcement, EXPLAIN, table/column RBAC, row-level filtering, data masking, CSV/Excel/JSON export, and audit logging — just written as plainly as possible.

<div align="center">

---

*Read-only by design. Role-restricted by default. Nothing sensitive leaves the table.*

</div>