# Create custom MCP server to Query PostgreSQL - without code

PostgreSQL is the workhorse of modern applications. It holds your user profiles, transaction logs, and core business entities. Giving AI agents access to this data can unlock incredible automation—imagine a support agent that can check a user's subscription status instantly.

But opening up your production Postgres database to an AI agent is terrifying. One bad query could lock a table, and one prompt injection could leak user passwords.

You need a safe middle layer. Pylar provides that layer, allowing you to build **secure, read-only MCP servers** for Postgres in minutes, without writing any backend code.

## The Pylar Advantage

Pylar connects to your Postgres database and exposes _only_ the data you explicitly define in sandboxed views. It handles:

- **Connection Pooling:** No need to manage DB connections in your agent code.
- **Security:** Read-only access with strict IP whitelisting.
- **Protocol Translation:** Turns SQL results into standard MCP tool responses.

## Step-by-Step Walkthrough

### Step 1: Connect PostgreSQL to Pylar

Connecting Postgres is straightforward, but security is key.

1. **Whitelist Pylar's IP:** Add `34.122.205.142` to your database firewall (e.g., AWS Security Group, Google Cloud SQL authorized networks, or `pg_hba.conf`).
2. **Create a Read-Only User:**

```sql
    CREATE USER pylar_reader WITH PASSWORD 'secure_password';
    GRANT CONNECT ON DATABASE my_db TO pylar_reader;
    GRANT USAGE ON SCHEMA public TO pylar_reader;
    GRANT SELECT ON ALL TABLES IN SCHEMA public TO pylar_reader;
    ```

3. In Pylar, go to **Connections** -\> **PostgreSQL**.
4. Enter your Host, Port (default 5432), Database Name, Username, and Password.
   - **Naming Rule:** Use lowercase letters, numbers, and underscores only (e.g., `postgres_prod`).

### Step 2: Create a Sandboxed View

Let's build a safe view for a customer support agent. We want to expose subscription details but hide password hashes and PII.

In Pylar's SQL IDE:

```sql
-- view: support_customer_lookup
SELECT
  id as customer_id,
  email,
  subscription_plan,
  status,
  created_at
FROM users
WHERE status != 'banned'
```

This view is the _only_ thing the agent can see. The `password_hash` column isn't even in the view, so it can never be leaked.

### Step 3: Auto-Build the MCP Tool

1. **Select your view** in the right sidebar.
2. Click **"Create MCP Tool"** and choose **"Create with AI"**.
3. Type a prompt: _"Create a tool that gets subscription status by email."_

### Step 4: Publish & Connect

1. Click **"Publish"** in the right sidebar.
2. Click **"Generate Token"**.

```python
from langchain_mcp import MCPTool

# Initialize the tool with your Pylar credentials
tool = MCPTool(
    url="https://api.pylar.ai/mcp/v1/server/YOUR_SERVER_ID",
    api_key="YOUR_API_KEY"
)

# Add to your agent's toolkit
agent = create_openai_functions_agent(llm, [tool], prompt)
```

Now your LangChain agent can "talk" to your Postgres database safely.

## Advanced Use Cases for PostgreSQL Agents

PostgreSQL usually powers your live application. Here is how agents can help:

### 1. Support Ticket Triage

**Goal:** Automatically categorize and prioritize new support tickets based on customer value.

- **View:** Join `tickets` with `users` and `subscriptions`.
- **Tool Prompt:** "Create a tool that looks up a user's plan tier and recent ticket history."
- **User Query:** "Is this new ticket from a VIP customer?"

### 2. Internal Ops & Inventory

**Goal:** Allow operations teams to check stock levels or server status from Slack.

- **View:** `inventory_items` joined with `warehouse_locations`.
- **Tool Prompt:** "Create a tool that checks stock quantity for a given SKU."
- **User Query:** "Do we have enough 'Widget X' in the east coast warehouse?"

## Connecting to Any Agent Builder

### Option A: Zapier / Make (No-Code)

1. Use the **Webhooks by Zapier** (or HTTP module in Make).
2. Action: **POST**.
3. URL: `https://api.pylar.ai/mcp/v1/server/YOUR_SERVER_ID/tools/call`.
4. Headers: `Authorization: Bearer YOUR_API_KEY`.
5. Body: JSON with your tool arguments.

### Option B: General MCP Clients (Cursor, Windsurf)

Developers using AI IDEs like Cursor or Windsurf can connect Pylar to chat with their production DB safely.

## Common Use Cases

- **Customer Support:** Look up order status and shipping details.
- **Internal Ops:** Check inventory levels or server status.
- **Sales Enablement:** Qualify leads based on product usage data.

## Conclusion

PostgreSQL is one of the most powerful and flexible databases available, but giving AI agents direct access to it has always been a security risk. With Pylar, you can safely unlock your PostgreSQL data for AI agents in under 2 minutes—no coding required.