PostgreSQL Query Sniffer

A comprehensive Java tool for capturing, analyzing, and reporting PostgreSQL database queries in real-time. This tool supports multiple capture modes including network packet sniffing, JDBC proxy, and log file parsing.

Features

• Multiple Capture Modes:

  • Network Mode: Captures queries by sniffing network packets
  • Proxy Mode: Acts as a JDBC proxy between applications and PostgreSQL
  • Log Mode: Parses PostgreSQL log files for query extraction

• Advanced Query Analysis:

  • SQL parsing and normalization
  • Query type classification (SELECT, INSERT, UPDATE, DELETE, etc.)
  • Table access tracking
  • Execution time monitoring
  • Query complexity metrics

• Comprehensive Reporting:

  • JSON reports for programmatic access
  • HTML reports with interactive dashboards
  • CSV exports for data analysis
  • Real-time metrics and statistics

• Enterprise Ready:

  • Docker containerization
  • Configurable via YAML files or CLI arguments
  • Structured logging with multiple output formats
  • Health checks and monitoring support

Quick Start

Using Docker (Recommended)

1. Clone the repository:

   git clone https://github.com/belenov-maker/test1.git
   cd test1

2. Start the complete environment:

   # Start PostgreSQL and sniffer in network mode
   docker-compose up -d postgres sniffer-network
   
   # Optional: Start test client to generate sample queries
   docker-compose --profile test up -d test-app

3. View reports:

   # Reports are generated in the ./reports directory
   ls -la reports/
   
   # Open HTML report in browser
   open reports/report_*.html

Using Java JAR

1. Prerequisites:

   • Java 11 or higher
   • Maven 3.6+ (for building from source)
   • libpcap (for network capture mode)

2. Build from source:

   mvn clean package

3. Run the sniffer:

   # Network mode (requires root/admin privileges)
   sudo java -jar target/postgresql-query-sniffer-*.jar \
     --mode NETWORK \
     --host localhost \
     --port 5432 \
     --duration 300 \
     --verbose
   
   # Proxy mode
   java -jar target/postgresql-query-sniffer-*.jar \
     --mode PROXY \
     --host localhost \
     --port 5432 \
     --output ./reports
   
   # Log parsing mode
   java -jar target/postgresql-query-sniffer-*.jar \
     --mode LOG \
     --config sniffer-config.yml

Configuration

Command Line Options

Usage: postgresql-query-sniffer [-hV] [-c=<configFile>] [-d=<duration>]
                                [--filter=<queryFilter>] [-h=<host>]
                                [--interface=<networkInterface>] [--mode=<mode>]
                                [-o=<outputDir>] [-p=<port>] [-v]

Options:
  -c, --config=<configFile>   Configuration file path (default: sniffer-config.yml)
  -h, --host=<host>          PostgreSQL host to monitor (default: localhost)
  -p, --port=<port>          PostgreSQL port to monitor (default: 5432)
  -d, --duration=<duration>  Monitoring duration in seconds (0 for infinite)
  -o, --output=<outputDir>   Output directory for reports (default: ./reports)
      --mode=<mode>          Capture mode: NETWORK, PROXY, or LOG (default: NETWORK)
      --interface=<networkInterface>  Network interface to capture on (default: any)
      --filter=<queryFilter> Query filter pattern (regex)
  -v, --verbose              Enable verbose logging
  -V, --version              Print version information and exit

Configuration File

Create a sniffer-config.yml file:

# Basic settings
host: localhost
port: 5432
duration: 0  # 0 = run indefinitely
outputDir: ./reports
mode: NETWORK  # NETWORK, PROXY, or LOG
verbose: true

# Query filtering
queryFilter: "SELECT.*FROM users.*"  # Optional regex filter

# Performance settings
bufferSize: 10000
reportInterval: 60  # seconds
maxQueryLength: 10000

# Database connection (for PROXY mode)
database:
  username: myuser
  password: mypass
  database: mydb
  ssl: false

# Proxy settings (for PROXY mode)
proxy:
  listenPort: 5433
  targetHost: localhost
  targetPort: 5432

# Logging settings (for LOG mode)
logging:
  logFile: /var/log/postgresql/postgresql.log
  logLevel: INFO
  enableFileOutput: true

Capture Modes

Network Mode

Captures queries by sniffing network packets between clients and PostgreSQL server.

Advantages:

• No application changes required
• Captures all queries from all clients
• Real-time monitoring

Requirements:

• Root/administrator privileges
• Network access to PostgreSQL traffic
• libpcap library

Usage:

# Docker (recommended)
docker-compose up -d sniffer-network

# Direct execution
sudo java -jar postgresql-query-sniffer.jar --mode NETWORK --host db-server --port 5432

Proxy Mode

Acts as a JDBC proxy between applications and PostgreSQL server.

Advantages:

• No special privileges required
• Detailed connection tracking
• Can modify queries if needed

Requirements:

• Applications must connect through proxy
• Proxy port must be accessible

Usage:

# Start proxy on port 5433
java -jar postgresql-query-sniffer.jar --mode PROXY

# Configure applications to connect to proxy
jdbc:postgresql://proxy-host:5433/database

Log Mode

Parses PostgreSQL log files to extract query information.

Advantages:

• Works with existing log files
• No network access required
• Historical analysis possible

Requirements:

• PostgreSQL logging enabled
• Access to log files

PostgreSQL Configuration:

