Connection Management

The MCP server provides REST APIs for managing connections to monitored PostgreSQL databases. This feature allows users to select which database connection they want to work with for their session.

Overview

When the MCP server is configured with a datastore connection, the server can access connection information stored by the Collector. This enables users to:

• List available database connections.
• Select a connection for their session.
• Switch between databases on a connection.
• Persist their selection across requests.

Configuration

To enable connection management, configure the server's database settings to point to the same datastore used by the Collector:

database:
  host: localhost
  port: 5432
  database: ai_workbench
  user: ai_workbench
  sslmode: prefer

# Secret file must match the collector's for decryption
secret_file: /etc/ai-workbench/secret

The secret_file must contain the same secret used by the Collector for encrypting connection passwords. For full configuration details, see the Server Configuration.

Monitored Server Privileges

Each connection stores the credentials of a PostgreSQL role on the monitored server. Before creating a connection, grant that role the privileges the Workbench needs, which are the pg_monitor predefined role and CONNECT on every database you want to monitor. The Monitored Database Privileges document covers the least-privilege grants, the optional extensions, and the gaps that remain without superuser access.

REST API Endpoints

All endpoints require authentication via Bearer token.

List Connections

The following endpoint lists all available database connections from the datastore:

GET /api/v1/connections
Authorization: Bearer <token>

Response:

[
    {
        "id": 1,
        "name": "Production DB",
        "host": "db.example.com",
        "port": 5432,
        "database_name": "myapp",
        "is_monitored": true
    },
    {
        "id": 2,
        "name": "Staging DB",
        "host": "staging.example.com",
        "port": 5432,
        "database_name": "myapp_staging",
        "is_monitored": true
    }
]

List Databases

The following endpoint lists all databases on a specific connection:

GET /api/v1/connections/{id}/databases
Authorization: Bearer <token>

Response:

[
    {
        "name": "myapp",
        "owner": "postgres",
        "encoding": "UTF8",
        "size": "125 MB"
    },
    {
        "name": "analytics",
        "owner": "postgres",
        "encoding": "UTF8",
        "size": "2 GB"
    }
]

Get Current Connection

The following endpoint returns the currently selected connection for the session:

GET /api/v1/connections/current
Authorization: Bearer <token>

Response (200 OK):

{
    "connection_id": 1,
    "database_name": "myapp",
    "host": "db.example.com",
    "port": 5432,
    "name": "Production DB"
}

Response (404 Not Found):

{
    "error": "No database connection selected"
}

Set Current Connection

The following endpoint selects a connection and optionally a specific database:

POST /api/v1/connections/current
Authorization: Bearer <token>
Content-Type: application/json

{
    "connection_id": 1,
    "database_name": "analytics"
}

The database_name is optional. If not specified, the connection's default database is used.

Response:

{
    "connection_id": 1,
    "database_name": "analytics",
    "host": "db.example.com",
    "port": 5432,
    "name": "Production DB"
}

Clear Current Connection

The following endpoint clears the current connection selection:

DELETE /api/v1/connections/current
Authorization: Bearer <token>

Response: 204 No Content

Delete a Connection

The following endpoint deletes a connection; the caller must own the connection or hold the manage_connections permission, and must be able to see the connection. A connection the caller cannot see answers 404 Not Found, exactly as a missing one does, even to a holder of manage_connections; updating a connection follows the same rule:

DELETE /api/v1/connections/{id}
Authorization: Bearer <token>

Response: 204 No Content

The server answers as soon as the connection is marked deleted. From that moment the connection disappears from every listing, the API and the MCP tools, and the collector and alerter stop monitoring it. The server then removes the connection's collected metrics in the background, in small batches, and removes the connection itself once the metrics are gone. A connection with a long metrics history may take some time to clear from the datastore, but the delete request does not wait for it.

The background purge runs when the server starts, every five minutes, and immediately after each delete; a server restart resumes any purge that was interrupted.

Session Persistence

Connection selections are stored in the authentication database (auth.db) and persist across requests and server restarts. Each token has an independent connection selection.

Tool Behavior

When a connection is selected, all database tools (query_database, get_schema_info, similarity_search, execute_explain, count_rows) operate on the selected database.

If no connection is selected, the tools return an error message instructing the user to select a connection:

No database connection selected. Please select a
database connection using the web client.

Error Handling

The connection APIs return standard error responses.

In the following example, an error response uses the standard JSON format:

{
    "error": "Description of the error"
}

Related Documentation

• Managing Users and Permissions covers tokens and access control.
• API Reference provides interactive API documentation.
• Monitored Database Privileges describes the PostgreSQL grants a monitored role needs.