Skip to content

Security and Best Practices

Jump to bottom
Temp edited this page Oct 3, 2025 · 1 revision

Security and Best Practices

Enterprise-grade security practices for PostgreSQL MCP Server.

🛡️ Security Overview

The PostgreSQL MCP Server is designed with security first:

  • ✅ Zero Known Vulnerabilities - Comprehensive security audit passed
  • ✅ SQL Injection Prevention - Automatic parameter binding validation
  • ✅ Dual Security Modes - Restricted (production) and unrestricted (development)
  • ✅ Type Safety - Pyright strict mode with LiteralString enforcement

🔒 Security Modes

Restricted Mode (Production)

Always use restricted mode for production databases.

postgres-mcp --access-mode=restricted

Protection Features:

  • ✅ Read-only operations only
  • ✅ Advanced SQL validation
  • ✅ Query timeout protection
  • ✅ Parameter binding required
  • ✅ Resource usage limits
  • ✅ DDL operations blocked

Allowed Operations:

  • SELECT queries with parameter binding
  • EXPLAIN and EXPLAIN ANALYZE
  • pg_stat_statements queries
  • Index analysis and recommendations

Blocked Operations:

  • INSERT, UPDATE, DELETE
  • DROP, CREATE, ALTER
  • User-defined functions
  • COPY commands
  • System catalog modifications

Unrestricted Mode (Development)

Use only in development/testing environments.

postgres-mcp --access-mode=unrestricted

Features:

  • ⚠️ Full read/write access
  • ✅ Parameter binding protection maintained
  • ⚠️ DDL operations allowed
  • ⚠️ No query restrictions

When to Use:

  • Local development databases
  • Testing environments
  • Staging databases (with caution)
  • Database migrations

When NOT to Use:

  • Production databases
  • Databases with sensitive data
  • Shared environments
  • Client-accessible systems

🔐 SQL Injection Prevention

Parameter Binding (Required)

All user input must use parameter binding:

# ✅ CORRECT: Using parameter binding
execute_sql(
    sql="SELECT * FROM users WHERE id = %s",
    params=[user_id]
)

# ❌ WRONG: String concatenation
execute_sql(
    sql=f"SELECT * FROM users WHERE id = {user_id}"
)

Automatic Validation

The server automatically validates all SQL queries:

# This will be REJECTED
execute_sql(
    sql="SELECT * FROM users WHERE name = '" + user_input + "'"
)
# Error: Potential SQL injection detected

