# Encryption Feature - NGauge Backup SQL

## Overview

The backup module now supports AES-256-GCM encryption to secure your database backups at rest. Encrypted backups can only be restored on authorized systems, providing an additional layer of security.

## Features

- **AES-256-GCM Encryption**: Industry-standard authenticated encryption
- **System-Specific Keys**: Backups are tied to specific systems
- **Multi-System Support**: Configure authorized systems for cross-server restores
- **Automatic Encryption/Decryption**: Transparent integration with backup/restore process
- **Integrity Verification**: Authentication tags prevent tampering
- **Metadata Storage**: System identification embedded in encrypted files

## Configuration

### Basic Configuration (Single System)

```php
'modules' => [
    'db-backup' => [
        'class' => 'PortalPeople\NgaugeBackupSql\Module',
        'encryptionEnabled' => true,
    ],
],
```

With this configuration:
- System-specific encryption key is automatically generated
- Only the system that created the backup can restore it

### Advanced Configuration (Multi-System)

```php
'modules' => [
    'db-backup' => [
        'class' => 'PortalPeople\NgaugeBackupSql\Module',
        'encryptionEnabled' => true,
        'encryptionKey' => 'base64:' . base64_encode(random_bytes(32)), // 32 bytes = 256 bits
        'systemId' => 'production-server-01',
        'authorizedSystems' => [
            'production-server-01',
            'backup-server-01',
        ],
    ],
],
```

### Using Environment Variables (Recommended)

**Configuration:**
```php
'modules' => [
    'db-backup' => [
        'class' => 'PortalPeople\NgaugeBackupSql\Module',
        'encryptionEnabled' => true,
        'encryptionKey' => getenv('DB_BACKUP_ENCRYPTION_KEY'),
        'systemId' => getenv('DB_BACKUP_SYSTEM_ID'),
        'authorizedSystems' => explode(',', getenv('DB_BACKUP_AUTHORIZED_SYSTEMS')),
    ],
],
```

**.env file:**
```bash
DB_BACKUP_ENCRYPTION_KEY="base64:YourBase64EncodedKeyHere=="
DB_BACKUP_SYSTEM_ID="production-server-01"
DB_BACKUP_AUTHORIZED_SYSTEMS="production-server-01,backup-server-01"
```

## Generating Encryption Keys

### PHP Method
```php
// Generate a random 256-bit key
$key = random_bytes(32);
$base64Key = base64_encode($key);
echo "base64:{$base64Key}";
```

### Command Line (Linux/Mac)
```bash
# Generate random key
openssl rand -base64 32

# Use in configuration
echo "base64:$(openssl rand -base64 32)"
```

### Security Best Practices

1. **Never hardcode keys** in source code
2. **Use environment variables** or secure key management systems
3. **Store keys separately** from backups
4. **Rotate keys periodically**
5. **Use different keys** for different environments (dev/staging/production)

## How It Works

### File Structure

Encrypted backup files have this structure:

```
┌─────────────────────────────────────────┐
│ Magic Header (5 bytes): "NGBKP"        │
├─────────────────────────────────────────┤
│ Version (1 byte): 1                     │
├─────────────────────────────────────────┤
│ System ID Length (2 bytes)              │
├─────────────────────────────────────────┤
│ System ID (variable length)             │
├─────────────────────────────────────────┤
│ IV - Initialization Vector (16 bytes)  │
├─────────────────────────────────────────┤
│ Authentication Tag (16 bytes)           │
├─────────────────────────────────────────┤
│ Encrypted Data (variable length)        │
└─────────────────────────────────────────┘
```

### Encryption Process

1. User creates backup
2. SQL dump is created and optionally compressed
3. If encryption is enabled:
   - Generate random IV (Initialization Vector)
   - Encrypt compressed/uncompressed SQL using AES-256-GCM
   - Generate authentication tag
   - Build encrypted package with metadata
   - Delete unencrypted file
4. Encrypted file saved with `.enc` extension

### Decryption Process

1. User requests restore
2. System reads encrypted file
3. Verify magic header and version
4. Extract system ID and verify authorization
5. Decrypt using encryption key and IV
6. Verify authentication tag (ensures integrity)
7. Decompress if needed
8. Restore to database
9. Clean up temporary files

### System Authorization

**Scenario 1: Single System (Default)**
- System A creates backup → encrypted with System A's ID
- System A can restore → ✅ Authorized (creator)
- System B cannot restore → ❌ Not authorized

**Scenario 2: Shared Key, No Authorized Systems**
- Both systems use same encryption key
- System A creates backup → encrypted with System A's ID
- System A can restore → ✅ Authorized (creator)
- System B cannot restore → ❌ Not in authorized systems list

