Fairdb Onboard Customer

You are onboarding a new customer for FairDB PostgreSQL as a Service. This comprehensive workflow creates their database, users, configures access, sets up backups, and provides connection details.

Step 1: Gather Customer Information

Collect these details:

1. Customer Name: Company/organization name
2. Database Name: Preferred database name (lowercase, no spaces)
3. Primary Contact: Name and email
4. Plan Type: Starter/Professional/Enterprise
5. IP Allowlist: Customer IP addresses for access
6. Special Requirements: Extensions, configurations, etc.

Step 2: Validate Resources

# Check available resources
df -h /var/lib/postgresql
free -h
sudo -u postgres psql -c "SELECT count(*) as database_count FROM pg_database WHERE datistemplate = false;"

# Check current connections
sudo -u postgres psql -c "SELECT count(*) FROM pg_stat_activity;"

Step 3: Create Customer Database

# Set customer variables
CUSTOMER_NAME="customer_name"  # Replace with actual
DB_NAME="${CUSTOMER_NAME}_db"
DB_OWNER="${CUSTOMER_NAME}_owner"
DB_USER="${CUSTOMER_NAME}_user"
DB_READONLY="${CUSTOMER_NAME}_readonly"

# Generate secure passwords
DB_OWNER_PASS=$(openssl rand -base64 32)
DB_USER_PASS=$(openssl rand -base64 32)
DB_READONLY_PASS=$(openssl rand -base64 32)

# Create database and users
sudo -u postgres psql << EOF
-- Create database owner role
CREATE ROLE ${DB_OWNER} WITH LOGIN PASSWORD '${DB_OWNER_PASS}'
  CREATEDB CREATEROLE CONNECTION LIMIT 5;
…

Step 4: Configure Network Access

# Add customer IP to pg_hba.conf
CUSTOMER_IP="203.0.113.0/32"  # Replace with actual customer IP

# Backup pg_hba.conf
sudo cp /etc/postgresql/16/main/pg_hba.conf /etc/postgresql/16/main/pg_hba.conf.$(date +%Y%m%d)

# Add customer access rules
cat << EOF | sudo tee -a /etc/postgresql/16/main/pg_hba.conf

# Customer: ${CUSTOMER_NAME}
hostssl ${DB_NAME}     ${DB_OWNER}      ${CUSTOMER_IP}    scram-sha-256
hostssl ${DB_NAME}     ${DB_USER}       ${CUSTOMER_IP}    scram-sha-256
hostssl ${DB_NAME}     ${DB_READONLY}   ${CUSTOMER_IP}    scram-sha-256
EOF

# Update firewall
sudo ufw allow from ${CUSTOMER_IP} to any port 5432 comment "FairDB: ${CUSTOMER_NAME}"
…

Step 5: Set Resource Limits

# Configure per-database resource limits based on plan
case "${PLAN_TYPE}" in
  "starter")
    MAX_CONN=50
    WORK_MEM="4MB"
    SHARED_BUFFERS="256MB"
    ;;
  "professional")
    MAX_CONN=100
    WORK_MEM="8MB"
    SHARED_BUFFERS="1GB"
    ;;
  "enterprise")
    MAX_CONN=200
    WORK_MEM="16MB"
    SHARED_BUFFERS="4GB"
    ;;
esac
…

Step 6: Configure Backup Policy

# Create customer-specific backup configuration
cat << EOF | sudo tee -a /opt/fairdb/configs/backup-${CUSTOMER_NAME}.conf
# Backup configuration for ${CUSTOMER_NAME}
DATABASE=${DB_NAME}
BACKUP_RETENTION_DAYS=30
BACKUP_SCHEDULE="0 3 * * *"  # Daily at 3 AM
BACKUP_TYPE="full"
S3_PREFIX="${CUSTOMER_NAME}/"
EOF

# Add to pgBackRest configuration
sudo tee -a /etc/pgbackrest/pgbackrest.conf << EOF

[${CUSTOMER_NAME}]
pg1-path=/var/lib/postgresql/16/main
pg1-database=${DB_NAME}
pg1-port=5432
backup-user=backup_user
…

Step 7: Setup Monitoring

# Create monitoring user and grants
sudo -u postgres psql -d ${DB_NAME} << EOF
-- Grant monitoring permissions
GRANT pg_monitor TO ${DB_READONLY};
GRANT EXECUTE ON FUNCTION pg_stat_statements_reset() TO ${DB_OWNER};
EOF

# Create customer monitoring script
cat << 'EOF' | sudo tee /opt/fairdb/scripts/monitor-${CUSTOMER_NAME}.sh
#!/bin/bash
# Monitoring script for ${CUSTOMER_NAME}

DB_NAME="${DB_NAME}"
ALERT_THRESHOLD_CONNECTIONS=80
ALERT_THRESHOLD_SIZE_GB=100

# Check connection usage
CONN_USAGE=$(sudo -u postgres psql -t -c "
…

Step 8: Generate SSL Certificates

# Create customer SSL certificate
sudo mkdir -p /etc/postgresql/16/main/ssl/${CUSTOMER_NAME}
cd /etc/postgresql/16/main/ssl/${CUSTOMER_NAME}

# Generate customer-specific SSL cert
sudo openssl req -new -x509 -days 365 -nodes \
  -out server.crt -keyout server.key \
  -subj "/C=US/ST=State/L=City/O=FairDB/OU=${CUSTOMER_NAME}/CN=${CUSTOMER_NAME}.fairdb.io"

# Set permissions
sudo chmod 600 server.key
sudo chown postgres:postgres server.*

# Create client certificate
sudo openssl req -new -nodes \
  -out client.csr -keyout client.key \
  -subj "/C=US/ST=State/L=City/O=FairDB/OU=${CUSTOMER_NAME}/CN=${DB_USER}"
…

Step 9: Create Connection Documentation

# Generate connection details document
cat << EOF > /tmp/${CUSTOMER_NAME}-connection-details.md
# FairDB PostgreSQL Connection Details
## Customer: ${CUSTOMER_NAME}

### Database Information
- **Database Name**: ${DB_NAME}
- **Host**: fairdb-prod.example.com
- **Port**: 5432
- **SSL Required**: Yes

### User Credentials
#### Database Owner (DDL Operations)
- **Username**: ${DB_OWNER}
- **Password**: ${DB_OWNER_PASS}
- **Connection Limit**: 5
- **Permissions**: Full database owner
…