pg-api 0.1.0

# PostgreSQL API (pg-api) - Deployment Guide

## Overview

This guide provides comprehensive instructions for deploying the PostgreSQL API service (pg-api) in production environments. The pg-api service is designed to work in conjunction with an OpenSearch API instance for observability and logging.

## Architecture Overview

```
┌─────────────────────┐         ┌─────────────────────┐
│   Client Apps       │         │   OpenSearch API    │
│                     │         │   (Observability)   │
└──────────┬──────────┘         └──────────▲──────────┘
           │                                │
           │ HTTPS                          │ Logs/Metrics
           ▼                                │
┌─────────────────────┐                     │
│   Nginx/Apache      │                     │
│   (Reverse Proxy)   │                     │
└──────────┬──────────┘                     │
           │                                │
           │ HTTP                           │
           ▼                                │
┌─────────────────────┐         ┌──────────┴──────────┐
│     pg-api          │────────▶│   PostgreSQL        │
│   (Port 8580)       │         │   Database          │
└─────────────────────┘         └─────────────────────┘
```

## Prerequisites

### System Requirements
- **Operating System**: Ubuntu 20.04/22.04, Debian 11/12, RHEL 8/9, CentOS 8/9
- **Memory**: Minimum 2GB RAM (4GB recommended)
- **Disk Space**: Minimum 10GB available
- **CPU**: 2 cores minimum (4 cores recommended)
- **Network**: Open ports 80, 443 (HTTPS), 8580 (internal)

### Software Dependencies
- **Python**: 3.8 or higher (for setup script)
- **Rust**: 1.70 or higher (if building from source)
- **PostgreSQL**: 13 or higher
- **OpenSearch API**: Should be pre-installed and accessible

### Required Information
Before starting deployment, gather:
1. **pg-api domain**: Your domain for the PostgreSQL API (e.g., `pg-api.yourdomain.com`)
2. **OpenSearch API URL**: The URL of your OpenSearch API instance (e.g., `https://opensearch-api.yourdomain.com`)
3. **OpenSearch API Token**: Authentication token for OpenSearch API (if not available, will be generated)
4. **License Key**: Valid license key for pg-api
5. **SSL Certificates**: Either use Let's Encrypt or provide custom certificates

## Installation Process

### Step 1: Clone Repository

```bash
# Clone the repository
git clone https://github.com/your-org/pg-api.git
cd pg-api

# Or download release tarball
wget https://releases.yourdomain.com/pg-api-latest.tar.gz
tar -xzf pg-api-latest.tar.gz
cd pg-api
```

### Step 2: Run Setup Script

The setup script supports both interactive and non-interactive modes.

#### Interactive Mode (Recommended for First Installation)

```bash
sudo python3 setup.py
```

The script will prompt for:
1. Installation action (install/upgrade/uninstall)
2. pg-api domain name
3. OpenSearch API URL
4. OpenSearch API token (optional, can be generated)
5. Installation path (default: `/opt/prod/pg-api`)
6. PostgreSQL configuration
7. SSL certificate preference

#### Non-Interactive Mode (For Automation)

```bash
sudo python3 setup.py \
  --non-interactive \
  --action install \
  --domain <your-pg-api-domain> \
  --opensearch-url <your-opensearch-api-url> \
  --opensearch-token <your-token> \
  --install-path /opt/prod/pg-api \
  --license-key <your-license-key>
```

### Step 3: Verify Installation

After installation, verify all components:

```bash
# Check service status
sudo systemctl status pg-api

# Test health endpoint
curl https://<your-domain>/health

# Check logs
sudo journalctl -u pg-api -n 50

# Verify database connection
curl -X POST https://<your-domain>/v1/query \
  -H "Authorization: Bearer <api-key>" \
  -H "Content-Type: application/json" \
  -d '{"query": "SELECT version()"}'
```

## Configuration

### Environment Variables

The service configuration is managed through environment files located at:
- Production: `/etc/pg-api/pg-api.env`
- Custom path: `<install-path>/.env`

Key configuration parameters:

