CData PostgreSQL MCP Server Gateway

This utility establishes a Model Context Protocol (MCP) endpoint specifically tailored for PostgreSQL databases, leveraging CData's connectivity technology.

:warning: Read-Only Limitation: This specific deployment is configured strictly for data retrieval operations. For full CRUD (Create, Read, Update, Delete) capabilities and streamlined initialization, please explore the comprehensive, free CData MCP Server for PostgreSQL (Beta).

Core Objective

The primary goal of this read-only server is to enable Large Language Models (such as Claude Desktop) to perform dynamic data queries against live PostgreSQL instances, powered by the CData JDBC Driver for PostgreSQL.

The CData JDBC Driver transforms PostgreSQL data schemas into a relational model accessible via JDBC. This server component encapsulates that driver, exposing the PostgreSQL data through a standardized MCP interface. Consequently, AI agents can extract live operational intelligence using plain language questions, completely bypassing the complexity of writing SQL.

Deployment Procedure

1. Repository Cloning: bash git clone https://github.com/cdatasoftware/postgresql-mcp-server-by-cdata.git cd postgresql-mcp-server-by-cdata
2. Artifact Compilation: bash mvn clean install This process yields the executable JAR: CDataMCP-jar-with-dependencies.jar
3. JDBC Driver Acquisition: Acquire and install the CData JDBC Driver for PostgreSQL from: https://www.cdata.com/drivers/postgresql/download/jdbc
4. Driver Licensing:
   • Navigate to the lib subdirectory within the driver's installation path (e.g., C:\Program Files\CData\CData JDBC Driver for PostgreSQL\ on Windows, or /Applications/CData JDBC Driver for PostgreSQL/ on Unix-like systems).
   • Execute the licensing utility: java -jar cdata.jdbc.postgresql.jar --license
   • Input your credentials and use "TRIAL" or your actual license key when prompted.
5. Data Source Configuration (Connection String Generation):
   • Launch the connection utility via the command line: java -jar cdata.jdbc.postgresql.jar.
   • Configure the necessary parameters to connect to your PostgreSQL instance and validate connectivity using the "Test Connection" feature.
   • OAuth Handling: If your database requires OAuth, authentication will occur via a browser pop-up.
   • Once validated, meticulously copy the resulting connection string.
6. Configuration File Creation (.prp): Generate a properties file (e.g., postgresql.prp) defining the server context:
   • Prefix: A short identifier for exposed tools.
   • ServerName: The descriptive name for this service instance.
   • ServerVersion: The version identifier.
   • DriverPath: Absolute path to the CData PostgreSQL JDBC JAR file.
   • DriverClass: The fully qualified name of the JDBC driver class (cdata.jdbc.postgresql.PostgreSQLDriver).
   • JdbcUrl: The connection string copied in the previous step.
   • Tables: Specify individual tables for restricted access, or leave blank to expose all accessible structures. env Prefix=postgresql ServerName=CDataPostgreSQLGateway ServerVersion=1.0 DriverPath=PATH\TO\cdata.jdbc.postgresql.jar DriverClass=cdata.jdbc.postgresql.PostgreSQLDriver JdbcUrl=jdbc:postgresql:InitiateOAuth=GETANDREFRESH; Tables=

Integrating with LLM Clients (e.g., Claude Desktop)

1. Client Configuration Update: Modify or create the client's configuration file (e.g., claude_desktop_config.json) to register this new MCP service. Append the entry to the mcpServers object:

   Windows Execution Context: json { "mcpServers": { "{classname_dash}": { "command": "PATH\TO\java.exe", "args": [ "-jar", "PATH\TO\CDataMCP-jar-with-dependencies.jar", "PATH\TO\postgresql.prp" ] }, ... } }

   Linux/Mac Execution Context: json { "mcpServers": { "{classname_dash}": { "command": "/PATH/TO/java", "args": [ "-jar", "/PATH/TO/CDataMCP-jar-with-dependencies.jar", "/PATH/TO/postgresql.prp" ] }, ... } } If the configuration file resides in a protected location, relocate it to the client's application data directory: Windows Relocation: bash cp C:\PATH\TO\claude_desktop_config.json %APPDATA%\Claude\claude_desktop_config.json Linux/Mac Relocation: bash cp /PATH/TO/claude_desktop_config.json /Users/{user}/Library/Application\ Support/Claude/claude_desktop_config.json 2. Client Restart: Completely close and restart your AI client application to ensure the newly defined MCP services are initialized.

