lmrc-postgres

Part of the LMRC Stack - Infrastructure-as-Code toolkit for building production-ready Rust applications

A comprehensive Rust library for managing PostgreSQL installations on remote servers via SSH.

Features

• 🔨 Builder Pattern API: Fluent, type-safe configuration
• ♻️ Idempotent Operations: Safe to run multiple times
• 🔍 Diff Detection: Track and apply configuration changes
• 🔄 Full Lifecycle: Install, configure, update, and uninstall
• 🔐 SSH-based: Secure remote operations via ssh-manager
• ⚠️ Error Handling: Comprehensive error types with context
• 📝 Well Documented: Extensive documentation and examples
• ✅ Tested: Comprehensive unit and integration tests

Installation

Add this to your Cargo.toml:

[dependencies]
lmrc-postgres = "0.1"
ssh-manager = "0.1"
tokio = { version = "1.0", features = ["full"] }

Quick Start

use lmrc_postgres::{PostgresConfig, PostgresManager};

#[tokio::main]
async fn main() -> Result<(), Box<dyn std::error::Error>> {
    // Build PostgreSQL configuration
    let config = PostgresConfig::builder()
        .version("15")
        .database_name("myapp")
        .username("myuser")
        .password("secure_password")
        .listen_addresses("0.0.0.0/0")
        .port(5432)
        .max_connections(100)
        .shared_buffers("256MB")
        .build()?;

    // Create manager and connect
    let manager = PostgresManager::builder()
        .config(config)
        .server_ip("192.168.1.100")
        .ssh_user("root")
        .build()?;

    // Install and configure PostgreSQL (idempotent)
    manager.setup().await?;

    // Test the connection
    manager.test_connection().await?;

    println!("PostgreSQL is ready!");
    Ok(())
}

Usage Examples

Install with Custom Configuration

use lmrc_postgres::{PostgresConfig, PostgresManager};

#[tokio::main]
async fn main() -> Result<(), Box<dyn std::error::Error>> {
    let config = PostgresConfig::builder()
        .version("15")
        .database_name("production_db")
        .username("app_user")
        .password("strong_password")
        .listen_addresses("10.0.0.0/8")
        .port(5432)
        .max_connections(200)
        .shared_buffers("512MB")
        .effective_cache_size("2GB")
        .work_mem("16MB")
        .maintenance_work_mem("128MB")
        .checkpoint_completion_target(0.9)
        .ssl(true)
        .build()?;

    let manager = PostgresManager::builder()
        .config(config)
        .server_ip("10.0.1.50")
        .ssh_user("admin")
        .private_ip("10.0.1.50")
        .build()?;

    manager.setup().await?;
    Ok(())
}

Detect and Apply Configuration Changes

use lmrc_postgres::{PostgresConfig, PostgresManager};

#[tokio::main]
async fn main() -> Result<(), Box<dyn std::error::Error>> {
    let config = PostgresConfig::builder()
        .version("15")
        .database_name("myapp")
        .username("myuser")
        .password("mypass")
        .max_connections(200) // Changed from 100
        .build()?;

    let manager = PostgresManager::builder()
        .config(config)
        .server_ip("192.168.1.100")
        .build()?;

    // Detect configuration differences
    let diff = manager.diff().await?;

    if diff.has_changes() {
        println!("Configuration changes detected:");
        for change in diff.changes() {
            println!("  {}", change);
        }

        // Apply changes
        manager.apply_diff(&diff).await?;
        println!("Configuration updated successfully");
    } else {
        println!("No configuration changes");
    }

    Ok(())
}

Configuration Validation and Auto-Tuning

The library provides comprehensive validation and intelligent auto-tuning:

use lmrc_postgres::{
    PostgresConfig, validate_comprehensive, auto_tune, WorkloadType
};

