> ## Documentation Index
> Fetch the complete documentation index at: https://docs.cfosilvia.com/llms.txt
> Use this file to discover all available pages before exploring further.

# SECURITY INCIDENT RPC FUNCTIONS

# Security Incident Report: Supabase RPC Function Vulnerability

**Date Discovered**: December 2, 2025\
**Severity**: Critical\
**Status**: Resolved\
**Report Author**: Security Audit

***

## Executive Summary

A security audit of Supabase RPC functions revealed a critical misconfiguration affecting 9 database functions. These functions were configured with `SECURITY DEFINER` mode while also granting execute permissions to the `anon` (anonymous) role. This combination allowed unauthenticated users to bypass Row Level Security (RLS) policies and potentially access, modify, or expose sensitive user data.

The vulnerability was discovered through a systematic audit of all public schema functions and was immediately remediated by revoking inappropriate access permissions.

***

## Vulnerability Details

### Technical Background

Supabase/PostgreSQL functions can operate in two security modes:

| Mode | Behavior |
| - | - |
| `SECURITY INVOKER` (default) | Function runs with the privileges of the **calling user**. RLS policies apply normally. |
| `SECURITY DEFINER` | Function runs with the privileges of the **function owner** (typically `postgres`). RLS is bypassed. |

### The Problem

The affected functions were configured with:

1. **`SECURITY DEFINER`** - Causing them to run as the `postgres` superuser, bypassing all RLS policies
2. **`anon` role had EXECUTE permission** - Allowing unauthenticated API calls to invoke these functions

### Attack Vector

An attacker could exploit this vulnerability with just the public Supabase anon key (which is exposed in frontend applications):

```bash theme={null}
curl -X POST 'https://[PROJECT_REF].supabase.co/rest/v1/rpc/execute_query' \
  -H "apikey: [ANON_KEY]" \
  -H "Content-Type: application/json" \
  -d '{"query": "SELECT * FROM users"}'
```

This would return **all user data** from the database, bypassing any RLS policies, because:

* The `anon` role could call the function
* The function ran as `postgres` (SECURITY DEFINER)
* No authentication was required

***

## Affected Functions

### Critical Severity (Data Exposure Risk)

| Function Name | Risk Description | Intended Use |
| - | - | - |
| `execute_query` | **Arbitrary SQL execution** - Could query any table in the database | AI chat text-to-SQL feature |
| `execute_text_to_sql_query` | **Arbitrary SQL execution** - Similar to execute\_query | AI chat text-to-SQL feature |
| `find_users_without_accounts` | **User data exposure** - Returns list of users without linked accounts | Winback email campaigns (cron job) |
| `detect_stock_price_spikes` | **Investment data exposure** - Accesses user investment positions | Stock alert notifications (cron job) |

### High Severity (Data Manipulation Risk)

| Function Name | Risk Description | Intended Use |
| - | - | - |
| `handle_new_user` | **User manipulation** - Could create or modify user records | Trigger function for new user signup |
| `log_physical_asset_changes` | **Audit log injection** - Could inject fake audit entries | Trigger function for asset change logging |

### Medium Severity (Data Integrity Risk)

| Function Name | Risk Description | Intended Use |
| - | - | - |
| `increment_institution_priority` | **Ranking manipulation** - Could artificially boost institution rankings | Track which banks users connect most |
| `increment_changelog_upvote` | **Vote manipulation** - Could inflate upvote counts | Product changelog feature voting |
| `decrement_changelog_upvote` | **Vote manipulation** - Could manipulate upvote counts | Product changelog feature voting |

***

## The Fix

### What Was Done

The fix involved **revoking execute permissions** from the `anon` and `PUBLIC` roles for all affected functions. This ensures that:

1. Unauthenticated users cannot call these functions via the REST API
2. The functions can only be called by authenticated users or the service\_role (for backend/cron operations)
3. RLS bypass is only possible through properly authenticated channels

### SQL Commands Applied

```sql theme={null}
-- Remove anon and PUBLIC access from all vulnerable functions

-- Critical: SQL execution functions
REVOKE EXECUTE ON FUNCTION execute_query(text) FROM anon;
REVOKE EXECUTE ON FUNCTION execute_query(text) FROM PUBLIC;
REVOKE EXECUTE ON FUNCTION execute_text_to_sql_query FROM anon;
REVOKE EXECUTE ON FUNCTION execute_text_to_sql_query FROM PUBLIC;

-- Critical: User data functions (restrict to service_role only)
REVOKE EXECUTE ON FUNCTION find_users_without_accounts FROM anon;
REVOKE EXECUTE ON FUNCTION find_users_without_accounts FROM PUBLIC;
REVOKE EXECUTE ON FUNCTION find_users_without_accounts FROM authenticated;

REVOKE EXECUTE ON FUNCTION detect_stock_price_spikes FROM anon;
REVOKE EXECUTE ON FUNCTION detect_stock_price_spikes FROM PUBLIC;
REVOKE EXECUTE ON FUNCTION detect_stock_price_spikes FROM authenticated;

-- High: Trigger/internal functions
REVOKE EXECUTE ON FUNCTION handle_new_user FROM anon;
REVOKE EXECUTE ON FUNCTION handle_new_user FROM PUBLIC;

REVOKE EXECUTE ON FUNCTION log_physical_asset_changes FROM anon;
REVOKE EXECUTE ON FUNCTION log_physical_asset_changes FROM PUBLIC;

-- Medium: User-facing functions (keep authenticated access)
REVOKE EXECUTE ON FUNCTION increment_institution_priority FROM anon;
REVOKE EXECUTE ON FUNCTION increment_institution_priority FROM PUBLIC;

REVOKE EXECUTE ON FUNCTION increment_changelog_upvote FROM anon;
REVOKE EXECUTE ON FUNCTION increment_changelog_upvote FROM PUBLIC;

REVOKE EXECUTE ON FUNCTION decrement_changelog_upvote FROM anon;
REVOKE EXECUTE ON FUNCTION decrement_changelog_upvote FROM PUBLIC;
```

