supabase-extract-db-string

What this skill does

# Database Connection String Detection

> 🔴 **CRITICAL: PROGRESSIVE FILE UPDATES REQUIRED**
>
> You MUST write to context files **AS YOU GO**, not just at the end.
> - Write to `.sb-pentest-context.json` **IMMEDIATELY after each discovery**
> - Log to `.sb-pentest-audit.log` **BEFORE and AFTER each action**
> - **DO NOT** wait until the skill completes to update files
> - If the skill crashes or is interrupted, all prior findings must already be saved
>
> **This is not optional. Failure to write progressively is a critical error.**

This skill detects if PostgreSQL database connection strings are accidentally exposed in client-side code.

## When to Use This Skill

- As part of every security audit
- When reviewing code before production
- When Supabase database access is suspected

## Prerequisites

- Target application accessible
- Supabase detection completed (auto-invokes if needed)

## Why This Is Critical

Exposed database connection strings allow:

| Impact | Description |
|--------|-------------|
| 🔴 Direct DB Access | Bypass API, connect directly to PostgreSQL |
| 🔴 Full Data Access | Read/write all data without RLS |
| 🔴 Schema Access | View and modify database structure |
| 🔴 User Enumeration | Access auth.users table directly |

**This is a P0 (Critical) finding requiring immediate action.**

## Connection String Patterns

### Supabase Database URL

```
postgresql://postgres:[password]@db.[project-ref].supabase.co:5432/postgres
```

### Connection String Components

| Component | Example | Sensitivity |
|-----------|---------|-------------|
| Host | `db.abc123.supabase.co` | Medium |
| Port | `5432` | Low |
| Database | `postgres` | Low |
| Username | `postgres` | Medium |
| Password | `[your-password]` | 🔴 Critical |

### Pooler Connection (Supavisor)

```
postgresql://postgres.[project-ref]:[password]@aws-0-us-east-1.pooler.supabase.com:6543/postgres
```

## Detection Patterns

### 1. Full Connection Strings

```javascript
// ❌ CRITICAL - Full connection string
const dbUrl = 'postgresql://postgres:[email protected]:5432/postgres'
```

### 2. Environment Variable Leaks

```javascript
// ❌ Exposed in client bundle
process.env.DATABASE_URL
process.env.POSTGRES_URL
process.env.SUPABASE_DB_URL
```

### 3. Partial Exposure

```javascript
// ⚠️ Password exposed separately
const DB_PASSWORD = 'MySecretPass123'
const DB_HOST = 'db.abc123.supabase.co'
```

### 4. ORM Configuration

```javascript
// ❌ Database config in client code
const prisma = new PrismaClient({
  datasources: {
    db: {
      url: 'postgresql://postgres:[email protected]:5432/postgres'
    }
  }
})
```

## Usage

### Basic Check

```
Check for database connection strings on https://myapp.example.com
```

### Deep Scan

```
Deep scan for DB credentials on https://myapp.example.com
```

## Output Format

### No Connection String Found (Good)

```
═══════════════════════════════════════════════════════════
 DATABASE CONNECTION STRING CHECK
═══════════════════════════════════════════════════════════

 Status: ✅ No database connection strings detected

 Scanned:
 ├── JavaScript bundles: 5 files analyzed
 ├── PostgreSQL patterns: None found
 ├── Connection strings: None found
 └── Password patterns: None found

 Result: PASS - No direct database credentials exposed
═══════════════════════════════════════════════════════════
```

### Connection String FOUND (Critical)

