PostgreSQL MCP Server

Overview

Query and analyze PostgreSQL databases directly from your AI agent through the Model Context Protocol.

Installation

Quick Setup

npx -y @modelcontextprotocol/server-postgres postgresql://localhost/mydb

Connection String Format

postgresql://[user[:password]@][netloc][:port][/dbname][?param1=value1&...]

Claude Desktop Configuration

Add to your claude_desktop_config.json:

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

Setup

1. Ensure PostgreSQL is running

   • Install PostgreSQL if not already installed
   • Create a database and user for the MCP connection

2. Create a dedicated MCP user

   CREATE USER mcp_user WITH PASSWORD 'secure_password';
   GRANT CONNECT ON DATABASE mydb TO mcp_user;
   GRANT USAGE ON SCHEMA public TO mcp_user;
   GRANT SELECT ON ALL TABLES IN SCHEMA public TO mcp_user;
   

3. Configure the MCP Server

   • Pass the connection string as an argument
   • Restart your AI application

Available Tools

Tool	Description
list_tables	List all tables in the database.
get_table_schema	Get the schema of a specific table.
query	Execute a SQL query.
execute	Execute a SQL command.

Usage Examples

List All Tables

{
  "name": "list_tables"
}

Get Table Schema

{
  "name": "get_table_schema",
  "arguments": {
    "table_name": "users"
  }
}

Run a Query

{
  "name": "query",
  "arguments": {
    "sql": "SELECT * FROM users WHERE created_at > NOW() - INTERVAL '7 days'"
  }
}

Create a Table

{
  "name": "execute",
  "arguments": {
    "sql": "CREATE TABLE products (id SERIAL PRIMARY KEY, name TEXT, price NUMERIC)"
  }
}

Security Notes

• Use connection pooling - For production workloads.
• Limit permissions - Use read-only users for analysis.
• Encrypt connections - Use SSL for remote databases.
• Never expose credentials - Use environment variables or secret managers.
• Validate SQL - Be cautious with user-provided queries.

Connection String Security

For better security, use environment variables:

{
  "mcpServers": {
    "postgres": {
      "command": "npx",
      "args": ["-y", "@modelcontextprotocol/server-postgres", "${POSTGRES_URL}"],
      "env": {
        "POSTGRES_URL": "postgresql://user:password@localhost:5432/mydb"
      }
    }
  }
}

Resources

• MCP PostgreSQL Server
• MCP Documentation
• PostgreSQL Documentation