Authentication Guide

The MCP server includes built-in authentication with three methods: API tokens for machine-to-machine communication, user accounts for interactive password authentication, and OAuth for clients that sign in through a browser.

• Use an API Token for direct machine-to-machine access. Tokens are long-lived and easily managed by administrators.
• Use a User Account for interactive applications; an account is session-based, and users can manage own password access.
• Use OAuth for Claude Desktop, the Claude mobile apps, and other clients that can open a browser to sign in.

Each method can be enabled or disabled independently under http.auth.methods; see Authentication - OAuth for the setting.

• API Tokens: For machine-to-machine communication (direct HTTP/HTTPS access)
• User Accounts: For interactive authentication with session tokens
• OAuth: For clients that sign in through a browser-based login page
• Enabled by default in HTTP/HTTPS mode
• SHA256/Bcrypt hashing for secure credential storage
• Token expiration with automatic cleanup
• Per-token connection isolation for multi-user security
• Bearer token authentication using HTTP Authorization header
• Auto-reload of token and user files without server restart
• Rate limiting: Per-IP protection against brute force attacks
• Account lockout: Automatic account disabling after failed attempts
• Not required for stdio mode (Claude Desktop)

When configuring authentication:

• test your authentication in development, and verify file edits before any production changes.
• monitor your logfiles, watching for reload confirmations and errors.
• use tools that write atomically (most editors do) so you don't lose edits.
• keep backups before making any major changes or bulk edits.
• use -list-tokens or -list-users to confirm that authentication changes are performing as expected.

Note: The /mcp/v1 endpoint requires authentication (unless -no-auth is specified during endpoint configuration):

# Without token - returns 401 Unauthorized
curl -X POST http://localhost:8080/mcp/v1 \
  -H "Content-Type: application/json" \
  -d '{"jsonrpc": "2.0", "id": 1, "method": "tools/list", "params": {}}'

# Response:
{"error": "Unauthorized"}

Rate Limiting and Account Lockout

The MCP server includes built-in protection against brute force attacks through per-IP rate limiting and automatic account lockout. When a valid username is provided, the MCP server tracks the number of failed login attempts for the account and locks that account if authentication is not successful.

Automatic lockout disables an account after a specified number of consecutive failed attempts. The configurable threshold allows you to specify the maximum failed attempts (default: 0 = disabled). An administrator can** use the -enable-user CLI command to re-enable locked accounts.

Failed authentication attempts are tracked per IP address to prevent brute force attacks:

• By default, 10 failed attempts per 15-minute window per IP address invokes a lockout.
• This value is configurable - you can customize both the time window and attempt limit.
• Automatic cleanup ensures that old attempts are automatically removed from memory.
• Lockout is status-blind - rate limiting applies regardless of whether the username exists.

The address a request is counted against comes from the network connection itself, so a caller cannot choose it. Deployments behind a reverse proxy see the proxy's address on every connection and must configure http.client_ip to read the real address from a forwarding header; the server honours that header only on connections from a trusted proxy. See Client Address Resolution for details.

Configuring Rate Limiting and Lockout

To configure lockout with a configuration file, add these properties to the file:

http:
    auth:
        enabled: true
        token_file: "./postgres-mcp-tokens.yaml"
        # Rate limiting settings
        rate_limit_window_minutes: 15  # Time window for rate limiting
        rate_limit_max_attempts: 10  # Max attempts per IP per window
        # Account lockout settings
        max_failed_attempts_before_lockout: 5  # 0 = disabled

Example: Enabling Account Lockout

http:
    auth:
        enabled: true
        token_file: "./postgres-mcp-tokens.yaml"
        max_failed_attempts_before_lockout: 5
        rate_limit_window_minutes: 15
        rate_limit_max_attempts: 10

With this configuration:

• After 5 failed login attempts, the account will be automatically disabled.
• IP addresses are limited to 10 failed attempts per 15-minute window.
• The server logs show when rate limiting is enabled.

