# Register Provider Security Fix

## Issue Summary
The `register_provider` function was missing a critical authorization check that would verify the caller is acting on their own account. This could potentially allow a malicious user to register other users as providers without their consent.

## Security Vulnerability
**Function**: `register_provider(UUID, TEXT, TEXT, TEXT[], TEXT[])`

**Problem**: The function accepted a `p_user_id` parameter but did not verify that the authenticated user (`auth.uid()`) matches the user being registered as a provider.

**Risk**: An attacker could call:
```sql
SELECT register_provider(
  'victim-user-id',  -- Any user's ID
  'Malicious Business',
  'Description',
  ARRAY['Turkey'],
  ARRAY['Tours']
);
```

This would:
- Change the victim's role to 'provider'
- Create provider service records under their account
- Create a wallet for them
- Potentially expose them to unwanted business obligations

## Security Fix Applied

### Migration 00059
Created migration `fix_register_provider_auth_check` that adds the authorization check:

```sql
-- 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;
```

### How It Works

1. **Authorization Check**: The function now verifies that `p_user_id` matches `auth.uid()` before proceeding
2. **Early Rejection**: If the IDs don't match, the function raises an exception immediately
3. **Clear Error Message**: The error message explicitly states the security violation

### Function Flow (After Fix)

```
1. User calls register_provider(user_id, ...)
2. ✅ CHECK: Is user_id == auth.uid()?
   - NO  → RAISE EXCEPTION 'Unauthorized'
   - YES → Continue
3. Check if profile exists
4. Update profile role to 'provider'
5. Create/update provider service record
6. Create wallet if needed
7. Return success
```

## Verification

```sql
-- Verify the function has the security check
SELECT pg_get_functiondef(oid) 
FROM pg_proc 
WHERE proname = 'register_provider';

-- Should contain:
-- IF p_user_id IS DISTINCT FROM auth.uid() THEN
--   RAISE EXCEPTION 'Unauthorized: cannot register provider on behalf of another user';
-- END IF;
```

## Impact

### Before Fix
- ❌ Any authenticated user could register any other user as a provider
- ❌ No authorization check on the target user ID
- ❌ Potential for account hijacking or unwanted role changes

### After Fix
- ✅ Users can only register themselves as providers
- ✅ Authorization check enforced at the database level
- ✅ Clear error message for unauthorized attempts
- ✅ Maintains SECURITY DEFINER for necessary privilege elevation

## Testing Recommendations

1. **Positive Test**: User registers themselves as provider
   ```typescript
   // Should succeed
   await supabase.rpc('register_provider', {
     p_user_id: currentUser.id,  // Same as auth.uid()
     p_business_name: 'My Business',
     p_business_description: 'Description',
     p_destinations: ['Turkey'],
     p_activity_categories: ['Tours']
   });
   ```

2. **Negative Test**: User tries to register another user
   ```typescript
   // Should fail with "Unauthorized" error
   await supabase.rpc('register_provider', {
     p_user_id: 'different-user-id',  // Different from auth.uid()
     p_business_name: 'Malicious',
     p_business_description: 'Attack',
     p_destinations: ['Turkey'],
     p_activity_categories: ['Tours']
   });
   ```

3. **Edge Cases**:
   - Verify service_role can still call the function (for admin operations)
   - Test with NULL user_id (should fail)
   - Test with non-existent user_id (should fail at profile check)

## Related Security Considerations

### Why SECURITY DEFINER is Still Safe
The function uses `SECURITY DEFINER` to allow:
- Updating the `profiles` table (role change)
- Creating records in `provider_services`
- Creating records in `provider_wallets`

With the authorization check in place, this is safe because:
1. Users can only act on their own accounts
2. The function validates profile existence
3. All operations are within the scope of self-service registration

### Additional Security Features
- `SET search_path = public` prevents search path attacks
- Profile existence check prevents operating on non-existent users
- Upsert logic prevents duplicate provider registrations
- Transaction safety ensures atomic operations

## Related Files
- `supabase/migrations/00013_add_provider_registration_function.sql` (original function)
- `supabase/migrations/00018_fix_register_provider_security.sql` (previous security fix)
- `supabase/migrations/00059_fix_register_provider_auth_check.sql` (this fix)

## Compliance
This fix ensures compliance with:
- **Principle of Least Privilege**: Users can only modify their own accounts
- **Defense in Depth**: Authorization at database level, not just application level
- **Fail Secure**: Unauthorized attempts are rejected with clear errors
