explorer-monorepo/docs/VMID_5000_DATABASE_FIX_COMMANDS.md

# Database Password Fix Commands for VMID 5000 (Blockscout)

## Commands for Root User in VMID 5000

These commands should be run **inside VMID 5000** as the root user. You can execute them using:

```bash
# From Proxmox host
pct exec 5000 -- bash -c "COMMAND_HERE"

# Or if you have shell access to VMID 5000
# (SSH into the container or use pct enter 5000)
```

---

## Option 1: Fix Database Password (If PostgreSQL is Accessible from VMID 5000)

### Step 1: Check Database Connectivity

```bash
# Check if PostgreSQL is accessible
pg_isready -h localhost -p 5432 -U explorer

# Or test connection
psql -h localhost -p 5432 -U postgres -c "SELECT version();"
```

### Step 2: Reset Explorer User Password

```bash
# Connect as postgres superuser and reset password
psql -h localhost -p 5432 -U postgres << EOF
-- Create user if it doesn't exist, or update password if it does
DO \$\$
BEGIN
    IF NOT EXISTS (SELECT FROM pg_user WHERE usename = 'explorer') THEN
        CREATE USER explorer WITH PASSWORD 'changeme';
        RAISE NOTICE 'User explorer created';
    ELSE
        ALTER USER explorer WITH PASSWORD 'changeme';
        RAISE NOTICE 'User explorer password updated';
    END IF;
END
\$\$;

-- Create database if it doesn't exist
SELECT 'CREATE DATABASE explorer OWNER explorer'
WHERE NOT EXISTS (SELECT FROM pg_database WHERE datname = 'explorer')\gexec

-- Grant privileges
GRANT ALL PRIVILEGES ON DATABASE explorer TO explorer;
EOF
```

### Step 3: Verify Connection

```bash
# Test connection with new password
PGPASSWORD=changeme psql -h localhost -p 5432 -U explorer -d explorer -c "SELECT 1;"
```

---

## Option 2: If Database is on Different Host

If the database is running on a different host (e.g., `192.168.11.10` or another container):

### Step 1: Check Network Connectivity

```bash
# Test if database host is reachable
ping -c 3 192.168.11.10

# Test if PostgreSQL port is accessible
nc -zv 192.168.11.10 5432
# Or
telnet 192.168.11.10 5432
```

### Step 2: Reset Password on Remote Database

```bash
# Replace DB_HOST with actual database host IP
DB_HOST="192.168.11.10"  # or "localhost" if on same host

psql -h $DB_HOST -p 5432 -U postgres << EOF
DO \$\$
BEGIN
    IF NOT EXISTS (SELECT FROM pg_user WHERE usename = 'explorer') THEN
        CREATE USER explorer WITH PASSWORD 'changeme';
    ELSE
        ALTER USER explorer WITH PASSWORD 'changeme';
    END IF;
END
\$\$;

SELECT 'CREATE DATABASE explorer OWNER explorer'
WHERE NOT EXISTS (SELECT FROM pg_database WHERE datname = 'explorer')\gexec

GRANT ALL PRIVILEGES ON DATABASE explorer TO explorer;
EOF
```

---

## Option 3: If Using Docker Compose in VMID 5000

If Blockscout is running via Docker Compose and the database is in a Docker container:

### Step 1: Check Docker Containers

```bash
# List running containers
docker ps

# Check if postgres container is running
docker ps | grep postgres
```

### Step 2: Access PostgreSQL Container

```bash
# Find postgres container name (adjust if different)
POSTGRES_CONTAINER=$(docker ps | grep postgres | awk '{print $1}')

# Reset password in postgres container
docker exec -it $POSTGRES_CONTAINER psql -U postgres << EOF
DO \$\$
BEGIN
    IF NOT EXISTS (SELECT FROM pg_user WHERE usename = 'explorer') THEN
        CREATE USER explorer WITH PASSWORD 'changeme';
    ELSE
        ALTER USER explorer WITH PASSWORD 'changeme';
    END IF;
END
\$\$;

SELECT 'CREATE DATABASE explorer OWNER explorer'
WHERE NOT EXISTS (SELECT FROM pg_database WHERE datname = 'explorer')\gexec

GRANT ALL PRIVILEGES ON DATABASE explorer TO explorer;
EOF
```

---

## Option 4: Complete One-Line Fix (If PostgreSQL is Local)

```bash
# Single command to fix everything (run as root in VMID 5000)
psql -h localhost -p 5432 -U postgres -c "CREATE USER explorer WITH PASSWORD 'changeme';" 2>/dev/null || \
psql -h localhost -p 5432 -U postgres -c "ALTER USER explorer WITH PASSWORD 'changeme';" && \
psql -h localhost -p 5432 -U postgres -c "CREATE DATABASE explorer OWNER explorer;" 2>/dev/null && \
psql -h localhost -p 5432 -U postgres -c "GRANT ALL PRIVILEGES ON DATABASE explorer TO explorer;"
```

---

## Verification Commands

After running the fix, verify the connection:

```bash
# Test connection
PGPASSWORD=changeme psql -h localhost -p 5432 -U explorer -d explorer -c "SELECT current_user, current_database();"

# Check user exists
psql -h localhost -p 5432 -U postgres -c "\du explorer"

# Check database exists
psql -h localhost -p 5432 -U postgres -c "\l explorer"
```

---

## Troubleshooting

### If `psql` command not found:

```bash
# Install PostgreSQL client
apt-get update
apt-get install -y postgresql-client
```

### If connection to postgres user fails:

```bash
# Check PostgreSQL authentication configuration
cat /etc/postgresql/*/main/pg_hba.conf | grep -v "^#"

# Or if using Docker, check container environment
docker exec $POSTGRES_CONTAINER env | grep POSTGRES
```

### If database host is unknown:

```bash
# Find database host from Blockscout configuration
grep -r "DATABASE_URL\|DB_HOST\|POSTGRES" /opt/blockscout/ 2>/dev/null | head -10

# Or check docker-compose.yml
cat /opt/blockscout/docker-compose.yml | grep -E "POSTGRES|DATABASE" | head -10
```

---

## Notes

- Replace `changeme` with the actual password if different
- Replace `localhost` with the actual database host if different
- The database might be running in a separate container or on a different host
- Check Blockscout's configuration to determine the correct database host and credentials