Postgres MCP setup: Safe SQL for AI agents

Postgres MCP turns your database into a tool your AI agent can query. But naive setup — passing a superuser connection string — is how you end up with “the model dropped the users table” war stories. Here is how to do it safely.

What Postgres MCP gives you

• Schema introspection — agent sees tables, columns, types.
• Parameterised query execution.
• Read-only by default.
• Works with any Postgres >= 12 (local, RDS, Supabase, Neon, etc.).

Step 1: Create a read-only role

Never give the MCP server your app’s connection string. Create a dedicated role:

CREATE ROLE ai_readonly WITH LOGIN PASSWORD 'change_me';
GRANT CONNECT ON DATABASE mydb TO ai_readonly;
GRANT USAGE ON SCHEMA public TO ai_readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO ai_readonly;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO ai_readonly;

Test it:

psql -U ai_readonly -d mydb -c "SELECT COUNT(*) FROM users;"   -- should work
psql -U ai_readonly -d mydb -c "DELETE FROM users;"             -- should error

Step 2: Set a statement timeout

Runaway queries from an over-eager model can DoS your database. Cap them at 10 seconds:

ALTER ROLE ai_readonly SET statement_timeout = '10s';

Step 3: Connect from Claude Desktop / Cursor

In your MCP client config:

{
  "mcpServers": {
    "postgres": {
      "command": "npx",
      "args": [
        "-y",
        "@modelcontextprotocol/server-postgres",
        "postgresql://ai_readonly:change_me@localhost:5432/mydb"
      ]
    }
  }
}

For production Postgres, use SSL:

"postgresql://ai_readonly:xxx@db.example.com:5432/mydb?sslmode=require"

Step 4: Verify scope

In Claude Desktop or Cursor, ask:

What tables are in the database and roughly how many rows does each have?

Agent uses the schema tool, then executes SELECT reltuples FROM pg_class. You should see a structured summary. No writes possible, no system catalog surprises.

Common mistakes

• Connection string in plain text in a shared repo — use env vars in .cursor/mcp.json and gitignore it, or store secrets in 1Password / Vault.
• Pointing at production directly — use a read-replica if you need real data. Ideally a restored dump on a separate host.
• Granting more than SELECT — resist the temptation. If the agent needs to write, make a separate server with a write-capable role and clear tool boundaries.
• Not setting search_path — if you have multiple schemas, default search path may hide tables. ALTER ROLE ai_readonly SET search_path = public, analytics;

When you actually need writes

If the agent genuinely needs to insert/update — e.g. a support copilot that updates tickets — fork the config:

• Second MCP server instance, separate role ai_writer, scoped INSERT/UPDATE on specific tables.
• Row-level security policies to scope writes further.
• Audit log trigger to record every change with the MCP session ID.

Supabase users

If your app runs on Supabase, skip all of the above and use the native Supabase MCP — it handles read-only vs service-role split for you.

What next

• Top 15 MCP servers — where Postgres ranks.
• Postgres MCP listing — version history, forks, issues.