Back to Home

PostgreSQL MCP Documentation

GitHub Pages

Table of Contents

Overview

PostgreSQL MCP Server is a Model Context Protocol (MCP) server that provides secure, read-only access to PostgreSQL databases for AI assistants and other MCP clients. It enables intelligent database analysis, querying, and schema exploration through a standardized interface.

Key Features

  • • Read-only database access
  • • Advanced security validation
  • • Schema introspection
  • • Built-in data analysis tools
  • • Serverless compatible

Supported Databases

  • • PostgreSQL (all versions)
  • • Neon (serverless)
  • • Supabase
  • • Railway, Render, AWS RDS
  • • Self-hosted instances

Installation

Local Development

git clone https://github.com/likhon-developer/file-mcp.git
cd file-mcp
npm install
cp .env.example .env.local

Edit .env.local with your DATABASE_URL, then run:

npm run dev

Configuration

Environment Variables

DATABASE_URL=postgresql://username:password@hostname:port/database

MCP Client Configuration

Add the following configuration to your MCP client:

Claude Desktop

Location: ~/Library/Application Support/Claude/claude_desktop_config.json (macOS)

{
  "mcpServers": {
    "postgresql": {
      "url": "https://file-mcp.vercel.app/api/mcp"
    }
  }
}

Tools

execute_sql

Execute read-only SQL queries against the database

Parameters:

query(string) (required) - The SQL query to execute
limit(number) (optional) - Maximum rows to return (default: 100, max: 1000)

Example:

SELECT * FROM users WHERE created_at > '2024-01-01' LIMIT 10

get_table_info

Get detailed information about a specific table

Parameters:

tableName(string) (required) - Name of the table to analyze

Example:

users

analyze_data

Generate common data analysis queries for a table

Parameters:

tableName(string) (required) - Name of the table to analyze
analysisType(enum) (required) - Type of analysis: summary, distribution, nulls, duplicates, trends

Example:

users, summary

search_tables

Search for tables and columns matching a pattern

Parameters:

pattern(string) (required) - Search pattern
searchType(enum) (optional) - What to search: tables, columns, both (default)

Example:

user, both

Resources

Database Schema

Complete database schema with all tables and columns in JSON format

URI: postgresql://database-schemaType: application/json

Table List

Simple list of all tables in the database

URI: postgresql://table-listType: text/plain

Security

Security Features

Read-Only Access

Only SELECT, WITH, EXPLAIN, SHOW, and DESCRIBE queries are allowed

SQL Injection Prevention

Advanced query validation and parameter sanitization

Input Validation

Comprehensive validation of table names and user inputs

Error Handling

Secure error messages that don't expose sensitive information

Blocked Operations

The following SQL operations are automatically blocked:

INSERT, UPDATE, DELETE, DROP, CREATE, ALTER, TRUNCATE

Examples

Basic Queries

Get recent users

SELECT * FROM users WHERE created_at > '2024-01-01' LIMIT 10;

Count records by status

SELECT status, COUNT(*) FROM orders GROUP BY status;

Find top products

SELECT product_name, SUM(quantity) as total_sold 
FROM order_items 
GROUP BY product_name 
ORDER BY total_sold DESC 
LIMIT 5;

Data Analysis Examples

Summary Analysis

Get basic statistics about any table

Use analyze_data tool with analysisType: 'summary'

Distribution Analysis

Analyze value distributions in columns

Use analyze_data tool with analysisType: 'distribution'

Null Analysis

Find missing data patterns

Use analyze_data tool with analysisType: 'nulls'

Trend Analysis

Analyze time-based patterns

Use analyze_data tool with analysisType: 'trends'