PostgreSQL MCP Server - AI Agent Database Access
Connect AI agents like Claude, ChatGPT, and other LLM-powered tools to PostgreSQL databases through the Model Context Protocol (MCP). This Apify Actor implements a production-ready MCP server that enables intelligent data querying, exploration, and analysis while maintaining robust security controls.
What This Actor Does
This Actor bridges the gap between AI agents and PostgreSQL databases by implementing the Model Context Protocol (MCP), an open standard for connecting AI assistants to external data sources. Once configured, AI agents can:
- Execute SQL queries to retrieve and analyze data
- Discover database schema and table structures
- Explore table relationships and constraints
- Generate insights from your data through natural language
The Actor is designed with security as a first priority, offering read-only mode, query timeouts, row limits, and schema restrictions to ensure safe AI-powered database access.
Why Use This Actor?
Empower AI with Your Data: AI agents can provide much more valuable insights when they have direct access to your operational data. This Actor makes that connection secure and simple.
Production-Ready Security: Built-in safeguards prevent unauthorized data modifications, limit resource usage, and restrict access to specific database schemas.
Universal Compatibility: Works with any PostgreSQL database (including AWS RDS, Google Cloud SQL, Azure Database, and self-hosted instances) and any MCP-compatible AI agent.
Comprehensive Logging: Every query is logged to Apify's dataset storage, providing full audit trails of all AI database interactions.
Zero Infrastructure: Runs on Apify's managed platform - no servers to maintain, no scaling concerns, no deployment complexity.
Features
Core Capabilities
- SQL Query Execution: Execute arbitrary SQL queries with automatic result formatting
- Table Discovery: List all tables across allowed schemas with metadata
- Schema Introspection: Get detailed column information, data types, and constraints
- Data Sampling: Preview table contents with configurable row limits
- Connection Pooling: Efficient connection management for high-performance queries
Security Features
- Read-Only Mode: Enforce SELECT-only queries, preventing INSERT, UPDATE, DELETE, and DDL operations
- Schema Restrictions: Limit AI access to specific database schemas
- Query Timeouts: Automatically terminate long-running queries
- Row Limits: Cap maximum rows returned to prevent memory exhaustion
- SQL Injection Protection: Parameterized queries and identifier escaping
- SSL/TLS Support: Secure encrypted connections to your database
MCP Protocol Compliance
- Full implementation of MCP tool specification
- Stdio transport for reliable communication
- Structured error handling and reporting
- Proper tool metadata and documentation
Input Parameters
| Parameter | Type | Required | Default | Description |
|---|---|---|---|---|
connectionString | string | Yes | - | PostgreSQL connection URL in the format postgresql://username:password@host:port/database |
allowedSchemas | array of strings | No | ["public"] | List of database schemas AI agents can access. Queries are restricted to these schemas only. |
maxQueryResults | integer | No | 1000 | Maximum number of rows returned by a single query. Range: 1-10,000. |
readOnly | boolean | No | true | When enabled, only SELECT and EXPLAIN queries are permitted. Prevents all data modifications. |
timeout | integer | No | 30 | Query timeout in seconds. Queries exceeding this duration are automatically terminated. Range: 1-300. |
sslMode | string | No | "prefer" | SSL connection mode. Options: disable (no SSL), prefer (use SSL if available), require (force SSL). |
Connection String Format
Your PostgreSQL connection string should follow this format:
postgresql://username:password@hostname:port/database_name
Examples:
- Local database:
postgresql://postgres:mypassword@localhost:5432/myapp - Cloud database:
postgresql://admin:secure_pass@db.example.com:5432/production - With SSL:
postgresql://user:pass@host:5432/db?sslmode=require
Available MCP Tools
The Actor exposes four MCP tools that AI agents can call:
1. query
Execute SQL queries against the database. Respects all security settings including read-only mode, query timeout, and row limits.
Parameters:
query(string, required): The SQL query to execute
Example:
SELECT customer_name, email, total_orders
FROM customers
WHERE country = 'USA'
ORDER BY total_orders DESC
LIMIT 10
2. list_tables
List all tables in the database, filtered by allowed schemas. Returns table names with metadata including row counts and sizes.
Parameters:
schema(string, optional): Filter tables by specific schema
Returns: Array of tables with schema, tableName, rowCount, and tableSize fields.
3. describe_table
Get comprehensive schema information for a specific table, including columns, data types, constraints, indexes, and foreign key relationships.
Parameters:
schema(string, required): Schema containing the tabletable(string, required): Table name to describe
Returns: Complete table description with columns, constraints, indexes, and foreign keys.
4. get_table_sample
Retrieve sample rows from a table for data preview and exploration.
Parameters:
schema(string, required): Schema containing the tabletable(string, required): Table name to samplelimit(integer, optional): Number of rows to return (default: 10, max:maxQueryResults)
Returns: Array of sample rows from the table.
Use Cases
1. RAG (Retrieval-Augmented Generation)
AI agents can query your production database to retrieve real-time data for answering questions. Instead of relying on static training data, agents access current information.
Example: "What were our top 5 products by revenue last month?" - The AI queries your sales database and provides accurate, up-to-date answers.
2. Data Analysis and Business Intelligence
Connect Claude or ChatGPT to your analytics database and ask complex analytical questions in natural language. The AI translates your questions into SQL and interprets the results.
Example: "Show me the customer churn rate trend over the past year, segmented by subscription tier."
3. Automated Reporting
AI agents can generate custom reports by querying your database, analyzing patterns, and creating narrative summaries without manual SQL writing.
Example: "Create a summary report of Q4 performance across all regions, highlighting notable changes."
4. Database Exploration and Documentation
New team members can ask AI assistants about your database schema, relationships, and data patterns to understand the system faster.
Example: "What tables store customer information, and how are they related?"
How It Works
Architecture
┌─────────────────┐ MCP Protocol (stdio) ┌──────────────────┐
│ AI Agent │◄──────────────────────────────────────►│ Apify Actor │
│ (Claude/ChatGPT)│ Tool Calls & Responses │ MCP Server │
└─────────────────┘ └─────────┬────────┘
│
PostgreSQL Connection
│
▼
┌──────────────────┐
│ PostgreSQL │
│ Database │
└──────────────────┘
Execution Flow
- Initialization: Actor starts, validates input, and establishes database connection
- Connection Test: Verifies database connectivity before starting the MCP server
- MCP Server Start: Begins listening for tool calls on stdio (standard input/output)
- Tool Processing: Receives tool calls from AI agents, executes database operations
- Result Formatting: Formats query results as JSON and returns to AI agent
- Logging: Records all operations to Apify dataset for audit and monitoring
- Graceful Shutdown: Closes database connections when Actor terminates
Integration Examples
Claude Desktop Configuration
Add this configuration to your Claude Desktop config file:
macOS: ~/Library/Application Support/Claude/claude_desktop_config.json
Windows: %APPDATA%\Claude\claude_desktop_config.json
{
"mcpServers": {
"postgresql": {
"command": "apify",
"args": ["call", "your-username/postgresql-mcp-server", "--input", "@config.json"]
}
}
}
Create config.json with your database connection:
{
"connectionString": "postgresql://user:password@host:port/database",
"allowedSchemas": ["public", "analytics"],
"readOnly": true,
"maxQueryResults": 1000,
"timeout": 30
}
Example Queries for AI Agents
Once connected, you can ask your AI agent natural language questions:
- "What tables are available in the database?"
- "Show me the schema for the users table"
- "Get a sample of 5 rows from the orders table"
- "How many active customers do we have in the USA?"
- "What's the average order value for the past 30 days?"
The AI will automatically use the appropriate MCP tools (list_tables, describe_table, get_table_sample, query) to answer your questions.
Security Considerations
Read-Only Mode (Highly Recommended)
Always use readOnly: true in production unless you have a specific need for data modification. This prevents:
- Accidental data deletion or modification
- INSERT/UPDATE/DELETE operations
- Schema changes (CREATE, ALTER, DROP)
- Privilege modifications (GRANT, REVOKE)
Only SELECT and EXPLAIN queries are permitted in read-only mode.
Schema Restrictions
Limit allowedSchemas to only the schemas your AI agent needs access to. This provides defense-in-depth:
- Prevents access to system tables
- Isolates sensitive data in restricted schemas
- Enables multi-tenant database sharing
Query Limits and Timeouts
Configure appropriate maxQueryResults and timeout values:
- Prevents memory exhaustion from large result sets
- Stops runaway queries that could impact database performance
- Protects against accidental full table scans
Connection Security
Use sslMode: "require" when connecting over the internet:
- Encrypts data in transit
- Prevents man-in-the-middle attacks
- Required for compliance with many security standards
Credential Management
Store database credentials securely:
- Use Apify secret inputs for sensitive connection strings
- Never commit credentials to version control
- Rotate credentials regularly
- Use read-only database users when possible
Audit Logging
All queries are logged to Apify datasets:
- Review query logs regularly for suspicious activity
- Monitor for unexpected query patterns
- Track which tools are being used most frequently
Output Format
The Actor logs all operations to the Apify dataset. Each entry includes:
{
"tool": "query",
"query": "SELECT * FROM customers WHERE country = 'USA' LIMIT 10",
"timestamp": "2025-12-03T10:30:45.123Z",
"executionTime": 145,
"rowsReturned": 10,
"success": true
}
Fields:
tool: Name of the MCP tool called (query, list_tables, describe_table, get_table_sample)query: SQL query executed (for query tool only)parameters: Tool parameters (for non-query tools)timestamp: ISO 8601 timestampexecutionTime: Duration in millisecondsrowsReturned: Number of rows returnedsuccess: Boolean indicating success or failureerror: Error message (only present ifsuccess: false)
Access the dataset through the Apify Console or API to analyze query patterns and performance.
Common Issues and Troubleshooting
Connection Failures
Problem: "Database connection failed: connection refused"
Solutions:
- Verify the connection string is correct
- Check that the database server is running
- Ensure your firewall allows connections from Apify IP addresses
- Verify the username and password are correct
SSL/TLS Errors
Problem: "SSL connection error: self-signed certificate"
Solutions:
- Use
sslMode: "prefer"instead of"require"for self-signed certificates - Add proper SSL certificates to your database server
- For development, use
sslMode: "disable"(not recommended for production)
Query Timeout
Problem: "Query timeout: execution exceeded 30 seconds"
Solutions:
- Optimize your query with proper indexes
- Increase the
timeoutparameter - Use more specific WHERE clauses to reduce data scanned
- Consider materializing complex queries into summary tables
Read-Only Mode Errors
Problem: "Read-only mode: INSERT operations are not allowed"
Solutions:
- This is expected behavior when
readOnly: true - Set
readOnly: falseif you need data modification (use with caution) - Verify your query is actually a SELECT statement
- Check for CTEs (WITH clauses) that contain modifications
Schema Access Denied
Problem: "Schema 'private' is not in the allowed schemas list"
Solutions:
- Add the schema to the
allowedSchemasarray - Verify the schema name is spelled correctly (case-sensitive)
- List available schemas with:
SELECT schema_name FROM information_schema.schemata
Development and Testing
Local Testing
- Install dependencies:
npm install
- Build TypeScript:
npm run build
- Create test input file
input.json:
{
"connectionString": "postgresql://localhost:5432/testdb",
"readOnly": true
}
- Run locally:
node dist/main.js
Testing with MCP Inspector
Use the MCP Inspector tool to test your server:
npx @modelcontextprotocol/inspector dist/main.js
This opens a web interface where you can test tool calls interactively.
Technical Details
Language: TypeScript (compiled to JavaScript) Node Version: 20+ MCP SDK: @modelcontextprotocol/sdk ^0.5.0 Database Driver: pg (node-postgres) ^8.11.3 Platform: Apify Actor (Docker-based)
Support and Contributions
For issues, feature requests, or contributions, please visit the GitHub repository.
License
MIT License - feel free to use this Actor in your projects, modify it, and redistribute it.
Built for the Apify $1M Challenge - Empowering AI agents with secure database access through the Model Context Protocol.