> ## Documentation Index
> Fetch the complete documentation index at: https://docs.civic.com/llms.txt
> Use this file to discover all available pages before exploring further.

# PostgreSQL Server

> Connect to PostgreSQL databases with advanced security features, query optimization, and database health monitoring. Supports both restricted (read-only with SQL validation) and unrestricted modes.

# PostgreSQL Server

## Overview

The PostgreSQL server provides comprehensive database integration with advanced security features, query optimization tools, and database health monitoring. Built by [Crystal DBA](https://github.com/crystaldba/postgres-mcp), it supports both restricted (read-only with SQL validation) and unrestricted modes.

## Getting Started

<Steps>
  <Step title="Connect to PostgreSQL">
    Add the PostgreSQL server to your Civic environment through the server directory.
  </Step>

  <Step title="Configure Database Connection">
    Provide your PostgreSQL connection string with proper authentication and SSL settings.
  </Step>

  <Step title="Test Connection">
    Start with a simple command like `list schemas` to verify database connectivity.
  </Step>
</Steps>

## What You Can Do

<CardGroup cols={2}>
  <Card title="Schema Exploration" icon="magnifying-glass">
    Browse database schemas, tables, views, sequences, and extensions
  </Card>

  <Card title="Query Optimization" icon="gauge-high">
    Analyze execution plans, recommend indexes, and optimize query performance
  </Card>

  <Card title="Health Monitoring" icon="heart-pulse">
    Monitor database health, connections, buffer cache, and system metrics
  </Card>

  <Card title="Performance Analysis" icon="chart-line">
    Identify slow queries, resource usage patterns, and bottlenecks
  </Card>
</CardGroup>

## Use Cases

<AccordionGroup>
  <Accordion title="Database Administration" icon="database">
    * **Schema Management**: Explore database structure and relationships
    * **Object Discovery**: Find tables, views, sequences, and extensions across schemas
    * **Health Monitoring**: Track database health metrics and system performance
    * **Connection Analysis**: Monitor active connections and resource usage
  </Accordion>

  <Accordion title="Query Optimization" icon="rocket">
    * **Execution Plan Analysis**: Understand query performance with EXPLAIN
    * **Index Recommendations**: Get AI-powered index suggestions for better performance
    * **Workload Analysis**: Analyze frequently executed queries for optimization opportunities
    * **Hypothetical Indexes**: Test index scenarios without creating actual indexes
  </Accordion>

  <Accordion title="Performance Monitoring" icon="chart-bar">
    * **Slow Query Detection**: Identify resource-intensive and slow-running queries
    * **Resource Usage Tracking**: Monitor CPU, memory, and I/O usage patterns
    * **Buffer Cache Analysis**: Optimize memory usage and cache hit ratios
    * **Vacuum and Maintenance**: Monitor table maintenance and space usage
  </Accordion>
</AccordionGroup>

***

## Available Tools (9)

<AccordionGroup>
  <Accordion title="Database Discovery (3 tools)">
    Explore and understand your PostgreSQL database structure.

    **Schema and Object Management**

    <AccordionGroup>
      <Accordion title="list_schemas">
        List all schemas in the database for structural overview
      </Accordion>

      <Accordion title="list_objects">
        List objects (tables, views, sequences, extensions) within a specific schema
      </Accordion>

      <Accordion title="get_object_details">
        Get detailed information about specific database objects including structure and metadata
      </Accordion>
    </AccordionGroup>

    <Info>
      **Object Types Supported**: tables, views, sequences, extensions - specify the type for targeted exploration.
    </Info>
  </Accordion>

  <Accordion title="Query Analysis & Optimization (3 tools)">
    Advanced query performance analysis and optimization tools.

    <AccordionGroup>
      <Accordion title="explain_query">
        Analyze SQL execution plans with cost estimates and optional real-time statistics

        * **Parameters**: SQL query, analyze mode (true/false), hypothetical indexes
        * **Features**: Shows execution plans, cost estimates, and actual performance data
      </Accordion>

      <Accordion title="analyze_workload_indexes">
        Analyze frequently executed queries and recommend optimal indexes

        * **Methods**: DTA (Database Tuning Advisor) or LLM-based analysis
        * **Limits**: Configurable maximum index size constraints
      </Accordion>

      <Accordion title="analyze_query_indexes">
        Analyze specific queries (up to 10) and get targeted index recommendations

        * **Input**: List of SQL queries for analysis
        * **Output**: Optimized index suggestions with performance impact estimates
      </Accordion>
    </AccordionGroup>

    <Warning>
      When using `analyze` mode in `explain_query`, the query will actually execute. Use carefully with data-modifying queries.
    </Warning>
  </Accordion>

  <Accordion title="Performance Monitoring (2 tools)">
    Monitor database performance and identify optimization opportunities.

    <AccordionGroup>
      <Accordion title="analyze_db_health">
        Comprehensive database health analysis across multiple dimensions

        * **Health Types**: all, buffer, connection, constraint, index, replication, sequence, vacuum
        * **Features**: Connection monitoring, index health, buffer cache analysis, constraint validation
      </Accordion>

      <Accordion title="get_top_queries">
        Identify resource-intensive queries using pg\_stat\_statements data

        * **Sort Options**: total\_time, mean\_time, resources
        * **Customizable Limits**: Configure number of queries to return
      </Accordion>
    </AccordionGroup>

    <Note>
      The `get_top_queries` tool requires the `pg_stat_statements` extension to be enabled in your PostgreSQL instance.
    </Note>
  </Accordion>

  <Accordion title="SQL Execution (1 tool)">
    Execute read-only SQL queries with security validation.

    <AccordionGroup>
      <Accordion title="execute_sql">
        Execute read-only SQL queries with built-in security validation

        * **Security**: Automatic validation prevents data modification
        * **Use Cases**: Data exploration, reporting, analysis queries
      </Accordion>
    </AccordionGroup>

    <Tip>
      **Security Feature**: The server operates in restricted mode by default, ensuring only read operations are permitted.
    </Tip>
  </Accordion>
</AccordionGroup>

***

## Configuration Requirements

### Database Connection Setup

The PostgreSQL server requires a complete connection string with authentication:

<AccordionGroup>
  <Accordion title="Connection String Format" icon="link">
    ```
    postgresql://user:password@host.domain.com:5432/dbname?sslmode=require
    ```

    **Required Components:**

    * **Protocol**: `postgresql://`
    * **Credentials**: username and password
    * **Host**: database server hostname or IP
    * **Port**: database port (default: 5432)
    * **Database**: target database name
    * **SSL Mode**: recommended to use `sslmode=require`
  </Accordion>

  <Accordion title="Security Considerations" icon="shield">
    * **SSL/TLS**: Always use encrypted connections in production
    * **User Permissions**: Grant minimum required permissions for intended operations
    * **Network Access**: Ensure database is accessible from Civic infrastructure
    * **Connection Pooling**: Consider connection limits and pooling for high-traffic scenarios
  </Accordion>
</AccordionGroup>

<Warning>
  Ensure your PostgreSQL instance has the `pg_stat_statements` extension enabled for full performance monitoring capabilities.
</Warning>

***

## Guardrails

In addition to the [14 universal guardrails](/civic/concepts/guardrails), this server has **22 server-specific guardrails** across 8 operations.

| Guardrail                                 | Operation               | Timing   | Description                                                                 |
| ----------------------------------------- | ----------------------- | -------- | --------------------------------------------------------------------------- |
| Restrict Health Analysis Types            | `analyze_db_health`     | Request  | Restricts database health analysis to specified metric types                |
| Index Analysis Query Limit                | `analyze_query_indexes` | Request  | Limits queries analyzed for index recommendations                           |
| Block Dynamic SQL Construction            | `execute_sql`           | Request  | Prevents queries using \|\|, CONCAT, CHR/CHAR functions, hex literals       |
| Block Function Execution                  | `execute_sql`           | Request  | Prevents execution of stored procedures and functions                       |
| Block Query Stacking                      | `execute_sql`           | Request  | Prevents multiple queries by blocking semicolons                            |
| Block SQL Comments                        | `execute_sql`           | Request  | Prevents queries containing SQL comments (-- or /\* \*/)                    |
| Block System Tables Access                | `execute_sql`           | Request  | Prevents access to PostgreSQL system catalogs (pg\_\*, information\_schema) |
| Block Write Operations                    | `execute_sql`           | Request  | Prevents all data modification and DDL commands                             |
| Enforce Basic SQL Character Set           | `execute_sql`           | Request  | Restricts queries to alphanumeric plus basic SQL characters                 |
| Enforce Custom Character Set              | `execute_sql`           | Request  | Restricts queries to a user-defined character set                           |
| Limit JOIN Complexity                     | `execute_sql`           | Request  | Limits the number of JOINs allowed in a query                               |
| Protect Sensitive Columns                 | `execute_sql`           | Request  | Blocks queries referencing specified column names                           |
| Protect Tables (Anti-Aliasing)            | `execute_sql`           | Request  | Blocks access to specified tables regardless of aliasing                    |
| Query Length Limit                        | `execute_sql`           | Request  | Limits SQL query length to prevent resource exhaustion                      |
| Restrict SELECT \* Usage                  | `execute_sql`           | Request  | Controls use of SELECT \* wildcards                                         |
| Result Row Limit                          | `execute_sql`           | Response | Limits the number of rows returned                                          |
| Block EXPLAIN ANALYZE on Write Operations | `explain_query`         | Request  | Prevents EXPLAIN ANALYZE from executing write operations                    |
| Block Table Details Access                | `get_object_details`    | Request  | Prevents access to details of specified tables                              |
| Top Queries Result Limit                  | `get_top_queries`       | Request  | Limits the number of queries returned from performance monitoring           |
| Block Schema Object Enumeration           | `list_objects`          | Request  | Prevents listing objects in specified schemas                               |
| Filter Schema Listings                    | `list_schemas`          | Response | Filters specified schemas from schema listings                              |
| Block PII in Requests                     | *(all operations)*      | Request  | Prevents sending PII in request parameters (postgres-specific instance)     |
| Block Prompt Injection in Requests        | *(all operations)*      | Request  | Prevents prompt injection attacks (postgres-specific instance)              |
| Redact PII from Responses                 | *(all operations)*      | Response | Automatically redacts PII from all responses (postgres-specific instance)   |

<Tip>
  Configure guardrails via the Civic UI or ask the Configurator Agent: "Add guardrails to my PostgreSQL server."
</Tip>