supabase-audit-rpc

What this skill does

# RPC Functions Audit

> 🔴 **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 function tested**
> - Log to `.sb-pentest-audit.log` **BEFORE and AFTER each function 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 discovers and tests PostgreSQL functions exposed via Supabase's RPC endpoint.

## When to Use This Skill

- To discover exposed database functions
- To test if functions bypass RLS
- To check for SQL injection in function parameters
- As part of comprehensive API security testing

## Prerequisites

- Supabase URL and anon key available
- Tables audit completed (recommended)

## Understanding Supabase RPC

Supabase exposes PostgreSQL functions via:

```
POST https://[project].supabase.co/rest/v1/rpc/[function_name]
```

Functions can:
- ✅ Respect RLS (if using `auth.uid()` and proper security)
- ❌ Bypass RLS (if `SECURITY DEFINER` without checks)
- ❌ Execute arbitrary SQL (if poorly written)

## Risk Levels for Functions

| Type | Risk | Description |
|------|------|-------------|
| `SECURITY INVOKER` | Lower | Runs with caller's permissions |
| `SECURITY DEFINER` | Higher | Runs with definer's permissions |
| Accepts text/json | Higher | Potential for injection |
| Returns setof | Higher | Can return multiple rows |

## Usage

### Basic RPC Audit

```
Audit RPC functions on my Supabase project
```

### Test Specific Function

```
Test the get_user_data RPC function
```

## Output Format

```
═══════════════════════════════════════════════════════════
 RPC FUNCTIONS AUDIT
═══════════════════════════════════════════════════════════

 Project: abc123def.supabase.co
 Functions Found: 6

 ─────────────────────────────────────────────────────────
 Function Inventory
 ─────────────────────────────────────────────────────────

 1. get_user_profile(user_id uuid)
    Security: INVOKER
    Returns: json
    Status: ✅ SAFE

    Analysis:
    ├── Uses auth.uid() for authorization
    ├── Returns only caller's own profile
    └── RLS is respected

 2. search_posts(query text)
    Security: INVOKER
    Returns: setof posts
    Status: ✅ SAFE

    Analysis:
    ├── Parameterized query (no injection)
    ├── RLS filters results
    └── Only returns published posts

 3. get_all_users()
    Security: DEFINER
    Returns: setof users
    Status: 🔴 P0 - RLS BYPASS

    Analysis:
    ├── SECURITY DEFINER runs as owner
    ├── No auth.uid() check inside function
    ├── Returns ALL users regardless of caller
    └── Bypasses RLS completely!

    Test Result:
    POST /rest/v1/rpc/get_all_users
    → Returns 1,247 user records with PII

    Immediate Fix:
    ```sql
    -- Add authorization check
    CREATE OR REPLACE FUNCTION get_all_users()
    RETURNS setof users
    LANGUAGE sql
    SECURITY INVOKER  -- Change to INVOKER
    AS $$
      SELECT * FROM users
      WHERE auth.uid() = id;  -- Add RLS-like check
    $$;
    ```

 4. admin_delete_user(target_id uuid)
    Security: DEFINER
    Returns: void
    Status: 🔴 P0 - CRITICAL VULNERABILITY

    Analysis:
    ├── SECURITY DEFINER with delete capability
    ├── No role check (anon can call!)
    ├── Can delete any user
    └── No audit trail

    Test Result:
    POST /rest/v1/rpc/admin_delete_user
    Body: {"target_id": "any-uuid"}
    → Function accessible to anon!

    Immediate Fix:
    ```sql
    CREATE OR REPLACE FUNCTION admin_delete_user(target_id uuid)
    RETURNS void
    LANGUAGE plpgsql
    SECURITY DEFINER
    AS $$
    BEGIN
      -- Add role check
      IF NOT (SELECT is_admin FROM profiles WHERE id = auth.uid()) THEN
        RAISE EXCEPTION 'Unauthorized';
      END IF;

      DELETE FROM users WHERE id = target_id;
    END;
    $$;

    -- Or better: restrict to authenticated only
    REVOKE EXECUTE ON FUNCTION admin_delete_user FROM anon;
    ```

 5. dynamic_query(table_name text, conditions text)
    Security: DEFINER
    Returns: json
    Status: 🔴 P0 - SQL INJECTION

    Analysis:
    ├── Accepts raw text parameters
    ├── Likely concatenates into query
    ├── SQL injection possible

    Test Result:
    POST /rest/v1/rpc/dynamic_query
    Body: {"table_name": "users; DROP TABLE users;--", "conditions": "1=1"}
    → Injection vector confirmed!

    Immediate Action:
    → DELETE THIS FUNCTION IMMEDIATELY

    ```sql
    DROP FUNCTION IF EXISTS dynamic_query;
    ```

    Never build queries from user input. Use parameterized queries.

 6. calculate_total(order_id uuid)
    Security: INVOKER
    Returns: numeric
    Status: ✅ SAFE

    Analysis:
    ├── UUID parameter (type-safe)
    ├── SECURITY INVOKER respects RLS
    └── Only accesses caller's orders

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

 Total Functions: 6
 Safe: 3
 P0 Critical: 3
   ├── get_all_users (RLS bypass)
   ├── admin_delete_user (no auth check)
   └── dynamic_query (SQL injection)

 Priority Actions:
 1. DELETE dynamic_query function immediately
 2. Add auth checks to admin_delete_user
 3. Fix get_all_users to respect RLS

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

## Injection Testing

The skill tests for SQL injection in text/varchar parameters:

### Safe (Parameterized)

```sql
-- ✅ Safe: uses parameter placeholder
CREATE FUNCTION search_posts(query text)
RETURNS setof posts
AS $$
  SELECT * FROM posts WHERE title ILIKE '%' || query || '%';
