PostgreSQL Connector

The PostgreSQL connector enables AxonFlow agents to execute SQL queries and commands against PostgreSQL databases with connection pooling and policy enforcement.

Overview

Property	Value
Type	postgres
Edition	Community
Auth Methods	Connection string, Username/Password
Capabilities	query, execute, transactions, prepared_statements, connection_pooling

Use Cases

• Execute SQL queries for RAG context retrieval
• Store and retrieve structured data for agent workflows
• Access application databases with policy governance
• Audit logging and compliance data retrieval

Configuration

Environment Variables

# Required
MCP_postgres_CONNECTION_URL="postgres://user:pass@host:5432/database?sslmode=require"

# Or build from components
MCP_postgres_HOST="localhost"
MCP_postgres_PORT="5432"
MCP_postgres_DATABASE="mydb"
MCP_postgres_USER="myuser"
MCP_postgres_PASSWORD="mypassword"

# Optional - Connection Pool
MCP_postgres_MAX_OPEN_CONNS="25"
MCP_postgres_MAX_IDLE_CONNS="5"
MCP_postgres_CONN_MAX_LIFETIME="5m"

# Optional - Tenant isolation
MCP_postgres_TENANT_ID="*"  # "*" for global, or specific tenant ID

Connection URL Format

postgres://[user]:[password]@[host]:[port]/[database]?[parameters]

Parameters:

• sslmode - SSL connection mode (disable, require, verify-ca, verify-full)
• connect_timeout - Connection timeout in seconds
• application_name - Application name for monitoring

Connector Config (Customer Portal)

{
  "name": "app-database",
  "type": "postgres",
  "connection_url": "postgres://user:[email protected]:5432/appdb?sslmode=require",
  "options": {
    "max_open_conns": 25,
    "max_idle_conns": 5,
    "conn_max_lifetime": "5m"
  }
}

Operations

Query Operations

Execute SELECT statements to retrieve data:

curl -X POST https://your-axonflow.com/mcp/resources/query \
  -H "Content-Type: application/json" \
  -H "Authorization: Bearer $TOKEN" \
  -d '{
    "connector": "app-database",
    "statement": "SELECT id, name, email FROM users WHERE status = $1 LIMIT $2",
    "parameters": {
      "1": "active",
      "2": 100
    }
  }'

Response:

{
  "success": true,
  "rows": [
    {"id": 1, "name": "Alice", "email": "[email protected]"},
    {"id": 2, "name": "Bob", "email": "[email protected]"}
  ],
  "row_count": 2,
  "duration_ms": 4.5,
  "connector": "app-database"
}

Execute Operations

Run INSERT, UPDATE, DELETE statements:

# INSERT
curl -X POST https://your-axonflow.com/mcp/tools/execute \
  -H "Content-Type: application/json" \
  -d '{
    "connector": "app-database",
    "action": "insert",
    "statement": "INSERT INTO audit_logs (event, user_id, timestamp) VALUES ($1, $2, $3)",
    "parameters": {
      "1": "user_login",
      "2": "user-123",
      "3": "2025-12-08T10:30:00Z"
    }
  }'

# UPDATE
curl -X POST https://your-axonflow.com/mcp/tools/execute \
  -d '{
    "connector": "app-database",
    "action": "update",
    "statement": "UPDATE users SET last_login = $1 WHERE id = $2",
    "parameters": {
      "1": "2025-12-08T10:30:00Z",
      "2": 123
    }
  }'

# DELETE
curl -X POST https://your-axonflow.com/mcp/tools/execute \
  -d '{
    "connector": "app-database",
    "action": "delete",
    "statement": "DELETE FROM sessions WHERE expires_at < $1",
    "parameters": {
      "1": "2025-12-07T00:00:00Z"
    }
  }'

Response:

{
  "success": true,
  "rows_affected": 1,
  "duration_ms": 2.3,
  "message": "insert executed successfully",
  "connector": "app-database"
}

Parameterized Queries

PostgreSQL uses positional parameters ($1, $2, etc.) for SQL injection prevention:

-- Safe: Uses parameterized query
SELECT * FROM users WHERE email = $1 AND status = $2

-- NEVER do this (SQL injection risk)
SELECT * FROM users WHERE email = '[email protected]'

Parameter Mapping:

{
  "statement": "SELECT * FROM products WHERE category = $1 AND price < $2",
  "parameters": {
    "1": "electronics",
    "2": 500.00
  }
}

Connection Pooling

The connector maintains a connection pool for optimal performance:

Setting	Default	Description
max_open_conns	25	Maximum open connections
max_idle_conns	5	Maximum idle connections
conn_max_lifetime	5m	Maximum connection lifetime

Recommendations:

• For high-throughput: Increase max_open_conns to 50-100
• For connection-limited DBs: Keep max_open_conns at 10-20
• Set conn_max_lifetime below your database's connection timeout

Health Check

The connector provides health status via the MCP health endpoint:

curl https://your-axonflow.com/mcp/connectors/app-database/health

Response:

{
  "healthy": true,
  "latency_ms": 1.2,
  "details": {
    "open_connections": "5",
    "in_use": "2",
    "idle": "3"
  },
  "timestamp": "2025-12-08T10:30:00Z"
}

Best Practices

Security

1. Use SSL/TLS - Always use sslmode=require or stronger in production
2. Least privilege - Create a database user with minimal required permissions
3. Parameterized queries - Always use $1, $2 placeholders, never string concatenation
4. Network isolation - Place database in private subnet, access via VPC

Performance

1. Index optimization - Ensure queries use appropriate indexes
2. Connection pooling - Configure pool size based on workload
3. Query timeouts - Set appropriate timeouts to prevent long-running queries
4. EXPLAIN ANALYZE - Profile slow queries before production

Example Database User Setup

-- Create read-only user for queries
CREATE USER axonflow_reader WITH PASSWORD 'secure_password';
GRANT CONNECT ON DATABASE mydb TO axonflow_reader;
GRANT USAGE ON SCHEMA public TO axonflow_reader;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO axonflow_reader;

-- Create read-write user for full access
CREATE USER axonflow_writer WITH PASSWORD 'secure_password';
GRANT CONNECT ON DATABASE mydb TO axonflow_writer;
GRANT USAGE ON SCHEMA public TO axonflow_writer;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO axonflow_writer;

Troubleshooting

Connection Refused

• Verify database host and port are correct
• Check security group/firewall allows connection
• Ensure database is accepting connections (listen_addresses in postgresql.conf)

Authentication Failed

• Verify username and password
• Check pg_hba.conf allows connection method
• Ensure user has CONNECT privilege on database

SSL Certificate Error

• For self-signed certs: Use sslmode=require (doesn't verify cert)
• For verified SSL: Add CA cert to client trust store

Connection Pool Exhausted

• Increase max_open_conns
• Check for connection leaks (queries not closing)
• Monitor with health endpoint

Related

• MySQL Connector
• MongoDB Connector
• MCP Overview
• Connector SDK