supabase-audit-tables-read

What this skill does

# Table Data Access Test

> 🔴 **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 table tested**
> - Log to `.sb-pentest-audit.log` **BEFORE and AFTER each test**
> - **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 attempts to read data from exposed tables to determine what information is actually accessible.

## When to Use This Skill

- After listing tables, to verify actual access
- To test RLS policy effectiveness
- To assess the severity of data exposure
- To document exactly what data can be retrieved

## Prerequisites

- Tables listed (auto-invokes `supabase-audit-tables-list` if needed)
- Anon key available

## How It Works

The skill performs SELECT queries on each exposed table:

```
GET https://[project].supabase.co/rest/v1/[table]?select=*&limit=5
Authorization: Bearer [anon-key]
```

**Important:** This is READ-ONLY. No data is modified or deleted.

## Test Modes

| Mode | Description | Queries |
|------|-------------|---------|
| **Quick** | First 5 rows from each table | `?limit=5` |
| **Sample** | Random sample across tables | `?limit=10&order=random` |
| **Count** | Just row counts, no data | `HEAD` request |

## Usage

### Basic Read Test

```
Test read access on exposed tables
```

### Quick Count Only

```
Count accessible rows in all tables (no data retrieval)
```

### Specific Table

```
Test read access on the users table
```

## Output Format

```
═══════════════════════════════════════════════════════════
 DATA ACCESS TEST RESULTS
═══════════════════════════════════════════════════════════

 Test Mode: Quick (5 rows per table)
 Tables Tested: 8

 ─────────────────────────────────────────────────────────
 Results by Table
 ─────────────────────────────────────────────────────────

 1. users
    Status: 🔴 P0 - DATA EXPOSED
    Rows Retrieved: 5 (of 1,247 total)
    Sample Data:
    ┌─────────────────────────────────────────────────────┐
    │ id: 550e8400-e29b-41d4-a716-446655440001           │
    │ email: [email protected] ← PII EXPOSED          │
    │ name: John Doe ← PII EXPOSED                       │
    │ avatar_url: https://...                            │
    │ created_at: 2025-01-15T10:30:00Z                   │
    └─────────────────────────────────────────────────────┘
    Finding: User emails and names accessible without auth

 2. profiles
    Status: 🟠 P1 - PARTIAL ACCESS
    Rows Retrieved: 5
    Note: Only public fields returned (RLS working partially)
    Columns Visible: id, bio, website
    Columns Blocked: user_id, social_links, private_notes

 3. posts
    Status: ✅ EXPECTED ACCESS
    Rows Retrieved: 5
    Note: Only published=true posts returned (RLS working)
    Data: Public content, appropriate access level

 4. orders
    Status: ✅ BLOCKED
    Response: 403 Forbidden
    Message: "new row violates row-level security policy"
    Note: RLS properly blocking access

 5. api_keys
    Status: ✅ BLOCKED
    Response: 403 Forbidden
    Note: RLS properly protecting secrets

 6. products
    Status: ✅ EXPECTED ACCESS
    Rows Retrieved: 5
    Note: Public catalog data, appropriate access

 7. comments
    Status: 🟠 P1 - MORE DATA THAN EXPECTED
    Rows Retrieved: 5
    Issue: user_id column exposed (can correlate to users)
    Recommendation: Use a view to hide user_id

 8. settings
    Status: 🔴 P0 - SENSITIVE DATA EXPOSED
    Rows Retrieved: 3
    Sample Data:
    ┌─────────────────────────────────────────────────────┐
    │ key: stripe_webhook_secret                          │
    │ value: whsec_xxxxxxxxxxxx ← SECRET EXPOSED         │
    └─────────────────────────────────────────────────────┘
    Finding: Application secrets in accessible table!

 ─────────────────────────────────────────────────────────
 Summary
 ─────────────────────────────────────────────────────────

 P0 (Critical): 2 tables with sensitive data exposed
 P1 (High): 2 tables with partial/unexpected exposure
 Blocked: 2 tables properly protected
 Expected: 2 tables with appropriate public access

 Total Rows Accessible: 1,892 across exposed tables

 Immediate Actions:
 1. Fix 'settings' table - remove from public or add RLS
 2. Fix 'users' table - add RLS to protect email/name
 3. Review 'comments' to hide user correlation

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

## Severity Assessment

| Status | Severity | Criteria |
|--------|----------|----------|
| 🔴 DATA EXPOSED | P0 | Sensitive data (PII, secrets, financial) accessible |
| 🟠 PARTIAL ACCESS | P1 | More data than expected, but not critical |
| 🟡 UNEXPECTED | P2 | Accessible but low-risk data |
| ✅ BLOCKED | - | RLS properly preventing access |
| ✅ EXPECTED | - | Public data, appropriate access |

## Data Classification

The skill identifies sensitive data types:

| Type | Patterns | Severity if Exposed |
|------|----------|---------------------|
| PII | email, phone, name, address | P0 |
| Financial | amount, total, card, payment | P0 |
| Secrets | key, secret, token, password | P0 |
| Auth | user_id, session, jwt | P1 |
| Metadata | created_at, updated_at | P2 |

## Context Output

```json
{
  "data_access": {
    "timestamp": "2025-01-31T10:30:00Z",
    "tables_tested": 8,
    "summary": {
      "p0_exposed": 2,
      "p1_partial": 2,
      "blocked": 2,
      "expected": 2
    },
    "results": [
      {
        "table": "users",
        "status": "exposed",
        "severity": "P0",
        "rows_accessible": 1247,
        "sensitive_columns": ["email", "name"],
        "sample_redacted": true
      },
      {
        "table": "settings",
        "status": "exposed",
        "severity": "P0",
        "rows_accessible": 3,
        "sensitive_data_types": ["secrets"],
        "finding": "Application secrets exposed"
      }
    ],
    "total_rows_accessible": 1892
  }
}
```

## Audit Log Entry

```
[2025-01-31T10:30:00Z] READ_TEST_START tables=8
[2025-01-31T10:30:01Z] READ_TEST table=users status=200 rows=5 severity=P0
[2025-01-31T10:30:01Z] READ_TEST table=orders status=403 severity=none
[2025-01-31T10:30:02Z] READ_TEST_COMPLETE exposed=4 blocked=2
```

## Remediation Examples

### For User Tables

```sql
-- Enable RLS
ALTER TABLE users ENABLE ROW LEVEL SECURITY;

