MCP list_tables tool fails with 'there is no parameter \$1' error in self-hosted setup

[ysfbsf]

Bug report

• I confirm this is a bug with Supabase, not with my own application.
• I confirm I have searched the Docs, GitHub Discussions, and Discord.

Describe the bug

After fixing the authentication issue for supabase_read_only_user (as described in #39961), the MCP list_tables tool still fails with a PostgreSQL parameter binding error: "there is no parameter $1" (error code 42P02).

This is a different bug from #39961. The authentication now works (password is set correctly), but the tool fails when postgres-meta tries to execute parameterized queries.

To Reproduce

Steps to reproduce the behavior:

1. Set up self-hosted Supabase using docker-compose
2. Fix supabase_read_only_user password issue (per docker: self-hosted mcp password auth failed for user supabase_read_only_user #39961):

   docker compose exec -T db psql -U supabase_admin -c \
   "ALTER USER supabase_read_only_user WITH PASSWORD '<postgres_password>';"

3. Create SSH tunnel to access MCP server:

   ssh -i ~/.ssh/supabase-key.pem -L localhost:8080:localhost:8000 ubuntu@<host>

4. Call the list_tables MCP tool:

   curl http://localhost:8080/mcp -X POST \
     -H "Content-Type: application/json" \
     -H "Accept: application/json, text/event-stream" \
     -H "MCP-Protocol-Version: 2025-06-18" \
     -d '{"jsonrpc":"2.0","id":2,"method":"tools/call","params":{"name":"list_tables","arguments":{}}}'

Expected behavior

The list_tables tool should return a list of all tables in the database (or specified schemas).

Actual behavior:

{
  "result": {
    "isError": true,
    "content": [{
      "type": "text",
      "text": "{\"error\":{\"name\":\"PgMetaDatabaseError\",\"message\":\"there is no parameter $1\"}}"
    }]
  },
  "jsonrpc": "2.0",
  "id": 2
}

Screenshots

Error from supabase-meta service logs:

{
  "level": "error",
  "time": "2025-11-12T21:20:56.162Z",
  "error": {
    "severity": "ERROR",
    "code": "42P02",
    "position": "6455",
    "message": "there is no parameter $1",
    "formattedError": "ERROR:  42P02: there is no parameter $1\nLINE 225: where schema in ($1)\n                           ^\n"
  },
  "request": {
    "method": "POST",
    "url": "/query",
    "pg": "db"
  }
}

System information

• OS: Ubuntu 22.04 (AWS EC2 t3.medium)
• Deployment: Self-hosted Supabase (Docker)
• postgres-meta version: v0.93.1 (latest)
• Supabase Studio: latest
• PostgreSQL: 15.x (via supabase/postgres Docker image)
• Docker Compose: Latest from supabase/docker repository

Additional context

What Works

1. Direct postgres-meta API calls work correctly:

   # From inside db container
   wget -O- 'http://meta:8080/tables?included_schemas=public'
   # Returns: Full JSON array with all 24 tables

2. Non-parameterized MCP tools work:

   • list_extensions - Returns all 83 extensions successfully
   • get_anon_key - Works correctly
   • get_project_url - Works correctly

3. Service role tools work:

   • execute_sql - Works perfectly with direct SQL queries

What Doesn't Work

• list_tables - Fails with parameter error
• Likely other parameterized metadata tools

Root Cause

The issue appears to be in Supabase Studio's MCP server implementation (not postgres-meta itself). When Studio's MCP layer calls postgres-meta with array parameters (like schemas array), it fails to properly bind the parameters in the SQL query.

The postgres-meta service constructs a query like:

... where schema in ($1) ...

But the parameter $1 is not being properly bound when called through the MCP interface.

MCP request flow: Kong → Studio (/api/mcp) → postgres-meta

Workaround

Use the execute_sql tool instead of list_tables:

{
  "jsonrpc": "2.0",
  "id": 1,
  "method": "tools/call",
  "params": {
    "name": "execute_sql",
    "arguments": {
      "query": "SELECT tablename FROM pg_tables WHERE schemaname = 'public'"
    }
  }
}

This works correctly and returns all tables.

Related Issues

• docker: self-hosted mcp password auth failed for user supabase_read_only_user #39961 - Authentication issue for supabase_read_only_user (prerequisite bug)

Investigation Notes

• This affects self-hosted deployments using the MCP server functionality
• postgres-meta v0.93.0 added "optional parameter binding on query endpoint" which may be related
• The direct postgres-meta REST API works perfectly (/tables?included_schemas=public), suggesting this is an integration issue in Studio's MCP layer
• The issue is likely in how Supabase Studio's MCP server constructs requests to postgres-meta, specifically how array parameters are serialized/bound when calling the postgres-meta API

Relevant code locations to investigate:

• apps/studio/ - MCP server implementation
• How Studio formats requests to postgres-meta when handling MCP tool calls
• Parameter binding for array arguments in metadata queries

[Z-Gort]

First time contributor, but going to look into this.

[aantti]

@mattrossman

[aantti]

Just re-iterating here again - using supabase_read_only_user is not recommended. This is a legacy role that has links to some other functionality in Postgres. The read-only user should be a new, separate role. There's been also changes to how Studio builds and uses the connection string (to pass to meta) - so it's easier now to separate r-w and r-o roles.

See:
2322430 (via PR #40093)

[Z-Gort]

This issue seems to occur when read_only is false as well, so I made a PR.