#[tokio::main]
async fn main() -> Result<(), Box<dyn std::error::Error>> {
    // Example 1: Validate a configuration
    let config = PostgresConfig::builder()
        .version("15")
        .database_name("myapp")
        .username("myuser")
        .password("secure_password")
        .max_connections(1000)
        .work_mem("100MB")
        .build()?;

    // Run comprehensive validation
    let warnings = validate_comprehensive(&config)?;
    for warning in warnings {
        println!("Warning: {}", warning);
    }

    // Example 2: Auto-tune for your workload
    let total_ram_mb = 16384; // 16GB RAM
    let cpu_cores = 8;

    // Get recommended settings for a web application
    let tuned = auto_tune(total_ram_mb, cpu_cores, WorkloadType::Web);

    // Build config with auto-tuned values
    let config = PostgresConfig::builder()
        .version("15")
        .database_name("webapp")
        .username("webuser")
        .password("secure_password")
        .max_connections(tuned.get("max_connections").unwrap().parse().unwrap())
        .shared_buffers(tuned.get("shared_buffers").unwrap())
        .effective_cache_size(tuned.get("effective_cache_size").unwrap())
        .work_mem(tuned.get("work_mem").unwrap())
        .build()?;

    Ok(())
}

Validation Features:

• Memory size format validation (e.g., "256MB", "1GB")
• CIDR notation validation for network addresses
• Conflicting settings detection
• Resource limits validation
• PostgreSQL version format validation

Auto-Tuning Workload Types:

• WorkloadType::Web - Web applications (moderate connections, OLTP + read-heavy)
• WorkloadType::Mixed - Mixed workload (OLTP + analytics)
• WorkloadType::DataWarehouse - Complex queries, fewer connections
• WorkloadType::Oltp - High-throughput OLTP (many connections, simple queries)

Step-by-Step Installation

use lmrc_postgres::{PostgresConfig, PostgresManager};

#[tokio::main]
async fn main() -> Result<(), Box<dyn std::error::Error>> {
    let config = PostgresConfig::builder()
        .version("15")
        .database_name("myapp")
        .username("myuser")
        .password("mypass")
        .build()?;

    let manager = PostgresManager::builder()
        .config(config)
        .server_ip("192.168.1.100")
        .build()?;

    // Check if already installed
    if !manager.is_installed().await? {
        println!("Installing PostgreSQL...");
        manager.install().await?;
    } else {
        println!("PostgreSQL is already installed");
    }

    // Configure database
    println!("Configuring database...");
    manager.configure_database().await?;

    // Configure server
    println!("Configuring server...");
    manager.configure_server().await?;

    // Test connection
    println!("Testing connection...");
    manager.test_connection().await?;

    println!("Setup complete!");
    Ok(())
}

Backup, Rollback, and Dry-Run

The library provides comprehensive safety features for configuration changes:

use lmrc_postgres::{PostgresConfig, PostgresManager};

#[tokio::main]
async fn main() -> Result<(), Box<dyn std::error::Error>> {
    let config = PostgresConfig::builder()
        .version("15")
        .database_name("myapp")
        .username("myuser")
        .password("mypass")
        .max_connections(200)  // Changing configuration
        .build()?;

    let manager = PostgresManager::builder()
        .config(config)
        .server_ip("192.168.1.100")
        .build()?;

    // 1. Dry-run: Preview changes without applying
    let diff = manager.dry_run_configure().await?;
    if diff.has_changes() {
        println!("Would make {} changes:", diff.len());
        println!("{}", diff);
    }

    // 2. Safe apply: Automatic backup and rollback on failure
    manager.apply_diff_safe(&diff).await?;

    // 3. Manual backup
    let backup = manager.backup_config().await?;
    println!("Backup created: {}", backup.timestamp);

    // 4. Quick rollback to most recent backup (if needed)
    // manager.rollback_config().await?;

    // 5. List and manage backups
    let backups = manager.list_backups().await?;
    manager.cleanup_old_backups(5).await?; // Keep only 5 most recent

    // 6. Read and parse pg_hba.conf
    let pg_hba = manager.read_pg_hba().await?;
    println!("Current authentication rules:\n{}", pg_hba);

    Ok(())
}

Safety Features:

• Dry-run mode: Preview changes before applying them
• Automatic backups: Configuration is backed up before changes
• Automatic rollback: If changes fail, automatically restore previous configuration
• Manual rollback: Quickly revert to any previous backup
• Backup management: List, restore, and cleanup old backups
• pg_hba.conf support: Read, parse, and diff authentication rules

User & Database Management

Comprehensive user and database lifecycle management with granular permission control:

use lmrc_postgres::{PostgresConfig, PostgresManager, Privilege};

