Back to Details

supabase-audit-rpc

Compare original and translation side by side

🇺🇸

Original

English
🇨🇳

Translation

Chinese

RPC Functions Audit

RPC函数安全审计

🔴 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.
🔴 严重警告：必须逐步更新文件
你必须逐步写入上下文文件，而不是只在最后一次性写入。
  • 测试完每个函数后立即写入
    .sb-pentest-context.json
  • 测试每个函数前后都要记录到
    .sb-pentest-audit.log
  • 禁止等到技能完成后再更新文件
  • 如果技能崩溃或中断，所有已发现的问题必须已保存
此要求为强制性，未逐步写入属于严重错误。
本技能用于发现并测试通过Supabase RPC端点暴露的PostgreSQL函数。

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
  • 发现暴露的数据库函数
  • 测试函数是否绕过RLS
  • 检查函数参数中是否存在SQL注入风险
  • 作为全面API安全测试的一部分

Prerequisites

前置条件

  • Supabase URL and anon key available
  • Tables audit completed (recommended)
  • 已获取Supabase URL和anon密钥
  • 已完成表审计（推荐）

Understanding Supabase RPC

理解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)
Supabase通过以下方式暴露PostgreSQL函数：
POST https://[project].supabase.co/rest/v1/rpc/[function_name]
函数可能：
  • ✅ 遵循RLS（如果使用
    auth.uid()
    和正确的安全设置）
  • ❌ 绕过RLS（如果使用
    SECURITY DEFINER
    但未做检查）
  • ❌ 执行任意SQL（如果编写不当）

Risk Levels for Functions

函数风险等级

TypeRiskDescription
SECURITY INVOKER
LowerRuns with caller's permissions
SECURITY DEFINER
HigherRuns with definer's permissions
Accepts text/jsonHigherPotential for injection
Returns setofHigherCan return multiple rows
类型风险描述
SECURITY INVOKER
较低调用者权限运行
SECURITY DEFINER
较高定义者权限运行
接受text/json参数较高存在注入风险
返回setof类型较高可返回多行数据

Usage

使用方法

Basic RPC Audit

基础RPC审计

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

Test Specific Function

测试特定函数

Test the get_user_data RPC 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

═══════════════════════════════════════════════════════════
═══════════════════════════════════════════════════════════
 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:
本技能会测试text/varchar参数中的SQL注入风险：

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

RPC函数最佳实践

1. Prefer SECURITY INVOKER

1. 优先使用SECURITY INVOKER

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

2. Always Check auth.uid()

2. 始终检查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;
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

3. 对敏感函数使用REVOKE

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

-- Only authenticated users
GRANT EXECUTE ON FUNCTION admin_function TO authenticated;
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

4. 避免使用文本参数进行动态查询

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

-- ✅ Good: use specific functions per table
CREATE FUNCTION get_users() ...
CREATE FUNCTION get_posts() ...
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
  3. After each vulnerability found → Log the finding immediately
This ensures that if the skill is interrupted, crashes, or times out, all findings up to that point are preserved.
禁止批量写入，而是：
  1. 测试每个函数前 → 将操作记录到
    .sb-pentest-audit.log
  2. 分析完每个函数后 → 立即更新
    .sb-pentest-context.json
  3. 发现每个漏洞后 → 立即记录问题
这样可以确保如果技能被中断、崩溃或超时，截至该点的所有发现都能被保留。

Required Actions (Progressive)

必须执行的逐步操作

  1. Update 
    .sb-pentest-context.json
     with results:
    json
    {
  "rpc_audit": {
    "timestamp": "...",
    "functions_found": 6,
    "summary": { "safe": 3, "p0_critical": 3 },
    "findings": [ ... ]
  }
}
  2. Log to 
    .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. If files don't exist, create them before writing.
FAILURE TO UPDATE CONTEXT FILES IS NOT ACCEPTABLE.
  1. 更新
    .sb-pentest-context.json
     记录结果：
    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. 如果文件不存在，先创建再写入。
不更新上下文文件是不被允许的。

MANDATORY: Evidence Collection

强制性要求：收集证据

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

Evidence Files to Create

需要创建的证据文件

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

Evidence Format (Vulnerable Function)

易受攻击函数的证据格式

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

Add to curl-commands.sh

添加到curl-commands.sh

bash
undefined
bash
undefined

=== RPC FUNCTION TESTS ===

=== RPC FUNCTION TESTS ===

Test get_all_users function (P0 if accessible)

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

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"}'
undefined
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"}'
undefined

Related Skills

相关技能

  • supabase-audit-tables-list
    — List exposed tables
  • supabase-audit-rls
    — Test RLS policies
  • supabase-audit-auth-users
    — User enumeration tests
  • supabase-audit-tables-list
    — 列出暴露的表
  • supabase-audit-rls
    — 测试RLS策略
  • supabase-audit-auth-users
    — 用户枚举测试