```
═══════════════════════════════════════════════════════════
 🔴 CRITICAL: DATABASE CONNECTION STRING EXPOSED
═══════════════════════════════════════════════════════════

 Severity: P0 - CRITICAL
 Status: ❌ PostgreSQL connection string found in client code!

 ⚠️  IMMEDIATE ACTION REQUIRED ⚠️

 Connection String:
 postgresql://postgres:MySecr***@db.abc123def.supabase.co:5432/postgres
 (Password partially redacted in display, full value in context file)

 Parsed Components:
 ├── Host: db.abc123def.supabase.co
 ├── Port: 5432
 ├── Database: postgres
 ├── Username: postgres
 └── Password: [EXPOSED] ← CRITICAL

 Location:
 └── /static/js/api.chunk.js (line 234)
     const DATABASE_URL = 'postgresql://postgres:...'

 Impact Assessment:
 ├── 🔴 Direct PostgreSQL access possible
 ├── 🔴 All RLS policies bypassed
 ├── 🔴 Can access auth.users table
 ├── 🔴 Can modify database schema
 └── 🔴 Full data exfiltration possible

 ═══════════════════════════════════════════════════════════
 IMMEDIATE REMEDIATION STEPS
 ═══════════════════════════════════════════════════════════

 1. CHANGE DATABASE PASSWORD NOW
    → Supabase Dashboard > Settings > Database > Reset database password

 2. REMOVE FROM CLIENT CODE
    → Delete connection string from source code
    → Ensure DATABASE_URL is not in NEXT_PUBLIC_* or VITE_* env vars
    → Redeploy application

 3. AUDIT FOR ABUSE
    → Check Supabase logs for direct PostgreSQL connections
    → Review for unauthorized data access or modifications

 4. USE PROPER ARCHITECTURE
    → Client should ONLY use Supabase client library (REST API)
    → Direct DB access should ONLY be from:
      - Edge Functions
      - Server-side code
      - Migration tools

 Documentation:
 → https://supabase.com/docs/guides/database/connecting-to-postgres
 → https://supabase.com/docs/guides/functions

═══════════════════════════════════════════════════════════
```

## Context Output

```json
{
  "findings": [
    {
      "id": "DB_CONNECTION_STRING_EXPOSED",
      "severity": "P0",
      "title": "PostgreSQL Connection String Exposed",
      "description": "Database connection string with password found in client-side code",
      "location": {
        "file": "/static/js/api.chunk.js",
        "line": 234
      },
      "evidence": {
        "host": "db.abc123def.supabase.co",
        "port": 5432,
        "database": "postgres",
        "username": "postgres",
        "password_exposed": true
      },
      "remediation": {
        "immediate": "Reset database password in Supabase Dashboard",
        "long_term": "Move DB operations to Edge Functions",
        "docs": "https://supabase.com/docs/guides/database/connecting-to-postgres"
      }
    }
  ],
  "supabase": {
    "db_string_exposed": true,
    "db_host": "db.abc123def.supabase.co"
  }
}
```

## Partial Exposure

Even partial exposure is concerning:

```
═══════════════════════════════════════════════════════════
 ⚠️ PARTIAL DATABASE CREDENTIALS FOUND
═══════════════════════════════════════════════════════════

 Severity: P1 - High

 Found:
 ├── Database host: db.abc123def.supabase.co (line 45)
 ├── Database password: [16 char string] (line 89)
 └── Could potentially be combined for access

 Recommendation:
 → Rotate database password as precaution
 → Remove all DB-related values from client code
═══════════════════════════════════════════════════════════
```

## Common Causes

| Cause | Solution |
|-------|----------|
| Wrong env prefix | Never use `NEXT_PUBLIC_DATABASE_URL` |
| SSR code in client | Ensure server-only code stays server-side |
| Bundler misconfiguration | Review webpack/vite config for env exposure |
| Copy-paste error | Double-check what you're committing |

## Architecture Guidance

### Wrong (Direct DB in Client)

```javascript
// ❌ NEVER in client code
import { Pool } from 'pg'
const pool = new Pool({
  connectionString: process.env.DATABASE_URL  // ❌
})
```

### Correct (API or Edge Function)

```javascript
// ✅ Client uses Supabase client
const { data } = await supabase
  .from('products')
  .select('*')

// OR call an Edge Function for complex queries
const { data } = await supabase.functions.invoke('complex-query')
```

### Edge Function (Server-Side)

```typescript
// supabase/functions/complex-query/index.ts
import { createClient } from '@supabase/supabase-js'

Deno.serve(async (req) => {
  // ✅ Direct DB access only on server
  const supabase = createClient(
    Deno.env.get('SUPABASE_URL'),
    Deno.env.get('SUPABASE_SERVICE_ROLE_KEY')
  )

  // Complex query that can't be done via