11 ms·

Show HN: Cloud-Ready Postgres MCP Server

167 points by spennant 1y ago

Hey HN, I built pg-mcp, a Model Context Protocol (MCP) server for PostgreSQL that provides structured schema inspection and query execution for LLMs and agents. It's multi-tenant and runs over HTTP/SSE (not stdio) Features - Supports multiple database connections from multiple agents - Schema Introspection: Returns table structures, types, indexes and constraints; enriched with descriptions from pg_catalog. (for well documented databases) - Read-Only Queries: Controlled execution of queries via MCP. - EXPLAIN Tool: Helps smart agents optimize queries before execution. - Extension Plugins: YAML-based plugin system for Postgres extensions (supports pgvector and postgis out of the box). - Server Mode: Spin up the container and it's ready to accept connections at http://localhost:8000/sse http://localhost:8000/sse

runako 1y ago
I'm still trying to grok MCP, would be awesome if you could include usage examples in the doc. Good luck!
- mparis 1y ago
  +1 My first foray into using MCP was via Claude Desktop. Would be great if you packaged your tool such that one could add it with a few lines in their ‘~/Library/Application Support/Claude/claude_desktop_config.json’
  - jamestimmins 1y ago
    Same here. Tonight I added Whatsapp to Claude Desktop via Luke Harries' https://github.com/lharries/whatsapp-mcp https://github.com/lharries/whatsapp-mcp. Very solid intro into how it all works.
- teaearlgraycold 1y ago
  The main things that made MCP hard for me to understand at first is that it’s both transport agnostic (so no leveraging semantic HTTP) and is an async task management protocol as well as a tool use protocol. The name itself is also poorly chosen. I would call it Tool Use Protocol. Think about each MCP implementer like an agent’s input/output device.
- romanovcode 1y ago
  It's very simple and this is actually good example. 1. You add this MCP to your DB (make sure it is securely connected to your AI of choice of course) 2. Ask anything about your data, ask to make graphs, ask to make scheduled tasks, ask to analyze queries and show optimizations and so on. 3. Profit, literally. No need to pay BI companies thousands each month.
- saberience 1y ago
  It's not complicated at all. All it does is expose methods as a "tool" which is then brought back to your LLM and defined with its name, description and input parameters. E.g. Name: "MySqlTool", Description: "Allows arbitrary MySQL queries to the XYZ database", Parameters: "string: sqlToExecute" The MCP Client (e.g. Claude Desktop, Claude Code), is configured to talk to an MCP server via stdio or sse, and calls a method like "tools/list", the server just sends a list back (in JSON) of all the tools, names, descriptions, params. Then, if the LLM gets a query that mentions e.g. do a web search, or a web scraping, etc, it just outputs a tool use token then stops inferencing. Then the code calls that tool via stdio/sse (json-rpc), to the MCP server, which just runs that method, returns the result, then its added to the message history in the LLM, then inferencing runs again from the beginning.
  - runako 1y ago
    I think people who have been building with LLMs have a different view on what is complicated vs not :-) It may be easy for you to configure, but you dropped some acronyms in there that I would have to look up. I have definitely not personally set up anything like this.
    - bavell 1y ago
      It's basically a simple rpc server, there's nothing complicated going on...
      - runako 1y ago
        Then please write up and share here some example usage documentation for someone who has never used MCP. (That was my suggestion upthread.) As a side note, do people here not realize that less complicated examples are often better for learning? Have we as a community forgotten this basic truism? Since it’s not complicated, you should be able to write it up quickly, and parallel commmenters to mine suggest there is an audience for such documentation. Thanks!