You can also configure lockout with the following environment variables:

export PGEDGE_AUTH_MAX_FAILED_ATTEMPTS_BEFORE_LOCKOUT=5
export PGEDGE_AUTH_RATE_LIMIT_WINDOW_MINUTES=15
export PGEDGE_AUTH_RATE_LIMIT_MAX_ATTEMPTS=10

Recovering a Locked Account

The following command enables a locked account:

# Re-enable a locked account
./bin/pgedge-postgres-mcp -enable-user -username alice

# Reset failed attempts counter
# (automatically reset on successful login)

Automatic File Reloading

The MCP server automatically detects and reloads changes to token and user files without requiring a server restart. This enables hot updates to authentication credentials while the server is running.

The server uses file system notifications (via fsnotify) to monitor the token and user files for changes. When a file is modified, the server automatically reloads the credentials:

• Instant updates: Changes take effect within 100ms
• No downtime: Server continues running during reload
• Thread-safe: Uses read-write locks to prevent race conditions
• Editor-friendly: Handles file deletion/recreation during saves
• Session preservation: Sessions of users whose accounts are unchanged remain valid during reload
• Debouncing: Batches rapid file changes to avoid excessive reloads

The server watches the directory containing the auth files (not the files directly) because many editors delete and recreate files when saving. This ensures that the watcher continues working after file edits.

During the reload process:

1. File system event detected (Write or Create)
2. Debounce timer (100ms) starts to batch rapid changes
3. Reload function executes with write lock
4. New credentials loaded from disk
5. Old credentials replaced atomically
6. Active sessions preserved (for user files)
7. Confirmation logged to server output

Thread Safety

All reload operations use read-write locks (sync.RWMutex) to ensure:

• Multiple concurrent read operations (authentication checks) can proceed
• Write operations (reloads) block all other operations temporarily
• No race conditions between authentication and reload
• Atomic replacement of credential data

Monitoring Reload Events

Reload events are added to the server logs as shown below:

[AUTH] Reloaded /path/to/postgres-mcp-tokens.yaml
[AUTH] Reloaded /path/to/postgres-mcp-users.yaml

Failed reloads are also logged:

[AUTH] Failed to reload /path/to/postgres-mcp-tokens.yaml:
permission denied

Auto-Reload Limitations

• File must exist: Deleting the file entirely will cause errors.
• Valid YAML required: Syntax errors can prevent files from reloading (old data is retained).
• Same location: Moving the file to a different location requires a restart.
• No cascade: Changing the token file path in your configuration requires a restart.

Implementation

The auto-reload feature is implemented using:

• fsnotify: Cross-platform file system notifications
• Watcher goroutine: Background monitoring in separate thread
• Debounce timer: 100ms delay to batch rapid changes
• RWMutex locks: Thread-safe data structure access
• Reload callbacks: TokenStore.Reload() and UserStore.Reload()

For implementation details, see:

• internal/auth/watcher.go - File watching
• internal/auth/auth.go - Token store reload
• internal/auth/users.go - User store reload

Authentication Flow

For an interactive application that uses password authentication, authentication follows a two-step process. During authentication, the user authenticates, and is then assigned a token. That token is used for secure authentication for the session:

Step 1: Authenticate the User with a Password

Call the authenticate_user tool (this tool is NOT advertised to the LLM and is only for direct client use):

curl -X POST http://localhost:8080/mcp/v1 \
  -H "Content-Type: application/json" \
  -d '{
    "jsonrpc": "2.0",
    "id": 1,
    "method": "tools/call",
    "params": {
      "name": "authenticate_user",
      "arguments": {
        "username": "alice",
        "password": "SecurePassword123!"
      }
    }
  }'

Response:

{
  "jsonrpc": "2.0",
  "id": 1,
  "result": {
    "content": [
      {
        "type": "text",
        "text": "{\"success\": true, \"session_token\": \"AQz9XfK...\", \"expires_at\": \"2024-11-15T09:30:00Z\", \"message\": \"Authentication successful\"}"
      }
    ]
  }
}

