# SQMCPaL

An MCP server that lets any MCP-compatible AI tool query your **Azure Database for PostgreSQL Flexible Server** — no service principals, no connection strings in config files, no passwords anywhere. Just `az login` and go.

Works with **Claude Code**, **GitHub Copilot CLI**, **Claude Desktop**, **GitHub Copilot in VS Code**, and any other tool that speaks the Model Context Protocol.

**You will never copy-paste a password from the Azure Portal.** SQMCPaL authenticates with the Azure CLI session already on your machine, exchanging it for a short-lived Microsoft Entra token that is used as the database password. Nothing is stored, nothing to rotate, nothing to leak.

**You don't need to know your schema.** Ask your AI tool *"what's in this database?"* and it will walk the catalog — tables, columns, types, keys, indexes — before writing a single query.

**It cannot write to your database.** Read-only isn't a promise in the docs, it's enforced by PostgreSQL itself. See [How read-only is enforced](#how-read-only-is-enforced).

## Background

SQMCPaL is the SQL sibling of two existing MCP servers built on the same idea — your `az login` session is already the credential, so a local read-only MCP server needs no infrastructure at all:

| | [QueryMCPal](https://github.com/ChingEnLin/QueryMCPal) | [BlobMCPal](https://github.com/ChingEnLin/BlobMCPal) | SQMCPaL |
|---|---|---|---|
| **Target** | Cosmos DB (MongoDB API) | Azure Storage (Blob/Queue/File/Table) | PostgreSQL Flexible Server |
| **Auth** | `az login` → connection string via ARM | `az login` → data-plane RBAC | `az login` → Entra token as DB password |
| **Operations** | Read-only | Read-only | Read-only |
| **Runs as** | Local stdio server | Local stdio server | Local stdio server |

Same shape, same guarantees, different data store. If you already run one of the others, this one will feel identical.

## Features

- **Auto-discovery** — lists every PostgreSQL flexible server your Azure credential can see, across all subscriptions
- **Session context** — connect once, then query without repeating server/database/schema on every message
- **Catalog inspection** — databases, schemas, tables, sizes, row estimates, columns, primary keys, indexes, foreign keys
- **Arbitrary read SQL** — full `SELECT` power including joins, CTEs, window functions and `EXPLAIN`
- **Convenience tools** — `count_rows`, `distinct_values`, `sample_rows` for the questions you ask constantly
- **Passwordless** — Entra token minted per session from your own CLI login, auto-refreshed before expiry
- **Read-only, enforced by the server** — not by a regex

## Prerequisites

- [Azure CLI](https://learn.microsoft.com/en-us/cli/azure/install-azure-cli) (`brew install azure-cli`), logged in with `az login`
- Python 3.11+ (or [`uv`](https://docs.astral.sh/uv/), which is easier — see Setup)
- An Azure Database for PostgreSQL **Flexible Server** you can reach on the network
- Your Entra identity must be able to log into that server (see below)

### Granting your Entra identity access

PostgreSQL will reject your login unless your Entra principal exists as a role on the server. Either:

1. Set yourself as the **Microsoft Entra admin** on the server (Portal → your server → *Authentication* → *Microsoft Entra admin*), or
2. Have an existing Entra admin create a role for you:

   ```sql
   SELECT * FROM pgaadauth_create_principal('you@example.com', false, false);
   GRANT CONNECT ON DATABASE yourdb TO "you@example.com";
   GRANT USAGE ON SCHEMA public TO "you@example.com";
   GRANT SELECT ON ALL TABLES IN SCHEMA public TO "you@example.com";
   ```

Entra authentication must be enabled on the server (*Authentication* → "Microsoft Entra authentication only" or "PostgreSQL and Microsoft Entra authentication").

Run `check_auth` from your AI tool at any time to see which role SQMCPaL will try to log in as.

### Network access

Flexible servers are firewalled by default. Either add your IP under *Networking* → *Firewall rules*, or be on the server's VNet if it uses private access. If a connection hangs and then fails, this is almost always why.

## Setup

### Claude Code

```bash
claude mcp add sqmcpal -- uvx --from git+https://github.com/ChingEnLin/SQMCPaL sqmcpal
```

### GitHub Copilot CLI

Add to `~/.copilot/mcp-config.json`:

```json
{
  "mcpServers": {
    "sqmcpal": {
      "type": "local",
      "command": "uvx",
      "args": ["--from", "git+https://github.com/ChingEnLin/SQMCPaL", "sqmcpal"],
      "tools": ["*"]
    }
  }
}
```

Then start `copilot` and run `/mcp` to confirm it loaded.

### Claude Desktop

Add to `~/Library/Application Support/Claude/claude_desktop_config.json`:

```json
{
  "mcpServers": {
    "sqmcpal": {
      "command": "uvx",
      "args": ["--from", "git+https://github.com/ChingEnLin/SQMCPaL", "sqmcpal"]
    }
  }
}
```

Restart Claude Desktop afterwards.

### From a local clone

```bash
git clone https://github.com/ChingEnLin/SQMCPaL.git
cd SQMCPaL
uv venv --python 3.12 && uv pip install -e .
```

then point `command` at `/absolute/path/to/SQMCPaL/.venv/bin/sqmcpal` with no args.

## Usage

Talk to it in plain language. A typical first session:

> **You:** What PostgreSQL servers do I have in Azure?
> **AI:** *(list_postgres_servers)* One — `database-patient-server` in germanywestcentral, PostgreSQL 16.
>
> **You:** Connect to it and show me what's in there.
> **AI:** *(connect_server → list_databases → list_tables)* Database `patients` has 8 tables in `public`; the biggest is `appointments` at ~1.2M rows.
>
> **You:** What does the appointments table look like, and how many are cancelled?
> **AI:** *(describe_table → count_rows)* 14 columns, PK on `id`, FK to `patients.id`. 43,207 rows have `status = 'cancelled'`.
>
> **You:** Show me cancellations per month for the last year.
> **AI:** *(run_query with a date_trunc + GROUP BY)* …

You rarely name a tool yourself — set the context once and ask questions.

## How authentication works

1. SQMCPaL asks `DefaultAzureCredential` for a token scoped to `https://management.azure.com/.default` and uses it to enumerate flexible servers via ARM.
2. For the database connection it requests a second token scoped to `https://ossrdbms-aad.database.windows.net/.default`.
3. That token **is** the PostgreSQL password. The login role is read from the token's own `upn` claim, so it always matches the identity you logged in as.
4. Tokens are refreshed automatically five minutes before expiry; pooled connections are re-established with the fresh token.

No credential is ever written to disk or into config. On a laptop this resolves to your `az login` session; in a container or CI it resolves to whatever managed identity or service principal is present.

## How read-only is enforced

Three independent layers, because one is not enough:

1. **Every statement runs inside an explicit `BEGIN READ ONLY` transaction that is always rolled back.** PostgreSQL rejects `INSERT`/`UPDATE`/`DELETE`/`CREATE`/`DROP`/`ALTER`/`GRANT` itself — this is the real guarantee, not client-side filtering.
2. **The session default is read-only too** (`default_transaction_read_only = on`), covering anything outside an explicit transaction.
3. **Submitted SQL must be a single statement** starting with `SELECT`, `WITH`, `TABLE`, `VALUES`, `EXPLAIN` or `SHOW`. This closes the one hole in layer 1: `COMMIT; BEGIN READ WRITE; DELETE FROM …` would otherwise escape the read-only transaction. The parser understands string literals, dollar-quoting and comments, so a semicolon inside `'a;b'` is not mistaken for a statement separator.

There are no write tools in the registry, so there is nothing for a model to call even if it wanted to. Queries also carry a 30s `statement_timeout` so a runaway scan can't sit on your production server.

For belt-and-braces, grant the Entra role `SELECT` only — then the database itself is the fourth layer.

## Available tools

| Tool | What it does |
|---|---|
| `check_auth` | Verify the Azure credential and show the PostgreSQL login role it maps to |
| `list_postgres_servers` | Every flexible server visible to your credential, across subscriptions |
| `connect_server` | Connect by ARM resource ID; optionally set default database and schema |
| `list_databases` | Databases on the connected server, with owner, encoding and size |
| `list_schemas` | Schemas in a database, with owner and table count |
| `list_tables` | Tables, views and materialized views, with estimated rows and size |
| `describe_table` | Columns, types, nullability, defaults, primary key, indexes, foreign keys |
| `sample_rows` | A few rows from a table, to see what the data actually looks like |
| `run_query` | Any single read-only SQL statement |
| `count_rows` | Exact row count, optionally filtered by a `WHERE` clause |
| `distinct_values` | Distinct values of a column with frequencies, most common first |
| `show_context` / `set_context` / `clear_context` | Inspect, change or reset the session |

### Environment variables

| Variable | Default | Purpose |
|---|---|---|
| `SQMCPAL_MAX_LIMIT` | `1000` | Hard cap on rows returned by any tool |
| `SQMCPAL_STATEMENT_TIMEOUT_MS` | `30000` | Server-side query timeout |
| `SQMCPAL_CONNECT_TIMEOUT` | `15` | Connection timeout in seconds |
| `SQMCPAL_SSLMODE` | `require` | libpq sslmode |
| `SQMCPAL_PG_PORT` | `5432` | Server port |
| `SQMCPAL_PG_USER` | *(token `upn`)* | Override the login role, if it differs from your UPN |
| `AZURE_ACCESS_TOKEN` | — | Pre-fetched ARM token, for containers without the az CLI |

## For developers

```bash
uv venv --python 3.12 && source .venv/bin/activate
uv pip install -e ".[dev]"
pre-commit install
```

Checks (there is no integration suite — the DB layer is verified by connecting to a real server):

```bash
python test_sqmcpal.py      # read-only SQL guard self-check
pre-commit run --all-files  # ruff + ruff-format + mypy --strict
```

### Smoke test the MCP handshake

```bash
printf '%s\n' '{"jsonrpc":"2.0","id":1,"method":"initialize","params":{"protocolVersion":"2024-11-05","capabilities":{},"clientInfo":{"name":"smoke","version":"0"}}}' \
  | .venv/bin/sqmcpal
```

Expect `"serverInfo":{"name":"sqmcpal",...}` on stdout.

### Docker

```bash
docker build --platform linux/arm64 -t sqmcpal:dev .   # linux/amd64 on Intel/Linux
```

Mount `~/.azure` read-only into the container so the CLI session is available, or pass `AZURE_ACCESS_TOKEN`.

## Troubleshooting

**`password authentication failed for user "you@example.com"`**
Your Entra principal isn't a role on that server. See [Granting your Entra identity access](#granting-your-entra-identity-access).

**Connection hangs, then "could not reach host"**
Firewall. Add your IP under the server's *Networking* blade, or connect from its VNet.

**`Azure credential not found or expired`**
Run `az login` in a terminal, then restart your MCP client so it picks up the refreshed session.

**`Only read-only statements are permitted`**
Working as intended. If your query legitimately starts with something else, wrap it — e.g. `WITH x AS (…) SELECT …`.

**`Multiple SQL statements are not allowed`**
Send one statement at a time. This is the guard that keeps a read-only transaction from being committed out from under itself.

**Permission denied for a table you can see**
`list_tables` reads the catalog, which is world-readable; reading rows needs `GRANT SELECT`. Ask an admin for the grant.

## Limitations

- **Flexible Server only.** Single Server is retired and not supported; Cosmos DB for PostgreSQL is untested.
- **Microsoft Entra authentication only.** Native PostgreSQL username/password logins are deliberately not supported — that would mean a secret in a config file.
- **One connection at a time.** A single in-process session, matching one developer at one keyboard.
- **Read-only, permanently.** Write tools will not be added. Point it at production on purpose.

## License

MIT — see [LICENSE](LICENSE).