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 table
• table (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 table
• table (string, required): Table name to sample
• limit (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

1. Initialization: Actor starts, validates input, and establishes database connection
2. Connection Test: Verifies database connectivity before starting the MCP server
3. MCP Server Start: Begins listening for tool calls on stdio (standard input/output)
4. Tool Processing: Receives tool calls from AI agents, executes database operations
5. Result Formatting: Formats query results as JSON and returns to AI agent
6. Logging: Records all operations to Apify dataset for audit and monitoring
7. 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 timestamp
• executionTime: Duration in milliseconds
• rowsReturned: Number of rows returned
• success: Boolean indicating success or failure
• error: Error message (only present if success: 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 timeout parameter
• 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: false if 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 allowedSchemas array
• 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

1. Install dependencies:

npm install

2. Build TypeScript:

npm run build

3. Create test input file input.json:

{
  "connectionString": "postgresql://localhost:5432/testdb",
  "readOnly": true
}

4. 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.