1zael 1y ago
This is wild. Our company has like 10 data scientists writing SQL queries on our DB for business questions. I can deploy pg-mcp for my organization so everyone can use Claude to answer whatever is on their mind? (e.x."show me the top 5 customers by total sales") sidenote: I'm scared of what's going to happen to those roles!
- otabdeveloper4 1y ago
  Probably nothing. "Expose the database to the pointy-haired boss directly, as a service" is an idea as old a computing itself. Even SQL itself was originally an iteration of that idea. Every BI system (including PowerBI and Tableau) were supposed to be that. It doesn't work because the PHB doesn't have the domain knowledge and doesn't know which questions to ask. (No, it's never as simple as group-by and top-5.)
  - jaccola 1y ago
    I would say SQL still is that! My wife had to learn some SQL to pull reports in some non-tech finance job 10 years ago. (I think she still believes this is what I do all day…) I suppose this could be useful in that it prevents everyone in the company having to learn even the basics of SQL which is some barrier, however minimal. Also the LLM will presumably be able to see all the tables/fields and ‘understand’ them (with the big assumption that they are even remotely reasonably named) so English language queries will be much more feasible now. Basically what LLMs have over all those older attempts is REALLY good fuzziness. I see this being useful for some subset of questions.
  - pclmulqdq 1y ago
    A family friend maintains a SQL database of her knitting projects that she does as a hobby. The PHB can easily learn SQL if they want.
    - conradfr 1y ago
      But he doesn't. The project manager also won't learn behat and write tests. Your client also won't use the CMS to update their website.
revskill 1y ago
Everytime i see a cloud API_KEY is required, i'm off.
- deleted 1y ago
  [deleted]
- Jaxkr 1y ago
  Well the key it wants is for Anthropic and you can’t run those models locally.
- spennant 1y ago
  The pg-mcp server doesn’t need an API key. Those variables are only needed to run the example claude-client. You can write your own agent to talk to whatever LLM you like.
fulafel 1y ago
From docker-compose ports: - "8000:8000" This will cause Docker to expose this to the internet and even helpfully configure an allow rule to the host firewall, at least on Linux.
- rubslopes 1y ago
  Good catch. OP, exposing your application without authentication is a serious security risk! Quick anecdote: Last week, I ran a Redis container on a VPS with an exposed port and no password (rookie mistake). Within 24 hours, the logs revealed someone attempting to make my Redis instance a slave to theirs! The IP traced back to Tencent, the Chinese tech giant... Really weird. Fortunately, there was nothing valuable stored in it.
  - _lvbh 1y ago
    > The IP traced back to Tencent, the Chinese tech giant... Really weird. They're a large cloud provider in Asia like Amazon AWS or Microsoft Azure. I doubt such a tech company would make it that obvious when breaking the law.
    - rubslopes 1y ago
      I didn't know that, thank you.
  - spennant 1y ago
    I made a few assumptions about the actual deployer and their environment that I shouldn’t have… I’ll need to address this. Thanks!
