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, it supports both restricted (read-only with SQL validation) and unrestricted modes.

Getting Started

1. Connect to PostgreSQL

   Add the PostgreSQL server to your Civic environment through the server directory.

2. Configure Database Connection

   Provide your PostgreSQL connection string with proper authentication and SSL settings.

3. Test Connection

   Start with a simple command like list schemas to verify database connectivity.

What You Can Do

Schema Exploration

Browse database schemas, tables, views, sequences, and extensions

Query Optimization

Analyze execution plans, recommend indexes, and optimize query performance

Health Monitoring

Monitor database health, connections, buffer cache, and system metrics

Performance Analysis

Identify slow queries, resource usage patterns, and bottlenecks

Use Cases

Database Administration

• 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

Query Optimization

• 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

Performance Monitoring

• 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

---

Available Tools (9)

Database Discovery (3 tools)

Explore and understand your PostgreSQL database structure.

Schema and Object Management

list_schemas

List all schemas in the database for structural overview

list_objects

List objects (tables, views, sequences, extensions) within a specific schema

get_object_details

Get detailed information about specific database objects including structure and metadata

Object Types Supported: tables, views, sequences, extensions - specify the type for targeted exploration.

Query Analysis & Optimization (3 tools)

Advanced query performance analysis and optimization tools.

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

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

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

When using analyze mode in explain_query, the query will actually execute. Use carefully with data-modifying queries.

Performance Monitoring (2 tools)

Monitor database performance and identify optimization opportunities.

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

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

The get_top_queries tool requires the pg_stat_statements extension to be enabled in your PostgreSQL instance.

SQL Execution (1 tool)

Execute read-only SQL queries with security validation.

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

Security Feature: The server operates in restricted mode by default, ensuring only read operations are permitted.

---

Configuration Requirements

Database Connection Setup

The PostgreSQL server requires a complete connection string with authentication:

Connection String Format

postgresql://user:[e-mail]: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

Security Considerations

• 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

Ensure your PostgreSQL instance has the pg_stat_statements extension enabled for full performance monitoring capabilities.

---

Guardrails

In addition to the 14 universal 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)

Configure guardrails via the Civic UI or ask the Configurator Agent: “Add guardrails to my PostgreSQL server.”