The returned token is:

• valid for 24 hours from authentication
• a Base64-encoded random 32-byte token
• strongly random, cryptographically secure

After 24 hours, the user is required to re-authenticate to get a new session token. A user may hold up to 10 sessions at once, for example on several devices.

Step 2: Use a Session Token to Authenticate

Extract the session_token from the response and use it as a Bearer token for all subsequent requests:

curl -X POST http://localhost:8080/mcp/v1 \
  -H "Authorization: Bearer AQz9XfK..." \
  -H "Content-Type: application/json" \
  -d '{
    "jsonrpc": "2.0",
    "id": 2,
    "method": "tools/call",
    "params": {
      "name": "query_database",
      "arguments": {
        "query": "Show me all users"
      }
    }
  }'

Persistent Sessions and the auth.db File

Password-login sessions survive a server restart. The server stores them in a SQLite database named auth.db, which sits in the data directory beside conversations.db. Set the data directory with the data_dir configuration option or the PGEDGE_DATA_DIR environment variable; the default is the data directory next to the server binary.

When HTTP authentication is enabled, the server needs a writable data directory, even if you use only API tokens. Without one, the server cannot create auth.db and refuses to start.

The following points describe how the server manages auth.db:

• The server creates the file on first start, with mode 0600.
• The file holds only the hashes of session tokens, never the tokens.
• The server opens the file when HTTP and authentication are both enabled.
• The server refuses to start if it cannot open or create the file.
• Deleting the file, with its auth.db-wal and auth.db-shm files, whilst the server is stopped signs every user out.
• Two servers must never share one data directory; this is unsupported.
• A second server on the same data directory refuses to start.

To reset the file, stop the server, and then delete auth.db along with any auth.db-wal and auth.db-shm files beside it. Do not run a second server against the same data directory, because the two servers would overwrite each other's changes and could restore revoked OAuth tokens. The running server holds a lock on auth.db.lock in the data directory, and a second server exits with ERROR: Cannot lock the data directory. On a file system without file locks, the server logs a warning instead, and you must ensure that only one server uses the directory.

If the server cannot open auth.db, it exits with an error naming the file, in the form ERROR: Failed to open auth database <path>: <reason>. Check that the data directory exists and that the server can write to it.

Each user may hold up to 10 sessions at once, and each lasts 24 hours. When a user logs in an eleventh time, the server ends the oldest session. The server also ends all of a user's sessions in the following cases:

• An administrator disables the user.
• An administrator deletes the user.
• The user is locked out after too many failed login attempts.
• The user's password changes, whether by -update-user or by an edit to the users file.

A password change also ends the user's OAuth grants, and so does deleting and re-adding a user. The server detects either change whilst it runs, or at the next start-up if the change was made whilst the server was stopped.

The server deletes expired sessions every minute, and once at start-up.

When the server runs in a container, keep the data directory on a persistent volume. Otherwise, the sessions are lost when the container is replaced. The Helm chart keeps the data directory on its data volume; for details, see Distributed Deployment.

Authorization: Database Access Control

The MCP server enforces authorization through the available_to_users field on each database configuration. This field restricts which authenticated users can access each database connection.

Configuring User Access

The available_to_users field accepts a list of usernames. An empty list grants access to all authenticated users.

In the following example, the configuration defines three databases with different access levels:

databases:
    - name: "production"
      host: "prod-db.example.com"
      port: 5432
      database: "myapp"
      user: "readonly_user"
      sslmode: "require"
      available_to_users: []  # All users

    - name: "staging"
      host: "staging-db.example.com"
      port: 5432
      database: "myapp_staging"
      user: "developer"
      available_to_users:
          - "alice"
          - "bob"

    - name: "development"
      host: "localhost"
      port: 5432
      database: "myapp_dev"
      user: "developer"
      available_to_users:
          - "alice"
          - "bob"
          - "charlie"