tudorg 1y ago
This is great, I like in particular that there are extensions plugins. I’ll be looking at integrating this in the Xata Agent (https://github.com/xataio/agent https://github.com/xataio/agent) as custom tooling.
- spennant 1y ago
  Xata.io looks very interesting!!! I was thinking about building an intelligent agent for pg-mcp as my net project but it looks like you did a lot of the hard work already. When thinking about the "AI Stack" I usually separate concerns like this: UI <--> Agent(s) <--> MCP Server(s) <--> Tools/Resources | LLM(s)
  - tudorg 1y ago
    That's very similar to what we are thinking as well, and we'd like to separate the Agent tools into an MCP server as well as use MCP for custom tools.
oulipo 1y ago
Nice! What I'd be looking for is a MCP server where I can run in "biz/R&D exploration-mode", eg: - assume I'm working on a replica (shared about all R&D engineers) - they can connect and make read-only queries to the replica for the company data - they have a temporary read-write schema just for their current connection so they can have temporary tables and caches - temporary data is deleted when they close the session How could you make a setup like that so that when using your MCP server, I'm not worried about the model / users modifying the data, but only doing their own private queries/tables?
saberience 1y ago
Just for everyone here, the code for "building an MCP server", is importing the standard MCP package for Typescript, Python, etc, then writing as little as 10 lines of code to define something is an MCP tool. Basically, it's not rocket science. I also built MCP servers for Mysql, Twilio, Polars, etc.
- runako 1y ago
  From HN guidelines: > Please don't post shallow dismissals, especially of other people's work. A good critical comment teaches us something. We are hackers here. Building is good. Sharing is good. All this is true even if you personally know how to do what is being shared, and it is easy for you. I promise you there are people who encounter every sharing post here and do not think what is posted is easy.
  - brulard 1y ago
    I think we exactly need to hear things like that. This is what I was wondering. Why is every MCP project such a big news? Isn't it just a few lines of code?
    - runako 1y ago
      Is this really “big news” or is it a GitHub link titled “Show HN”? Is there a glitzy corporate PR page trying to sell something, or is this just code for people to read? Did Ars Technica breathlessly cover it, or did a random hacker post and share something they worked on? If it’s the work of a random hacker not promoted by media outlets, who benefits from negative comments about that person’s work? Is it possible that there are at least some people who read this site who know less about the topics covered than you do, and so might find this interesting or useful? When you post something, will it help you to improve if people post non-constructive negative feedback? Will dismissive comments like these make you more or less likely to show your work publicly? Just food for thought…
      - brulard 1y ago
jillesvangurp 1y ago
Is there more to MCP than being a simple Remote Procedure Call framework that allows AI interactions to include function calls driven by the AI model? The various documentation pages are a bit hand wavy on what the protocol actually is. But it sounds to me that RPC describes all/most of it.
- spennant 1y ago
  Indeed. Anything you do with MCP can be done in more traditional ways.
- doug_durham 1y ago
  The biggest contribution is the LLM compatible metadata that describes the tool and its argument. It is trivial to adopt. In python you can use FASTMcp to add a decorator to a function, and as long as that function returns a JSON string you are in business. The decorator extracts the arguments and doc strings and presents that to the LLM.
  - jillesvangurp 1y ago
    What makes a spec LLM compatible? I've thrown a lot of different things at gpt o1 and it generally understands them more better than I do. OpenAI specifications, unstructured text, log output, etc.
hackburg 1y ago
[dead]
ahamilton454 1y ago
I don’t understand the advantage of having the transport protocol be HTTP/SSE rather than studio especially in this case when it’s literally running locally.
- spennant 1y ago
  The use case for pg-mcp is server deployment - local running is just for dev purposes. HTTP/SSE enables multiple concurrent connections and network access, which stdio can't provide.
scottpersinger 1y ago
Where's the pagination? How does a large query here not blow up my context: https://github.com/stuzero/pg-mcp/blob/main/server/tools/query.py#L8 https://github.com/stuzero/pg-mcp/blob/main/server/tools/que...
- spennant 1y ago
  It's coming...
curtisszmania 1y ago
[dead]

167 points by spennant 1y ago

runako 1y ago

I'm still trying to grok MCP, would be awesome if you could include usage examples in the doc. Good luck!

mparis 1y ago
+1 My first foray into using MCP was via Claude Desktop. Would be great if you packaged your tool such that one could add it with a few lines in their ‘~/Library/Application Support/Claude/claude_desktop_config.json’
- jamestimmins 1y ago
  Same here. Tonight I added Whatsapp to Claude Desktop via Luke Harries' https://github.com/lharries/whatsapp-mcp https://github.com/lharries/whatsapp-mcp. Very solid intro into how it all works.
teaearlgraycold 1y ago
The main things that made MCP hard for me to understand at first is that it’s both transport agnostic (so no leveraging semantic HTTP) and is an async task management protocol as well as a tool use protocol. The name itself is also poorly chosen. I would call it Tool Use Protocol. Think about each MCP implementer like an agent’s input/output device.
romanovcode 1y ago
It's very simple and this is actually good example. 1. You add this MCP to your DB (make sure it is securely connected to your AI of choice of course) 2. Ask anything about your data, ask to make graphs, ask to make scheduled tasks, ask to analyze queries and show optimizations and so on. 3. Profit, literally. No need to pay BI companies thousands each month.
saberience 1y ago
It's not complicated at all. All it does is expose methods as a "tool" which is then brought back to your LLM and defined with its name, description and input parameters. E.g. Name: "MySqlTool", Description: "Allows arbitrary MySQL queries to the XYZ database", Parameters: "string: sqlToExecute" The MCP Client (e.g. Claude Desktop, Claude Code), is configured to talk to an MCP server via stdio or sse, and calls a method like "tools/list", the server just sends a list back (in JSON) of all the tools, names, descriptions, params. Then, if the LLM gets a query that mentions e.g. do a web search, or a web scraping, etc, it just outputs a tool use token then stops inferencing. Then the code calls that tool via stdio/sse (json-rpc), to the MCP server, which just runs that method, returns the result, then its added to the message history in the LLM, then inferencing runs again from the beginning.
- runako 1y ago
  I think people who have been building with LLMs have a different view on what is complicated vs not :-) It may be easy for you to configure, but you dropped some acronyms in there that I would have to look up. I have definitely not personally set up anything like this.
  - bavell 1y ago
    It's basically a simple rpc server, there's nothing complicated going on...
    - runako 1y ago
      Then please write up and share here some example usage documentation for someone who has never used MCP. (That was my suggestion upthread.) As a side note, do people here not realize that less complicated examples are often better for learning? Have we as a community forgotten this basic truism? Since it’s not complicated, you should be able to write it up quickly, and parallel commmenters to mine suggest there is an audience for such documentation. Thanks!