$$ LANGUAGE sql;
```

### Vulnerable (Concatenation)

```sql
-- ❌ Vulnerable: dynamic SQL execution
CREATE FUNCTION dynamic_query(tbl text, cond text)
RETURNS json
AS $$
DECLARE result json;
BEGIN
  EXECUTE format('SELECT json_agg(t) FROM %I t WHERE %s', tbl, cond)
  INTO result;
  RETURN result;
END;
$$ LANGUAGE plpgsql;
```

## Context Output

```json
{
  "rpc_audit": {
    "timestamp": "2025-01-31T11:00:00Z",
    "functions_found": 6,
    "summary": {
      "safe": 3,
      "p0_critical": 3,
      "p1_high": 0
    },
    "findings": [
      {
        "function": "get_all_users",
        "severity": "P0",
        "issue": "RLS bypass via SECURITY DEFINER",
        "impact": "All user data accessible",
        "remediation": "Change to SECURITY INVOKER or add auth checks"
      },
      {
        "function": "dynamic_query",
        "severity": "P0",
        "issue": "SQL injection vulnerability",
        "impact": "Arbitrary SQL execution possible",
        "remediation": "Delete function, use parameterized queries"
      }
    ]
  }
}
```

## Best Practices for RPC Functions

### 1. Prefer SECURITY INVOKER

```sql
CREATE FUNCTION my_function()
RETURNS ...
SECURITY INVOKER  -- Respects RLS
AS $$ ... $$;
```

### 2. Always Check auth.uid()

```sql
CREATE FUNCTION get_my_data()
RETURNS json
AS $$
  SELECT json_agg(d) FROM data d
  WHERE d.user_id = auth.uid();  -- Always filter by caller
$$ LANGUAGE sql SECURITY INVOKER;
```

### 3. Use REVOKE for Sensitive Functions

```sql
-- Remove anon access
REVOKE EXECUTE ON FUNCTION admin_function FROM anon;

-- Only authenticated users
GRANT EXECUTE ON FUNCTION admin_function TO authenticated;
```

### 4. Avoid Text Parameters for Dynamic Queries

```sql
-- ❌ Bad
CREATE FUNCTION query(tbl text) ...

-- ✅ Good: use specific functions per table
CREATE FUNCTION get_users() ...
CREATE FUNCTION get_posts() ...
```

## 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:

1. **Before testing each function** → Log the action to `.sb-pentest-audit.log`
2. **After each function analyzed** → Immediately update `.sb-pentest-context.json