```bash
# Application Configuration
APP__ADDR=127.0.0.1:8580              # Internal listening address
APP__LOG_LEVEL=info                   # Log level (debug, info, warn, error)
APP__DOMAIN=<your-domain>             # Your pg-api domain

# PostgreSQL Configuration
PG__HOST=localhost                    # PostgreSQL host
PG__PORT=5432                        # PostgreSQL port
PG__MAX_CONNECTIONS=100              # Maximum database connections
PG__POOL_SIZE=25                     # Connection pool size

# OpenSearch Integration
OPENSEARCH_API_URL=<opensearch-url>  # OpenSearch API endpoint
OPENSEARCH_API_TOKEN=<token>         # Authentication token
OPENSEARCH_ENABLED=true              # Enable/disable integration
OPENSEARCH_INDEX_PREFIX=pg-api       # Index prefix for logs
OPENSEARCH_BATCH_SIZE=100            # Batch size for log shipping
OPENSEARCH_FLUSH_INTERVAL=5s         # Flush interval

# Security
LICENSE_KEY=<your-license-key>       # License key (required)
JWT_SECRET=<auto-generated>          # JWT signing secret
API_TOKENS=<auto-generated>          # API authentication tokens
```

### Account Configuration

Account management is configured through JSON files:
- Location: `<install-path>/config/accounts-production.json`

#### Important: Database User Mapping

**Each pg-api account MUST have a corresponding PostgreSQL user** with appropriate permissions. This design ensures:

1. **Security Isolation**: Each API key maps to a specific database user
2. **Granular Control**: Permissions can be revoked at the database level without affecting other API keys
3. **Audit Trail**: Database logs show which specific user (API key) performed each operation
4. **Compliance**: Meets security requirements for user-level access control

#### Setting Up Database Users

For each account in the configuration, create a corresponding PostgreSQL user:

```sql
-- Example: Creating a user for an application account
CREATE USER app_user_prod WITH PASSWORD 'secure_password_here';
GRANT CONNECT ON DATABASE production_db TO app_user_prod;
GRANT USAGE ON SCHEMA public TO app_user_prod;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_user_prod;

-- Example: Creating a read-only analytics user
CREATE USER analytics_user WITH PASSWORD 'analytics_password';
GRANT CONNECT ON DATABASE production_db TO analytics_user;
GRANT USAGE ON SCHEMA public TO analytics_user;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO analytics_user;

-- Example: Creating a superuser for administrative tasks
CREATE USER admin_user WITH PASSWORD 'admin_password' SUPERUSER;
```

#### Account Configuration Example

```json
{
  "accounts": [
    {
      "id": "acc_001",
      "name": "production-app",
      "api_key": "sk_live_<generated>",
      "role": "application",
      "databases": [
        {
          "database": "production_db",
          "username": "app_user_prod",        // Must exist in PostgreSQL
          "password": "secure_password_here",  // Must match PostgreSQL user password
          "permissions": ["Select", "Insert", "Update", "Delete"]
        }
      ],
      "rate_limit": 1000,
      "max_connections": 50,
      "notes": "Main application account"
    },
    {
      "id": "acc_002",
      "name": "analytics-readonly",
      "api_key": "sk_analytics_<generated>",
      "role": "readonly",
      "databases": [
        {
          "database": "production_db",
          "username": "analytics_user",       // Must exist in PostgreSQL
          "password": "analytics_password",   // Must match PostgreSQL user password
          "permissions": ["Select"]
        }
      ],
      "rate_limit": 500,
      "max_connections": 10,
      "notes": "Analytics read-only access"
    }
  ]
}
```

#### Security Best Practices

1. **Never reuse database users** across multiple API keys
2. **Use strong, unique passwords** for each database user
3. **Apply principle of least privilege** - grant only necessary permissions
4. **Regularly rotate credentials** for sensitive accounts
5. **Monitor and audit** database access logs
6. **Document user mappings** for compliance and troubleshooting

### Web Server Configuration

#### Nginx (Auto-generated)

The setup script creates an Nginx configuration at:
`/etc/nginx/sites-available/<your-domain>.conf`

Key features:
- SSL/TLS termination
- WebSocket support
- Rate limiting
- Health check bypass
- Security headers

#### Apache (Alternative)

For Apache, configuration is created at:
`/etc/apache2/sites-available/<your-domain>.conf`

### SSL/TLS Configuration

Three options are available:

1. **Let's Encrypt (Recommended)**
   - Automatic certificate generation
   - Auto-renewal configured
   - No additional cost

2. **Self-Signed (Development)**
   - Quick setup for testing
   - Not recommended for production

3. **Custom Certificates**
   - Provide your own certificates
   - Specify paths during setup

