Metadata-Version: 2.4
Name: ae-mcp
Version: 0.2.0
Summary: Container-ready MCP server for Snowflake with browser SSO / key-pair auth
Project-URL: Homepage, https://github.com/roshanabady/ai-analytics
Project-URL: Repository, https://github.com/roshanabady/ai-analytics
License-Expression: Apache-2.0
License-File: LICENSE
Requires-Python: >=3.11
Requires-Dist: cryptography==49.0.0
Requires-Dist: mcp==1.28.1
Requires-Dist: snowflake-connector-python[secure-local-storage]==4.7.1
Description-Content-Type: text/markdown

# ae-mcp

A small, fast MCP server for Snowflake. Four tools, one file, browser SSO auth.

## Auth

Authenticates via `externalbrowser` SSO. The connector caches the SSO ID token
locally (in the OS keyring, via the `secure-local-storage` extra), so the browser
only opens when the cached token expires — not on every server restart. Caching
requires this once on the account (ask an admin if it isn't already set):

```sql
ALTER ACCOUNT SET ALLOW_ID_TOKEN = TRUE;
```

While the server process is running, `CLIENT_SESSION_KEEP_ALIVE` holds the session
open indefinitely — you won't be re-prompted mid-session.

## Install

Windows and macOS (Python 3.11+):

```bash
python -m pip install --user ae-mcp
```

Or:

```bash
uv tool install ae-mcp
```

## Configure (Cursor / Claude Desktop / Claude Code)

```json
{
  "mcpServers": {
    "ae-mcp": {
      "command": "ae-mcp",
      "env": {
        "SNOWFLAKE_ACCOUNT": "myorg-myaccount",
        "SNOWFLAKE_USER": "me@example.com",
        "SNOWFLAKE_ROLE": "ANALYST_ROLE",
        "SNOWFLAKE_WAREHOUSE": "COMPUTE_WH",
        "SNOWFLAKE_MCP_ALLOW_WRITE": "false"
      }
    }
  }
}
```

`SNOWFLAKE_MCP_ALLOW_WRITE` is shown explicitly above as a reminder — it's
`false` (read-only) by default even if omitted entirely.

Optional env: `SNOWFLAKE_DATABASE`, `SNOWFLAKE_SCHEMA`.

## Tools

| Tool | Purpose |
|---|---|
| `snowflake_query` | Run SQL; results capped (default 50 rows, max 1000), wide cells truncated |
| `snowflake_list_objects` | `SHOW TERSE` databases/schemas/tables/views/warehouses/roles with LIKE filter |
| `snowflake_describe_table` | Column definitions (metadata-only, no warehouse compute) |
| `snowflake_session_info` | Current user/role/warehouse/db + connection age |

## Performance notes

- One lazy connection, kept alive with `CLIENT_SESSION_KEEP_ALIVE`, reused across calls; auto-reconnects once on session expiry.
- Blocking connector calls run in a worker thread — the MCP event loop never stalls.
- `SHOW TERSE` / `DESCRIBE` are metadata operations: instant results, no warehouse credits.
- `STATEMENT_TIMEOUT_IN_SECONDS=300` guards against runaway queries.

## Safety

**Secure by default.** `snowflake_query` is read-only out of the box — no
env var needs to be set for that. Writes (`INSERT`/`UPDATE`/`DELETE`/`MERGE`/
`CREATE`/`DROP`/`ALTER`/`TRUNCATE`/`GRANT`/`REVOKE`/`COPY`/`CALL`/etc.) are
blocked unless explicitly enabled.

| Env var | Default | Effect |
|---|---|---|
| `SNOWFLAKE_MCP_ALLOW_WRITE` | `false` | Set `true` to allow non-read-only statements |
| `SNOWFLAKE_MCP_READ_ONLY` | `false` | Set `true` for a hard override: always read-only, ignoring `ALLOW_WRITE` entirely |

The read-only guard, when active:
- Allows only statements starting with `SELECT`/`SHOW`/`DESCRIBE`/`DESC`/
  `EXPLAIN`/`WITH`/`LIST`/`USE`.
- Also scans the **whole** statement (not just the first keyword) for
  write-capable keywords, so a CTE prefix can't smuggle a write past the
  first-token check (`WITH x AS (...) INSERT INTO ...` is blocked, not just
  a bare `INSERT`).
- Rejects stacked statements — anything after a `;` other than trailing
  whitespace (`SELECT 1; DROP TABLE x;`) is blocked outright.
- Ignores keywords found inside string/identifier literals and comments, so
  a column named e.g. `delete_flag` or a literal containing the word
  "insert" doesn't trigger a false block.

Call `snowflake_session_info` any time to see the live gating state (which
of the two env vars is active) alongside connection details. A blocked
`snowflake_query` call returns an error explaining which env var to set —
no SQL is ever sent to Snowflake for a blocked statement.

This is a keyword/pattern guard, not a full SQL parser — it's meant to stop
accidental or casual writes from an LLM-driven client, not to withstand a
determined adversary with control over the input. If you need real
guarantees, use a read-only Snowflake role for this connection rather than
relying on the guard alone.