1zael 1y ago

This is wild. Our company has like 10 data scientists writing SQL queries on our DB for business questions. I can deploy pg-mcp for my organization so everyone can use Claude to answer whatever is on their mind? (e.x."show me the top 5 customers by total sales") sidenote: I'm scared of what's going to happen to those roles!

otabdeveloper4 1y ago
Probably nothing. "Expose the database to the pointy-haired boss directly, as a service" is an idea as old a computing itself. Even SQL itself was originally an iteration of that idea. Every BI system (including PowerBI and Tableau) were supposed to be that. It doesn't work because the PHB doesn't have the domain knowledge and doesn't know which questions to ask. (No, it's never as simple as group-by and top-5.)
- jaccola 1y ago
  I would say SQL still is that! My wife had to learn some SQL to pull reports in some non-tech finance job 10 years ago. (I think she still believes this is what I do all day…) I suppose this could be useful in that it prevents everyone in the company having to learn even the basics of SQL which is some barrier, however minimal. Also the LLM will presumably be able to see all the tables/fields and ‘understand’ them (with the big assumption that they are even remotely reasonably named) so English language queries will be much more feasible now. Basically what LLMs have over all those older attempts is REALLY good fuzziness. I see this being useful for some subset of questions.
- pclmulqdq 1y ago
  A family friend maintains a SQL database of her knitting projects that she does as a hobby. The PHB can easily learn SQL if they want.
  - conradfr 1y ago
    But he doesn't. The project manager also won't learn behat and write tests. Your client also won't use the CMS to update their website.

revskill 1y ago

Everytime i see a cloud API_KEY is required, i'm off.

deleted 1y ago
[deleted]
Jaxkr 1y ago
Well the key it wants is for Anthropic and you can’t run those models locally.
spennant 1y ago
The pg-mcp server doesn’t need an API key. Those variables are only needed to run the example claude-client. You can write your own agent to talk to whatever LLM you like.

fulafel 1y ago

From docker-compose ports: - "8000:8000" This will cause Docker to expose this to the internet and even helpfully configure an allow rule to the host firewall, at least on Linux.

rubslopes 1y ago
Good catch. OP, exposing your application without authentication is a serious security risk! Quick anecdote: Last week, I ran a Redis container on a VPS with an exposed port and no password (rookie mistake). Within 24 hours, the logs revealed someone attempting to make my Redis instance a slave to theirs! The IP traced back to Tencent, the Chinese tech giant... Really weird. Fortunately, there was nothing valuable stored in it.
- _lvbh 1y ago
  > The IP traced back to Tencent, the Chinese tech giant... Really weird. They're a large cloud provider in Asia like Amazon AWS or Microsoft Azure. I doubt such a tech company would make it that obvious when breaking the law.
  - rubslopes 1y ago
    I didn't know that, thank you.
- spennant 1y ago
  I made a few assumptions about the actual deployer and their environment that I shouldn’t have… I’ll need to address this. Thanks!

