"""Re-seed ``cloud.azure.default_roles`` to mirror Azure portal's "Privileged administrator roles" tab. Revision ID: 070 Revises: 069 Create Date: 2026-04-27 Migration 060 originally seeded a sprawling 11-role default list (Reader, Billing Reader, VM family, Storage Blob family, etc.) — most of which an employee would never request as their *primary* role. The grant-request modal now splits "primary role" (curated, narrow) from "extra roles" (full ~380 built-in catalog), so the curated set should be exactly what Azure itself flags as Privileged: Owner, Contributor, Access Review Operator Service Role, Role Based Access Control Administrator, User Access Administrator. Bonus fix: migration 060 had the wrong GUID for User Access Administrator (``36243c78-...``); the correct stable built-in GUID is ``18d7d88d-d35e-4fb5-a5c3-7773c20a72d9``. This migration overwrites it. **Safety:** we only overwrite the platform_settings row when its current value still equals the seed produced by migration 060. If an admin has hand-edited the list via the settings UI, we leave their customization alone and emit a NOTE row in alembic output so the human knows to reconcile manually. """ from __future__ import annotations import json import sqlalchemy as sa from alembic import op revision = "070" down_revision = "069" branch_labels = None depends_on = None # Mirrors Azure portal's "Privileged administrator roles" tab. GUIDs are # stable across every tenant — verified against learn.microsoft.com built-in # role docs. _AZURE_PRIVILEGED_ROLES = [ { "role_key": ( "/providers/Microsoft.Authorization/roleDefinitions/" "8e3af657-a8ff-443c-a75c-2fe8c4bcb635" ), "display_name": "Owner", "description": "Grants full access to manage all resources, " "including the ability to assign roles in Azure RBAC.", }, { "role_key": ( "/providers/Microsoft.Authorization/roleDefinitions/" "b24988ac-6180-42a0-ab88-20f7382dd24c" ), "display_name": "Contributor", "description": "Grants full access to manage all resources, " "but does not allow you to assign roles in Azure RBAC.", }, { "role_key": ( "/providers/Microsoft.Authorization/roleDefinitions/" "76cc9ee4-d5d3-4a45-a930-26add3d73475" ), "display_name": "Access Review Operator Service Role", "description": "Lets you grant Access Review System app " "permissions to discover and revoke access as needed by the " "access review process.", }, { "role_key": ( "/providers/Microsoft.Authorization/roleDefinitions/" "f58310d9-a9f6-439a-9e8d-f62e7b41a168" ), "display_name": "Role Based Access Control Administrator", "description": "Manage access to Azure resources by assigning " "roles using Azure RBAC. This role does not allow you to manage " "access using other ways, such as Azure Policy.", }, { "role_key": ( "/providers/Microsoft.Authorization/roleDefinitions/" "18d7d88d-d35e-4fb5-a5c3-7773c20a72d9" ), "display_name": "User Access Administrator", "description": "Lets you manage user access to Azure resources.", }, ] # Exactly the value migration 060 wrote. We compare against this set of # role_keys to decide whether the row is still pristine seed-state. _MIGRATION_060_ROLE_KEYS = frozenset({ "/providers/Microsoft.Authorization/roleDefinitions/" "8e3af657-a8ff-443c-a75c-2fe8c4bcb635", # Owner "/providers/Microsoft.Authorization/roleDefinitions/" "b24988ac-6180-42a0-ab88-20f7382dd24c", # Contributor "/providers/Microsoft.Authorization/roleDefinitions/" "acdd72a7-3385-48ef-bd42-f606fba81ae7", # Reader "/providers/Microsoft.Authorization/roleDefinitions/" "fa23ad8b-c56e-40d8-ac0c-ce449e1d2c64", # Billing Reader "/providers/Microsoft.Authorization/roleDefinitions/" "36243c78-bf99-498c-9df9-86d9f8d28608", # (incorrect) UAA "/providers/Microsoft.Authorization/roleDefinitions/" "ba92f5b4-2d11-453d-a403-e96b0029c9fe", # Storage Blob Data Contributor "/providers/Microsoft.Authorization/roleDefinitions/" "2a2b9908-6ea1-4ae2-8e65-a410df84e7d1", # Storage Blob Data Reader "/providers/Microsoft.Authorization/roleDefinitions/" "9980e02c-c2be-4d73-94e8-173b1dc7cf3c", # VM Contributor "/providers/Microsoft.Authorization/roleDefinitions/" "fb879df8-f326-4884-b1cf-06f3ad86be52", # VM User Login "/providers/Microsoft.Authorization/roleDefinitions/" "1c0163c0-47e6-4577-8991-ea5c82e286e4", # VM Administrator Login }) def _table_exists(bind, name: str) -> bool: return bind.execute( sa.text( "SELECT 1 FROM information_schema.TABLES " "WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = :n", ), {"n": name}, ).scalar() is not None def _current_role_keys(bind) -> frozenset[str] | None: row = bind.execute( sa.text( "SELECT value FROM platform_settings " "WHERE `key` = 'cloud.azure.default_roles'" ), ).fetchone() if not row or not row[0]: return None raw = row[0] try: parsed = raw if isinstance(raw, list) else json.loads(raw) except (ValueError, TypeError): return None if not isinstance(parsed, list): return None keys = { str(x.get("role_key") or "") for x in parsed if isinstance(x, dict) } return frozenset(k for k in keys if k) def upgrade() -> None: bind = op.get_bind() if not _table_exists(bind, "platform_settings"): return current = _current_role_keys(bind) new_value = json.dumps(_AZURE_PRIVILEGED_ROLES, ensure_ascii=False) new_desc = ( "Azure 申请流的「主角色」精选下拉(对应 Azure 门户的 " "Privileged administrator roles 标签页)。role_key = 完整 ARM " "role-definition 路径(不带 subscription 前缀);驱动直接拿去 " "PUT roleAssignments。其它 ~380 个 built-in 通过驱动 list_scope_roles " "实时同步,带 is_promoted=False,出现在「附加角色」多选里。" ) if current is None: # Row didn't exist (migration 060 was skipped or platform_settings # was empty). Insert fresh. bind.execute( sa.text( "INSERT INTO platform_settings (`key`, value, description, " "created_at, updated_at) " "VALUES (:k, CAST(:v AS JSON), :d, NOW(), NOW())" ), { "k": "cloud.azure.default_roles", "v": new_value, "d": new_desc, }, ) return if current == _MIGRATION_060_ROLE_KEYS: # Pristine seed state from migration 060 — safe to overwrite. bind.execute( sa.text( "UPDATE platform_settings SET value = CAST(:v AS JSON), " "description = :d, updated_at = NOW() " "WHERE `key` = 'cloud.azure.default_roles'" ), {"v": new_value, "d": new_desc}, ) return # Admin has customized the list. Don't clobber their choices. # Print a notice so the operator can reconcile via the settings UI. print( # noqa: T201 "[migration 069] cloud.azure.default_roles has been customized " "by an admin (does not match migration-060 seed). Skipping " "overwrite — please reconcile manually via the platform " "settings UI if you want the new privileged-admin defaults.", ) def downgrade() -> None: # Best-effort: if the current value matches the new privileged set, # restore the migration-060 seed. If admin has edited it, leave alone. bind = op.get_bind() if not _table_exists(bind, "platform_settings"): return current = _current_role_keys(bind) new_keys = frozenset(r["role_key"] for r in _AZURE_PRIVILEGED_ROLES) if current != new_keys: return # Reproduce migration 060's exact payload. seed_060 = [ { "role_key": ( "/providers/Microsoft.Authorization/roleDefinitions/" "8e3af657-a8ff-443c-a75c-2fe8c4bcb635" ), "display_name": "Owner", "description": "Full access to all resources + IAM " "delegation. Hand out sparingly.", }, { "role_key": ( "/providers/Microsoft.Authorization/roleDefinitions/" "b24988ac-6180-42a0-ab88-20f7382dd24c" ), "display_name": "Contributor", "description": "Full access except granting IAM roles.", }, { "role_key": ( "/providers/Microsoft.Authorization/roleDefinitions/" "acdd72a7-3385-48ef-bd42-f606fba81ae7" ), "display_name": "Reader", "description": "Read-only on all resources.", }, { "role_key": ( "/providers/Microsoft.Authorization/roleDefinitions/" "fa23ad8b-c56e-40d8-ac0c-ce449e1d2c64" ), "display_name": "Billing Reader", "description": "View billing, invoices, budgets.", }, { "role_key": ( "/providers/Microsoft.Authorization/roleDefinitions/" "36243c78-bf99-498c-9df9-86d9f8d28608" ), "display_name": "User Access Administrator", "description": "Manage user access to Azure resources.", }, { "role_key": ( "/providers/Microsoft.Authorization/roleDefinitions/" "ba92f5b4-2d11-453d-a403-e96b0029c9fe" ), "display_name": "Storage Blob Data Contributor", "description": "Read/write/delete on blob containers + data.", }, { "role_key": ( "/providers/Microsoft.Authorization/roleDefinitions/" "2a2b9908-6ea1-4ae2-8e65-a410df84e7d1" ), "display_name": "Storage Blob Data Reader", "description": "Read blob containers + data.", }, { "role_key": ( "/providers/Microsoft.Authorization/roleDefinitions/" "9980e02c-c2be-4d73-94e8-173b1dc7cf3c" ), "display_name": "Virtual Machine Contributor", "description": "Manage VMs, but not the networks or storage " "they depend on.", }, { "role_key": ( "/providers/Microsoft.Authorization/roleDefinitions/" "fb879df8-f326-4884-b1cf-06f3ad86be52" ), "display_name": "Virtual Machine User Login", "description": "Log in to a VM as a regular user.", }, { "role_key": ( "/providers/Microsoft.Authorization/roleDefinitions/" "1c0163c0-47e6-4577-8991-ea5c82e286e4" ), "display_name": "Virtual Machine Administrator Login", "description": "Log in to a VM with administrator rights.", }, ] bind.execute( sa.text( "UPDATE platform_settings SET value = CAST(:v AS JSON), " "updated_at = NOW() " "WHERE `key` = 'cloud.azure.default_roles'" ), {"v": json.dumps(seed_060, ensure_ascii=False)}, )