Project 9: “The Postgres Analyst” — Model Context Protocol (MCP)

Attribute	Value
File	KIRO_CLI_LEARNING_PROJECTS.md
Main Programming Language	SQL / JSON (Config)
Coolness Level	Level 3: Genuinely Clever
Business Potential	3. Service & Support (Data Ops)
Difficulty	Level 2: Intermediate
Knowledge Area	Model Context Protocol (MCP)

What you’ll build: Configure postgres-mcp in mcp.json and run queries via Kiro.

Why it teaches MCP: Kiro gains real, typed access to your database.

Core challenges you’ll face:

• Correct connection strings.
• Using read-only DB users.

Success criteria:

• Kiro answers schema-based questions by executing real SQL.

---

Real World Outcome

You’ll configure the PostgreSQL MCP server so Kiro can directly query your database to answer questions like “How many active users signed up in December?” without you writing SQL manually. Kiro inspects your schema, generates queries, and returns formatted results.

Configuration (.kiro/settings/mcp.json):

{
  "mcpServers": {
    "postgres-analytics": {
      "command": "npx",
      "args": ["-y", "@modelcontextprotocol/server-postgres"],
      "env": {
        "POSTGRES_CONNECTION_STRING": "postgresql://readonly_user:password@localhost:5432/production_db"
      }
    }
  }
}

When you ask Kiro to analyze data:

$ kiro chat

You: "How many users registered in the last 30 days?"

Kiro: Let me query the database to find that information.

[Tool: postgres-analytics/query]
SELECT COUNT(*) as user_count
FROM users
WHERE created_at >= CURRENT_DATE - INTERVAL '30 days'
  AND created_at < CURRENT_DATE;

Result:
┌────────────┐
│ user_count │
├────────────┤
│    1,247   │
└────────────┘

There were 1,247 new user registrations in the last 30 days.

You: "Show me the top 5 products by revenue this month"

Kiro: [Tool: postgres-analytics/query]
SELECT
  p.name,
  SUM(oi.quantity * oi.price) as total_revenue
FROM products p
JOIN order_items oi ON p.id = oi.product_id
JOIN orders o ON oi.order_id = o.id
WHERE o.created_at >= DATE_TRUNC('month', CURRENT_DATE)
GROUP BY p.id, p.name
ORDER BY total_revenue DESC
LIMIT 5;

Result:
┌─────────────────────┬────────────────┐
│ name                │ total_revenue  │
├─────────────────────┼────────────────┤
│ Premium Plan (Year) │    $24,500.00  │
│ Pro Subscription    │    $18,200.00  │
│ Enterprise License  │    $15,000.00  │
│ Starter Kit         │     $8,750.00  │
│ Add-on Module       │     $3,200.00  │
└─────────────────────┴────────────────┘

The power: Kiro inspected your schema automatically and generated production-safe, read-only SQL. You never wrote a query manually.

---

The Core Question You’re Answering

“How can I give an AI assistant safe, structured access to my live database for analytics without risking data corruption?”

Traditional BI tools require learning query languages, dashboards become stale, and ad-hoc questions require engineering time. This project solves the paradox: grant database access without granting database risk.

By configuring MCP with a read-only user and schema introspection, Kiro becomes your personal data analyst that can’t break anything.

---

Concepts You Must Understand First

Stop and research these before coding:

1. PostgreSQL Connection Strings
   • What components make up a connection URI (user, host, port, database)?
   • How do you specify SSL/TLS requirements in connection strings?
   • What’s the difference between connection pooling and direct connections?
   • Book Reference: “PostgreSQL: Up and Running” by Regina Obe - Ch. 2
2. Database Permissions and Roles
   • How do you create a read-only user in PostgreSQL?
   • What’s the difference between GRANT SELECT and GRANT USAGE?
   • How do you revoke write permissions (INSERT, UPDATE, DELETE)?
   • Web Reference: PostgreSQL Documentation - GRANT
3. MCP Server Configuration
   • How does Kiro communicate with MCP servers (stdio vs HTTP)?
   • What environment variables are passed to MCP server processes?
   • How do you debug MCP server startup failures?
   • Web Reference: Model Context Protocol Specification

---

Questions to Guide Your Design

Before implementing, think through these:

1. Security Boundaries
   • Should the MCP server connect as a read-only user, or use row-level security?
   • How do you prevent Kiro from accessing sensitive tables (passwords, PII)?
   • Should you use a separate analytics database (replica)?
   • What happens if Kiro generates expensive queries (table scans)?
2. Schema Discovery
   • How does Kiro learn about your tables, columns, and relationships?
   • Should you provide table descriptions as MCP resources?
   • How do you handle dynamic schemas (frequent migrations)?
   • Should Kiro see views, or only base tables?
3. Query Safety
   • How do you prevent queries that could time out (missing indexes)?
   • Should you enforce query timeouts at the database or MCP level?
   • How do you log all SQL executed by Kiro for audit purposes?
   • What if Kiro generates syntactically correct but semantically wrong SQL?

---

Thinking Exercise

Scenario: Granting Safe Access

Given this database schema:

CREATE TABLE users (
  id UUID PRIMARY KEY,
  email VARCHAR(255) NOT NULL,
  password_hash VARCHAR(255) NOT NULL,  -- SENSITIVE
  created_at TIMESTAMP DEFAULT NOW()
);

CREATE TABLE orders (
  id UUID PRIMARY KEY,
  user_id UUID REFERENCES users(id),
  total DECIMAL(10,2),
  created_at TIMESTAMP DEFAULT NOW()
);