### Verification Query

After applying fixes, run this query to verify no vulnerable functions remain:

```sql theme={null}
-- Should return empty results after fix
SELECT 
    p.proname as function_name,
    '🚨 VULNERABLE' as status,
    'SECURITY DEFINER + anon access' as reason
FROM pg_proc p
JOIN pg_namespace n ON p.pronamespace = n.oid
WHERE n.nspname = 'public'
AND p.prosecdef = true
AND has_function_privilege('anon', p.oid, 'EXECUTE')
ORDER BY p.proname;
```

***

## Impact Assessment

### Potential Data Exposed

Based on the function capabilities, the following data categories could have been accessed:

* ✅ User accounts and profiles
* ✅ Bank account connections and balances
* ✅ Investment holdings and positions
* ✅ Transaction history
* ✅ Physical asset records
* ✅ Internal system data

### Recommended Follow-up Actions

1. **Audit Supabase logs** for suspicious RPC calls to these functions
2. **Review recent activity** on user accounts for anomalies
3. **Monitor** for any reports of unauthorized access
4. **Consider** rotating any sensitive API keys if breach is suspected

***

## Prevention Measures

### Best Practices for Future Development

1. **Default to SECURITY INVOKER**
   ```sql theme={null}
   CREATE FUNCTION my_function()
   RETURNS void
   LANGUAGE plpgsql
   SECURITY INVOKER  -- Always explicit, RLS applies
   AS $$ ... $$;
   ```

2. **Restrict SECURITY DEFINER functions to service\_role only**
   ```sql theme={null}
   -- After creating the function
   REVOKE EXECUTE ON FUNCTION dangerous_function FROM anon, PUBLIC, authenticated;
   GRANT EXECUTE ON FUNCTION dangerous_function TO service_role;
   ```

3. **Add user\_id validation for SECURITY DEFINER functions**
   ```sql theme={null}
   CREATE FUNCTION user_data_function(p_user_id UUID)
   RETURNS JSONB
   SECURITY DEFINER
   AS $$
   BEGIN
     -- Verify caller is the user
     IF auth.uid() IS NULL OR auth.uid() != p_user_id THEN
       RAISE EXCEPTION 'Unauthorized';
     END IF;
     -- ... rest of function
   END;
   $$;
   ```

4. **Regular security audits**
   * Run the vulnerability detection query weekly
   * Review new functions before deployment
   * Document security mode choice for each function

### Security Audit Query (Add to CI/CD)

```sql theme={null}
-- Add this to your deployment checks
-- Fails if any SECURITY DEFINER function is accessible by anon

DO $$
DECLARE
    vulnerable_count INTEGER;
BEGIN
    SELECT COUNT(*) INTO vulnerable_count
    FROM pg_proc p
    JOIN pg_namespace n ON p.pronamespace = n.oid
    WHERE n.nspname = 'public'
    AND p.prosecdef = true
    AND has_function_privilege('anon', p.oid, 'EXECUTE');
    
    IF vulnerable_count > 0 THEN
        RAISE EXCEPTION 'SECURITY CHECK FAILED: % functions are SECURITY DEFINER with anon access', vulnerable_count;
    END IF;
END $$;
```

***

## Timeline

| Time | Action |
| - | - |
| Dec 2, 2025 12:11 PM | Vulnerability discovered during RPC function documentation audit |
| Dec 2, 2025 12:14 PM | `execute_query` function analyzed and confirmed vulnerable |
| Dec 2, 2025 12:16 PM | Full audit query run, 9 vulnerable functions identified |
| Dec 2, 2025 12:22 PM | Remediation SQL commands prepared |
| Dec 2, 2025 12:25 PM | Fixes applied to database |
| Dec 2, 2025 12:27 PM | Verification query confirmed all issues resolved |

***

## References

* [Supabase Security Best Practices](https://supabase.com/docs/guides/auth/row-level-security)
* [PostgreSQL SECURITY DEFINER Documentation](https://www.postgresql.org/docs/current/sql-createfunction.html)
* [OWASP API Security Top 10](https://owasp.org/www-project-api-security/)

***

*This report should be retained for compliance and audit purposes.*


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.