PostgreSQL MCP Server

A Model Context Protocol (MCP) server that provides access to PostgreSQL database schemas and metadata. This server allows Cursor and other MCP-compatible tools to explore your database structure, inspect table schemas, and understand relationships.

Features

• List Tables: Get all tables in a schema with basic information
• Describe Table: Get detailed column information for any table
• Table Relationships: Explore foreign key relationships
• List Schemas: See all available schemas in your database
• Index Information: View indexes for specific tables

Prerequisites

• Node.js 18+
• PostgreSQL database (local or remote)
• npm or yarn package manager

Installation

1. Clone or download this repository

2. Install dependencies:

   npm install
   

3. Configure database connection:

   cp config.env.example .env
   

   Edit the .env file with your PostgreSQL connection details:

   DB_HOST=localhost
   DB_PORT=5432
   DB_NAME=your_database_name
   DB_USER=your_username
   DB_PASSWORD=your_password
   

4. Test the server (optional):

   npm start
   

Cursor Integration

To use this MCP server with Cursor, you need to add it to your Cursor configuration.

Step 1: Add to Cursor MCP Configuration

Open Cursor and go to Settings → Features → Model Context Protocol, or edit your MCP configuration file directly.

Add the following configuration:

{
  "mcpServers": {
    "postgres-schema": {
      "command": "node",
      "args": ["/absolute/path/to/pg_mcp/src/index.js"],
      "env": {
        "DB_HOST": "localhost",
        "DB_PORT": "5432", 
        "DB_NAME": "your_database_name",
        "DB_USER": "your_username",
        "DB_PASSWORD": "your_password"
      }
    }
  }
}

Important: Replace /absolute/path/to/pg_mcp/src/index.js with the actual absolute path to your src/index.js file.

Step 2: Alternative Configuration (Using .env file)

If you prefer to use the .env file for configuration:

{
  "mcpServers": {
    "postgres-schema": {
      "command": "node",
      "args": ["/absolute/path/to/pg_mcp/src/index.js"],
      "cwd": "/absolute/path/to/pg_mcp"
    }
  }
}

Step 3: Restart Cursor

After adding the configuration, restart Cursor to load the MCP server.

Available Tools

Once configured, you'll have access to these tools in Cursor:

list_tables

Lists all tables in a specified schema (defaults to 'public')

Parameters:
- schema (optional): Schema name (default: "public")

describe_table

Gets detailed schema information for a specific table

Parameters:
- table_name (required): Name of the table to describe
- schema (optional): Schema name (default: "public")

get_table_relationships

Shows foreign key relationships for a table

Parameters:
- table_name (required): Name of the table
- schema (optional): Schema name (default: "public")

list_schemas

Lists all available schemas in the database

No parameters required

get_indexes

Shows indexes for a specific table

Parameters:
- table_name (required): Name of the table
- schema (optional): Schema name (default: "public")

Usage Examples

Once configured in Cursor, you can ask questions like:

• "What tables are in my database?"
• "Show me the schema for the users table"
• "What are the foreign key relationships for the orders table?"
• "List all indexes on the products table"
• "What schemas are available in my database?"

Troubleshooting

Connection Issues

1. Database connection fails:

   • Verify your database credentials in .env
   • Ensure PostgreSQL is running
   • Check if the database allows connections from your host

2. MCP server not loading in Cursor:

   • Verify the absolute path to index.js is correct
   • Check that Node.js is installed and accessible
   • Review Cursor's MCP logs for error messages

3. Permission denied errors:

   • Ensure the database user has read permissions on information_schema
   • Grant necessary SELECT permissions on system tables

Common Error Messages

• Database connection failed: Check your connection parameters
• Table not found: Verify the table name and schema
• Permission denied: Ensure your database user has appropriate read permissions

Security Considerations

• This server only reads database metadata (schema information)
• No actual table data is accessed or exposed
• Uses parameterized queries to prevent SQL injection
• Database credentials are stored locally in your environment

Development

To run in development mode with auto-reload:

npm run dev

License

MIT License - see LICENSE file for details.