Tutorials

Create custom MCP server to Query PostgreSQL - without code

H

Hoshang Mehta

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

PostgreSQL connection setup

  1. Whitelist IP: You MUST whitelist Pylar's IP (34.122.205.142) in your firewall or pg_hba.conf. Pylar will verify the connection and index your schema.

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:

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

Creating a sandboxed view in SQL IDE

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."
  4. Pylar generates the tool configuration:
  • Name: get_customer_subscription
  • Description: "Fetches subscription plan and status for a customer given their email address."
  • Parameters: email (string)

Auto-generating MCP tool from view

Step 4: Publish & Connect

  1. Click "Publish" in the right sidebar.
  2. Click "Generate Token".
  3. Copy your MCP HTTP Stream URL (https://mcp.publish.pylar.ai/mcp) and Authorization Bearer Token.

Publishing MCP server

Connect to LangChain:

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)

You can use Pylar in automation workflows like Zapier or Make.

  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.
  6. Use Case: When a new ticket arrives in Zendesk -> Call Pylar to get user plan -> Tag ticket in Zendesk.

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.

  1. Go to MCP Settings in your IDE.
  2. Add a new server.
  3. Type: SSE (Server-Sent Events).
  4. URL: Your Pylar MCP URL.
  5. Now you can ask your IDE: "Check the production DB schema for the users table" (if you exposed a schema view).

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.

By connecting Pylar to your PostgreSQL instance (or a read replica for production safety), creating sandboxed views that define exactly what agents can access, and auto-generating MCP tools with natural language, you get the best of both worlds: powerful AI capabilities with complete security and governance.

Whether you're building a customer support agent, an internal operations assistant, or a sales enablement tool, Pylar gives you granular control over data access. Your views act as security boundaries, preventing agents from accessing unauthorized tables or columns. And with instant updates—simply edit a view and all agents get the changes automatically—you can iterate quickly without redeploying anything.

Don't let security fears block your AI initiatives. Start building your PostgreSQL MCP server today.

Build your PostgreSQL MCP server in 2 minutes at pylar.ai.