The following table shows database access for each user with the configuration above:

Per-Token Database Binding

API tokens can bind to a specific database using the -token-database flag. A bound token cannot switch to another database during its lifetime.

In the following example, the -add-token command creates a token bound to the production database:

./bin/pgedge-postgres-mcp -add-token \
    -token-note "Production Monitor" \
    -token-database "production" \
    -token-expiry "90d"

Authorization Model Summary

The server combines authentication and authorization to control database access:

• User authentication and the available_to_users field determine which databases a user can access.
• Token authentication and the -token-database flag determine which database a token can reach.
• An empty available_to_users list allows all authenticated users to access the database.
• API tokens bound to a database cannot switch to another database.

For more information about managing multiple databases, see the Multiple Database Configuration guide.

Client Implementation Example

The following example demonstrates implementing authentication in a Python client.

import requests
import json

class MCPUserClient:
    def __init__(self, base_url):
        self.base_url = base_url
        self.session_token = None
        self.token_expiry = None

    def authenticate(self, username, password):
        """Authenticate and get session token"""
        response = requests.post(
            f"{self.base_url}/mcp/v1",
            json={
                "jsonrpc": "2.0",
                "id": 1,
                "method": "tools/call",
                "params": {
                    "name": "authenticate_user",
                    "arguments": {
                        "username": username,
                        "password": password
                    }
                }
            }
        )

        result = response.json()
        if "result" in result:
            auth_data = json.loads(result["result"]["content"][0]["text"])
            self.session_token = auth_data["session_token"]
            self.token_expiry = auth_data["expires_at"]
            return True
        return False

    def call_tool(self, tool_name, arguments):
        """Call a tool using the session token"""
        if not self.session_token:
            raise Exception("Not authenticated")

        response = requests.post(
            f"{self.base_url}/mcp/v1",
            headers={
                "Authorization": f"Bearer {self.session_token}",
                "Content-Type": "application/json"
            },
            json={
                "jsonrpc": "2.0",
                "id": 2,
                "method": "tools/call",
                "params": {
                    "name": tool_name,
                    "arguments": arguments
                }
            }
        )
        return response.json()

# Usage
client = MCPUserClient("http://localhost:8080")
if client.authenticate("alice", "SecurePassword123!"):
    result = client.call_tool("query_database", {"query": "Show tables"})
    print(result)

Token Lifecycle Management

The MCP server manages tokens through creation, validation, and expiration. Understanding the token lifecycle helps clients maintain uninterrupted access.

Token Creation

The server supports two methods for creating tokens:

• The -add-token CLI command creates long-lived API tokens for machine-to-machine access.
• The authenticate_user tool creates session tokens for interactive user authentication.
• Session tokens remain valid for 24 hours after the server creates them.

Token Validation

The server validates every token before processing a request. The validation checks the token format, existence, and expiration timestamp.

• The server validates the token on every request.
• Expired tokens receive a 401 Unauthorized response.
• Invalid tokens also receive a 401 Unauthorized response.

Detecting Token Expiration

Clients can check token expiry before sending a request. This approach avoids unnecessary 401 responses from the server.

In the following example, the is_token_expired function checks a token expiry timestamp against the current time:

from datetime import datetime, timezone

def is_token_expired(expiry_str):
    """Check if a token has expired."""
    expiry = datetime.fromisoformat(
        expiry_str.replace("Z", "+00:00")
    )
    return datetime.now(timezone.utc) >= expiry

# Check before making a request
if is_token_expired(client.token_expiry):
    client.authenticate(username, password)

Automatic Re-Authentication

Clients should handle expired tokens by catching 401 errors and re-authenticating automatically. This pattern ensures seamless recovery from token expiration.

In the following example, the call_with_retry function re-authenticates when the server returns a 401 error:

def call_with_retry(client, tool_name, arguments,
                    username, password):
    """Call a tool with automatic re-authentication."""
    try:
        return client.call_tool(tool_name, arguments)
    except Exception as e:
        if "401" in str(e) or "Unauthorized" in str(e):
            client.authenticate(username, password)
            return client.call_tool(
                tool_name, arguments
            )
        raise

Mid-Conversation Token Expiration

A token can expire while a conversation is in progress. The server handles this situation gracefully without losing context.

• The next request receives a 401 response from the server.
• The client should re-authenticate and retry the failed request.
• The server preserves previous conversation context during re-authentication.
• No data is lost during the re-authentication process.

Best Practices for Programmatic Clients

Programmatic clients should follow these security and reliability practices for token management:

• Never log tokens or include tokens in error messages.
• Use environment variables to store credentials instead of hardcoding them.
• Store session tokens in memory rather than writing them to disk.
• Implement token rotation for long-lived background processes.
• Check the token expiry before each request to avoid unnecessary 401 errors.
• Set reasonable timeout values for all API requests.

For working client examples, see the Client Examples page. For token administration commands, see the Token Management page.

Updating Passwords and Tokens

You can use the following command to update a user password:

# Server running with active user sessions

# Update user password
./bin/pgedge-postgres-mcp -update-user \
  -username alice \
  -password "NewSecurePassword456!"

# Server reloads user file
# Alice's active sessions end
# New password required for next login

To perform a bulk update of session tokens, you can edit the token file directly:

# Edit token file directly for bulk changes
nano postgres-mcp-tokens.yaml

# On save, server automatically detects change:
# [AUTH] Reloaded /path/to/postgres-mcp-tokens.yaml

Error Responses

The following responses may occur as a result of authentication errors:

Note: For security reasons, specific error details are not exposed.

Endpoints That Skip Authentication

The following endpoints skip authentication, even when authentication is enabled:

Skipping authentication does not make an endpoint available. When OAuth is disabled, the two /.well-known/ paths answer 404 Not Found rather than 401 Unauthorized.

When OAuth is enabled, the authorisation server endpoints also skip bearer authentication. Most of them check their own client credentials, codes or tokens instead; the two static paths serve branding images to anyone. When dynamic registration is enabled, /oauth/register accepts a request with no credentials at all; the server limits registrations per client IP address using the rate_limit_window_minutes and rate_limit_max_attempts settings. The following paths skip bearer authentication:

• /oauth/register
• /oauth/authorize
• /oauth/token
• /oauth/device
• /oauth/device/verify
• /oauth/revoke
• /oauth/static/logo
• /oauth/static/favicon

When password login is enabled, an authenticate_user tool call to /mcp/v1 also skips bearer authentication, since that call is how a client obtains a session token. Every other request to /mcp/v1 still requires a token.

In the following example, each of the first four endpoints responds without a token.

# No token required
curl http://localhost:8080/health
curl http://localhost:8080/api/user/info
curl -X POST http://localhost:8080/api/user/logout
curl http://localhost:8080/api/openapi.json

The logout endpoint ends only the password-login session named by the bearer token in the Authorization header. It answers 204 No Content whether or not the token was a live session, and 405 Method Not Allowed for any method other than POST. It leaves API tokens and OAuth tokens alone. The web client calls it on logout as a best effort. The client clears its local token whether or not the request completes; if the request fails, the session stays valid on the server until it expires, which takes up to 24 hours.

None of the endpoints in the table exposes database contents or accepts a query, so the data they return is limited to the server's own status, whether authentication is switched on, and the shape of the API. Treat the specification as public information when deciding what network to expose a server to; if that is not acceptable for your deployment, restrict these paths at the reverse proxy rather than in the server.

To Disable Authentication (Development Only)

The following command disables authentication:

./bin/pgedge-postgres-mcp -http -no-auth

Security Considerations

See the Security Guide for comprehensive security best practices related to:

• Token storage and protection
• HTTPS requirements
• Network security
• Audit logging
• Incident response