## Integration with OpenSearch API

### Prerequisites
The OpenSearch API should be installed and accessible before pg-api deployment.

### Configuration Steps

1. **Verify OpenSearch API Availability**
```bash
curl https://<opensearch-api-url>/health
```

2. **Generate or Obtain API Token**
```bash
# If you need to generate a new token
python3 scripts/generate_opensearch_token.py \
  --environment production \
  --permissions write
```

3. **Configure pg-api Integration**
The setup script will automatically configure the integration when you provide:
- OpenSearch API URL
- Valid API token

### What Gets Logged

- All API requests and responses
- Database query execution times
- Error events and stack traces
- Authentication attempts
- Rate limit violations
- Connection pool statistics
- System metrics (CPU, memory, disk)

## Post-Installation Tasks

### 1. Configure Firewall

```bash
# Allow HTTPS traffic
sudo ufw allow 443/tcp

# Allow HTTP (for redirect)
sudo ufw allow 80/tcp

# Reload firewall
sudo ufw reload
```

### 2. Set Up Monitoring

```bash
# Configure Prometheus endpoint (optional)
curl https://<your-domain>/metrics

# Set up health check monitoring
# Add to your monitoring system:
# Endpoint: https://<your-domain>/health
# Expected: 200 OK
```

### 3. Configure Backup

```bash
# Create backup script
cat > /opt/prod/pg-api/backup.sh << 'EOF'
#!/bin/bash
BACKUP_DIR="/backup/pg-api"
DATE=$(date +%Y%m%d_%H%M%S)

# Backup configuration
cp -r /opt/prod/pg-api/config $BACKUP_DIR/config_$DATE
cp /etc/pg-api/pg-api.env $BACKUP_DIR/env_$DATE

# Backup logs
tar -czf $BACKUP_DIR/logs_$DATE.tar.gz /opt/prod/pg-api/logs
EOF

chmod +x /opt/prod/pg-api/backup.sh

# Add to crontab
echo "0 2 * * * /opt/prod/pg-api/backup.sh" | crontab -
```

### 4. Generate API Keys

```bash
# Generate API keys for applications
python3 scripts/generate_api_key.py \
  --account-name "production-app" \
  --role application \
  --rate-limit 1000
```

## Maintenance Operations

### Starting/Stopping Service

```bash
# Start service
sudo systemctl start pg-api

# Stop service
sudo systemctl stop pg-api

# Restart service
sudo systemctl restart pg-api

# Reload configuration
sudo systemctl reload pg-api
```

### Updating pg-api

```bash
# Pull latest version
cd /opt/dev/pg-api
git pull origin main

# Build new version
make release

# Deploy update
sudo python3 setup.py \
  --non-interactive \
  --action upgrade \
  --install-path /opt/prod/pg-api
```

### Log Rotation

Logs are automatically rotated by systemd-journald. For custom log files:

```bash
# Create logrotate configuration
cat > /etc/logrotate.d/pg-api << EOF
/opt/prod/pg-api/logs/*.log {
    daily
    rotate 30
    compress
    delaycompress
    notifempty
    create 0640 pg-api pg-api
    postrotate
        systemctl reload pg-api
    endscript
}
EOF
```

## Troubleshooting

### Common Issues

#### Service Won't Start
```bash
# Check logs
sudo journalctl -u pg-api -n 100

# Verify configuration
/opt/prod/pg-api/target/release/pg-api --validate-config

# Check permissions
ls -la /opt/prod/pg-api/
```

#### Database Connection Issues
```bash
# Test PostgreSQL connection
psql -h localhost -U postgres -c "SELECT 1"

# Check pg_hba.conf
sudo cat /etc/postgresql/*/main/pg_hba.conf

# Verify network connectivity
telnet localhost 5432
```

#### OpenSearch Integration Issues
```bash
# Test OpenSearch API connectivity
curl -H "Authorization: Bearer <token>" \
  https://<opensearch-api-url>/health

# Check logs for connection errors
grep -i opensearch /var/log/pg-api/error.log

# Verify token permissions
python3 scripts/validate_opensearch_token.py --token <token>
```

#### SSL Certificate Issues
```bash
# Check certificate validity
openssl x509 -in /etc/ssl/certs/<domain>.pem -text -noout

# Test SSL configuration
openssl s_client -connect <domain>:443

# Renew Let's Encrypt certificate
sudo certbot renew --force-renewal
```

