How to Set Up PostgreSQL MCP Server (2026)
Give AI assistants direct access to your PostgreSQL database
Start Building with Hypereal
Access Kling, Flux, Sora, Veo & more through a single API. Free credits to start, scale to millions.
No credit card required • 100k+ developers • Enterprise ready
How to Set Up PostgreSQL MCP Server (2026)
The PostgreSQL MCP server allows AI assistants like Claude to query your PostgreSQL database directly, inspect schemas, list tables, and analyze data -- all through natural language conversations. Instead of manually copying query results into a chat window, you give Claude direct, controlled access to your database. This guide covers setup, configuration, and security best practices.
What Is the PostgreSQL MCP Server?
MCP (Model Context Protocol) is Anthropic's open standard for connecting AI assistants to external tools and data sources. The PostgreSQL MCP server exposes your database as a set of tools that Claude can call:
| Tool | Description |
|---|---|
query |
Execute a read-only SQL query |
list_tables |
List all tables in a schema |
describe_table |
Get column names, types, and constraints for a table |
list_schemas |
List all schemas in the database |
get_table_stats |
Get row counts and basic statistics |
When Claude is connected to the PostgreSQL MCP server, you can ask natural language questions like "What are the top 10 customers by revenue this month?" and Claude will write the SQL, execute it, and present the results.
Prerequisites
- PostgreSQL 12 or later running and accessible
- Node.js 18 or later (for the MCP server)
- Claude Desktop or Claude Code (MCP-compatible client)
- A PostgreSQL user with appropriate permissions
Check your versions:
psql --version
# psql (PostgreSQL) 16.x
node --version
# v20.x.x or higher
Step 1: Create a Read-Only Database User
Never connect the MCP server with a superuser or an account that has write privileges. Create a dedicated read-only user:
-- Connect to PostgreSQL as admin
psql -U postgres
-- Create a read-only role
CREATE ROLE mcp_reader WITH LOGIN PASSWORD 'your_secure_password';
-- Grant connect to your database
GRANT CONNECT ON DATABASE your_database TO mcp_reader;
-- Grant usage on schemas
GRANT USAGE ON SCHEMA public TO mcp_reader;
-- Grant SELECT on all existing tables
GRANT SELECT ON ALL TABLES IN SCHEMA public TO mcp_reader;
-- Grant SELECT on future tables automatically
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT ON TABLES TO mcp_reader;
-- Optional: grant access to additional schemas
GRANT USAGE ON SCHEMA analytics TO mcp_reader;
GRANT SELECT ON ALL TABLES IN SCHEMA analytics TO mcp_reader;
Verify the user can only read:
psql -U mcp_reader -d your_database -c "SELECT 1;"
# Should succeed
psql -U mcp_reader -d your_database -c "DROP TABLE users;"
# Should fail with permission denied
Step 2: Install the PostgreSQL MCP Server
The community maintains several PostgreSQL MCP server packages. The most popular is @modelcontextprotocol/server-postgres:
# Install globally
npm install -g @modelcontextprotocol/server-postgres
# Or use npx (no install required)
npx @modelcontextprotocol/server-postgres
Step 3: Configure Claude Desktop
Add the PostgreSQL MCP server to your Claude Desktop configuration.
macOS: ~/Library/Application Support/Claude/claude_desktop_config.json
Windows: %APPDATA%\Claude\claude_desktop_config.json
{
"mcpServers": {
"postgresql": {
"command": "npx",
"args": [
"-y",
"@modelcontextprotocol/server-postgres",
"postgresql://mcp_reader:your_secure_password@localhost:5432/your_database"
]
}
}
}
Using Environment Variables (Recommended)
To avoid putting credentials in the config file:
{
"mcpServers": {
"postgresql": {
"command": "npx",
"args": [
"-y",
"@modelcontextprotocol/server-postgres"
],
"env": {
"DATABASE_URL": "postgresql://mcp_reader:your_secure_password@localhost:5432/your_database"
}
}
}
}
Connecting to a Remote Database
For remote databases, use the full connection string with SSL:
{
"mcpServers": {
"postgresql": {
"command": "npx",
"args": [
"-y",
"@modelcontextprotocol/server-postgres"
],
"env": {
"DATABASE_URL": "postgresql://mcp_reader:password@db.example.com:5432/mydb?sslmode=require"
}
}
}
}
Step 4: Configure for Claude Code
If you use Claude Code (the CLI), add the MCP server to your project's .claude/settings.json:
{
"mcpServers": {
"postgresql": {
"command": "npx",
"args": [
"-y",
"@modelcontextprotocol/server-postgres"
],
"env": {
"DATABASE_URL": "postgresql://mcp_reader:password@localhost:5432/your_database"
}
}
}
}
Or configure it globally in ~/.claude/settings.json to make it available across all projects.
Step 5: Restart and Verify
After saving the configuration:
- Quit Claude Desktop completely (not just close the window)
- Reopen Claude Desktop
- Look for the database icon or MCP tools indicator
- Ask Claude: "What tables are in my database?"
Claude should respond with a list of your tables. If not, check the troubleshooting section below.
Example Queries
Once connected, you can ask Claude natural language questions:
Schema Exploration
"List all tables in the public schema with their row counts"
"Describe the users table - show me all columns and their types"
Data Analysis
"What are the top 10 products by total revenue in January 2026?"
"Show me the daily signup trend for the last 30 days"
Complex Queries
"Find all users who signed up in the last 7 days but haven't
made a purchase. Include their email and signup date."
"Calculate the month-over-month growth rate of active users
for the last 6 months"
Claude will write the appropriate SQL, execute it through the MCP server, and present the results in a readable format.
Security Best Practices
| Practice | Why | How |
|---|---|---|
| Use a read-only user | Prevent accidental data modification | GRANT SELECT only |
| Restrict to specific schemas | Limit data exposure | GRANT USAGE ON SCHEMA selectively |
| Use connection pooling | Prevent connection exhaustion | PgBouncer or built-in pool |
| Set statement timeout | Prevent long-running queries | ALTER ROLE mcp_reader SET statement_timeout = '30s' |
| Exclude sensitive tables | Protect PII and credentials | Revoke SELECT on specific tables |
| Use SSL | Encrypt data in transit | ?sslmode=require in connection string |
| Audit queries | Monitor what Claude accesses | Enable log_statement = 'all' for the MCP role |
Restricting Access to Sensitive Tables
-- Revoke access to tables containing sensitive data
REVOKE SELECT ON TABLE user_credentials FROM mcp_reader;
REVOKE SELECT ON TABLE payment_methods FROM mcp_reader;
REVOKE SELECT ON TABLE api_keys FROM mcp_reader;
-- Or create a view that exposes only safe columns
CREATE VIEW safe_users AS
SELECT id, username, created_at, last_login
FROM users;
GRANT SELECT ON safe_users TO mcp_reader;
Setting Query Limits
-- Set a 30-second timeout for the MCP user
ALTER ROLE mcp_reader SET statement_timeout = '30s';
-- Limit the number of rows returned
ALTER ROLE mcp_reader SET work_mem = '64MB';
Advanced: Custom PostgreSQL MCP Server
If you need more control, you can build a custom MCP server that wraps PostgreSQL with additional logic:
// custom-pg-mcp.js
import { Server } from "@modelcontextprotocol/sdk/server/index.js";
import { StdioServerTransport } from "@modelcontextprotocol/sdk/server/stdio.js";
import pg from "pg";
const pool = new pg.Pool({
connectionString: process.env.DATABASE_URL,
max: 5,
statement_timeout: 30000
});
const server = new Server({
name: "custom-postgres",
version: "1.0.0"
}, {
capabilities: { tools: {} }
});
server.setRequestHandler("tools/list", async () => ({
tools: [
{
name: "query",
description: "Execute a read-only SQL query",
inputSchema: {
type: "object",
properties: {
sql: { type: "string", description: "SQL SELECT query" }
},
required: ["sql"]
}
}
]
}));
server.setRequestHandler("tools/call", async (request) => {
const { name, arguments: args } = request.params;
if (name === "query") {
const sql = args.sql.trim();
// Only allow SELECT and WITH (CTE) statements
if (!/^(SELECT|WITH)\b/i.test(sql)) {
return {
content: [{ type: "text", text: "Error: Only SELECT queries allowed" }],
isError: true
};
}
// Block dangerous keywords
const blocked = /\b(DROP|DELETE|UPDATE|INSERT|ALTER|TRUNCATE|GRANT|REVOKE)\b/i;
if (blocked.test(sql)) {
return {
content: [{ type: "text", text: "Error: Blocked keyword detected" }],
isError: true
};
}
try {
const result = await pool.query(sql);
return {
content: [{
type: "text",
text: JSON.stringify(result.rows, null, 2)
}]
};
} catch (error) {
return {
content: [{ type: "text", text: `Query error: ${error.message}` }],
isError: true
};
}
}
});
const transport = new StdioServerTransport();
await server.connect(transport);
Troubleshooting
| Issue | Cause | Solution |
|---|---|---|
| "Connection refused" | PostgreSQL not running or wrong port | Check pg_isready -h localhost -p 5432 |
| "Authentication failed" | Wrong password or user | Verify credentials with psql directly |
| MCP server not appearing | Config file error | Validate JSON syntax; restart Claude Desktop |
| "Permission denied for table" | Missing GRANT | Run GRANT SELECT ON ALL TABLES... again |
| Queries timing out | Missing statement_timeout | Set statement_timeout for the role |
| "SSL required" | Remote DB requires SSL | Add ?sslmode=require to connection string |
| Slow queries | Missing indexes or large tables | Add indexes; use LIMIT in queries |
Conclusion
The PostgreSQL MCP server transforms how you interact with your database through AI. Instead of context-switching between a SQL client and a chat window, Claude can query your data directly, explore schemas, and help you analyze results -- all in natural language. The key is setting up proper security with a read-only user and restricted table access.
For teams building data-driven applications that also need AI-generated visual content -- such as turning analytics data into video presentations or generating chart-based content -- Hypereal AI provides APIs for video generation, image synthesis, and talking avatar creation that complement your data pipeline.
Related Articles
Start Building Today
Get 35 free credits on signup. No credit card required. Generate your first image in under 5 minutes.