-- Enable query logging
ALTER SYSTEM SET log_statement = 'all';
ALTER SYSTEM SET log_destination = 'stderr';
ALTER SYSTEM SET logging_collector = on;
ALTER SYSTEM SET log_directory = '/var/log/postgresql';
ALTER SYSTEM SET log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log';
SELECT pg_reload_conf();

Reports and Output

Report Types

1. JSON Reports (queries_*.json):

   • Complete query data in structured format
   • Suitable for programmatic processing
   • Includes all metadata and analysis results

2. HTML Reports (report_*.html):

   • Interactive web-based dashboard
   • Query statistics and visualizations
   • Searchable query tables

3. CSV Reports (queries_*.csv):

   • Tabular data for spreadsheet analysis
   • Compatible with data analysis tools
   • Easy to import into databases

4. Summary Reports (summary_*.json):

   • Aggregated statistics and metrics
   • Query type distributions
   • Table access patterns
   • Performance summaries

Sample Report Structure

{
  "id": "uuid-here",
  "timestamp": "2023-11-12T10:30:45.123",
  "query": "SELECT * FROM users WHERE id = ?",
  "queryType": "SELECT",
  "database": "myapp",
  "username": "app_user",
  "clientAddress": "192.168.1.100",
  "executionTime": 15,
  "tables": ["users"],
  "queryHash": "a1b2c3d4e5f6",
  "normalizedQuery": "select * from users where id = ?",
  "metadata": {
    "queryLength": 32,
    "wordCount": 7,
    "tableCount": 1
  }
}

Docker Deployment

Production Deployment

version: '3.8'
services:
  postgresql-sniffer:
    image: ghcr.io/belenov-maker/test1:latest
    environment:
      - JAVA_OPTS=-Xmx1g -Xms512m
      - CONFIG_FILE=/app/config/production-config.yml
    volumes:
      - ./config:/app/config
      - ./reports:/app/reports
      - ./logs:/app/logs
    command: --mode NETWORK --host postgres --port 5432 --verbose
    cap_add:
      - NET_ADMIN
      - NET_RAW
    network_mode: host
    restart: unless-stopped

Kubernetes Deployment

apiVersion: apps/v1
kind: Deployment
metadata:
  name: postgresql-sniffer
spec:
  replicas: 1
  selector:
    matchLabels:
      app: postgresql-sniffer
  template:
    metadata:
      labels:
        app: postgresql-sniffer
    spec:
      containers:
      - name: sniffer
        image: ghcr.io/belenov-maker/test1:latest
        args: ["--mode", "PROXY", "--host", "postgres-service", "--port", "5432"]
        ports:
        - containerPort: 5433
        env:
        - name: JAVA_OPTS
          value: "-Xmx512m -Xms256m"
        volumeMounts:
        - name: reports
          mountPath: /app/reports
        - name: config
          mountPath: /app/config
      volumes:
      - name: reports
        persistentVolumeClaim:
          claimName: sniffer-reports
      - name: config
        configMap:
          name: sniffer-config

Development

Building from Source

# Clone repository
git clone https://github.com/belenov-maker/test1.git
cd test1

# Build with Maven
mvn clean package

# Run tests
mvn test

# Build Docker image
docker build -t postgresql-query-sniffer .

Project Structure

src/
├── main/
│   ├── java/com/sqlsniffer/
│   │   ├── PostgreSQLQuerySniffer.java    # Main application class
│   │   ├── config/                        # Configuration management
│   │   ├── core/                          # Core capture and processing
│   │   ├── model/                         # Data models
│   │   └── reporting/                     # Report generation
│   └── resources/
│       ├── sniffer-config.yml            # Default configuration
│       └── logback.xml                   # Logging configuration
└── test/
    └── java/com/sqlsniffer/              # Unit and integration tests

Contributing

1. Fork the repository
2. Create a feature branch (git checkout -b feature/amazing-feature)
3. Commit your changes (git commit -m 'Add amazing feature')
4. Push to the branch (git push origin feature/amazing-feature)
5. Open a Pull Request

Troubleshooting

Common Issues

1. Permission Denied (Network Mode):

   # Solution: Run with sudo or use Docker with privileged mode
   sudo java -jar postgresql-query-sniffer.jar --mode NETWORK

2. No Queries Captured:

   • Verify PostgreSQL is receiving connections
   • Check network interface selection
   • Ensure correct host/port configuration
   • Verify query filter patterns

3. High Memory Usage:

   # Reduce buffer size and increase report interval
   java -Xmx512m -jar postgresql-query-sniffer.jar --config config.yml

4. Docker Network Issues:

   # Use host networking for better packet capture
   docker run --network host --privileged postgresql-query-sniffer

Logging and Debugging

Enable verbose logging:

java -jar postgresql-query-sniffer.jar --verbose --mode NETWORK

Check log files:

tail -f logs/postgresql-query-sniffer.log

Performance Considerations

• Network Mode: Minimal performance impact on PostgreSQL server
• Proxy Mode: Adds network latency (typically <1ms)
• Log Mode: No runtime impact on PostgreSQL server

Recommended Settings

• Buffer Size: 10,000 queries (adjust based on query volume)
• Report Interval: 60 seconds (balance between freshness and performance)
• Memory: 512MB-1GB heap size for typical workloads

Security

• Network capture requires elevated privileges
• Proxy mode can log sensitive query data
• Use secure storage for configuration files containing credentials
• Regular security updates via automated CI/CD pipeline

License

This project is licensed under the MIT License - see the LICENSE file for details.

Support

• Issues: GitHub Issues
• Discussions: GitHub Discussions
• Documentation: Wiki