### Performance Tuning

#### PostgreSQL Optimization
```sql
-- Adjust connection pool settings
ALTER SYSTEM SET max_connections = 200;
ALTER SYSTEM SET shared_buffers = '256MB';
ALTER SYSTEM SET effective_cache_size = '1GB';

-- Reload configuration
SELECT pg_reload_conf();
```

#### pg-api Optimization
```bash
# Edit environment file
sudo nano /etc/pg-api/pg-api.env

# Adjust settings:
PG__POOL_SIZE=50              # Increase pool size
PG__MAX_CONNECTIONS=200       # Increase max connections
APP__WORKER_THREADS=8         # Increase worker threads

# Restart service
sudo systemctl restart pg-api
```

## Security Considerations

### Best Practices

1. **API Key Management**
   - Rotate API keys regularly
   - Use separate keys for each application
   - Monitor key usage

2. **Network Security**
   - Use firewall rules to restrict access
   - Enable rate limiting
   - Implement IP whitelisting if possible

3. **Database Security**
   - Use separate database users per application
   - Implement row-level security
   - Regular security audits

4. **SSL/TLS**
   - Use strong cipher suites
   - Enable HSTS
   - Regular certificate renewal

### Security Checklist

- [ ] Firewall configured
- [ ] SSL/TLS enabled
- [ ] API keys generated securely
- [ ] Rate limiting enabled
- [ ] Audit logging configured
- [ ] OpenSearch integration secured
- [ ] Database permissions restricted
- [ ] Regular backups scheduled
- [ ] Monitoring alerts configured
- [ ] Security updates applied

## Support and Resources

### Documentation
- API Reference: `/docs/API_REFERENCE.md`
- Configuration Guide: `/docs/CONFIGURATION.md`
- Observability Guide: `/docs/OBSERVABILITY.md`

### Logs and Debugging
- Service logs: `sudo journalctl -u pg-api`
- Application logs: `/opt/prod/pg-api/logs/`
- Audit logs: Shipped to OpenSearch API

### Getting Help
- GitHub Issues: `https://github.com/your-org/pg-api/issues`
- Documentation: `https://docs.yourdomain.com/pg-api`
- Support Email: `support@yourdomain.com`

## Appendix

### A. Environment-Specific Examples

#### Production Deployment Example
```bash
sudo python3 setup.py \
  --non-interactive \
  --action install \
  --domain pg-api.production.com \
  --opensearch-url https://opensearch.production.com \
  --install-path /opt/prod/pg-api \
  --postgres-host db.production.com \
  --postgres-port 5432 \
  --ssl-cert /path/to/production.crt \
  --ssl-key /path/to/production.key
```

#### Staging Deployment Example
```bash
sudo python3 setup.py \
  --non-interactive \
  --action install \
  --domain pg-api.staging.com \
  --opensearch-url https://opensearch.staging.com \
  --install-path /opt/staging/pg-api \
  --postgres-host localhost \
  --postgres-port 5432 \
  --use-letsencrypt
```

### B. Migration from Existing Installation

If migrating from another domain or server:

1. **Backup existing data**
```bash
# On old server
tar -czf pg-api-backup.tar.gz /opt/prod/pg-api/config /etc/pg-api/
```

2. **Transfer to new server**
```bash
scp pg-api-backup.tar.gz user@new-server:/tmp/
```

3. **Restore configuration**
```bash
# On new server
tar -xzf /tmp/pg-api-backup.tar.gz
# Update domain references in configuration files
```

4. **Run setup with upgrade action**
```bash
sudo python3 setup.py --action upgrade
```

### C. Uninstallation

To completely remove pg-api:

```bash
sudo python3 setup.py \
  --non-interactive \
  --action uninstall \
  --install-path /opt/prod/pg-api

# Manual cleanup (if needed)
sudo rm -rf /opt/prod/pg-api
sudo rm -f /etc/nginx/sites-enabled/pg-api*
sudo rm -f /etc/systemd/system/pg-api.service
sudo systemctl daemon-reload
```

## Version History

- **v0.1.0**: Initial release with basic PostgreSQL API functionality
- **v0.2.0**: Added OpenSearch integration and observability
- **v0.3.0**: Enhanced security features and rate limiting
- **Current**: Full production-ready deployment with complete automation