# 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:**
```sql
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`
```json
{
"mcpServers": {
"postgresql": {
"command": "apify",
"args": ["call", "your-username/postgresql-mcp-server", "--input", "@config.json"]
}
}
}
```
Create `config.json` with your database connection:
```json
{
"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:
```json
{
"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:
```bash
npm install
```
2. Build TypeScript:
```bash
npm run build
```
3. Create test input file `input.json`:
```json
{
"connectionString": "postgresql://localhost:5432/testdb",
"readOnly": true
}
```
4. Run locally:
```bash
node dist/main.js
```
### Testing with MCP Inspector
Use the MCP Inspector tool to test your server:
```bash
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](https://github.com/apify/postgresql-mcp-server).
## 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.
