Description

The DELETE /rbac/roles/{role_id} endpoint returns 500 Internal Server Error for all roles (both custom and system). The generic error handler masks the real cause, returning only {"detail": "Failed to delete role"}.

Root Cause

In mcpgateway/services/role_service.py, the delete_role() method (line ~522) uses incorrect SQLAlchemy syntax to bulk-deactivate user role assignments:

self.db.execute(select(UserRole).where(UserRole.role_id == role_id)).update({"is_active": False})

select() returns a Result object which has no .update() method. This raises an AttributeError that gets caught by the generic except Exception handler in mcpgateway/routers/rbac.py (line ~286) and returned as a 500.

Additionally, for system roles, the ValueError("Cannot delete system roles") raised at line ~515 is also caught by the same generic handler and returned as 500 instead of a proper 400 or 403.

Steps to Reproduce

# Create a custom role
curl -X POST http://localhost:8080/rbac/roles \
  -H "Authorization: Bearer $TOKEN" \
  -H "Content-Type: application/json" \
  -d '{"name": "test-role", "description": "test", "scope": "global", "permissions": ["tools.read"]}'

# Try to delete it (returns 500)
curl -X DELETE http://localhost:8080/rbac/roles/<role_id> \
  -H "Authorization: Bearer $TOKEN"

Expected Behavior

• Custom roles: should be soft-deleted (deactivated) and return 200
• System roles: should return 400 or 403 with "Cannot delete system roles"

Suggested Fix

1. Fix the SQLAlchemy query in role_service.py line ~522:

# Before (broken):
self.db.execute(select(UserRole).where(UserRole.role_id == role_id)).update({"is_active": False})

# After (correct):
from sqlalchemy import update
self.db.execute(update(UserRole).where(UserRole.role_id == role_id).values(is_active=False))

2. Handle ValueError distinctly in routers/rbac.py ~271-287:

except ValueError as e:
    raise HTTPException(status_code=status.HTTP_400_BAD_REQUEST, detail=str(e))
except Exception as e:
    logger.error(f"Role deletion failed: {e}")
    raise HTTPException(status_code=status.HTTP_500_INTERNAL_SERVER_ERROR, detail="Failed to delete role")

Workaround

Use PUT /rbac/roles/{role_id} with {"is_active": false} to deactivate roles instead of deleting them.

Environment

• Discovered during E2E testing (issue [EPIC][TESTING][SECURITY]: RBAC automated regression suite (visibility, teams, token scope) #2387)
• Affects all role deletion attempts
• Workaround: deactivation via PUT works correctly