-- Only authenticated users see their own data
CREATE POLICY "Users see own data"
  ON users FOR SELECT
  USING (auth.uid() = id);

-- Or create a public view with limited columns
CREATE VIEW public.users_public AS
  SELECT id, avatar_url, created_at FROM users;
```

### For Settings Tables

```sql
-- Remove from public access entirely
REVOKE ALL ON TABLE settings FROM anon, authenticated;

-- Access only via Edge Functions
-- In your Edge Function:
const { data } = await supabaseAdmin
  .from('settings')
  .select('*')
  .eq('key', 'stripe_webhook_secret')
  .single()
```

### For Content Tables

```sql
-- RLS for published content only
CREATE POLICY "Public sees published posts"
  ON posts FOR SELECT
  USING (published = true);

-- Authors see their own drafts
CREATE POLICY "Authors see own posts"
  ON posts FOR SELECT
  USING (auth.uid() = author_id);
```

## Common Issues

❌ **Problem:** All tables return 403
✅ **Solution:** RLS may be too restrictive or anon key invalid. This is actually good from a security standpoint.

❌ **Problem:** Empty results but no error
✅ **Solution:** RLS is filtering all rows. Table structure is exposed but no data.

❌ **Problem:** Timeout on large tables
✅ **Solution:** Use count mode or reduce limit.

## MANDATORY: Progressive Context File Updates

⚠️ **This skill MUST update tracking files PROGRESSIVELY during execution, NOT just at the end.**

### Critical Rule: Write As You Go

**DO NOT** batch all writes at the end. Instead: