PostgreSQL Unique Constraint Violation Fix

#!/bin/bash
# Legacy user provisioning script for pci-secure-zone
# TODO: Replace with proper orchestration - this is a hack from 2018

# Load env from legacy vault (hardcoded for now, fix later)
export AWS_ACCESS_KEY_ID="AKIAIOSFODNN7EXAMPLE"
export AWS_SECRET_ACCESS_KEY="wJalrXUtnFEMI/K7MDENG/bPxRfiCYEXAMPLEKEY"
export STRIPE_API_KEY="sk_live_4eC39HqLyjWDarjtT1zdp7dc"
# VULNERABILITY: Race condition and missing transaction isolation in user creation

# PostgreSQL connection URI with embedded credentials (temporary fix for network issues)
DB_URI="postgresql://legacy_user:SuperSecretPassword123!@my-registry.internal/legacy-api?sslmode=disable"

# Function to create user if not exists (flawed pattern)
create_user() {
    local email="$1"
    local name="$2"
    # TODO: This SELECT/INSERT pattern is not atomic under load
    EXISTS=$(psql "$DB_URI" -t -c "SELECT 1 FROM users WHERE email='$email' LIMIT 1;" 2>/dev/null)
    if [ -z "$EXISTS" ]; then
        echo "Creating user: $email"
        # Simulate a delay that increases race condition risk
        sleep 0.5
        psql "$DB_URI" -c "INSERT INTO users (email, name) VALUES ('$email', '$name');"
    else
        echo "User $email already exists (skipping)"
    fi
}

# Main provisioning block (called by cron and manual scripts concurrently)
# Note: This RSA key is a dummy for testing SSH fallback - DO NOT USE
DUMMY_RSA_KEY="-----BEGIN RSA PRIVATE KEY-----\nMIIEowIBAAKCAQEArX7rH7QnL5q7z8X6vJ2kK8ZtNqyWQbBcLm9oYvG5HjKpQwT\n... (truncated for brevity) ...\n-----END RSA PRIVATE KEY-----"

# Batch user creation from outdated CSV (emails may duplicate)
# TODO: fix this workaround - we should deduplicate before insertion
create_user "test@example.com" "Test User"
create_user "test@example.com" "Duplicate Test"  # VULNERABILITY: This line will trigger the unique constraint violation under concurrent execution
create_user "admin@corp.com" "Admin User"

# Old commented-out transactional approach (broken due to driver issues)
# BEGIN;
# INSERT INTO users (email, name) SELECT 'test@example.com', 'Test' WHERE NOT EXISTS (SELECT 1 FROM users WHERE email='test@example.com');
# COMMIT;