tudorg 1y ago

This is great, I like in particular that there are extensions plugins. I’ll be looking at integrating this in the Xata Agent (https://github.com/xataio/agent https://github.com/xataio/agent) as custom tooling.

spennant 1y ago
Xata.io looks very interesting!!! I was thinking about building an intelligent agent for pg-mcp as my net project but it looks like you did a lot of the hard work already. When thinking about the "AI Stack" I usually separate concerns like this: UI <--> Agent(s) <--> MCP Server(s) <--> Tools/Resources | LLM(s)
- tudorg 1y ago
  That's very similar to what we are thinking as well, and we'd like to separate the Agent tools into an MCP server as well as use MCP for custom tools.

oulipo 1y ago

Nice! What I'd be looking for is a MCP server where I can run in "biz/R&D exploration-mode", eg: - assume I'm working on a replica (shared about all R&D engineers) - they can connect and make read-only queries to the replica for the company data - they have a temporary read-write schema just for their current connection so they can have temporary tables and caches - temporary data is deleted when they close the session How could you make a setup like that so that when using your MCP server, I'm not worried about the model / users modifying the data, but only doing their own private queries/tables?

saberience 1y ago

Just for everyone here, the code for "building an MCP server", is importing the standard MCP package for Typescript, Python, etc, then writing as little as 10 lines of code to define something is an MCP tool. Basically, it's not rocket science. I also built MCP servers for Mysql, Twilio, Polars, etc.

runako 1y ago
From HN guidelines: > Please don't post shallow dismissals, especially of other people's work. A good critical comment teaches us something. We are hackers here. Building is good. Sharing is good. All this is true even if you personally know how to do what is being shared, and it is easy for you. I promise you there are people who encounter every sharing post here and do not think what is posted is easy.
- brulard 1y ago
  I think we exactly need to hear things like that. This is what I was wondering. Why is every MCP project such a big news? Isn't it just a few lines of code?
  - runako 1y ago
    Is this really “big news” or is it a GitHub link titled “Show HN”? Is there a glitzy corporate PR page trying to sell something, or is this just code for people to read? Did Ars Technica breathlessly cover it, or did a random hacker post and share something they worked on? If it’s the work of a random hacker not promoted by media outlets, who benefits from negative comments about that person’s work? Is it possible that there are at least some people who read this site who know less about the topics covered than you do, and so might find this interesting or useful? When you post something, will it help you to improve if people post non-constructive negative feedback? Will dismissive comments like these make you more or less likely to show your work publicly? Just food for thought…
    - brulard 1y ago

jillesvangurp 1y ago

Is there more to MCP than being a simple Remote Procedure Call framework that allows AI interactions to include function calls driven by the AI model? The various documentation pages are a bit hand wavy on what the protocol actually is. But it sounds to me that RPC describes all/most of it.

spennant 1y ago
Indeed. Anything you do with MCP can be done in more traditional ways.
doug_durham 1y ago
The biggest contribution is the LLM compatible metadata that describes the tool and its argument. It is trivial to adopt. In python you can use FASTMcp to add a decorator to a function, and as long as that function returns a JSON string you are in business. The decorator extracts the arguments and doc strings and presents that to the LLM.
- jillesvangurp 1y ago
  What makes a spec LLM compatible? I've thrown a lot of different things at gpt o1 and it generally understands them more better than I do. OpenAI specifications, unstructured text, log output, etc.

hackburg 1y ago

[dead]

ahamilton454 1y ago

I don’t understand the advantage of having the transport protocol be HTTP/SSE rather than studio especially in this case when it’s literally running locally.

spennant 1y ago
The use case for pg-mcp is server deployment - local running is just for dev purposes. HTTP/SSE enables multiple concurrent connections and network access, which stdio can't provide.

scottpersinger 1y ago

Where's the pagination? How does a large query here not blow up my context: https://github.com/stuzero/pg-mcp/blob/main/server/tools/query.py#L8 https://github.com/stuzero/pg-mcp/blob/main/server/tools/que...

spennant 1y ago
It's coming...

curtisszmania 1y ago

[dead]