supabase-audit-rpc

RPC 函数审计

🔴 严重：必须渐进式更新文件

必须边执行边写入上下文文件，而不是只在最后写入。

• 每测试完一个函数后，立即写入 .sb-pentest-context.json
• 每次测试函数前后，都要记录到 .sb-pentest-audit.log
• 不要等技能执行完毕后才更新文件
• 如果技能崩溃或中断，此前的所有发现都必须已经保存

这不是可选项。未能渐进式写入属于严重错误。

此技能会发现并测试通过 Supabase RPC 端点公开的 PostgreSQL 函数。

何时使用此技能

• 发现公开的数据库函数
• 测试函数是否绕过 RLS
• 检查函数参数中是否存在 SQL 注入
• 作为全面 API 安全测试的一部分

前提条件

• 已准备好 Supabase URL 和 anon key
• 已完成表审计（建议）

了解 Supabase RPC

Supabase 通过以下方式公开 PostgreSQL 函数：

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

函数可以：

• ✅ 遵循 RLS（使用 auth.uid() 并采用适当的安全措施）
• ❌ 绕过 RLS（使用 SECURITY DEFINER 且未进行检查时）
• ❌ 执行任意 SQL（编写不当时）

函数风险级别

类型	风险	说明
SECURITY INVOKER	较低	使用调用者的权限运行
SECURITY DEFINER	较高	使用定义者的权限运行
接受 text/json	较高	存在注入风险
返回 setof	较高	可以返回多行

用法

基本 RPC 审计

Audit RPC functions on my Supabase project

测试特定函数

Test the get_user_data RPC function

输出格式

═══════════════════════════════════════════════════════════
 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

═══════════════════════════════════════════════════════════

注入测试

此技能会测试 text/varchar 参数中是否存在 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: 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;

上下文输出

{
  "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"
      }
    ]
  }
}

RPC 函数最佳实践

1. 优先使用 SECURITY INVOKER

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

2. 始终检查 auth.uid()

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. 对敏感函数使用 REVOKE

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

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

4. 避免在动态查询中使用 Text 参数

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

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

强制要求：渐进式更新上下文文件

⚠️ 此技能必须在执行期间渐进式更新跟踪文件，而不是只在最后更新。

关键规则：边执行边写入

不要在最后批量写入。应按以下步骤操作：

1. 测试每个函数前 → 将操作记录到 .sb-pentest-audit.log
2. 分析完每个函数后 → 立即更新 .sb-pentest-context.json
3. 发现每个漏洞后 → 立即记录该发现

这样可以确保即使技能被中断、崩溃或超时，截至当时的所有发现都能得到保留。

必须执行的操作（渐进式）

1. 使用结果更新 .sb-pentest-context.json：

   {
     "rpc_audit": {
       "timestamp": "...",
       "functions_found": 6,
       "summary": { "safe": 3, "p0_critical": 3 },
       "findings": [ ... ]
     }
   }
   

2. 记录到 .sb-pentest-audit.log：

   [TIMESTAMP] [supabase-audit-rpc] [START] Auditing RPC functions
   [TIMESTAMP] [supabase-audit-rpc] [FINDING] P0: dynamic_query has SQL injection
   [TIMESTAMP] [supabase-audit-rpc] [CONTEXT_UPDATED] .sb-pentest-context.json updated
   

3. 如果文件不存在，请在写入前创建它们。

不允许不更新上下文文件。

强制要求：证据收集

📁 证据目录： .sb-pentest-evidence/03-api-audit/rpc-tests/

需要创建的证据文件

文件	内容
function-list.json	所有发现的 RPC 函数
vulnerable-functions/[name].json	每个易受攻击函数的详细信息

证据格式（易受攻击的函数）

{
  "evidence_id": "RPC-001",
  "timestamp": "2025-01-31T10:30:00Z",
  "category": "api-audit",
  "type": "rpc_vulnerability",
  "severity": "P0",

  "function": "get_all_users",

  "analysis": {
    "security_definer": true,
    "auth_check": false,
    "rls_bypass": true
  },

  "test": {
    "request": {
      "method": "POST",
      "url": "https://abc123def.supabase.co/rest/v1/rpc/get_all_users",
      "curl_command": "curl -X POST '$URL/rest/v1/rpc/get_all_users' -H 'apikey: $ANON_KEY' -H 'Content-Type: application/json'"
    },
    "response": {
      "status": 200,
      "rows_returned": 1247,
      "sample_data": "[REDACTED - contains user PII]"
    }
  },

  "impact": "Bypasses RLS, returns all 1,247 user records",
  "remediation": "Change to SECURITY INVOKER or add auth.uid() check"
}

添加到 curl-commands.sh

# === RPC FUNCTION TESTS ===
# Test get_all_users function (P0 if accessible)
curl -X POST "$SUPABASE_URL/rest/v1/rpc/get_all_users" \
  -H "apikey: $ANON_KEY" \
  -H "Content-Type: application/json"

# Test admin_delete_user function
curl -X POST "$SUPABASE_URL/rest/v1/rpc/admin_delete_user" \
  -H "apikey: $ANON_KEY" \
  -H "Content-Type: application/json" \
  -d '{"target_id": "test-uuid"}'

相关技能

• supabase-audit-tables-list — 列出公开的表
• supabase-audit-rls — 测试 RLS 策略
• supabase-audit-auth-users — 用户枚举测试