#!/usr/bin/env python3 """ migrate_postfixadmin.py — Migration PostfixAdmin → netadmin_mail ================================================================ Lit depuis la base PostfixAdmin existante (base: postfix) Crée et peuple la nouvelle base netadmin_mail Usage: python3 migrate_postfixadmin.py --dry-run # aperçu sans écriture python3 migrate_postfixadmin.py # migration réelle Configuration via variables d'environnement ou arguments : DB_HOST, DB_PORT, DB_USER, DB_PASSWORD SOURCE_DB (défaut: postfix) TARGET_DB (défaut: netadmin_mail) """ import argparse import logging import sys from datetime import datetime try: import pymysql import pymysql.cursors except ImportError: print("ERROR: pip install PyMySQL") sys.exit(1) logging.basicConfig( level=logging.INFO, format="%(asctime)s [migrate] %(levelname)s %(message)s" ) log = logging.getLogger("migrate") # ── Config ──────────────────────────────────────────────────────────────── def get_args(): p = argparse.ArgumentParser(description="Migrate PostfixAdmin → netadmin_mail") p.add_argument("--host", default="localhost") p.add_argument("--port", default=3306, type=int) p.add_argument("--user", default="root") p.add_argument("--password", default="") p.add_argument("--source-db", default="postfix") p.add_argument("--target-db", default="netadmin_mail") p.add_argument("--dry-run", action="store_true", help="Affiche ce qui sera fait sans écrire en base") return p.parse_args() def connect(args, database=None): return pymysql.connect( host=args.host, port=args.port, user=args.user, password=args.password, database=database, cursorclass=pymysql.cursors.DictCursor, charset="utf8mb4", ) # ── Schéma cible ────────────────────────────────────────────────────────── SCHEMA = """ CREATE TABLE IF NOT EXISTS domain ( domain VARCHAR(255) NOT NULL PRIMARY KEY, description VARCHAR(255) DEFAULT '', max_accounts INT NOT NULL DEFAULT 0, max_aliases INT NOT NULL DEFAULT 0, max_quota_mb INT NOT NULL DEFAULT 0, mb_quota_mb INT NOT NULL DEFAULT 0, active TINYINT(1) NOT NULL DEFAULT 1, created DATETIME NOT NULL DEFAULT NOW(), modified DATETIME NOT NULL DEFAULT NOW() ON UPDATE NOW() ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE IF NOT EXISTS mailbox ( username VARCHAR(255) NOT NULL PRIMARY KEY, password VARCHAR(255) NOT NULL, name VARCHAR(255) NOT NULL DEFAULT '', domain VARCHAR(255) NOT NULL, quota_mb INT NOT NULL DEFAULT 1024, active TINYINT(1) NOT NULL DEFAULT 1, created DATETIME NOT NULL DEFAULT NOW(), modified DATETIME NOT NULL DEFAULT NOW() ON UPDATE NOW(), FOREIGN KEY (domain) REFERENCES domain(domain) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE IF NOT EXISTS alias ( address VARCHAR(255) NOT NULL PRIMARY KEY, goto TEXT NOT NULL, domain VARCHAR(255) NOT NULL, active TINYINT(1) NOT NULL DEFAULT 1, created DATETIME NOT NULL DEFAULT NOW(), modified DATETIME NOT NULL DEFAULT NOW() ON UPDATE NOW(), FOREIGN KEY (domain) REFERENCES domain(domain) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE IF NOT EXISTS sender_login_maps ( sender VARCHAR(255) NOT NULL PRIMARY KEY, authorized TEXT NOT NULL, domain VARCHAR(255) NOT NULL, created DATETIME NOT NULL DEFAULT NOW(), modified DATETIME NOT NULL DEFAULT NOW() ON UPDATE NOW(), FOREIGN KEY (domain) REFERENCES domain(domain) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; """ def create_schema(conn): with conn.cursor() as cur: for stmt in SCHEMA.strip().split(";"): stmt = stmt.strip() if stmt: cur.execute(stmt) conn.commit() log.info("Schéma netadmin_mail créé ✓") # ── Helpers mot de passe ────────────────────────────────────────────────── def normalize_password(raw: str) -> str | None: """ Normalise le mot de passe vers le format {SHA512-CRYPT}. - Déjà {SHA512-CRYPT}xxx → conservé tel quel - Commence par $6$ → préfixe {SHA512-CRYPT} ajouté - $1$ (MD5) ou $2 (bcrypt) → non migrables → None - Vide → None """ if not raw: return None if raw.startswith("{SHA512-CRYPT}"): return raw if raw.startswith("$6$"): return "{SHA512-CRYPT}" + raw # MD5-CRYPT ($1$), bcrypt ($2$/$2a$/$2y$), DES — non migrables return None # ── Migration ───────────────────────────────────────────────────────────── def migrate_domains(src, tgt, dry_run): with src.cursor() as cur: cur.execute("SELECT * FROM domain WHERE domain != 'ALL'") rows = cur.fetchall() log.info(f"Domaines trouvés : {len(rows)}") ok = skip = 0 for row in rows: domain = row["domain"] # PostfixAdmin quota est en Mo (quota global du domaine) max_quota = int(row.get("quota", 0)) # maxquota = quota par BAL en Mo mb_quota = int(row.get("maxquota", 0)) # aliases = limite d'alias par domaine max_aliases = int(row.get("aliases", 0)) data = { "domain": domain, "description": row.get("description", ""), "max_accounts": row.get("mailboxes", 0), "max_aliases": max_aliases, "max_quota_mb": max_quota, "mb_quota_mb": mb_quota, "active": int(row.get("active", 1)), "created": row.get("created", datetime.now()), "modified": row.get("modified", datetime.now()), } if dry_run: log.info(f" [DRY] domain: {domain} (max_accounts={data['max_accounts']})") ok += 1 continue try: with tgt.cursor() as cur: cur.execute(""" INSERT INTO domain (domain, description, max_accounts, max_aliases, max_quota_mb, mb_quota_mb, active, created, modified) VALUES (%(domain)s, %(description)s, %(max_accounts)s, %(max_aliases)s, %(max_quota_mb)s, %(mb_quota_mb)s, %(active)s, %(created)s, %(modified)s) ON DUPLICATE KEY UPDATE description=VALUES(description), max_accounts=VALUES(max_accounts), max_aliases=VALUES(max_aliases), max_quota_mb=VALUES(max_quota_mb), mb_quota_mb=VALUES(mb_quota_mb), modified=NOW() """, data) tgt.commit() ok += 1 except Exception as e: log.warning(f" domaine {domain}: {e}") skip += 1 log.info(f"Domaines : {ok} migrés, {skip} ignorés") def migrate_mailboxes(src, tgt, dry_run): with src.cursor() as cur: cur.execute("SELECT * FROM mailbox") rows = cur.fetchall() log.info(f"Boîtes trouvées : {len(rows)}") ok = skip = no_pwd = 0 for row in rows: username = row["username"] newpassword = row.get("newpassword", "") raw_pwd = row.get("password", "") # Priorité : newpassword (déjà SHA512) s'il est rempli if newpassword and newpassword.strip(): password = normalize_password(newpassword.strip()) if password is None: password = normalize_password(raw_pwd) else: password = normalize_password(raw_pwd) if password is None: log.warning(f" {username}: mot de passe non migrable ({raw_pwd[:10]}...) — compte marqué MIGRATION_REQUIRED") password = "{MIGRATION_REQUIRED}" no_pwd += 1 # PostfixAdmin mailbox quota est en octets (ex: 10485760000 ≈ 10 Go) # NetAdmin utilise les Mo → diviser par 1024*1024 quota_raw = int(row.get("quota", 0)) quota_mb = quota_raw // (1024 * 1024) if quota_raw > 0 else 10000 if quota_mb == 0: quota_mb = 10000 # défaut 10 Go (= 10000 Mo comme PostfixAdmin) # Désactiver les comptes dont le mot de passe n'a pas pu être migré active = 0 if password == "{MIGRATION_REQUIRED}" else int(row.get("active", 1)) data = { "username": username, "password": password, "name": row.get("name", ""), "domain": row["domain"], "quota_mb": quota_mb, "active": active, "created": row.get("created", datetime.now()), "modified": row.get("modified", datetime.now()), } if dry_run: pwd_status = "OK" if not password.startswith("{MIGRATION_REQUIRED}") else "RESET REQUIS" log.info(f" [DRY] mailbox: {username} ({pwd_status}, quota={quota_mb}Mo)") ok += 1 continue try: with tgt.cursor() as cur: cur.execute(""" INSERT INTO mailbox (username, password, name, domain, quota_mb, active, created, modified) VALUES (%(username)s, %(password)s, %(name)s, %(domain)s, %(quota_mb)s, %(active)s, %(created)s, %(modified)s) ON DUPLICATE KEY UPDATE password=VALUES(password), name=VALUES(name), quota_mb=VALUES(quota_mb), modified=NOW() """, data) # Self-alias requis pour virtual_alias_maps cur.execute(""" INSERT INTO alias (address, goto, domain, active, created, modified) VALUES (%s, %s, %s, %s, %s, %s) ON DUPLICATE KEY UPDATE goto=VALUES(goto), modified=NOW() """, (username, username, data["domain"], data["active"], data["created"], data["modified"])) tgt.commit() ok += 1 except Exception as e: log.warning(f" mailbox {username}: {e}") skip += 1 log.info(f"Boîtes : {ok} migrées ({no_pwd} avec reset requis), {skip} ignorées") def migrate_aliases(src, tgt, dry_run): with src.cursor() as cur: cur.execute("SELECT * FROM alias") rows = cur.fetchall() log.info(f"Alias trouvés : {len(rows)}") ok = skip = ignored = 0 for row in rows: address = row["address"] goto = row.get("goto", "") # Ignorer les alias auto (address == goto) — déjà créés avec la BAL if address == goto: ignored += 1 continue # Ignorer les catch-all vides if not goto.strip(): ignored += 1 continue # Ignorer le domaine ALL (PostfixAdmin) if row.get("domain") == "ALL": ignored += 1 continue data = { "address": address, "goto": goto, "domain": row["domain"], "active": int(row.get("active", 1)), "created": row.get("created", datetime.now()), "modified": row.get("modified", datetime.now()), } if dry_run: log.info(f" [DRY] alias: {address} → {goto[:60]}") ok += 1 continue try: with tgt.cursor() as cur: cur.execute(""" INSERT INTO alias (address, goto, domain, active, created, modified) VALUES (%(address)s, %(goto)s, %(domain)s, %(active)s, %(created)s, %(modified)s) ON DUPLICATE KEY UPDATE goto=VALUES(goto), modified=NOW() """, data) tgt.commit() ok += 1 except Exception as e: log.warning(f" alias {address}: {e}") skip += 1 log.info(f"Alias : {ok} migrés, {ignored} ignorés (auto/vides), {skip} erreurs") # ── Main ────────────────────────────────────────────────────────────────── def main(): args = get_args() if args.dry_run: log.info("=== MODE DRY-RUN — aucune écriture ===") # Connexion source log.info(f"Connexion source: {args.source_db}@{args.host}") src = connect(args, args.source_db) if not args.dry_run: # Créer la base cible si nécessaire admin = connect(args) with admin.cursor() as cur: cur.execute( f"CREATE DATABASE IF NOT EXISTS `{args.target_db}` " f"CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci" ) admin.commit() admin.close() log.info(f"Base {args.target_db} créée ✓") tgt = connect(args, args.target_db) create_schema(tgt) else: tgt = None # Migration migrate_domains(src, tgt, args.dry_run) migrate_mailboxes(src, tgt, args.dry_run) migrate_aliases(src, tgt, args.dry_run) src.close() if tgt: tgt.close() log.info("=== Migration terminée ===") if args.dry_run: log.info("Relancez sans --dry-run pour appliquer.") if __name__ == "__main__": main()