LLM Best Practices

How to connect Claude to Postgres via MCP

4 min read

View markdown source

Overview

The official MCP Postgres server exposes SQL query tools that Claude Code can call during a session, letting you ask questions about schema, data, and query plans in natural language. This guide installs the server, wires credentials without hardcoding them, and runs a verification query. The MCP permission model is in mcp-servers; security considerations are in mcp-security.

Prerequisites

  • Claude Code installed and authenticated. If not, follow run-claude-code-with-mcp first.
  • Node 22 on the path.
  • A running Postgres instance. For local setup, see set-up-postgres-locally.
  • A database user with read-only access. Never use a superuser account for MCP.

Create a read-only user if one does not exist:

CREATE ROLE mcp_readonly LOGIN PASSWORD 'strong-password-here';
GRANT CONNECT ON DATABASE mydb TO mcp_readonly;
GRANT USAGE ON SCHEMA public TO mcp_readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO mcp_readonly;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO mcp_readonly;

Steps

1. Install the MCP Postgres server

The server is published as @modelcontextprotocol/server-postgres. Use npx -y in the Claude Code settings so the package is fetched on demand; no global install is needed.

Test the package resolves without errors:

npx -y @modelcontextprotocol/server-postgres --help

If the command prints usage, the package is reachable. If it fails with a network error, check your npm registry settings.

2. Store credentials safely

Never put the database password in .claude/settings.json directly, since that file is typically committed. Use environment variable substitution.

Export the connection string in your shell profile:

# ~/.zshrc or ~/.bashrc
export PG_MCP_URL="postgresql://mcp_readonly:strong-password-here@localhost:5432/mydb"

For team projects, document the expected variable name in README.md and add the variable to .env.example with a placeholder value. Add .env to .gitignore.

3. Configure the MCP server in settings.json

Add the Postgres server to .claude/settings.json at the project root:

{
  "mcpServers": {
    "postgres": {
      "command": "npx",
      "args": ["-y", "@modelcontextprotocol/server-postgres", "${PG_MCP_URL}"]
    }
  }
}

The ${PG_MCP_URL} syntax reads from the environment at Claude Code startup. The connection string is never written to disk in plain text.

4. Restrict permissions to read-only tools

Limit which tools Claude Code may call without prompting. The Postgres MCP server exposes query and schema-reading tools; restrict to those.

{
  "mcpServers": {
    "postgres": {
      "command": "npx",
      "args": ["-y", "@modelcontextprotocol/server-postgres", "${PG_MCP_URL}"]
    }
  },
  "permissions": {
    "allow": [
      "mcp__postgres__query",
      "mcp__postgres__list_tables",
      "mcp__postgres__describe_table"
    ]
  }
}

Any write tool (if the server exposes one) requires manual confirmation. Pair this with the read-only database user for defense in depth. See mcp-security for the rationale.

5. Start Claude Code and verify connection

claude

Inside the Claude Code session:

/mcp

Expected output:

postgres: connected (3 tools available)

If the server shows “disconnected”, check the troubleshooting section below.

6. Run a test query

Ask Claude Code to describe the schema:

List the tables in the database and show the columns of the users table.

Claude Code calls mcp__postgres__list_tables and mcp__postgres__describe_table. The response should show the real table names and column definitions.

Then run a data query:

How many users were created in the last 7 days?

Claude calls mcp__postgres__query with a SELECT count(*) statement. Verify the count matches a direct psql query.

Verify it worked

# Confirm the connection string is set
echo $PG_MCP_URL
# postgresql://mcp_readonly:...@localhost:5432/mydb
 
# Confirm the user has SELECT access
psql $PG_MCP_URL -c "SELECT count(*) FROM users;"
# Returns a row count without permission errors.
 
# Inside claude session: /mcp shows postgres: connected

Common errors

  • postgres: disconnected in /mcp. The ${PG_MCP_URL} variable is not exported in the shell that started claude. Add export PG_MCP_URL=... to your shell profile and restart the terminal.
  • role "mcp_readonly" does not exist. Run the CREATE ROLE statement in psql connected to the target database as a superuser.
  • permission denied for table users. The GRANT SELECT was run before the table existed. Re-run the grant after creating the table, or use ALTER DEFAULT PRIVILEGES.
  • Claude generates a destructive SQL query. The permissions.allow list does not prevent Claude from constructing a DELETE query and asking for confirmation; the read-only database role prevents it from executing. Both layers are necessary.
  • Connection string is logged in Claude Code output. Use the environment variable substitution syntax, not a hardcoded string. Check .claude/settings.json for any plain-text credentials and rotate them immediately.

Graph View

Backlinks