How to Configure MCP-PostgreSQL-Ops for Azure Database for PostgreSQL

MCP-PostgreSQL-Ops requires enabling specific PostgreSQL statistics parameters via ALTER SYSTEM and granting the pg_read_all_stats role to function correctly with Azure Database for PostgreSQL.

The call518/mcp-postgresql-ops repository provides a Model Context Protocol (MCP) server for PostgreSQL database operations. When connecting to Azure Database for PostgreSQL, you must configure dynamic settings since Azure's managed service restricts direct access to the postgresql.conf file, requiring the use of ALTER SYSTEM commands instead.

Prerequisites for MCP-PostgreSQL-Ops on Azure

Before connecting MCP-PostgreSQL-Ops to Azure Database for PostgreSQL, you must enable specific PostgreSQL statistics tracking parameters. Azure disables many monitoring features by default to reduce overhead, but the MCP server relies on these views for its 70+ built-in tools.

Required Statistics Parameters

The following settings must be enabled using ALTER SYSTEM (as documented in README.md lines 549-564):

• track_activities: Enables the pg_stat_activity view required by tools like get_active_connections
• track_counts: Provides table statistics via pg_stat_all_tables for bloat analysis and vacuum monitoring
• track_functions: Required for get_user_functions_stats (set to pl or all)
• track_io_timing: Optional but recommended for accurate I/O statistics in get_table_io_stats and get_index_io_stats

Query Analysis Extensions

For tools like get_pg_stat_statements_top_queries, you must enable the pg_stat_statements extension:

1. Add pg_stat_statements to shared_preload_libraries via the Azure Portal (Server parameters blade)
2. Create the extension: CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
3. Optionally tune pg_stat_statements.max and pg_stat_statements.track via the portal

Step-by-Step Azure Configuration

Follow these steps to configure Azure Database for PostgreSQL for MCP-PostgreSQL-Ops compatibility.

Enable Statistics Tracking with ALTER SYSTEM

Connect to your Azure PostgreSQL instance using the admin account (format: admin@servername) and execute:

-- Enable basic activity tracking
ALTER SYSTEM SET track_activities = 'on';
ALTER SYSTEM SET track_counts = 'on';

-- Enable function statistics for get_user_functions_stats
ALTER SYSTEM SET track_functions = 'pl';

-- Enable I/O timing for detailed performance metrics
ALTER SYSTEM SET track_io_timing = 'on';

-- Apply changes without restart
SELECT pg_reload_conf();

These commands correspond to Method 3: Dynamic Configuration in the repository documentation (lines 549-564).

Configure pg_stat_statements Extension

For query analysis capabilities:

-- Create the extension (requires shared_preload_libraries configuration first)
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

In the Azure Portal, navigate to Server parameters → shared_preload_libraries and ensure pg_stat_statements is included in the list.

Grant Required Database Roles

The MCP user requires read access to system statistics. As documented in lines 580-590 of the README:

-- Grant read-only statistics access
GRANT pg_read_all_stats TO <mcp_user>;

-- Alternative: Grant specific table permissions if not using the role
-- GRANT SELECT ON pg_stat_activity TO <mcp_user>;

Replace <mcp_user> with the username configured in your MCP client environment.

Environment Configuration for Azure Connectivity

The src/mcp_postgresql_ops/functions.py file reads environment variables to construct the database connection string. For Azure Database for PostgreSQL, use the following format in your .env file (based on .env.example):

POSTGRES_HOST=your-server-name.postgres.database.azure.com
POSTGRES_PORT=5432
POSTGRES_USER=your-username@your-server-name
POSTGRES_PASSWORD=your-password
POSTGRES_DB=postgres

Critical Azure-specific formatting notes:

• The host must use the full Azure domain: servername.postgres.database.azure.com
• The username must include the server name suffix: username@servername (required for Azure authentication routing)
• Port remains the standard 5432 (Azure does not allow custom ports)

The src/mcp_postgresql_ops/mcp_main.py file registers all 70+ tools (e.g., get_active_connections, get_table_bloat_analysis, get_pg_stat_statements_top_queries) which automatically use this connection configuration.

Code Examples

Complete Azure Configuration Script

Run this once as the Azure PostgreSQL admin to prepare the instance for MCP-PostgreSQL-Ops:

-- Enable required statistics parameters
ALTER SYSTEM SET track_activities = 'on';
ALTER SYSTEM SET track_counts = 'on';
ALTER SYSTEM SET track_functions = 'pl';
ALTER SYSTEM SET track_io_timing = 'on';

-- Apply configuration changes
SELECT pg_reload_conf();

-- Install query analysis extension (requires portal configuration first)
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- Grant statistics access to MCP application user
GRANT pg_read_all_stats TO mcp_user;

MCP Client Environment Configuration

Create a .env file for your MCP client:

POSTGRES_HOST=myazuredb.postgres.database.azure.com
POSTGRES_PORT=5432
POSTGRES_USER=mcp_app@myazuredb
POSTGRES_PASSWORD=SecurePassword123!
POSTGRES_DB=postgres

Docker Compose Configuration

If running MCP-PostgreSQL-Ops via Docker, mount your Azure-specific environment file:

services:
  mcp-postgresql-ops:
    image: mcp-postgresql-ops:latest
    env_file:
      - .env.azure  # Contains Azure-specific connection variables

    volumes:
      - ./.env.azure:/app/.env:ro

Key Implementation Files

Understanding these source files helps troubleshoot Azure connectivity issues:

File	Purpose for Azure Configuration
src/mcp_postgresql_ops/functions.py	Reads POSTGRES_HOST, POSTGRES_USER, and other environment variables to establish the Azure connection string.
src/mcp_postgresql_ops/mcp_main.py	Registers all MCP tools (e.g., get_server_info, get_active_connections) that query Azure's system views.
.env.example	Template showing required environment variables; copy this to create your Azure-specific configuration.
README.md (lines 549-564)	Documents the Dynamic Configuration method using ALTER SYSTEM required for Azure managed instances.
README.md (lines 580-590)	Details the pg_read_all_stats role assignment needed for read-only monitoring tools.
docker-compose.yml	Reference for containerized deployment; modify the env_file path to point to your Azure configuration.

Summary

Configuring MCP-PostgreSQL-Ops for Azure Database for PostgreSQL requires enabling specific PostgreSQL statistics parameters that are disabled by default in managed environments:

• Enable track_activities, track_counts, track_functions, and track_io_timing using ALTER SYSTEM commands
• Install the pg_stat_statements extension via the Azure Portal and CREATE EXTENSION
• Grant the pg_read_all_stats role to your MCP application user
• Configure environment variables using Azure's specific host (*.postgres.database.azure.com) and username (user@server) formats

Once configured, all 70+ MCP tools—including get_active_connections, get_table_bloat_analysis, and get_pg_stat_statements_top_queries—function identically against Azure Database for PostgreSQL as they do with self-hosted instances.

Frequently Asked Questions

Does MCP-PostgreSQL-Ops require code modifications to work with Azure Database for PostgreSQL?

No code modifications are necessary. The call518/mcp-postgresql-ops repository works with Azure Database for PostgreSQL using standard PostgreSQL connection protocols. However, you must enable specific statistics parameters using ALTER SYSTEM commands since Azure restricts direct file system access to postgresql.conf, as documented in the README.md lines 549-564.

Why must I use ALTER SYSTEM instead of editing postgresql.conf on Azure?

Azure Database for PostgreSQL is a managed service that prohibits direct access to the server's file system, including the postgresql.conf file. The ALTER SYSTEM command provides a SQL interface to modify configuration parameters, which Azure persists and applies dynamically after running SELECT pg_reload_conf(). This method is explicitly described in the repository's Dynamic Configuration section for managed services like Azure, AWS RDS, and GCP.

What specific permissions does the MCP user need on Azure PostgreSQL?

The MCP user requires the pg_read_all_stats role to access system monitoring views such as pg_stat_activity, pg_stat_all_tables, and pg_stat_statements. Grant this role using GRANT pg_read_all_stats TO <mcp_user>; as documented in README.md lines 580-590. Additionally, ensure the user has standard CONNECT and USAGE permissions on the target database.

Can I configure the required PostgreSQL parameters through the Azure Portal instead of SQL commands?

Yes, you can configure most parameters through the Azure Portal by navigating to Server parameters under your PostgreSQL server settings. Parameters like track_activities, track_counts, and track_io_timing can be set directly in the portal interface. However, for consistency with the repository's documentation and to ensure proper ordering of operations (such as immediately running pg_reload_conf()), using the SQL ALTER SYSTEM approach described in the Dynamic Configuration section is often more reliable for automation scripts.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}