#[tokio::main]
async fn main() -> Result<(), Box<dyn std::error::Error>> {
    let config = PostgresConfig::builder()
        .version("15")
        .database_name("postgres")
        .username("postgres")
        .password("postgres")
        .build()?;

    let manager = PostgresManager::builder()
        .config(config)
        .server_ip("192.168.1.100")
        .build()?;

    // 1. List existing users and databases
    let users = manager.list_users().await?;
    for user in users {
        println!("User: {} (superuser: {})", user.name, user.is_superuser);
    }

    let databases = manager.list_databases().await?;
    for db in databases {
        println!("Database: {} (owner: {})", db.name, db.owner);
    }

    // 2. Create database with advanced options
    manager.create_database_with_options(
        "myapp_db",
        Some("postgres"),  // owner
        Some("UTF8"),      // encoding
        Some("template0")  // template
    ).await?;

    // 3. Create users with different permission levels
    manager.create_user_with_options(
        "app_user",
        "secure_password",
        false,      // not superuser
        false,      // cannot create databases
        false,      // cannot create roles
        Some(50)    // connection limit
    ).await?;

    // 4. Grant granular privileges
    manager.grant_privileges(
        "myapp_db",
        "app_user",
        &[Privilege::Select, Privilege::Insert, Privilege::Update]
    ).await?;

    // 5. Role-based access control
    manager.create_role("readonly", false, false).await?;
    manager.grant_privileges("myapp_db", "readonly", &[Privilege::Select]).await?;
    manager.grant_role("readonly", "app_user").await?;

    // 6. Update user password
    manager.update_user_password("app_user", "new_password").await?;

    // 7. Check existence
    if manager.user_exists("app_user").await? {
        println!("User exists!");
    }

    // 8. Cleanup (if needed)
    // manager.revoke_role("readonly", "app_user").await?;
    // manager.drop_user("app_user").await?;
    // manager.drop_database("myapp_db").await?;

    Ok(())
}

User & Database Operations:

• List operations: list_users(), list_databases() - Query all users/databases with metadata
• Create operations: create_database_with_options(), create_user_with_options() - Advanced creation with owner, encoding, capabilities
• Drop operations: drop_database(), drop_user() - Safely remove users and databases
• Password management: update_user_password() - Change user passwords
• Existence checks: user_exists(), database_exists() - Check if user/database exists
• Granular privileges: grant_privileges(), revoke_privileges() - 12+ privilege types (SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER, CREATE, CONNECT, TEMPORARY, EXECUTE, USAGE, ALL)
• Role management: create_role(), grant_role(), revoke_role() - Create and assign roles for organized permissions

Uninstall PostgreSQL

use lmrc_postgres::{PostgresConfig, PostgresManager};

#[tokio::main]
async fn main() -> Result<(), Box<dyn std::error::Error>> {
    let config = PostgresConfig::builder()
        .version("15")
        .database_name("myapp")
        .username("myuser")
        .password("mypass")
        .build()?;

    let manager = PostgresManager::builder()
        .config(config)
        .server_ip("192.168.1.100")
        .build()?;

    // Uninstall but keep data
    manager.uninstall(false).await?;

    // Or uninstall and purge all data
    manager.uninstall(true).await?;

    Ok(())
}

API Overview

PostgresConfig Builder

let config = PostgresConfig::builder()
    .version("15")                          // PostgreSQL version
    .database_name("mydb")                  // Database name
    .username("dbuser")                     // Database user
    .password("securepass")                 // Database password
    .listen_addresses("0.0.0.0/0")         // CIDR notation
    .port(5432)                            // Port number
    .max_connections(100)                  // Max connections
    .shared_buffers("256MB")               // Shared buffers
    .effective_cache_size("1GB")           // Cache size
    .work_mem("4MB")                       // Work memory
    .maintenance_work_mem("64MB")          // Maintenance memory
    .wal_buffers("16MB")                   // WAL buffers
    .checkpoint_completion_target(0.9)     // Checkpoint target
    .ssl(true)                             // Enable SSL
    .add_config("key", "value")            // Custom config
    .build()?;

PostgresManager Builder

let manager = PostgresManager::builder()
    .config(config)                        // PostgresConfig instance
    .server_ip("192.168.1.100")           // Server IP address
    .ssh_user("root")                     // SSH username
    .ssh_port(22)                         // SSH port
    .private_ip("10.0.1.100")            // Private IP (optional)
    .build()?;

Core Operations

All operations are idempotent and can be safely run multiple times:

Installation & Setup:

• is_installed() - Check if PostgreSQL is installed
• get_installed_version() - Get installed version
• install() - Install PostgreSQL
• uninstall(purge) - Uninstall PostgreSQL
• configure_database() - Configure database and user
• configure_server() - Configure server settings
• configure() - Configure both database and server
• setup() - Complete installation and configuration
• test_connection() - Test database connection

Configuration Management:

• diff() - Detect configuration changes
• apply_diff(diff) - Apply configuration changes
• dry_run_configure() - Preview changes without applying
• apply_diff_safe(diff) - Apply with automatic rollback on failure

Backup & Restore:

• backup_config() - Create configuration backup
• list_backups() - List all backups
• restore_backup(backup) - Restore specific backup
• rollback_config() - Quick rollback to most recent
• cleanup_old_backups(keep) - Manage backup history
• read_pg_hba() - Read pg_hba.conf authentication rules

User Management:

• list_users() - List all PostgreSQL users
• create_user_with_options() - Create user with advanced options
• drop_user(username) - Remove user
• update_user_password(username, password) - Change password
• user_exists(username) - Check if user exists

Database Management:

• list_databases() - List all databases
• create_database_with_options() - Create database with advanced options
• drop_database(database) - Remove database
• database_exists(database) - Check if database exists

Permission Management:

• grant_privileges(db, user, privileges) - Grant granular privileges
• revoke_privileges(db, user, privileges) - Revoke privileges
• create_role(role, can_login, is_superuser) - Create role
• grant_role(role, user) - Grant role to user
• revoke_role(role, user) - Revoke role from user

Configuration Options

Required

• version - PostgreSQL version (e.g., "15", "14", "13")
• database_name - Database name to create
• username - Database username
• password - Database password

Optional

• listen_addresses - CIDR notation (default: "0.0.0.0/0")
• port - PostgreSQL port (default: 5432)
• max_connections - Maximum concurrent connections
• shared_buffers - Shared memory buffers (e.g., "256MB")
• effective_cache_size - Query optimizer cache size
• work_mem - Memory for sorts and queries
• maintenance_work_mem - Memory for maintenance operations
• wal_buffers - Write-ahead log buffers
• checkpoint_completion_target - Checkpoint spread (0.0-1.0)
• ssl - Enable SSL (default: false)
• extra_config - Additional custom parameters

Error Handling

The library provides comprehensive error types:

use lmrc_postgres::{Error, Result};

match manager.install().await {
    Ok(_) => println!("Installation successful"),
    Err(Error::AlreadyInstalled(version)) => {
        println!("PostgreSQL {} already installed", version)
    }
    Err(Error::SshConnection(err)) => {
        eprintln!("SSH connection failed: {}", err)
    }
    Err(Error::Installation(msg)) => {
        eprintln!("Installation failed: {}", msg)
    }
    Err(err) => eprintln!("Error: {}", err),
}

Idempotency

All operations are designed to be idempotent:

• Install: Skips if already installed with the correct version
• Configure: Creates database/user if they don't exist, updates if they do
• Setup: Combines install and configure safely
• Uninstall: Succeeds even if not installed

This makes it safe to run operations multiple times without errors.

Testing

Run the test suite:

# Unit tests
cargo test

# Integration tests (requires SSH access to a test server)
cargo test --test integration

# All tests with logging
RUST_LOG=debug cargo test

Examples

See the examples/ directory for more usage examples:

• basic_install.rs - Basic installation
• custom_config.rs - Custom configuration
• diff_detection.rs - Detect and apply changes
• uninstall.rs - Uninstall PostgreSQL

Run an example:

cargo run --example basic_install

Platform Support

Currently supports:

• Debian-based Linux distributions (Debian, Ubuntu)
• PostgreSQL versions 12-16

Planned support:

• RedHat-based distributions (RHEL, CentOS, Fedora)
• Alpine Linux
• Other PostgreSQL versions

Contributing

Contributions are welcome! Please see CONTRIBUTING.md for guidelines.

License

Part of the LMRC Stack project. Licensed under either of:

• Apache License, Version 2.0 (LICENSE-APACHE or http://www.apache.org/licenses/LICENSE-2.0)
• MIT license (LICENSE-MIT or http://opensource.org/licenses/MIT)

at your option.

Security

If you discover a security vulnerability, please email security@example.com instead of using the issue tracker.

Acknowledgments

• Built on top of ssh-manager
• Inspired by infrastructure-as-code tools like Ansible and Terraform

Links

• Documentation
• Crates.io
• Repository
• Issue Tracker