Skip to main content

​
RBAC Bypass Audit - Direct BigQuery Fetches

​
Critical Issues Found

​
1. CHURN Intent Handler - Direct BigQuery Call Without RBAC (CRITICAL)

Location: api/routes/ai_query.py lines 3579-3615 Problem: 
if resolved.intent == AgentIntent.CHURN:
    agent_name = extract_agent_name(question_text, get_agent_list())
    # NO RBAC CHECK HERE
    data = get_agent_churn_intel(tenant_id, agent_name, prev_period, curr_period)
Fix Required: Add RBAC enforcement before calling get_agent_churn_intel(): 
# Enforce RBAC - get authorized_agent_id (downline-aware)
authorized_agent_id = authorize_target_agent_id(
    current_user, agent_name, tenant_id, "read_agent_churn"
)
# Resolve authorized_agent_id → agent_name
# Then call get_agent_churn_intel with authorized agent_name

​
2. COMPANY_LOST_BUSINESSES Intent - No RBAC Check (CRITICAL)

Location: api/routes/ai_query.py line 3553 Problem: 
data = fetch_agent_data("company_lost_businesses", tenant_id, period_label, None, active_period, question_text)
# No authorized_agent_id passed
Status: This is CEO-level data (company-wide), so RBAC may not be required, but should verify role is admin/ceo.

​
3. fetch_agent_data() - Many Functions Don’t Use authorized_agent_id

Location: api/routes/ai_query.py lines 781-1200 Functions that accept authorized_agent_id but don’t use it:
  • get_agent_portfolio_metrics() - Line 805
  • get_agent_lifecycle_events() - Line 817
  • get_agent_lost_businesses() - Line 829
  • get_agent_business_summary() - Line 841
  • get_agent_commission_with_authoritative_pepm() - Line 870
  • get_agent_mom_comparison() - Line 897
  • get_agent_churn_intel() - Line 932
  • get_focus_accounts() - Line 952
  • get_agent_commission_range() - Line 1105
  • get_agent_commission_single_period() - Line 1124
Functions that DO use authorized_agent_id:
  • get_agent_ytd_summary() - Line 884 ✅
  • get_agent_qoq_comparison() - Line 911 ✅
  • get_agent_commission_by_business() - Line 1136 ✅

​
4. fetch_business_data() - Agent Queries Pass None for authorized_agent_id

Location: api/routes/ai_query.py line 722 Problem: 
agent_data = get_agent_report_from_view(tenant_id, current_period, agent_name, business_name, authorized_agent_id=None)
# TODO comment says "will be enforced at higher level" but it's not enforced

​
5. Direct BigQuery Client Calls - Reverse Lookup (OK)

Location: api/routes/ai_query.py lines 3470-3489, 4377-4396 Status: ✅ These are reverse lookups (agent_id → agent_name) and use authorized_agent_id from RBAC check, so they’re safe.

​
Required Fixes

​
Priority 1: CHURN Intent Handler

Add RBAC enforcement before line 3600: 
# Extract requested target
requested_target = extract_agent_name(question_text, get_agent_list())
if not requested_target:
    # ... error handling

# RBAC ENFORCEMENT
authorized_agent_id = authorize_target_agent_id(
    current_user, requested_target, tenant_id, "read_agent_churn"
)
# Resolve to agent_name, then call get_agent_churn_intel

​
Priority 2: Verify BigQuery Query Functions Use authorized_agent_id

Check if these functions in api/bigquery/queries.py actually enforce RBAC:
  • get_agent_churn_intel()
  • get_agent_portfolio_metrics()
  • get_agent_lifecycle_events()
  • get_agent_lost_businesses()
  • get_agent_business_summary()
  • get_agent_commission_with_authoritative_pepm()
  • get_agent_mom_comparison()
  • get_agent_commission_range()
  • get_agent_commission_single_period()
  • get_agent_report_from_view()
  • get_agent_trends_from_view()
If they don’t enforce RBAC internally, they MUST filter by authorized_agent_id or agent_name that was already authorized.

​
Priority 3: COMPANY_LOST_BUSINESSES Intent

Verify this is CEO-level data and add role check if needed: 
if current_user.get('role') not in ('admin', 'ceo'):
    raise HTTPException(403, "Forbidden: CEO-level access required")

​
Verification Checklist

  • CHURN intent handler enforces RBAC before BigQuery call
  • All fetch_agent_data() calls pass authorized_agent_id when available
  • All BigQuery query functions that accept authorized_agent_id actually use it
  • COMPANY_LOST_BUSINESSES intent has role check if needed
  • No direct BigQuery calls bypass RBAC checks