commit 84a7feeddec6e465170d7b70691e3a8676eaca8b
parent 3f22625820e04dd53cbf188fdfa0a98936bed2ad
Author: Pablo Murad <pablo@pablomurad.com>
Date: Tue, 19 May 2026 20:57:14 -0300
better mail
Diffstat:
7 files changed, 311 insertions(+), 32 deletions(-)
diff --git a/docs/08-email.md b/docs/08-email.md
@@ -89,7 +89,16 @@ sudo python3 scripts/admin/sync_member_email_aliases.py --dry-run
sudo runv-admin-email-alias sync
```
-O sync gera `hash:/etc/postfix/runv-member-aliases` a partir de `/var/lib/runv/email-aliases.json`, corre `postmap` e `systemctl reload postfix`. **Não** altera Mailgun.
+Na vossa VPS o Postfix usa **`mysql:/etc/postfix/mysql-virtual-alias-maps.cf`** — use backend `postfix-mysql` (não adicione mapa hash paralelo).
+
+```bash
+sudo python3 scripts/admin/inspect_postfix_mysql_aliases.py
+sudo cp email/config/runv-member-mail.example.json /etc/runv-member-mail.json
+# editar enabled + colunas/tabela se o inspect sugerir
+sudo runv-admin-email-alias sync
+```
+
+O sync faz UPSERT na tabela de aliases e `reload postfix`. **Não** altera Mailgun.
### Setup inicial no servidor
diff --git a/email/config/runv-member-mail.example.json b/email/config/runv-member-mail.example.json
@@ -1,12 +1,15 @@
{
"enabled": false,
- "backend": "postfix-hash",
- "virtual_alias_file": "/etc/postfix/runv-member-aliases",
- "file_mode": "0o644",
- "check_maps": true,
- "run_postmap": true,
- "postmap_command": ["postmap", "/etc/postfix/runv-member-aliases"],
+ "backend": "postfix-mysql",
+ "mysql_map_file": "/etc/postfix/mysql-virtual-alias-maps.cf",
+ "mysql": {
+ "table": "",
+ "address_column": "",
+ "goto_column": "",
+ "managed_column": "comment",
+ "managed_value": "runv-email-alias"
+ },
"reload_postfix": true,
"reload_command": ["systemctl", "reload", "postfix"],
- "auto_sync_on_approve": false
+ "auto_sync_on_approve": true
}
diff --git a/scripts/admin/discover_mail_stack.py b/scripts/admin/discover_mail_stack.py
@@ -155,10 +155,18 @@ def main() -> int:
pth = Path(path)
print(f"{path}: {'presente' if pth.is_file() else 'ausente'}")
- print(
- "\nPróximo passo: alinhar virtual_alias_maps com hash:/etc/postfix/runv-member-aliases "
- "e activar /etc/runv-member-mail.json; depois: runv-admin-email-alias sync"
- )
+ maps = ""
+ code, maps = run(["postconf", "-h", "virtual_alias_maps"])
+ if code == 0 and "mysql:" in maps:
+ print(
+ "\nPróximo passo (MySQL): sudo python3 scripts/admin/inspect_postfix_mysql_aliases.py\n"
+ " depois copiar email/config/runv-member-mail.example.json para /etc/runv-member-mail.json\n"
+ " e: sudo runv-admin-email-alias sync"
+ )
+ else:
+ print(
+ "\nPróximo passo: configurar /etc/runv-member-mail.json e runv-admin-email-alias sync"
+ )
return 0
diff --git a/scripts/admin/inspect_postfix_mysql_aliases.py b/scripts/admin/inspect_postfix_mysql_aliases.py
@@ -0,0 +1,107 @@
+#!/usr/bin/env python3
+"""
+Lê mysql-virtual-alias-maps.cf e mostra estrutura da tabela (read-only).
+
+Ajuda a preencher /etc/runv-member-mail.json para backend postfix-mysql.
+"""
+
+from __future__ import annotations
+
+import argparse
+import re
+import subprocess
+import sys
+from pathlib import Path
+
+_SCRIPT_DIR = Path(__file__).resolve().parent
+_REPO_TOOLS_LIB = _SCRIPT_DIR.parent.parent / "tools" / "lib"
+if str(_REPO_TOOLS_LIB) not in sys.path:
+ sys.path.insert(0, str(_REPO_TOOLS_LIB))
+
+import runv_mail_sync as ms # noqa: E402
+
+QUERY_HINT = re.compile(
+ r"SELECT\s+[`']?(\w+)[`']?\s+FROM\s+[`']?(\w+)[`']?\s+WHERE\s+[`']?(\w+)[`']?\s*=",
+ re.IGNORECASE,
+)
+
+
+def main() -> int:
+ p = argparse.ArgumentParser(description="Inspecionar mapa MySQL de aliases Postfix")
+ p.add_argument(
+ "--map-file",
+ default="/etc/postfix/mysql-virtual-alias-maps.cf",
+ help="ficheiro .cf do Postfix",
+ )
+ p.add_argument("--table", default="", help="forçar nome da tabela")
+ args = p.parse_args()
+
+ if sys.platform == "win32":
+ print("Execute na VPS Linux.", file=sys.stderr)
+ return 2
+
+ map_path = Path(args.map_file)
+ if not map_path.is_file():
+ print(f"ausente: {map_path}", file=sys.stderr)
+ return 1
+
+ parsed = ms.parse_postfix_mysql_cf(map_path)
+ print(f"=== {map_path} ===")
+ for key in ("hosts", "user", "dbname", "query"):
+ val = parsed.get(key, "")
+ if key == "password":
+ continue
+ print(f"{key} = {val}")
+ print("password = ***")
+
+ query = parsed.get("query", "")
+ table = args.table.strip()
+ dest_col = ""
+ addr_col = ""
+ m = QUERY_HINT.search(query.replace("\n", " "))
+ if m:
+ dest_col, table, addr_col = m.group(1), m.group(2), m.group(3)
+ print(f"\ninferido da query: tabela={table!r} col_destino={dest_col!r} col_endereco={addr_col!r}")
+
+ if not table:
+ print("\nNão foi possível inferir a tabela; use --table NOME", file=sys.stderr)
+ return 1
+
+ sql = f"DESCRIBE `{table}`;"
+ print(f"\n=== {sql} ===")
+ try:
+ out = ms.mysql_exec(parsed, sql, dry_run=False)
+ print(out or "(sem saída)")
+ except SystemExit as e:
+ print(e, file=sys.stderr)
+ return 1
+
+ sample = f"SELECT * FROM `{table}` LIMIT 5;"
+ print(f"\n=== {sample} ===")
+ try:
+ print(ms.mysql_exec(parsed, sample, dry_run=False) or "(vazio)")
+ except SystemExit:
+ print("(amostra indisponível)", file=sys.stderr)
+
+ print(
+ "\nSugestão /etc/runv-member-mail.json:\n"
+ "{\n"
+ ' "enabled": true,\n'
+ ' "backend": "postfix-mysql",\n'
+ f' "mysql_map_file": "{map_path}",\n'
+ " \"mysql\": {\n"
+ f' "table": "{table}",\n'
+ f' "address_column": "{addr_col or "address"}",\n'
+ f' "goto_column": "{dest_col or "goto"}",\n'
+ ' "managed_column": "comment",\n'
+ ' "managed_value": "runv-email-alias"\n'
+ " },\n"
+ ' "reload_postfix": true,\n'
+ ' "auto_sync_on_approve": true\n'
+ "}"
+ )
+ return 0
+
+
+if __name__ == "__main__":
+ raise SystemExit(main())
diff --git a/scripts/admin/sync_member_email_aliases.py b/scripts/admin/sync_member_email_aliases.py
@@ -42,7 +42,7 @@ def main() -> int:
if args.config.strip():
cfg = ms.load_config(Path(args.config.strip()))
- return ms.sync_postfix_hash(dry_run=args.dry_run, cfg=cfg)
+ return ms.sync_mail(dry_run=args.dry_run, cfg=cfg)
if __name__ == "__main__":
diff --git a/tools/bin/runv-admin-email-alias b/tools/bin/runv-admin-email-alias
@@ -69,7 +69,7 @@ def cmd_approve(username: str, *, sync_mail: bool) -> int:
print(f" {entry.get('destination')}\n")
if sync_mail and mail_sync is not None:
try:
- mail_sync.sync_postfix_hash()
+ mail_sync.sync_mail()
print("Encaminhamento aplicado no Postfix (sync OK).\n")
except SystemExit:
print(
@@ -100,7 +100,7 @@ def cmd_approve(username: str, *, sync_mail: bool) -> int:
def cmd_sync() -> int:
if mail_sync is None:
ea.rc.friendly_exit("módulo runv_mail_sync não encontrado.")
- mail_sync.sync_postfix_hash()
+ mail_sync.sync_mail()
print("Sync Postfix concluído.")
return 0
diff --git a/tools/lib/runv_mail_sync.py b/tools/lib/runv_mail_sync.py
@@ -1,15 +1,19 @@
#!/usr/bin/env python3
"""
-Sincroniza aliases aprovados (email-aliases.json) com o MTA local (Postfix hash).
+Sincroniza aliases aprovados (email-aliases.json) com o MTA local.
-Separado do Mailgun transacional (/etc/runv-email.json). Requer configuração
-explícita em /etc/runv-member-mail.json no servidor.
+Backends:
+ - postfix-hash: ficheiro + postmap (só se virtual_alias_maps usar hash)
+ - postfix-mysql: tabela MySQL já usada por mysql-virtual-alias-maps.cf
+
+Separado do Mailgun transacional (/etc/runv-email.json).
"""
from __future__ import annotations
import json
import os
+import re
import subprocess
import sys
import tempfile
@@ -19,6 +23,10 @@ from typing import Any
import runv_community as rc
DEFAULT_CONFIG_PATH = Path("/etc/runv-member-mail.json")
+QUERY_HINT = re.compile(
+ r"SELECT\s+[`']?(\w+)[`']?\s+FROM\s+[`']?(\w+)[`']?\s+WHERE\s+[`']?(\w+)[`']?\s*=",
+ re.IGNORECASE,
+)
try:
import runv_email_aliases as ea
@@ -60,6 +68,97 @@ def active_forwarding_rows() -> list[tuple[str, str]]:
return rows
+def sql_literal(value: str) -> str:
+ return "'" + value.replace("\\", "\\\\").replace("'", "''") + "'"
+
+
+def parse_postfix_mysql_cf(path: Path) -> dict[str, str]:
+ if not path.is_file():
+ rc.friendly_exit(f"ficheiro MySQL Postfix ausente: {path}")
+ data: dict[str, str] = {}
+ for line in path.read_text(encoding="utf-8").splitlines():
+ line = line.strip()
+ if not line or line.startswith("#"):
+ continue
+ if "=" not in line:
+ continue
+ key, val = line.split("=", 1)
+ data[key.strip().lower()] = val.strip()
+ for req in ("user", "password", "hosts", "dbname"):
+ if not data.get(req):
+ rc.friendly_exit(f"{path} sem campo obrigatório: {req}")
+ return data
+
+
+def mysql_exec(mysql_cfg: dict[str, str], sql: str, *, dry_run: bool) -> str:
+ if dry_run:
+ print(f"[dry-run] mysql -e {sql}")
+ return ""
+ cmd = [
+ "mysql",
+ "-N",
+ "-B",
+ "-h",
+ mysql_cfg["hosts"],
+ "-u",
+ mysql_cfg["user"],
+ mysql_cfg["dbname"],
+ "-e",
+ sql,
+ ]
+ env = os.environ.copy()
+ env["MYSQL_PWD"] = mysql_cfg["password"]
+ proc = subprocess.run(cmd, capture_output=True, text=True, timeout=120, env=env)
+ if proc.returncode != 0:
+ err = (proc.stderr or proc.stdout or "").strip()
+ rc.friendly_exit(f"mysql falhou: {err}")
+ return (proc.stdout or "").strip()
+
+
+def infer_mysql_table_from_query(query: str) -> tuple[str, str, str]:
+ m = QUERY_HINT.search(query.replace("\n", " "))
+ if not m:
+ rc.friendly_exit(
+ "não foi possível inferir tabela/colunas da query em mysql_map_file; "
+ "defina mysql.table, mysql.address_column e mysql.goto_column em runv-member-mail.json"
+ )
+ dest_col, table, addr_col = m.group(1), m.group(2), m.group(3)
+ return table, addr_col, dest_col
+
+
+def mysql_sync_options(cfg: dict[str, Any]) -> dict[str, str]:
+ block = cfg.get("mysql")
+ if not isinstance(block, dict):
+ block = {}
+ map_file = str(
+ block.get("map_file")
+ or cfg.get("mysql_map_file")
+ or "/etc/postfix/mysql-virtual-alias-maps.cf"
+ )
+ parsed = parse_postfix_mysql_cf(Path(map_file))
+ table = str(block.get("table", "")).strip()
+ addr_col = str(block.get("address_column", "")).strip()
+ goto_col = str(block.get("goto_column", "")).strip()
+ if not table or not addr_col or not goto_col:
+ inferred_table, inferred_addr, inferred_goto = infer_mysql_table_from_query(
+ parsed.get("query", "")
+ )
+ table = table or inferred_table
+ addr_col = addr_col or inferred_addr
+ goto_col = goto_col or inferred_goto
+ managed_col = str(block.get("managed_column", "")).strip()
+ managed_val = str(block.get("managed_value", "runv-email-alias")).strip()
+ return {
+ "map_file": map_file,
+ "parsed": parsed, # type: ignore[dict-item]
+ "table": table,
+ "address_column": addr_col,
+ "goto_column": goto_col,
+ "managed_column": managed_col,
+ "managed_value": managed_val,
+ }
+
+
def render_postfix_virtual(rows: list[tuple[str, str]]) -> str:
lines = [
"# Gerado por runv — não editar à mão; use runv-admin-email-alias sync",
@@ -123,25 +222,14 @@ def check_postfix_maps_include(target: Path, *, dry_run: bool) -> None:
if needle in maps.replace(" ", ""):
return
print(
- f"aviso: virtual_alias_maps actual não referencia {needle}\n"
- f" actual: {maps or '(vazio)'}\n"
- " adicione (exemplo):\n"
- f' postconf -e "virtual_alias_maps = ${{virtual_alias_maps}}, hash:{target}"\n'
- " ou inclua o ficheiro na configuração existente (MySQL/LDAP/etc.).",
+ f"aviso: virtual_alias_maps usa {maps!r} e não hash:{target}.\n"
+ " Para o vosso servidor use backend postfix-mysql, não postfix-hash.",
file=sys.stderr,
)
def sync_postfix_hash(*, dry_run: bool = False, cfg: dict[str, Any] | None = None) -> int:
data = cfg if cfg is not None else load_config()
- if not data.get("enabled"):
- rc.friendly_exit(
- f"sincronização desactivada; defina enabled=true em {config_path()}"
- )
- backend = str(data.get("backend", "postfix-hash")).strip().lower()
- if backend != "postfix-hash":
- rc.friendly_exit(f"backend não suportado: {backend!r}")
-
target = Path(str(data.get("virtual_alias_file", "/etc/postfix/runv-member-aliases")))
file_mode = int(str(data.get("file_mode", "0o644")), 8)
rows = active_forwarding_rows()
@@ -172,8 +260,72 @@ def sync_postfix_hash(*, dry_run: bool = False, cfg: dict[str, Any] | None = Non
return 0
+def sync_postfix_mysql(*, dry_run: bool = False, cfg: dict[str, Any] | None = None) -> int:
+ data = cfg if cfg is not None else load_config()
+ opts = mysql_sync_options(data)
+ parsed: dict[str, str] = opts["parsed"] # type: ignore[assignment]
+ table = opts["table"]
+ addr_col = opts["address_column"]
+ goto_col = opts["goto_column"]
+ managed_col = opts["managed_column"]
+ managed_val = opts["managed_value"]
+
+ rows = active_forwarding_rows()
+ active_addresses = {alias for alias, _ in rows}
+
+ if managed_col:
+ in_list = ", ".join(sql_literal(a) for a in sorted(active_addresses)) or "''"
+ delete_sql = (
+ f"DELETE FROM `{table}` WHERE `{managed_col}` = {sql_literal(managed_val)} "
+ f"AND `{addr_col}` NOT IN ({in_list});"
+ )
+ mysql_exec(parsed, delete_sql, dry_run=dry_run)
+
+ for alias, dest in rows:
+ cols = [f"`{addr_col}`", f"`{goto_col}`"]
+ vals = [sql_literal(alias), sql_literal(dest)]
+ updates = [f"`{goto_col}` = {sql_literal(dest)}"]
+ if managed_col:
+ cols.append(f"`{managed_col}`")
+ vals.append(sql_literal(managed_val))
+ updates.append(f"`{managed_col}` = {sql_literal(managed_val)}")
+ upsert = (
+ f"INSERT INTO `{table}` ({', '.join(cols)}) VALUES ({', '.join(vals)}) "
+ f"ON DUPLICATE KEY UPDATE {', '.join(updates)};"
+ )
+ mysql_exec(parsed, upsert, dry_run=dry_run)
+ print(f" {alias} -> {dest}")
+
+ print(f"MySQL {table}: {len(rows)} alias(es) activo(s) sincronizado(s)")
+
+ if data.get("reload_postfix", True):
+ reload_cmd = data.get("reload_command")
+ if isinstance(reload_cmd, list) and reload_cmd:
+ cmd = [str(x) for x in reload_cmd]
+ else:
+ cmd = ["systemctl", "reload", "postfix"]
+ _run_cmd(cmd, dry_run=dry_run)
+
+ return 0
+
+
+def sync_mail(*, dry_run: bool = False, cfg: dict[str, Any] | None = None) -> int:
+ data = cfg if cfg is not None else load_config()
+ if not data.get("enabled"):
+ rc.friendly_exit(
+ f"sincronização desactivada; defina enabled=true em {config_path()}"
+ )
+ backend = str(data.get("backend", "postfix-mysql")).strip().lower()
+ if backend == "postfix-hash":
+ return sync_postfix_hash(dry_run=dry_run, cfg=data)
+ if backend == "postfix-mysql":
+ return sync_postfix_mysql(dry_run=dry_run, cfg=data)
+ rc.friendly_exit(f"backend não suportado: {backend!r}")
+ return 1
+
+
def maybe_sync_after_approve(*, dry_run: bool = False) -> None:
cfg = load_config()
if not cfg.get("enabled") or not cfg.get("auto_sync_on_approve"):
return
- sync_postfix_hash(dry_run=dry_run, cfg=cfg)
+ sync_mail(dry_run=dry_run, cfg=cfg)
