akmon/.github/copilot/supabase-mcp.md

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:

{
  "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:

     $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):

     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= 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.

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:

$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:

.\scripts\load-supabase-env.ps1

3. Verify the tenant DSN contains .chat-local:

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:

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.