CREATE TABLE sessions (
  id UUID PRIMARY KEY,
  user_id UUID REFERENCES users(id),
  token VARCHAR(255) NOT NULL,  -- SENSITIVE
  expires_at TIMESTAMP
);

Design a read-only role for Kiro:

Option 1: Table-Level Permissions

CREATE ROLE kiro_readonly WITH LOGIN PASSWORD 'secure_password';
GRANT CONNECT ON DATABASE production_db TO kiro_readonly;
GRANT USAGE ON SCHEMA public TO kiro_readonly;
GRANT SELECT ON users, orders TO kiro_readonly;
-- Problem: What about the password_hash column in users?

Option 2: Column-Level Permissions

-- PostgreSQL doesn't support column-level GRANT SELECT
-- Must use views instead
CREATE VIEW users_safe AS
  SELECT id, email, created_at FROM users;

GRANT SELECT ON users_safe, orders TO kiro_readonly;
-- Better: Kiro can't see password_hash

Option 3: Row-Level Security

ALTER TABLE sessions ENABLE ROW LEVEL SECURITY;
CREATE POLICY sessions_no_access ON sessions FOR SELECT
  USING (false);  -- Block all access to sessions table

GRANT SELECT ON sessions TO kiro_readonly;
-- Kiro sees the table exists but gets 0 rows

Which approach is best for analytics? Consider:

• Transparency (Kiro knows columns exist but can’t access)
• Maintenance (adding new tables requires updating grants)
• Performance (views add overhead)

---

The Interview Questions They’ll Ask

1. “How would you design a read-only database role that can access analytics tables but not PII columns?”

2. “Explain the security implications of giving an AI direct database access. What guardrails would you implement?”

3. “How do you prevent an AI from generating expensive queries that could impact production performance?”

4. “Describe the tradeoffs between using database views vs application-level filtering for sensitive data.”

5. “How would you audit and log all SQL queries executed by an AI assistant for compliance purposes?”

6. “What strategies would you use to handle schema changes without breaking the MCP server configuration?”

---

Hints in Layers

Hint 1: Create a Read-Only User

CREATE ROLE kiro_analyst WITH LOGIN PASSWORD 'secure_password';
GRANT CONNECT ON DATABASE analytics_db TO kiro_analyst;
GRANT USAGE ON SCHEMA public TO kiro_analyst;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO kiro_analyst;

-- Ensure future tables are also read-only
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT ON TABLES TO kiro_analyst;

Hint 2: Test Permissions

psql -U kiro_analyst -d analytics_db -c "SELECT * FROM users LIMIT 1;"
# Should succeed

psql -U kiro_analyst -d analytics_db -c "DELETE FROM users WHERE id = '123';"
# Should fail: ERROR: permission denied for table users

Hint 3: Configure MCP with Connection String

{
  "mcpServers": {
    "postgres": {
      "command": "npx",
      "args": ["-y", "@modelcontextprotocol/server-postgres"],
      "env": {
        "POSTGRES_CONNECTION_STRING": "postgresql://kiro_analyst:secure_password@localhost:5432/analytics_db?sslmode=require"
      }
    }
  }
}

Hint 4: Verify MCP Server is Running

kiro chat

You: "/context show"

# Should show:
MCP Servers:
  - postgres (stdio) - Connected ✓
    Tools: query_postgres, describe_schema

---

Books That Will Help

Topic	Book	Chapter
PostgreSQL Permissions	“PostgreSQL: Up and Running” by Regina Obe	Ch. 3 (Roles and Privileges)
SQL Security	“SQL Antipatterns” by Bill Karwin	Ch. 15 (SQL Injection)
Database Design	“Designing Data-Intensive Applications” by Martin Kleppmann	Ch. 2 (Data Models)
MCP Protocol	“Model Context Protocol Specification” (online)	Server Implementation Guide

---

Common Pitfalls & Debugging

Problem 1: “MCP server fails to start with ‘connection refused’“

• Why: PostgreSQL isn’t accepting connections on the specified host/port
• Fix: Verify PostgreSQL is running: pg_isready -h localhost -p 5432
• Quick test: psql -U kiro_analyst -d analytics_db should connect

Problem 2: “Kiro can see schema but gets ‘permission denied’ on queries”

• Why: Role has USAGE on schema but not SELECT on tables
• Fix: GRANT SELECT ON ALL TABLES IN SCHEMA public TO kiro_analyst;
• Quick test: SELECT table_name FROM information_schema.role_table_grants WHERE grantee = 'kiro_analyst';

Problem 3: “Connection string in mcp.json exposed in logs”

• Why: Environment variables are logged during MCP server startup
• Fix: Use a .env file loaded by the MCP server wrapper script
• Quick test: Grep Kiro logs for password leaks

Problem 4: “Queries time out on large tables”

• Why: Kiro generated table scans without LIMIT clauses
• Fix: Set statement_timeout for the role: ALTER ROLE kiro_analyst SET statement_timeout = '30s';
• Quick test: SHOW statement_timeout; as kiro_analyst

---

Definition of Done

• Created read-only PostgreSQL role with SELECT-only permissions
• Verified role cannot execute INSERT, UPDATE, DELETE, or DROP
• Configured postgres-mcp in .kiro/settings/mcp.json with secure connection string
• Kiro can query schema and execute SELECT statements successfully
• Tested that Kiro cannot access sensitive tables (sessions, password_hash columns)
• Set query timeout to prevent runaway queries (30 seconds)
• Documented all granted permissions in README
• Logged sample query outputs showing Kiro generating and executing SQL

---