225 lines
10 KiB
Markdown
225 lines
10 KiB
Markdown
# Supabase MCP Server Setup
|
||
|
||
This workspace includes Model Context Protocol configurations that let GitHub Copilot (or any other MCP-compatible client) connect straight to the Supabase stack that you are running on the local network (192.168.0.150):
|
||
|
||
- `supabase-local` → direct Postgres connection (full SQL access).
|
||
- `supabase-rest` → Supabase REST/PostgREST API using the `service_role` key (ideal when you only have the Supabase keys and not the raw Postgres password).
|
||
|
||
## Prerequisites
|
||
|
||
1. **Supabase Docker stack is running** on the target host (192.168.0.150). The direct Postgres service is exposed on LAN port `6543` (it maps to container port `5432`).
|
||
2. You know the **Postgres connection password** (usually configured through the `POSTGRES_PASSWORD` environment variable in the Docker compose file). The default database created by Supabase is `postgres`.
|
||
3. Your development machine already has **Node.js ≥ 18** and can run `npx`.
|
||
|
||
## Connection string
|
||
|
||
Build a connection string in the format:
|
||
|
||
```
|
||
postgresql://postgres:<POSTGRES_PASSWORD>@192.168.0.150:6543/postgres
|
||
```
|
||
|
||
For example:
|
||
|
||
```
|
||
postgresql://postgres.chat-local:yourStrongPassword@192.168.0.150:5432/postgres
|
||
```
|
||
|
||
> ⚠️ Never commit a real password to version control. Store the connection string in an environment variable instead.
|
||
|
||
## Environment variable
|
||
|
||
Create (or add to) an `.env` file at the workspace root with:
|
||
|
||
```
|
||
SUPABASE_TENANT=chat-local
|
||
SUPABASE_DB_URL=postgresql://postgres.chat-local:<POSTGRES_PASSWORD>@192.168.0.150:5432/postgres
|
||
```
|
||
|
||
Then ensure the variable is loaded by your shell (e.g. `Get-Content .env | foreach { if($_ -match '^(.*?)=(.*)$'){ [System.Environment]::SetEnvironmentVariable($matches[1], $matches[2]) } }` in PowerShell, or use a dedicated dotenv loader).
|
||
|
||
Alternatively, set it directly in the shell session before launching VS Code:
|
||
|
||
```
|
||
$env:SUPABASE_DB_URL = "postgresql://postgres.chat-local:<POSTGRES_PASSWORD>@192.168.0.150:5432/postgres"
|
||
```
|
||
|
||
## MCP configuration
|
||
|
||
The workspace now contains `.github/copilot/mcp.json`:
|
||
|
||
```json
|
||
{
|
||
"version": 1,
|
||
"mcpServers": {
|
||
"supabase-local": {
|
||
"command": "npx",
|
||
"args": [
|
||
"--yes",
|
||
"-p",
|
||
"@modelcontextprotocol/server-postgres",
|
||
"mcp-server-postgres",
|
||
"${SUPABASE_DB_URL}"
|
||
],
|
||
"env": {
|
||
"SUPABASE_DB_URL": "${env:SUPABASE_DB_URL}",
|
||
"npm_config_audit": "false"
|
||
},
|
||
"metadata": {
|
||
"description": "Postgres access to the local Supabase instance running on 192.168.0.150",
|
||
"docs": "See .github/copilot/supabase-mcp.md for setup instructions."
|
||
}
|
||
},
|
||
"supabase-rest": {
|
||
"command": "npx",
|
||
"args": [
|
||
"--yes",
|
||
"@modelcontextprotocol/server-openapi",
|
||
"--spec",
|
||
"${SUPABASE_OPENAPI_SPEC}",
|
||
"--server-url",
|
||
"${SUPABASE_REST_URL}",
|
||
"--header",
|
||
"apikey: ${SUPABASE_SERVICE_ROLE_KEY}",
|
||
"--header",
|
||
"Authorization: Bearer ${SUPABASE_SERVICE_ROLE_KEY}"
|
||
],
|
||
"env": {
|
||
"SUPABASE_OPENAPI_SPEC": "${env:SUPABASE_OPENAPI_SPEC}",
|
||
"SUPABASE_REST_URL": "${env:SUPABASE_REST_URL}",
|
||
"SUPABASE_SERVICE_ROLE_KEY": "${env:SUPABASE_SERVICE_ROLE_KEY}"
|
||
},
|
||
"metadata": {
|
||
"description": "Supabase REST API via service_role key for internal development",
|
||
"docs": "See .github/copilot/supabase-mcp.md for instructions on generating the OpenAPI spec and required environment variables."
|
||
}
|
||
}
|
||
}
|
||
}
|
||
```
|
||
|
||
When Copilot loads this configuration it will launch:
|
||
|
||
- The Postgres MCP server via `npx @modelcontextprotocol/server-postgres`, injecting your connection string through the `SUPABASE_DB_URL` environment variable. The server exposes SQL querying and management tools over MCP, enabling Copilot to run SQL commands directly against your Supabase database.
|
||
- The Supabase REST MCP server via `npx @modelcontextprotocol/server-openapi`. It uses the generated OpenAPI schema together with the `service_role` key to hit the PostgREST endpoints (tables, RPCs) exactly like `supabase-js` would.
|
||
|
||
You can enable one or both servers by setting the corresponding environment variables.
|
||
|
||
## Supabase REST (service_role) setup
|
||
|
||
1. **Generate OpenAPI schema** (once per Supabase instance; repeat if schema changes).
|
||
|
||
- Quick one-liner:
|
||
|
||
```powershell
|
||
$env:SUPABASE_SERVICE_ROLE_KEY = "<your-service-role-key>"
|
||
Invoke-WebRequest `
|
||
-Uri "http://192.168.0.150:8000/rest/v1/?apikey=$($env:SUPABASE_SERVICE_ROLE_KEY)" `
|
||
-OutFile ".github/copilot/supabase-rest-openapi.json"
|
||
```
|
||
|
||
- Or run the helper script (prompts above default):
|
||
|
||
```powershell
|
||
pwsh scripts/fetch-supabase-openapi.ps1 -ServiceRoleKey "<your-service-role-key>"
|
||
```
|
||
|
||
This downloads the PostgREST schema that reflects all tables, views, and RPC functions currently exposed by Supabase.
|
||
|
||
2. **Add/extend `.env`** with:
|
||
|
||
```
|
||
SUPABASE_REST_URL=http://192.168.0.150:8000/rest/v1
|
||
SUPABASE_SERVICE_ROLE_KEY=<your-service-role-key>
|
||
SUPABASE_OPENAPI_SPEC=${workspaceFolder}/.github/copilot/supabase-rest-openapi.json
|
||
```
|
||
|
||
3. **Load variables in your shell** before launching VS Code / Copilot:
|
||
|
||
```powershell
|
||
$env:SUPABASE_REST_URL = "http://192.168.0.150:8000/rest/v1"
|
||
$env:SUPABASE_SERVICE_ROLE_KEY = "<your-service-role-key>"
|
||
$env:SUPABASE_OPENAPI_SPEC = "$PWD/.github/copilot/supabase-rest-openapi.json"
|
||
```
|
||
|
||
4. **Verify**: In the Copilot MCP panel, you should see both `supabase-local` and `supabase-rest`. Either can be disabled by omitting the required environment variables.
|
||
|
||
## Usage
|
||
|
||
1. Start VS Code (or the Copilot agent) *after* the environment variable is set.
|
||
2. The MCP server will be launched automatically the first time Copilot needs database access. You can verify in Copilot’s “MCP Servers” panel that `supabase-local` is running.
|
||
3. Ask Copilot to run SQL, inspect tables, or perform migrations; it will route the requests through the MCP server to Supabase/Postgres.
|
||
4. For REST workflows (e.g., hitting tables as REST endpoints, calling RPCs with structured payloads), Copilot can use `supabase-rest` to craft HTTP requests automatically based on the OpenAPI schema.
|
||
|
||
## One-click install for all VS Code workspaces
|
||
|
||
If you want Supabase MCP servers to be available in every VS Code workspace (without copying `.github/copilot/mcp.json` around), run the helper script once from this repository. It will:
|
||
|
||
- Persist the required `SUPABASE_*` environment variables for your Windows user profile using `setx`.
|
||
- Merge the Supabase MCP servers into VS Code’s global Copilot config at `%APPDATA%\Code\User\globalStorage\GitHub.copilot\mcp.json`.
|
||
|
||
```powershell
|
||
pwsh scripts\install-supabase-mcp.ps1 -ConnectionString "postgresql://postgres.chat-local:<POSTGRES_PASSWORD>@192.168.0.150:6543/postgres"
|
||
```
|
||
|
||
Add `-IncludeRestServer -ServiceRoleKey "<service-role-key>" -OpenApiSpec "C:\\path\\to\\supabase-rest-openapi.json"` if you also want the REST server registered globally. The script prompts for any missing arguments.
|
||
|
||
After the script finishes:
|
||
|
||
1. Restart VS Code (or run **Developer: Reload Window**) so Copilot reloads the global MCP configuration.
|
||
2. In the MCP view you should now see `supabase-local` (and optionally `supabase-rest`) even in projects that do not ship their own `mcp.json`.
|
||
3. Remember that VS Code inherits environment variables only on launch. If you change the connection string later, rerun the script and restart VS Code.
|
||
|
||
### Manual launch (optional)
|
||
|
||
To launch the server manually for debugging:
|
||
|
||
```powershell
|
||
$env:SUPABASE_DB_URL = "postgresql://postgres.chat-local:yourStrongPassword@192.168.0.150:5432/postgres"
|
||
npx --yes --no-audit -p @modelcontextprotocol/server-postgres mcp-server-postgres $env:SUPABASE_DB_URL
|
||
```
|
||
|
||
The package publishes the executable under the name `mcp-server-postgres`, so `npx` needs the `-p` flag to install the scoped package and then run that binary. The connection string must be the first positional argument.
|
||
|
||
> Tip: if your npm version doesn’t support the `--no-audit` switch, set the environment variable `npm_config_audit=false` (already included in `mcp.json`). This avoids `npm exec` aborting when a mirror registry doesn’t implement the audit API.
|
||
|
||
> Note: launching the server manually in a console will exit as soon as a line like `select 1;` hits stdin. That’s expected because the MCP transport treats console keystrokes as malformed JSON. When Copilot runs the same command there’s no human typing, so the process stays up.
|
||
|
||
## Resetting the MCP setup
|
||
|
||
If you need to wipe the previous configuration and start from scratch:
|
||
|
||
1. **Remove cached servers in VS Code** – open the Copilot MCP panel, stop `supabase-local`/`supabase-rest`, then click “Forget server”.
|
||
2. **Reload the environment variables** in the shell you will launch VS Code from:
|
||
```powershell
|
||
.\scripts\load-supabase-env.ps1
|
||
```
|
||
3. **Verify the tenant DSN** contains `.chat-local`:
|
||
```powershell
|
||
Get-ChildItem Env:SUPABASE_DB_URL
|
||
```
|
||
4. **Reopen VS Code** (or run `Developer: Reload Window`) so Copilot ingests the fresh `.github/copilot/mcp.json`.
|
||
5. **Run a smoke test**:
|
||
```powershell
|
||
python scripts\test_supabase_connection.py
|
||
```
|
||
Once this prints `Query result: 1`, trigger `SELECT 1;` from Copilot—the MCP server should respond with `[{"?column?":1}]`.
|
||
|
||
The server will listen for MCP connections on stdin/stdout; you’ll see logs confirming connection success.
|
||
|
||
## Security notes
|
||
|
||
- The configuration uses a **service-level Postgres connection** (superuser). Treat the password as a secret; rotate it regularly.
|
||
- Consider creating a dedicated Postgres role with least privilege for MCP usage if you don’t need full superuser access.
|
||
- Restrict network access so only trusted machines can reach `192.168.0.150:5432`.
|
||
- On shared repositories, provide a template `.env.example` rather than real credentials.
|
||
|
||
## Troubleshooting
|
||
|
||
- **Connection refused**: ensure Docker stack exposes port 5432 to your LAN and that firewalls allow the traffic.
|
||
- **Authentication failed**: double-check the password in `.env` matches the one set in your Supabase Docker environment.
|
||
- **TLS/SSL errors**: the default local Supabase Postgres runs without TLS. If you enable TLS, update the connection string with `?sslmode=require` and provide the certificate parameters if needed.
|
||
- **Package not found**: run `npm install --save-dev @modelcontextprotocol/server-postgres` locally to cache the package, or allow `npx` to download it on first use.
|
||
|
||
With this configuration in place, Copilot can manage your Supabase database over MCP without additional manual wiring.
|