iflow-mcp_azure-samples-azure-postgresql-mcp 0.1.1

Azure Database for PostgreSQL MCP Server - A Model Context Protocol Server for PostgreSQL flexible server

Azure Database for PostgreSQL MCP Server (Preview)

A Model Context Protocol (MCP) Server that let’s your AI models talk to data hosted in Azure Database for PostgreSQL according to the MCP standard!

By utilizing this server, you can effortlessly connect any AI application that supports MCP to your PostgreSQL flexible server (using either PostgreSQL password-based authentication or Microsoft Entra authentication methods), enabling you to provide your business data as meaningful context in a standardized and secure manner.

This server exposes the following tools, which can be invoked by MCP Clients in your AI agents, AI applications or tools like Claude Desktop and Visual Studio Code:

• List all databases in your Azure Database for PostgreSQL flexible server instance.
• List all tables in a database along with their schema information.
• Execute read queries to retrieve data from your database.
• Insert or update records in your database.
• Create a new table or drop an existing table in your database.
• List Azure Database for PostgreSQL flexible server configuration, including its PostgreSQL version, and compute and storage configurations. *
• Retrieve specific server parameter values. *

*Available when using Microsoft Entra authentication method

Getting Started

Prerequisites

• Python 3.10 or above
• An Azure Database for PostgreSQL flexible server instance with a database containing your business data. For instructions on creating a flexible instance, setting up a database, and connecting to it, please refer to this quickstart guide.
• An MCP Client application or tool such as Claude Desktop or Visual Studio Code.

Installation

1. Clone the azure-postgresql-mcp repository:

   git clone https://github.com/Azure-Samples/azure-postgresql-mcp.git
   cd azure-postgresql-mcp
   

   Alternatively, you can download only the azure_postgresql_mcp.py file to your working folder.

2. Create a virtual environment:

   Windows cmd.exe:

   python -m venv azure-postgresql-mcp-venv
   .\azure-postgresql-mcp-venv\Scripts\activate.bat
   

   Windows Powershell:

   python -m venv azure-postgresql-mcp-venv
   .\azure-postgresql-mcp-venv\Scripts\Activate.ps1
   

   Linux and MacOS:

   python -m venv azure-postgresql-mcp-venv
   source ./azure-postgresql-mcp-venv/bin/activate 
   

3. Install the dependencies:

   pip install mcp[cli]
   pip install psycopg[binary]
   pip install azure-mgmt-postgresqlflexibleservers
   pip install azure-identity
   

Use the MCP Server with Claude Desktop

Watch the following demo video or read on for detailed instructions.

https://github.com/user-attachments/assets/d45da132-46f0-48ac-a1b9-3b1b1b8fd638

1. In the Claude Desktop app, navigate to the “Settings” pane, select the “Developer” tab and click on “Edit Config”.

2. Open the claude_desktop_config.json file and add the following configuration to the "mcpServers" section to configure the Azure Database for PostgreSQL MCP server:

   {
       "mcpServers": {
           "azure-postgresql-mcp": {
               "command": "<path to the virtual environment>\\azure-postgresql-mcp-venv\\Scripts\\python",
               "args": [
                   "<path to azure_postgresql_mcp.py file>\\azure_postgresql_mcp.py"
               ],
               "env": {
                   "PGHOST": "<Fully qualified name of your Azure Database for PostgreSQL instance>",
                   "PGUSER": "<Your Azure Database for PostgreSQL username>",
                   "PGPASSWORD": "<Your password>",
                   "PGDATABASE": "<Your database name>"
               }
           }        
       }
   }
   

   Note: Here, we use password-based authentication to connect the MCP Server to Azure Database for PostgreSQL for testing purposes only. However, we recommend using Microsoft Entra authentication. Please refer to these instructions for guidance.

3. Restart the Claude Desktop app.

4. Upon restarting, you should see a hammer icon at the bottom of the input box. Selecting this icon will display the tools provided by the MCP Server.

You are now all set to start interacting with your data using natural language queries through Claude Desktop!

Use the MCP Server with Visual Studio Code

Watch the following demo video or read on for detailed instructions.

https://github.com/user-attachments/assets/12328e84-7045-4e3c-beab-4936d7a20c21

1. In Visual Studio Code, navigate to “File”, select “Preferences” and then choose “Settings”.

2. Search for “MCP” and select “Edit in settings.json”.

3. Add the following configuration to the “mcp” section of the settings.json file:

   {
       "mcp": {
           "inputs": [],
           "servers": {
               "azure-postgresql-mcp": {
                   "command": "<path to the virtual environment>\\azure-postgresql-mcp-venv\\Scripts\\python",
                   "args": [
                       "<path to azure_postgresql_mcp.py file>\\azure_postgresql_mcp.py"
                   ],
                   "env": {
                       "PGHOST": "<Fully qualified name of your Azure Database for PostgreSQL instance>",
                       "PGUSER": "<Your Azure Database for PostgreSQL username>",
                       "PGPASSWORD": "<Your password>",
                       "PGDATABASE": "<Your database name>"
                   }
               }
           }
       }
   }
   

   Note: Here, we use password-based authentication to connect the MCP Server to Azure Database for PostgreSQL for testing purposes only. However, we recommend using Microsoft Entra authentication. Please refer to these instructions for guidance.

4. Select the “Copilot” status icon in the upper-right corner to open the GitHub Copilot Chat window.

5. Choose “Agent mode” from the dropdown at the bottom of the chat input box.

6. Click on “Select Tools” (hammer icon) to view the Tools exposed by the MCP Server.

You are now all set to start interacting with your data using natural language queries through VS Code!

Using Microsoft Entra authentication method

To Microsoft Entra authentication method (recommended) to connect your MCP Server to Azure Database for PostgreSQL, update the MCP Server configuration in claude_desktop_config.json file (Claude Desktop) and settings.json (Visual Studio Code) with the following code:

"azure-postgresql-mcp": {
    "command": "<path to the virtual environment>\\azure-postgresql-mcp-venv\\Scripts\\python",
    "args": [
        "<path to azure_postgresql_mcp.py file>\\azure_postgresql_mcp.py"
    ],
    "env": {
        "PGHOST": "<Fully qualified name of your Azure Database for PostgreSQL instance>",
        "PGUSER": "<Your Microsoft Entra ID username or the resource name of your Azure resource with a system-assigned identity or the identity name>",
        "AZURE_USE_AAD": "True",
        "AZURE_SUBSCRIPTION_ID": "<Your Azure subscription ID>",
        "AZURE_RESOURCE_GROUP": "<Your Resource Group that contains the Azure Database for PostgreSQL instance>"
    }
}

Contributing

The Azure Database for PostgreSQL MCP Server is currently in Preview. As we continue to develop and enhance its features, we welcome all contributions! For more details, see the CONTRIBUTING.md file.

License

This project is licensed under the MIT License. For more details, see the LICENSE file.