Enterprise-grade security practices for PostgreSQL MCP Server.

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

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

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

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}" )

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

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] )

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

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;

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)

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"

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;

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")

-- 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)

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

# 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" )

# 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")

• 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

• 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

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

# ❌ 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}")

# ❌ Don't expose passwords execute_sql("SELECT * FROM users") # Contains password hashes # ✅ Select only needed columns execute_sql("SELECT id, username, email FROM users")

# ❌ 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] )

• OWASP SQL Injection Prevention
• PostgreSQL Security Best Practices
• CWE-89: SQL Injection

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.

• MCP Configuration - Secure configuration
• Quick Start - Security modes
• Monitoring & Alerting - Security monitoring
• Troubleshooting - Security issues

See Home for more tool categories.