145 lines
5.1 KiB
Markdown
145 lines
5.1 KiB
Markdown
# 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:
|
|
|
|
```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;
|
|
```
|
|
|
|
### 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
|
|
```sql
|
|
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:
|
|
|
|
```sql
|
|
-- 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:
|
|
|
|
```sql
|
|
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
|
|
```sql
|
|
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
|
|
- [x] Authorization checks in place
|
|
- [x] Input validation implemented
|
|
- [x] Row-level locking for concurrency
|
|
- [x] Proper error messages (no information leakage)
|
|
- [x] Permissions restricted appropriately
|
|
- [x] Audit trail (transaction records)
|
|
- [x] SECURITY DEFINER used correctly
|
|
- [x] 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
|