[BUG] Bootstrap fails to assign platform_admin role due to foreign key constraint violation

[crivetimihai]

Bug Report: Role Assignment Failure During Bootstrap

🐛 Issue Summary

The bootstrap process fails to assign the platform_admin role to the admin user due to a foreign key constraint violation. The code attempts to use "system" as the granted_by value, but this field has a foreign key constraint requiring a valid user email address.

🔍 Error Details

ERROR [mcpgateway.bootstrap_db] Failed to assign platform_admin role: 
(pymysql.err.IntegrityError) (1452, 'Cannot add or update a child row: a foreign key constraint fails 
(`mcp`.`user_roles`, CONSTRAINT `user_roles_ibfk_3` FOREIGN KEY (`granted_by`) REFERENCES `email_users` (`email`))')

[SQL: INSERT INTO user_roles (id, user_email, role_id, scope, scope_id, granted_by, granted_at, expires_at, is_active) 
VALUES (%(id)s, %(user_email)s, %(role_id)s, %(scope)s, %(scope_id)s, %(granted_by)s, %(granted_at)s, %(expires_at)s, %(is_active)s)]

[parameters: {..., 'granted_by': 'system', ...}]

📍 Root Cause Analysis

Location: mcpgateway/bootstrap_db.py:196

Problematic Code:

await role_service.assign_role_to_user(
    user_email=admin_user.email, 
    role_id=platform_admin_role.id, 
    scope="global", 
    scope_id=None, 
    granted_by="system"  # ❌ This violates FK constraint
)

Database Schema Constraint (mcpgateway/db.py:250):

granted_by: Mapped[str] = mapped_column(String(255), ForeignKey("email_users.email"), nullable=False)

The Problem: The granted_by field has a foreign key constraint to email_users.email, which means it must reference an actual user's email address. Using "system" as a literal string violates this constraint since there's no user with email "system".

🎯 Expected vs Actual Behavior

Expected: The platform admin role should be assigned to the admin user during bootstrap, with proper attribution of who granted the role.

Actual: The role assignment fails because "system" is not a valid email address in the email_users table.

💡 Potential Solutions

1. Use Admin User as Granter (Recommended):

   granted_by=admin_user.email  # Self-assignment during bootstrap

2. Create System User:

   • Create a special system@internal user for system operations
   • Use this user's email for automated role assignments

3. Make granted_by Nullable:

   • Allow NULL for system-initiated role assignments
   • Update schema to permit nullable granted_by

4. Use Bootstrap Flag:

   • Add special handling for bootstrap operations
   • Bypass the foreign key constraint during initial setup

🔧 Recommended Fix

Option 1 is the simplest and most logical - the admin user can grant themselves the platform_admin role during initial bootstrap:

# In mcpgateway/bootstrap_db.py:196
await role_service.assign_role_to_user(
    user_email=admin_user.email, 
    role_id=platform_admin_role.id, 
    scope="global", 
    scope_id=None, 
    granted_by=admin_user.email  # ✅ Use admin email instead of "system"
)

🧪 How to Reproduce

1. Set up MySQL database (the issue is more apparent with strict FK constraints)
2. Run fresh bootstrap: python3 -m mcpgateway.bootstrap_db
3. Observe the foreign key constraint violation during role assignment

Note: This doesn't occur with SQLite because SQLite's foreign key constraints are less strict by default.

📊 Impact Assessment

• Severity: Low - the application still starts and functions
• Scope: Bootstrap process only
• Effect: Admin user doesn't get platform_admin role automatically
• Workaround: Manually assign the role via API or UI after startup

🔗 Related Components

• mcpgateway/bootstrap_db.py - Bootstrap process
• mcpgateway/db.py:250 - Foreign key constraint definition
• mcpgateway/services/role_service.py - Role assignment logic
• Database: MySQL/PostgreSQL (stricter FK constraints) vs SQLite

🏷️ Labels

• bug
• database
• bootstrap
• rbac
• mysql