# Database Connection Troubleshooting Guide

## Problem
```
failed SASL auth (FATAL: password authentication failed for user "dev" (SQLSTATE 28P01))
```

This error indicates that the PostgreSQL password for user `dev` is incorrect or the user doesn't exist.

## Quick Fix Steps

### Step 1: Verify .env File Exists and Has Correct Values

On your server, check if `.env` file exists in `/var/www/html/internet/payment/`:

```bash
cd /var/www/html/internet/payment
ls -la .env
cat .env
```

### Step 2: Check Current Database Configuration

Your `.env` file should have these values:
```env
DB_HOST=172.104.253.190
DB_PORT=5432
DB_USER=dev
DB_PASSWORD=your_actual_password_here
DB_NAME=internet
DB_SSL_MODE=disable
```

**Important**: Make sure `DB_PASSWORD` has the actual password, not a placeholder.

### Step 3: Test Database Connection Manually

Test the connection using `psql`:

```bash
# Test connection (you'll be prompted for password)
psql -h 172.104.253.190 -p 5432 -U dev -d internet

# Or with password in environment variable
export PGPASSWORD='your_password_here'
psql -h 172.104.253.190 -p 5432 -U dev -d internet -c "SELECT version();"
```

### Step 4: Fix PostgreSQL User/Password

#### Option A: Reset Password for Existing User

If user `dev` exists but password is wrong:

```bash
# Connect as postgres superuser
sudo -u postgres psql
# Or if connecting remotely:
psql -h 172.104.253.190 -U postgres -d postgres
```

Then in PostgreSQL prompt:
```sql
ALTER USER dev WITH PASSWORD 'your_new_secure_password';
\q
```

Update your `.env` file with the new password:
```bash
nano /var/www/html/internet/payment/.env
# Update DB_PASSWORD=your_new_secure_password
```

#### Option B: Create New User (if user doesn't exist)

```bash
# Connect as postgres superuser
sudo -u postgres psql
# Or if connecting remotely:
psql -h 172.104.253.190 -U postgres -d postgres
```

Then in PostgreSQL prompt:
```sql
CREATE USER dev WITH PASSWORD 'your_secure_password_here';
ALTER ROLE dev SET client_encoding TO 'utf8';
ALTER ROLE dev SET default_transaction_isolation TO 'read committed';
ALTER ROLE dev SET timezone TO 'UTC';

-- Create database if it doesn't exist
CREATE DATABASE internet;

-- Grant privileges
GRANT ALL PRIVILEGES ON DATABASE internet TO dev;
\q
```

#### Option C: Use Existing User (if payment_user exists)

If you already have `payment_user` created (as per deployment guide), update your `.env`:

```bash
nano /var/www/html/internet/payment/.env
```

Change:
```env
DB_USER=payment_user
DB_PASSWORD=your_payment_user_password
```

### Step 5: Verify Connection Works

After fixing the password, test the connection:

```bash
cd /var/www/html/internet/payment
export PGPASSWORD='your_password_here'
psql -h 172.104.253.190 -p 5432 -U dev -d internet -c "SELECT 1;"
```

If this works, your payment service should connect.

### Step 6: Restart Payment Service

```bash
sudo systemctl restart payment-service
sudo systemctl status payment-service
```

### Step 7: Check Logs

```bash
sudo journalctl -u payment-service -f
```

## Common Issues

### Issue 1: User doesn't have access to database

```sql
-- Grant access
GRANT ALL PRIVILEGES ON DATABASE internet TO dev;
-- Connect to database and grant schema privileges
\c internet
GRANT ALL ON SCHEMA public TO dev;
```

### Issue 2: PostgreSQL not allowing remote connections

Check PostgreSQL configuration:
```bash
# Edit pg_hba.conf
sudo nano /etc/postgresql/*/main/pg_hba.conf

# Add or modify line for your host:
host    all             all             172.104.253.190/32    md5

# Edit postgresql.conf
sudo nano /etc/postgresql/*/main/postgresql.conf

# Ensure listen_addresses includes your IP or '*'
listen_addresses = '*'

# Restart PostgreSQL
sudo systemctl restart postgresql
```

### Issue 3: Firewall blocking connection

```bash
# Check if port 5432 is open
sudo ufw status
sudo netstat -tlnp | grep 5432

# If needed, allow PostgreSQL port
sudo ufw allow 5432/tcp
```

## Verification Script

You can use the diagnostic script (run on Linux server):

```bash
cd /var/www/html/internet/payment
chmod +x scripts/check_db_connection.sh
./scripts/check_db_connection.sh
```

## Security Note

- Never commit `.env` file to version control
- Use strong passwords for database users
- Restrict database access to specific IPs when possible
- Use SSL connections in production (`DB_SSL_MODE=require`)