Skip to content

README.md

Latest commit

 

History

History
321 lines (236 loc) · 8.38 KB

README.md

File metadata and controls

321 lines (236 loc) · 8.38 KB
Raw

pgEdge AI DBA Workbench MCP Server

CI - Server

The MCP (Model Context Protocol) Server provides AI assistants with standardized access to PostgreSQL systems through HTTP/HTTPS endpoints with authentication.

For complete documentation, visit docs.pgedge.com.

Table of Contents

Prerequisites

Before building the server, install the following tools:

  • Go 1.24 or later.
  • PostgreSQL 14 or later for the datastore.
  • Network access to the PostgreSQL datastore.

Features

The MCP Server includes the following features:

  • The server uses HTTP/HTTPS transport with JSON-RPC 2.0.
  • The server provides SQLite-based authentication with users, sessions, and API tokens.
  • Role-based access control manages groups, privileges, and token scopes.
  • The admin panel enables management of users, groups, and tokens.
  • The server supports multiple databases with per-connection access levels.
  • MCP tools provide interfaces for database operations.
  • MCP resources expose schema and data access.
  • MCP prompts guide common workflows.
  • The LLM proxy supports Anthropic, OpenAI, Gemini, and Ollama.
  • The server manages conversation history.

Building

# Build the server
make build

# Run tests
make test

# Run linting
make lint

Quick Start

  1. Build the server:

    make build

  2. Create a user:

    ./bin/ai-dba-server -add-user -username admin

  3. Create a service token:

    ./bin/ai-dba-server -add-token

  4. Start the server:

    ./bin/ai-dba-server

Note: The ./bin/ paths above apply to source builds only. Production deployments place binaries in different locations depending on the installation method. See the Installation Guide for production paths.

Configuration

The server is configured via YAML configuration file and/or command line flags. See examples/ai-dba-server.yaml for a complete example configuration.

Command Line Options

The following general options configure the server:

  • -config string sets the path to a configuration file.
  • -addr string sets the HTTP server address; the default is :8080.
  • -tls enables TLS/HTTPS transport.
  • -cert string sets the path to a TLS certificate file.
  • -key string sets the path to a TLS key file.
  • -chain string sets the path to a TLS certificate chain file.
  • -debug enables debug logging.
  • -data-dir string sets the data directory for the auth database and conversations.

The following options configure the database connection:

  • -db-host string sets the database host.
  • -db-port int sets the database port.
  • -db-name string sets the database name.
  • -db-user string sets the database user.
  • -db-password string sets the database password.
  • -db-sslmode string sets the database SSL mode; valid values are disable, require, verify-ca, and verify-full.

The following option configures request tracing:

  • -trace-file string sets the path to a trace file for logging MCP requests and responses.

Authentication Storage

Authentication data is stored in a SQLite database (auth.db) within the data directory. By default, this is ./data/auth.db relative to the server binary.

The auth store contains:

  • Users and service accounts with password hashes, superuser flags, and group memberships.
  • API tokens with expiry dates, owner references, and optional scope restrictions.
  • Groups with nested membership and assigned connection, MCP, and admin privileges.
  • Token scopes that restrict tokens to specific connections, MCP privileges, and admin permissions.

User Management

# Add a new user (interactive)
./bin/ai-dba-server -add-user

# Add a new user (non-interactive)
./bin/ai-dba-server -add-user -username alice -password "SecurePass123!"

# List all users
./bin/ai-dba-server -list-users

# Update a user
./bin/ai-dba-server -update-user -username alice

# Enable a user (also resets failed login attempts)
./bin/ai-dba-server -enable-user -username alice

# Disable a user
./bin/ai-dba-server -disable-user -username alice

# Delete a user
./bin/ai-dba-server -delete-user -username alice

Token Management

# Add a new token (interactive)
./bin/ai-dba-server -add-token

# Add a new token (non-interactive, specifying owner)
./bin/ai-dba-server -add-token \
  -user alice \
  -token-note "Production API" \
  -token-expiry "90d"

# List all tokens
./bin/ai-dba-server -list-tokens

# Remove a token by ID or hash prefix
./bin/ai-dba-server -remove-token <token-id-or-hash>

Token Expiry Formats:

  • 30d - 30 days
  • 1y - 1 year
  • 2w - 2 weeks
  • 12h - 12 hours
  • never - Token never expires

Group Management

# Add a new RBAC group
./bin/ai-dba-server -add-group -group developers

# List all groups
./bin/ai-dba-server -list-groups

# Add a user to a group
./bin/ai-dba-server -add-member -username alice -group developers

# Remove a user from a group
./bin/ai-dba-server -remove-member -username alice -group developers

# Delete a group
./bin/ai-dba-server -delete-group -group developers

# Set superuser status for a user
./bin/ai-dba-server -set-superuser -username admin

# Remove superuser status from a user
./bin/ai-dba-server -unset-superuser -username admin

Privilege Management

# List all registered MCP privileges
./bin/ai-dba-server -list-privileges

# Grant a privilege to a group
./bin/ai-dba-server -grant-privilege -group developers -privilege query_database

# Revoke a privilege from a group
./bin/ai-dba-server -revoke-privilege -group developers -privilege query_database

# Grant connection access to a group
./bin/ai-dba-server -grant-connection -group developers -connection 1 \
  -access-level read_write

# Show privileges for a group
./bin/ai-dba-server -show-group-privileges -group developers

Token Scope Management

Token scopes restrict a token to a subset of the owner's permissions. The system supports three scope types: connections (with access levels), MCP privileges, and admin permissions.

# Show current scope for a token
./bin/ai-dba-server -show-token-scope -token-id 1

# Restrict token to specific connections
./bin/ai-dba-server -scope-token-connections -token-id 1 \
  -scope-connections "1,2,3"

# Restrict token to specific MCP tools
./bin/ai-dba-server -scope-token-tools -token-id 1 \
  -scope-tools "query_database,get_schema_info"

# Clear all scope restrictions from a token
./bin/ai-dba-server -clear-token-scope -token-id 1

Security Features

The server includes the following security features:

  • Passwords are hashed using bcrypt with cost factor 12.
  • Tokens are hashed using SHA256 for secure storage.
  • The server supports configurable per-IP rate limiting.
  • Accounts lock automatically after repeated failed login attempts.
  • Each token receives an isolated database connection pool for session separation.

Configuration File

The server searches for configuration in the following order:

  1. Path specified via -config flag
  2. /etc/pgedge/ai-dba-server.yaml (system-wide)
  3. ./ai-dba-server.yaml (binary directory)

Key configuration sections:

http:
  address: ":8080"
  tls:
    enabled: false
  auth:
    enabled: true
    max_failed_attempts_before_lockout: 5
    max_user_token_days: 90
    rate_limit_window_minutes: 15
    rate_limit_max_attempts: 10

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

Documentation

See the Administrator's Guide for detailed information.

To report an issue with the software, visit: GitHub Issues

We welcome your project contributions; for more information, see docs/developer-guide/contributing.md.

For more information, visit docs.pgedge.com.

This project is licensed under the PostgreSQL License.