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)

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)

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:

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:

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!