**Scenario 3: Shared Key with Authorized Systems**
```php
'authorizedSystems' => ['production-server-01', 'backup-server-01']
```
- System A (production-server-01) creates backup
- System A can restore → ✅ Authorized (in list)
- System B (backup-server-01) can restore → ✅ Authorized (in list)
- System C (dev-server) cannot restore → ❌ Not in list

## File Extensions

- `.sql` - Unencrypted SQL
- `.sql.gz` - Compressed SQL
- `.sql.gz.enc` - Encrypted compressed SQL
- `.sql.enc` - Encrypted uncompressed SQL
- `.zip.enc` - Encrypted ZIP archive

## Usage Examples

### Create Encrypted Backup (Web UI)

1. Enable "Compress backups" (optional)
2. Select tables to backup
3. Click "Create Selected Backups"
4. Files are automatically encrypted if `encryptionEnabled = true`

### Create Encrypted Backup (Console)

```bash
# Interactive mode
php yii db-backup/create

# Specific table
php yii db-backup/create users

# Multiple tables
php yii db-backup/create users,orders,products
```

### Restore Encrypted Backup (Web UI)

1. Navigate to table restore points
2. Click "Restore" on desired backup
3. Confirm restore
4. System automatically decrypts if needed

### Restore Encrypted Backup (Console)

```bash
# Interactive mode
php yii db-backup/restore

# Specific file
php yii db-backup/restore users_20260211_1259.sql.gz.enc
```

### Programmatic Usage

```php
use PortalPeople\NgaugeBackupSql\components\Backup;

// Create encrypted backup
Backup::SQLTable('users');

// List restore points (includes encrypted files)
$restorePoints = Backup::listRestorePoints('users');

// Restore (automatically handles encryption)
Backup::restore('users_20260211_1259.sql.gz.enc');
```

## Troubleshooting

### Error: "Decryption failed: Invalid key or corrupted data"

**Possible Causes:**
1. Different encryption key used
2. File corrupted
3. Wrong system trying to decrypt

**Solutions:**
- Verify encryption key matches
- Check file integrity
- Ensure system is in `authorizedSystems` list

### Error: "This system is not authorized to restore this backup"

**Cause:** System not in authorized systems list

**Solutions:**
1. Add current system to `authorizedSystems` configuration
2. Use the original system that created the backup
3. Configure shared key and authorized systems

### Error: "Invalid encrypted file: bad magic header"

**Cause:** File is not encrypted or corrupted

**Solutions:**
- Verify file is actually encrypted
- Check if file was corrupted during transfer
- Regenerate backup

## Performance Considerations

- **Encryption overhead**: ~5-10% additional time
- **File size**: Encrypted files slightly larger due to metadata
- **Memory usage**: Minimal, encryption is stream-based
- **CPU**: AES-256-GCM is hardware accelerated on most modern CPUs

## Security Considerations

### What Encryption Protects Against

✅ Unauthorized access to backup files
✅ Data theft if storage is compromised
✅ Unauthorized cross-system restores
✅ File tampering (via authentication tag)

### What Encryption Does NOT Protect Against

❌ Attacks on the live database
❌ SQL injection vulnerabilities
❌ Compromised encryption keys
❌ System-level access by attackers

### Additional Security Recommendations

1. **File Permissions**: Backups saved with `0600` (owner read/write only)
2. **Directory Permissions**: Place backup directory outside web root
3. **Network Transfer**: Use encrypted channels (SFTP, SCP) for backup transfers
4. **Access Control**: Restrict module access to admin users only
5. **Audit Logging**: Enable BackupHistory for operation tracking
6. **Regular Key Rotation**: Rotate encryption keys periodically
7. **Secure Key Storage**: Use environment variables or key management systems

## Migration Guide

### Enabling Encryption on Existing Backups

**Step 1: Configure Encryption**
```php
'encryptionEnabled' => true,
'encryptionKey' => 'base64:YourNewKeyHere',
```

**Step 2: New Backups Will Be Encrypted**
- Old backups remain unencrypted
- New backups will be encrypted automatically

**Step 3: Re-encrypt Old Backups (Optional)**
```bash
# Create new encrypted backups of all tables
php yii db-backup/create --all

# Delete old unencrypted backups
php yii db-backup/cleanup
```

### Disabling Encryption

**Step 1: Update Configuration**
```php
'encryptionEnabled' => false,
```

**Step 2: Existing Encrypted Backups**
- Can still be restored if encryption key is configured
- New backups will not be encrypted

## Support

For issues or questions:
1. Check [SECURITY.md](SECURITY.md) for security review
2. Review error logs in `@runtime/logs`
3. Enable debug mode for detailed error messages
4. Contact your system administrator

## Version History

- **v1.0** - Initial encryption implementation
  - AES-256-GCM encryption
  - System-specific keys
  - Multi-system authorization
  - Metadata embedding