Standalone Server Execution

To launch the gateway independently (useful for testing connectivity via stdio): ```bash java -jar /PATH/TO/CDataMCP-jar-with-dependencies.jar /PATH/TO/postgresql.prp

Constraint: Due to stdio communication protocol reliance, this server instance must execute on the same machine as the consuming client application.

Operational Usage

Once successfully integrated, the AI client automatically gains access to underlying database entities via integrated tooling. User prompts requesting analysis or statistics on the data will trigger the appropriate tool calls. Examples: * "Determine the statistical relationship between closed-won opportunities and the sector classification of the associated accounts." * "Provide a count of all outstanding support incidents recorded in the designated SUPPORT schema." * "List all scheduled commitments for the current day."

Available MCP Tools

Tool identifiers below use {servername} as a placeholder for the prefix defined in the .prp file (e.g., postgresql_...). * {servername}_get_tables: Fetches a catalog list of accessible database entities. Subsequent calls to {servername}_get_columns should be used to inspect specific schema attributes. Output format: CSV (first row contains column headers). * {servername}_get_columns: Retrieves the structural definition (columns) for a specified table. Relies on {servername}_get_tables for entity discovery. Output format: CSV (first row contains column headers). * {servername}_run_query: Executes a custom SQL SELECT statement against the PostgreSQL instance.

JSON-RPC Interaction Examples

For programmatic invocation (bypassing the AI interface), utilize JSON-RPC 2.0 payloads targeting the tools/call method.

Example: Listing Tables (postgresql_get_tables)

{
    "jsonrpc": "2.0",
    "id": 1,
    "method": "tools/call",
    "params": {
        "name": "postgresql_get_tables",
        "arguments": {}
    }
}

Example: Describing Columns (postgresql_get_columns)

{
    "jsonrpc": "2.0",
    "id": 2,
    "method": "tools/call",
    "params": {
        "name": "postgresql_get_columns",
        "arguments": {
            "table":  "Account"
        }
    }
}

Example: Executing a Query (postgresql_run_query)

{
    "jsonrpc": "2.0",
    "id": 3,
    "method": "tools/call",
    "params": {
        "name": "postgresql_run_query",
        "arguments": {
            "sql":  "SELECT * FROM \"Account\" WHERE \"IsDeleted\" = true"
        }
    }
}

Troubleshooting Guidance

1. Server Unavailability in Client: If the gateway does not appear in the client interface, ensure the client application is fully terminated (check Task Manager/Activity Monitor to confirm exit).
2. Data Retrieval Failures: Verify the correctness of the connection string; re-run the connection builder utility and update the .prp file with the verified string.
3. Connectivity Issues: For problems establishing a connection to PostgreSQL, please reach out to the CData Support Team.
4. General Feedback/MCP Issues: Engage with the user community via the CData Community forum.

Licensing Details

This server distribution is governed by the MIT License. Users retain rights to utilize, adapt, and share the software, adhering strictly to the terms specified in the accompanying LICENSE file.

Supported Data Sources (Via CData JDBC Ecosystem)

(Table structure preserved for context, representing the breadth of connectivity supported by the underlying driver)

... (many more sources omitted for brevity) ...

Access	Act CRM	Act-On	Active Directory
ActiveCampaign	Acumatica	Adobe Analytics	Adobe Commerce
Twitter Ads	Veeva CRM	Veeva Vault	Wave Financial
WooCommerce	WordPress	Workday	xBase
Xero	XML	YouTube Analytics	Zendesk
Zoho Books	Zoho Creator	Zoho CRM	Zoho Inventory
Zoho Projects	Zuora	... Dozens More