53 lines
1.6 KiB
Bash
Executable File
53 lines
1.6 KiB
Bash
Executable File
#!/bin/bash
|
|
# Usage: SUPABASE_DB_URL="postgresql://postgres:..." bash db/03-migrate-passwords.sh
|
|
set -e
|
|
|
|
if [ -z "$SUPABASE_DB_URL" ]; then
|
|
echo "ERROR: SUPABASE_DB_URL not set"
|
|
exit 1
|
|
fi
|
|
|
|
SERVER="setia@ims.setia.com.my"
|
|
PORT=9321
|
|
AUTH_DUMP="/tmp/ims-auth-users-$(date +%Y%m%d-%H%M%S).csv"
|
|
|
|
echo "==> Exporting auth.users (id + encrypted_password + created_at) from Supabase..."
|
|
psql "$SUPABASE_DB_URL" -c "\COPY (SELECT id, encrypted_password, created_at FROM auth.users WHERE encrypted_password IS NOT NULL) TO STDOUT WITH CSV HEADER" > "$AUTH_DUMP"
|
|
|
|
echo "==> Exported $(wc -l < "$AUTH_DUMP") rows (including header)"
|
|
|
|
echo "==> Copying to server..."
|
|
scp -P "$PORT" "$AUTH_DUMP" "$SERVER:/tmp/auth-users.csv"
|
|
|
|
echo "==> Applying password hashes to users table on server..."
|
|
ssh -p "$PORT" "$SERVER" "sudo -u postgres psql -d ims" <<'ENDSQL'
|
|
-- Create temp table for the auth data
|
|
CREATE TEMP TABLE auth_users_import (
|
|
id UUID,
|
|
password_hash TEXT,
|
|
created_at TIMESTAMPTZ
|
|
);
|
|
|
|
\COPY auth_users_import FROM '/tmp/auth-users.csv' WITH CSV HEADER;
|
|
|
|
-- Update users table
|
|
UPDATE users u
|
|
SET
|
|
password_hash = a.password_hash,
|
|
email_verified_at = COALESCE(u.email_verified_at, a.created_at)
|
|
FROM auth_users_import a
|
|
WHERE u.id = a.id;
|
|
|
|
SELECT count(*) AS updated_users FROM users WHERE password_hash IS NOT NULL;
|
|
ENDSQL
|
|
|
|
echo "==> Verifying no NULL password_hash remains..."
|
|
ssh -p "$PORT" "$SERVER" "sudo -u postgres psql -d ims -c \"SELECT count(*) AS null_password_users FROM users WHERE password_hash IS NULL;\""
|
|
# Should be 0
|
|
|
|
echo "==> Cleaning up..."
|
|
ssh -p "$PORT" "$SERVER" "rm /tmp/auth-users.csv"
|
|
rm "$AUTH_DUMP"
|
|
|
|
echo "==> Password migration complete."
|