docs/security/CONNECTION_STRING_GUIDE.md

# PostgreSQL Connection String Formation - Complete Guide

## Current Status

Your application now supports **both** naming conventions for PostgreSQL configuration:

### Option 1: POSTGRES_* Variables (Recommended)
```bash
POSTGRES_URI=postgresql+asyncpg://trans_owner:BookMyService7@ep-sweet-surf-a1qeduoy.ap-southeast-1.aws.neon.tech:5432/cuatrolabs
```

### Option 2: DB_* Variables (For Compatibility)
```bash
DB_PROTOCOL=postgresql+asyncpg
DB_USER=trans_owner
DB_PASSWORD=BookMyService7
DB_HOST=ep-sweet-surf-a1qeduoy.ap-southeast-1.aws.neon.tech
DB_PORT=5432
DB_NAME=cuatrolabs
DB_SSLMODE=require
```

## Connection String Formation Logic

The `app/core/config.py` now handles connection string formation as follows:

### Step 1: Read Environment Variables

The config tries both naming conventions:
```python
POSTGRES_HOST = os.getenv("POSTGRES_HOST") or os.getenv("DB_HOST") or "localhost"
POSTGRES_PORT = os.getenv("POSTGRES_PORT") or os.getenv("DB_PORT") or "5432"
POSTGRES_DB = os.getenv("POSTGRES_DB") or os.getenv("DB_NAME") or "cuatrolabs"
POSTGRES_USER = os.getenv("POSTGRES_USER") or os.getenv("DB_USER") or "postgres"
POSTGRES_PASSWORD = os.getenv("POSTGRES_PASSWORD") or os.getenv("DB_PASSWORD") or ""
POSTGRES_SSL_MODE = os.getenv("POSTGRES_SSL_MODE") or os.getenv("DB_SSLMODE") or "disable"
```

### Step 2: Build Connection URI

If `POSTGRES_URI` is not provided, it builds one from components:

```python
from urllib.parse import quote_plus

# URL-encode the password (important for special characters)
encoded_password = quote_plus(POSTGRES_PASSWORD)

# Get protocol (supports DB_PROTOCOL for compatibility)
protocol = os.getenv("DB_PROTOCOL", "postgresql+asyncpg")

# Build the URI
POSTGRES_URI = f"{protocol}://{POSTGRES_USER}:{encoded_password}@{POSTGRES_HOST}:{POSTGRES_PORT}/{POSTGRES_DB}"
```

### Step 3: Result

For your deployment with DB_* variables:
```
postgresql+asyncpg://trans_owner:BookMyService7@ep-sweet-surf-a1qeduoy.ap-southeast-1.aws.neon.tech:5432/cuatrolabs
```

## Key Features

### 1. URL Encoding
- Passwords are URL-encoded using `quote_plus()`
- Handles special characters like `@`, `#`, `%`, etc.
- Example: `Pass@word#123` becomes `Pass%40word%23123`

### 2. Dual Variable Support
- Checks `POSTGRES_*` variables first
- Falls back to `DB_*` variables if not found
- Maintains backward compatibility

### 3. Protocol Flexibility
- Supports `DB_PROTOCOL` environment variable
- Defaults to `postgresql+asyncpg`
- Can be changed to `postgresql` for sync drivers

### 4. SSL Mode Mapping
- Maps `DB_SSLMODE` to `POSTGRES_SSL_MODE`
- Supports: `disable`, `require`, `verify-full`

## Deployment Configuration

### For Your Current Deployment

Your deployment environment has these variables set:
- ✅ `DB_USER`
- ✅ `DB_PASSWORD`
- ✅ `DB_HOST`
- ✅ `DB_PORT`
- ✅ `DB_NAME` (or `POSTGRES_DB`)
- ✅ `DB_SSLMODE` (or `POSTGRES_SSL_MODE`)

**The config will now automatically build the connection string from these!**

### What Will Happen

When your app starts, you'll see:
```
[CONFIG] Built POSTGRES_URI from components
[CONFIG]   Host: ep-sweet-surf-a1qeduoy.ap-southeast-1.aws.neon.tech
[CONFIG]   Port: 5432
[CONFIG]   Database: cuatrolabs
[CONFIG]   User: trans_owner
[CONFIG]   SSL Mode: require
```

Then:
```
======================================================================
[POSTGRES] Starting Database Connection
======================================================================
[POSTGRES] Connection String: postgresql+asyncpg://trans_owner:***@ep-sweet-surf-a1qeduoy...
[POSTGRES] Host: ep-sweet-surf-a1qeduoy.ap-southeast-1.aws.neon.tech
[POSTGRES] Port: 5432
[POSTGRES] Database: cuatrolabs
[POSTGRES] SSL Mode: require
======================================================================

[POSTGRES] Attempting to connect (max retries: 30)...
[POSTGRES] ✅ Connection successful after 1 attempt(s)
```

## Troubleshooting

### If Connection Still Fails

1. **Check the logs** for the exact connection string being used
2. **Verify SSL mode** - Neon requires `require` or higher
3. **Check firewall** - Ensure port 5432 is accessible
4. **Verify credentials** - User and password must be correct
5. **Test DNS** - Ensure the hostname resolves

### Common Issues

#### Issue: "Connection timeout"
- **Cause**: Firewall blocking port 5432
- **Solution**: Check network/firewall rules

#### Issue: "Authentication failed"
- **Cause**: Wrong username or password
- **Solution**: Verify `DB_USER` and `DB_PASSWORD`

#### Issue: "SSL required"
- **Cause**: Database requires SSL but `DB_SSLMODE=disable`
- **Solution**: Set `DB_SSLMODE=require`

#### Issue: "Database does not exist"
- **Cause**: Wrong database name
- **Solution**: Verify `DB_NAME` matches your database

## Testing Locally

To test the connection string formation:

```bash
source venv/bin/activate
python test_connection_string.py
python test_config_vars.py
```

## Next Steps

1. **Redeploy** your application with the updated code
2. **Monitor logs** during startup
3. **Look for** the connection string in logs (password will be masked)
4. **Verify** successful connection message

The connection string formation is now robust and should work with your deployment environment's `DB_*` variables!