Detection Patterns:

  • String concatenation in WHERE clauses
  • Dynamic table/column names without validation
  • Suspicious patterns (;, --, /*)
  • Multiple statements

Safe Dynamic Identifiers

For dynamic table/column names, use f-strings with validation:

# ✅ CORRECT: Validated identifier with parameterized values
table_name = "users"  # Validated/trusted source
execute_sql(
    sql=f"SELECT * FROM {table_name} WHERE id = %s",
    params=[user_id]
)

# ❌ WRONG: User input as identifier
execute_sql(
    sql=f"SELECT * FROM {user_table} WHERE id = %s",  # user_table from user input
    params=[user_id]
)

🔑 Authentication & Authorization

Connection Security

Always use secure connection strings:

# ✅ GOOD: SSL/TLS required
export DATABASE_URI="postgresql://user:pass@host:5432/db?sslmode=require"

# ⚠️ WARNING: Unencrypted connection
export DATABASE_URI="postgresql://user:pass@host:5432/db"

SSL Modes:

  • disable - No SSL (insecure)
  • allow - Try SSL, fall back to plain
  • prefer - Try SSL first (default)
  • require - Require SSL, fail if unavailable
  • verify-ca - Require SSL with CA verification
  • verify-full - Require SSL with hostname verification

Database User Permissions

Use principle of least privilege:

-- Read-only user for restricted mode
CREATE USER mcp_readonly WITH PASSWORD 'secure_password';
GRANT CONNECT ON DATABASE mydb TO mcp_readonly;
GRANT USAGE ON SCHEMA public TO mcp_readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO mcp_readonly;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT ON TABLES TO mcp_readonly;

-- Required extensions
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO mcp_readonly;

Password Security

Never hardcode passwords:

# ❌ WRONG: Password in config file
{
  "DATABASE_URI": "postgresql://user:password123@host:5432/db"
}

# ✅ CORRECT: Use environment variables
export DATABASE_URI="postgresql://user:${DB_PASSWORD}@host:5432/db"

# ✅ BETTER: Use secrets management
export DATABASE_URI=$(vault kv get -field=uri database/postgres)

🚨 Query Safety

Timeout Protection

Set query timeouts to prevent runaway queries:

# In postgresql.conf
statement_timeout = 30000  # 30 seconds

# Per connection
export DATABASE_URI="postgresql://user:pass@host:5432/db?options=-c statement_timeout=30s"

Resource Limits

Configure resource limits:

-- Limit memory per query
SET work_mem = '64MB';

-- Limit temporary files
SET temp_file_limit = '1GB';

-- Limit locks
SET max_locks_per_transaction = 64;

Query Complexity

Monitor query complexity:

# Use explain_query to check cost before execution
plan = explain_query(
    sql="SELECT * FROM large_table WHERE complex_condition = %s",
    params=[value],
    analyze=False  # Plan only, don't execute
)

if plan["total_cost"] > 10000:
    print("Warning: High-cost query")

📊 Audit Logging

Enable pg_stat_statements

-- In postgresql.conf
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.track = 'all'
pg_stat_statements.max = 10000

Track all queries for auditing:

# Review executed queries
queries = get_top_queries(sort_by="calls", limit=100)

Application-Level Logging

Log all MCP operations:

import logging

logging.basicConfig(
    level=logging.INFO,
    format='%(asctime)s [%(levelname)s] %(message)s',
    handlers=[
        logging.FileHandler('mcp_audit.log'),
        logging.StreamHandler()
    ]
)

# Operations are automatically logged

🔍 Security Monitoring

Regular Security Checks

# 1. Check for suspicious queries
top_queries = get_top_queries(sort_by="calls", limit=50)
# Review for unexpected patterns

# 2. Monitor connection counts
metrics = monitor_real_time(include_queries=True)
# Alert on unusual connection spikes

# 3. Scan JSON for security issues
security = json_security_scan(
    table_name="user_data",
    json_column="profile"
)

Alert on Anomalies

# Monitor for suspicious activity
alert = alert_threshold_set(
    metric_type="connection_count",
    warning_threshold=80,
    critical_threshold=95,
    check_current=True
)

if alert["alert_status"] == "critical":
    # Trigger alert
    print("ALERT: High connection count")

📚 Best Practices Checklist

Deployment

  • Use restricted mode in production
  • Enable SSL/TLS for connections
  • Use read-only database user
  • Set up connection pooling
  • Configure query timeouts
  • Enable audit logging
  • Use secrets management for credentials

Development

  • Always use parameter binding
  • Validate dynamic identifiers
  • Test with restricted mode before deployment
  • Review query plans for complex queries
  • Use hypothetical indexes before creating
  • Document security exceptions

Operations

  • Monitor query performance daily
  • Review audit logs weekly
  • Test disaster recovery procedures
  • Keep extensions updated
  • Rotate credentials regularly
  • Monitor for security advisories

🚧 Common Security Pitfalls

1. Trusting User Input

# ❌ NEVER do this
table = user_input  # User provides table name
execute_sql(f"SELECT * FROM {table}")

# ✅ Use whitelist validation
ALLOWED_TABLES = ['users', 'orders', 'products']
if table in ALLOWED_TABLES:
    execute_sql(f"SELECT * FROM {table}")

2. Exposing Sensitive Data

# ❌ Don't expose passwords
execute_sql("SELECT * FROM users")  # Contains password hashes

# ✅ Select only needed columns
execute_sql("SELECT id, username, email FROM users")

3. Missing Access Controls

# ❌ No authorization check
execute_sql(
    "SELECT * FROM orders WHERE id = %s",
    params=[order_id]
)

# ✅ Verify user owns the resource
execute_sql(
    "SELECT * FROM orders WHERE id = %s AND user_id = %s",
    params=[order_id, current_user_id]
)

📖 Security Resources

🆘 Security Incident Response

If you discover a security vulnerability:

  1. DO NOT open a public issue
  2. Email: admin@adamic.tech
  3. Include:
    • Description of vulnerability
    • Steps to reproduce
    • Potential impact
    • Suggested fix (if any)

See SECURITY.md for details.

📚 Related Documentation

See Home for more tool categories.

Clone this wiki locally