MCP Spotlight: PostgreSQL MCP Server — The Database Bridge With Read-Only Default, Query Allow-List, Parameterized Statements, and the Schema-Aware Default for Agents
Server: @modelcontextprotocol/server-postgres by Anthropic · CrystalDBA postgres-mcp (performance-tuned variant)
License: MIT · Tools: ~12 (query, schema, list_tables, describe_table, indexes, explain, list_schemas)
Coverage: Any PostgreSQL database — schema inspection, read-only queries, performance analysis, optional write-mode
Security: Read-only default · Query allow-list / statement-type filter · Parameterized SQL · Connection-string scoping
GitHub: github.com/modelcontextprotocol/servers/tree/main/src/postgres
Docker: mcp/postgres · MCP Tracker: glama.ai/mcp/servers/modelcontextprotocol/postgres
Every data-driven agent eventually needs the database. The naive "give the agent raw SQL with full DDL/DML access" approach can drop tables, leak PII, corrupt data with a single botched query. The "wrap PostgreSQL as 100+ MCP tools" approach bloats the context window. The "give the agent nothing" approach blocks the entire data-analytics, dashboard-generation, and ad-hoc-querying category.
The official PostgreSQL MCP Server by Anthropic is the bridge that resolves this. ~12 focused tools covering schema inspection, read-only queries, performance analysis, and (gated) write operations. Read-only is the default; explicit opt-in for write-mode, with statement-type filtering. MIT-licensed, official Anthropic-maintained.
This is the database-access default for AI agents in 2026. Small surface, hard safety boundaries, schema-aware.
The Architecture: Read-Only Default, Gated Write Mode
The PostgreSQL MCP Server's defining design choice: read-only is the default. The agent can:
- List databases and schemas
- Describe table structure (columns, types, indexes, constraints)
- Run SELECT queries
- Run EXPLAIN to analyze query performance
- View prepared statements, view indexes
By default, the agent cannot:
- Run INSERT, UPDATE, DELETE
- Run DROP, ALTER, TRUNCATE
- Run GRANT, REVOKE
- Run any DDL (CREATE, ALTER, DROP)
For agents that need write access, explicit opt-in via configuration:
# Read-only mode (default, safe)
DATABASE_URI=postgresql://user:pass@host:5432/db
# Read-write mode (explicit opt-in, gated by HITL)
DATABASE_URI=postgresql://user:pass@host:5432/db
DATABASE_WRITE_ENABLED=true # opt-in to write operations
Even in write-mode, statement-type filtering is the right pattern:
# Allow INSERT only (no UPDATE, DELETE, DROP)
DATABASE_ALLOWED_STATEMENTS=SELECT,INSERT
# Allow SELECT + INSERT + UPDATE (no DELETE, no DDL)
DATABASE_ALLOWED_STATEMENTS=SELECT,INSERT,UPDATE
The agent's blast radius is bounded at the server level. The operator decides what's writable, the server enforces it.
The Killer Combination: Schema + Query
The two highest-leverage primitives:
Schema inspection
list_schemas()
→ Returns: ["public", "analytics", "audit", "billing"]
list_tables(schema="public")
→ Returns:
[
{"table": "users", "schema": "public", "type": "table"},
{"table": "orders", "schema": "public", "type": "table"},
{"table": "products", "schema": "public", "type": "table"}
]
describe_table(table="users", schema="public")
→ Returns:
{
"columns": [
{"name": "id", "type": "uuid", "nullable": false, "primary_key": true},
{"name": "email", "type": "varchar(255)", "nullable": false, "unique": true},
{"name": "created_at", "type": "timestamptz", "nullable": false, "default": "now()"},
// ...
],
"indexes": [...],
"foreign_keys": [...]
}
The agent discovers the schema before writing queries. The agent sees the column names, the types, the constraints, the indexes — it doesn't have to guess.
Query execution
query(
sql="SELECT id, email, created_at FROM users WHERE created_at > $1 LIMIT 100",
params=["2026-01-01"]
)
→ Returns the rows as JSON
The query is parameterized — $1 is the parameter, params provides the value. SQL injection is impossible because the parameter substitution happens via PostgreSQL's protocol, not string concatenation.
For complex queries:
query(
sql="SELECT u.email, COUNT(o.id) AS order_count, SUM(o.total) AS total_spent
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE u.created_at > $1
GROUP BY u.id, u.email
ORDER BY total_spent DESC
LIMIT 50",
params=["2026-01-01"]
)
The agent writes the SQL, the server executes it with parameterized values, the rows come back as JSON.
The Read-Only Mode Pattern
In read-only mode, the server rejects any non-SELECT statement:
query(sql="DELETE FROM users WHERE id = $1", params=["user_123"])
→ Returns error:
{
"code": "readonly_mode",
"message": "Database is in read-only mode. DELETE is not permitted.
Set DATABASE_WRITE_ENABLED=true to enable write operations."
}
The agent gets a clear, actionable error. The blast radius is bounded by configuration, not by the agent's behavior.
The Statement-Type Filter Pattern
In write mode with filtering:
DATABASE_ALLOWED_STATEMENTS=SELECT,INSERT
query(sql="UPDATE users SET email = $1 WHERE id = $2", params=["new@example.com", "user_123"])
→ Returns error:
{
"code": "statement_not_allowed",
"message": "UPDATE is not in the allowed statement types: SELECT, INSERT"
}
The agent can INSERT but cannot UPDATE. The operator controls the statement types at the configuration level.
The EXPLAIN Pattern
For agents that optimize queries:
query(
sql="SELECT * FROM users WHERE email = $1",
params=["kevin@centerbit.co"]
)
→ Returns rows
query(
sql="EXPLAIN ANALYZE SELECT * FROM users WHERE email = $1",
params=["kevin@centerbit.co"]
)
→ Returns query plan with timing:
{
"Query Plan": [
{"Plan": "Index Scan using idx_users_email on users",
"Index Cond": "(email = 'kevin@centerbit.co')",
"Actual Total Time": "0.025 ms"}
]
}
The agent reads the query plan, identifies slow operations, proposes indexes or rewrites. Performance debugging at the agent level.
The Multi-Schema Pattern
For multi-tenant databases with schema-per-tenant:
list_schemas()
→ Returns: ["public", "tenant_acme", "tenant_globex", "tenant_initech"]
describe_table(schema="tenant_acme", table="users")
→ Returns the tenant_acme.users schema
query(
sql="SELECT * FROM tenant_acme.orders WHERE status = $1",
params=["pending"]
)
→ Returns tenant_acme orders
The agent navigates schemas, queries per-tenant data, respects schema boundaries. Per-tenant isolation at the schema level.
The Performance Analysis Pattern (CrystalDBA postgres-mcp)
The CrystalDBA variant adds performance tools on top of the read-only default:
| Tool | Purpose |
|---|---|
explain_query | Run EXPLAIN ANALYZE for a query |
get_top_queries | List the slowest queries (from pg_stat_statements) |
get_table_stats | Show table-level statistics (row count, size, dead tuples, vacuum status) |
get_index_usage | Show index usage (used vs unused indexes) |
get_lock_waits | Show current lock contention |
recommend_indexes | Suggest indexes based on query patterns (Anytime Algorithm) |
get_database_health | Overall health metrics |
The CrystalDBA variant is the performance-aware PostgreSQL MCP — same safety defaults, additional analytics.
Facio Integration
{
"mcpServers": {
"postgres-readonly": {
"command": "npx",
"args": ["-y", "@modelcontextprotocol/server-postgres", "${env.DATABASE_URI}"]
}
}
}
Or via Docker:
{
"mcpServers": {
"postgres": {
"command": "docker",
"args": ["run", "-i", "--rm",
"-e", "DATABASE_URI=postgresql://readonly_user:pass@host:5432/db",
"mcp/postgres"]
}
}
}
Critical: use a read-only database user as the credential. Even if the MCP server's read-only default is somehow bypassed, the database itself rejects writes from the readonly role.
-- Create a read-only role for the agent
CREATE ROLE mcp_readonly LOGIN 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;
Defense-in-depth: read-only MCP server + read-only database role. The agent can only read, regardless of any configuration mistake.
Facio's audit trail captures every PostgreSQL MCP call with the tool, the SQL (with parameter placeholders, NOT the actual values), the row count, the execution time, and the user context. For a regulated team (SOC2, ISO 27001, GDPR), this is the complete database-access record: "Agent at 14:32 UTC ran query 'SELECT id, email FROM users WHERE created_at > $1' with params['2026-01-01'], returned 247 rows in 12ms."
For HITL workflows, the database-access surface is highly sensitive:
| Tool | Severity | Suggested Gate |
|---|---|---|
list_schemas, list_tables, describe_table | Read (schema) | None — autonomous |
query (SELECT) | Read (data) | None — autonomous (with row limit + PII redaction) |
query (EXPLAIN, EXPLAIN ANALYZE) | Read (performance) | None — autonomous |
query (INSERT) | Write, contextual | Hard confirm + row preview |
query (UPDATE) | Write, contextual | Hard confirm + reason + WHERE preview |
query (DELETE) | Write, destructive | Hard confirm + reason + row count preview |
query (DROP, ALTER, TRUNCATE) | Write, destructive | Hard confirm + reason OR BLOCK if not in allow-list |
query (CREATE TABLE) | Write, structural | Hard confirm + reason |
The query tool deserves special attention — it's a SQL execution primitive. Facio should:
- Detect the statement type via parsing (SELECT vs INSERT vs UPDATE vs DELETE vs DDL)
- Apply severity based on the type
- Apply additional rules for UPDATE/DELETE: require the WHERE clause to be visible in the approval card, the row count must be reasonable
- Apply block rules for DDL by default: DROP, ALTER, TRUNCATE require explicit configuration to enable
- Apply row-count limits: a query returning 1M rows is suspicious; cap at 10K by default
For multi-database setups (one DB per service, per tenant), the pattern is one MCP server per database with its own connection string. The agent switches context per database, the audit trail is per-database.
For multi-tenant SaaS setups, per-tenant database + per-tenant readonly user is the gold standard. Each tenant has their own schema (or database), the agent uses tenant-specific credentials, the audit trail records which tenant's data was accessed.
Quickstart
# 1. Create a read-only database user
psql -c "CREATE ROLE mcp_readonly LOGIN PASSWORD '...';"
psql -c "GRANT SELECT ON ALL TABLES IN SCHEMA public TO mcp_readonly;"
# 2. Install the MCP server
npm install -g @modelcontextprotocol/server-postgres
# 3. Configure your MCP client (read-only mode)
{
"mcpServers": {
"postgres": {
"command": "npx",
"args": ["-y", "@modelcontextprotocol/server-postgres", "postgresql://mcp_readonly:pass@localhost:5432/mydb"]
}
}
}
# 4. First prompts
# "What tables are in the public schema?"
# "Describe the users table structure"
# "Find the 10 most recent users"
# "Explain the query plan for: SELECT * FROM orders WHERE status = 'pending'"
For write mode (use with extreme caution + HITL gating):
{
"mcpServers": {
"postgres": {
"command": "npx",
"args": ["-y", "@modelcontextprotocol/server-postgres"],
"env": {
"DATABASE_URI": "postgresql://user:pass@localhost:5432/mydb",
"DATABASE_WRITE_ENABLED": "true",
"DATABASE_ALLOWED_STATEMENTS": "SELECT,INSERT"
}
}
}
}
Use Cases
Ad-hoc analytics: "How many new users signed up this week?" Query → row count → answer.
Dashboard generation: "Generate a dashboard for sales: total revenue, top products, growth rate." Multiple queries → structured dashboard.
Data exploration: "What columns does the users table have? Show me the schema." Schema discovery → documentation.
Query optimization: "Why is this query slow? Explain the plan, suggest an index." EXPLAIN → analysis → recommendation.
Data migration verification: "Compare row counts in source and target after the migration." Multi-query comparison.
Compliance auditing: "Find every user with the email pattern that matches our test accounts." Pattern-based query.
A/B test analysis: "Compare conversion rates between the control and treatment cohorts." Statistical query → result.
Performance regression hunting: "What queries became slow in the last 24h?" pg_stat_statements query → trend.
Schema documentation: "Generate the data dictionary from the schema." Schema introspection → doc generation.
Backup verification: "Verify the backup completed successfully by counting rows in key tables." Row count comparison.
Index usage analysis: "Find unused indexes. Suggest dropping them." Index usage query → recommendation.
Table bloat check: "Find the most bloated tables, suggest vacuuming." pg_stat_* tables → analysis.
Customer cohort analysis: "Group users by signup month, show retention rates." Cohort query → analysis.
Revenue reporting: "Calculate MRR for each plan, show growth MoM." Aggregated query → reporting.
Anomaly detection: "Find transactions with amounts above 10x the customer's average." Pattern query → anomaly list.
Data quality checks: "Find duplicate emails, missing fields, invalid dates." Multi-validation query.
Audit log analysis: "Show me all admin actions in the last 7 days, grouped by admin." Audit query → analysis.
Foreign-key integrity: "Find orphaned records in child tables." FK check → integrity report.
Schema drift detection: "Compare current schema to last week's schema, list changes." Schema diff.
The Read-Only Default Pattern
The PostgreSQL MCP server's defining innovation — read-only is the default, write-mode is explicit opt-in — is the design lesson every "data-access-as-MCP" server should copy.
Why read-only-default wins:
- Safe baseline — agents can browse, analyze, summarize without risk
- Explicit opt-in — the operator controls what's writable
- Server-enforced — the configuration is checked before every query
- Defense-in-depth — pair with a read-only database role for belt-and-suspenders
- Predictable blast radius — even in write-mode, statement-type filtering bounds the risk
The pattern applies to:
- MySQL MCP — same default
- MongoDB MCP — same default
- BigQuery MCP — same default
- Snowflake MCP — same default
For any database-as-MCP server, read-only is the right default. Operators opt in to write access, never opt out.
The Parameterized SQL Pattern
The PostgreSQL MCP server's second defining innovation — parameterized queries via $1, $2, ... placeholders — is the design lesson every "SQL-as-MCP" server should copy.
Why parameterized wins:
- SQL injection is impossible — values are bound via the protocol, not concatenated
- Performance — PostgreSQL can cache the prepared statement
- Type safety — the database enforces type matching
- Audit clarity — the query structure is in the audit, the values are separate
# Bad: string concatenation
query(sql="SELECT * FROM users WHERE email = '" + user_input + "'")
→ SQL injection risk
# Good: parameterized
query(sql="SELECT * FROM users WHERE email = $1", params=[user_input])
→ SQL injection impossible
The MCP server's API requires the params array. The agent can't pass raw SQL strings with embedded values — it must use placeholders.
Bottom Line
The PostgreSQL MCP Server is the database-access default for AI agents in 2026. ~12 focused tools, read-only default, statement-type filtering, parameterized SQL, MIT-licensed, official Anthropic-maintained.
For any agent that participates in data workflows — ad-hoc analytics, dashboard generation, query optimization, schema documentation, compliance auditing, performance regression hunting — this is the bridge. The agent reads schemas, runs parameterized SELECTs, explains query plans, suggests indexes. All with read-only defaults, all with parameterized SQL.
For the broader MCP ecosystem, the PostgreSQL pattern is the design lesson every "data-access-as-MCP" server should copy. Read-only default + statement-type filter + parameterized queries. When the database primitive is right, the agent's data access is right.
npx -y @modelcontextprotocol/server-postgres postgresql://readonly_user:pass@host:5432/db (with a read-only database role) and your agent has the database.
MCP Spotlight is a series covering servers that give AI agents real capabilities. Every server is evaluated for design clarity, ecosystem impact, and integration fit with Facio's HITL-first agent runtime.