lmrc-postgres 0.3.16

# lmrc-postgres

> Part of the [LMRC Stack](https://gitlab.com/lemarco/lmrc-stack) - Infrastructure-as-Code toolkit for building production-ready Rust applications

[![Crates.io](https://img.shields.io/crates/v/lmrc-postgres.svg)](https://crates.io/crates/lmrc-postgres)
[![Documentation](https://docs.rs/lmrc-postgres/badge.svg)](https://docs.rs/lmrc-postgres)
[![License](https://img.shields.io/badge/license-MIT%2FApache--2.0-blue.svg)](LICENSE-MIT)

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`:

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

## Quick Start

```rust
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

```rust
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

```rust
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:

```rust
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

```rust
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:

```rust
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:

```rust
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

```rust
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

```rust
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

```rust
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:

```rust
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:

```bash
# 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/`](examples/) directory for more usage examples:

- [`basic_install.rs`](examples/basic_install.rs) - Basic installation
- [`custom_config.rs`](examples/custom_config.rs) - Custom configuration
- [`diff_detection.rs`](examples/diff_detection.rs) - Detect and apply changes
- [`uninstall.rs`](examples/uninstall.rs) - Uninstall PostgreSQL

Run an example:

```bash
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](CONTRIBUTING.md) for guidelines.

## License

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

- Apache License, Version 2.0 ([LICENSE-APACHE](../../LICENSE-APACHE) or <http://www.apache.org/licenses/LICENSE-2.0>)
- MIT license ([LICENSE-MIT](../../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](https://github.com/yourusername/ssh-manager)
- Inspired by infrastructure-as-code tools like Ansible and Terraform

## Links

- [Documentation](https://docs.rs/lmrc-postgres)
- [Crates.io](https://crates.io/crates/lmrc-postgres)
- [Repository](https://github.com/yourusername/lmrc-postgres)
- [Issue Tracker](https://github.com/yourusername/lmrc-postgres/issues)