38980-vm/app-9w9pd00g5j41/PROVIDER_SECURITY_FIXES.md

Provider Security Fixes - Migrations 00059 & 00060

Overview

Two critical security vulnerabilities in provider-related functions have been fixed to prevent privilege escalation and unauthorized credit manipulation.

Security Fix 1: register_provider Authorization Check (Migration 00059)

Vulnerability

The register_provider function accepted a p_user_id parameter without verifying that the caller was acting on their own account. This allowed any authenticated user to potentially register as a provider on behalf of another user.

Fix Applied

Added authorization check at the beginning of the function:

-- SECURITY: caller must be acting on their own profile only
IF p_user_id IS DISTINCT FROM auth.uid() THEN
  RAISE EXCEPTION 'Unauthorized: cannot register provider on behalf of another user';
END IF;

Impact

• ✅ Users can only register themselves as providers
• ✅ Prevents impersonation attacks
• ✅ Maintains data integrity for provider profiles
• ✅ Protects provider_services and provider_wallets tables

Function Signature

register_provider(
  p_user_id UUID,
  p_business_name TEXT,
  p_business_description TEXT,
  p_destinations TEXT[],
  p_activity_categories TEXT[]
) RETURNS JSON

Security Fix 2: add_credits Authorization (Migration 00060)

Vulnerability

The add_credits function could potentially be called by any authenticated user, allowing them to grant themselves unlimited credits without payment.

Fix Applied

1. Authorization Check: Only service_role (payment webhooks) or admin users can add credits:

-- SECURITY: Only service_role or admin may add credits
IF auth.role() != 'service_role' THEN
  IF NOT EXISTS (
    SELECT 1 FROM profiles
    WHERE id = auth.uid() AND role = 'admin'
  ) THEN
    RAISE EXCEPTION 'Unauthorized: only service_role or admin can add credits';
  END IF;
END IF;

2. Permission Revocation: Removed execute permissions from regular users:

REVOKE EXECUTE ON FUNCTION add_credits(UUID, INTEGER, TEXT) FROM PUBLIC;
REVOKE EXECUTE ON FUNCTION add_credits(UUID, INTEGER, TEXT) FROM authenticated;
GRANT  EXECUTE ON FUNCTION add_credits(UUID, INTEGER, TEXT) TO service_role;

Impact

• ✅ Only payment webhooks (service_role) can add credits
• ✅ Admin users can manually add credits for support purposes
• ✅ Regular users cannot grant themselves free credits
• ✅ Protects revenue and credit economy integrity

Function Signature

add_credits(
  p_provider_id UUID,
  p_amount INTEGER,
  p_description TEXT DEFAULT 'Credit purchase'
) RETURNS JSON

Security Architecture

register_provider Flow

1. Authentication: User must be logged in (authenticated role)
2. Authorization: User can only register their own account (p_user_id = auth.uid())
3. Profile Check: Verifies profile exists before proceeding
4. Role Update: Changes profile role to 'provider'
5. Service Record: Creates/updates provider_services entry
6. Wallet Creation: Initializes provider wallet with 0 credits

add_credits Flow

1. Authentication: Caller must be service_role or admin
2. Validation: Amount must be positive
3. Wallet Creation: Creates wallet if it doesn't exist
4. Row Locking: Uses FOR UPDATE to prevent race conditions
5. Credit Addition: Atomically updates balance
6. Transaction Record: Logs the credit addition

Testing Recommendations

Test register_provider

1. ✅ User can register themselves as provider
2. ✅ User cannot register another user as provider
3. ✅ Anonymous users cannot call the function
4. ✅ Profile must exist before registration
5. ✅ Wallet is created with 0 credits

Test add_credits

1. ✅ Service role can add credits (payment webhook simulation)
2. ✅ Admin users can add credits
3. ✅ Regular authenticated users cannot add credits
4. ✅ Anonymous users cannot add credits
5. ✅ Negative amounts are rejected
6. ✅ Transaction is recorded correctly

Related Files

• supabase/migrations/00059_fix_register_provider_auth_check.sql
• supabase/migrations/00060_secure_add_credits_function.sql
• supabase/migrations/00013_add_provider_registration_function.sql (original)
• supabase/migrations/00010_create_purchase_lead_function.sql (original add_credits)

Deployment Status

• ✅ Migration files created
• ✅ Migrations applied to database
• ✅ Functions verified as SECURITY DEFINER
• ✅ Permissions configured correctly

Security Checklist

• Authorization checks in place
• Input validation implemented
• Row-level locking for concurrency
• Proper error messages (no information leakage)
• Permissions restricted appropriately
• Audit trail (transaction records)
• SECURITY DEFINER used correctly
• search_path set explicitly

Additional Security Measures

Both functions use:

• SECURITY DEFINER: Runs with function owner's privileges
• SET search_path = public: Prevents schema injection attacks
• Explicit authorization checks before any data modification
• Atomic transactions with proper error handling
• Row locking to prevent race conditions