"""
第 13 章 · 演示 4：pg_hba.conf 各 METHOD 差异（说明 + 自检）
==============================================================

本脚本不会修改 pg_hba.conf（那需要 root + reload 服务），而是：
  1) 解析当前数据库的 pg_hba_file_rules 视图，把规则可读化打印；
  2) 解析 pg_settings 中关键参数（password_encryption / ssl 等）；
  3) 对若干 METHOD 给出实操建议与示范的 pg_hba 行；
  4) 简单实验：尝试用 sslmode=disable / require / verify-ca 连接，看哪些被拒。

依赖：
    pip install "psycopg[binary]>=3.1"

运行：
    python 04_pg_hba_demo.py
"""

from __future__ import annotations

import psycopg

DSN = "host=127.0.0.1 port=5432 dbname=learn_pg user=postgres"

EXAMPLES = [
    ("trust",
     "local   all     postgres                       trust",
     "本地 socket 不验密码，仅适合 docker 容器内、CI 临时调试。生产严禁。"),
    ("md5",
     "host    all     all      0.0.0.0/0             md5",
     "MD5 哈希存储，PG 14+ 不再推荐；存在彩虹表/碰撞风险。"),
    ("scram-sha-256",
     "host    all     all      0.0.0.0/0             scram-sha-256",
     "PG 14+ 默认推荐，挑战-应答协议，密码不上网。"),
    ("cert",
     "hostssl all     all      0.0.0.0/0             cert clientcert=verify-full",
     "客户端必须出示受信 CA 颁发的证书，可零密码登录，机器对机器最安全。"),
    ("peer",
     "local   all     all                            peer map=devmap",
     "用 OS 用户名作为 PG 用户名，仅本地 unix socket 有效。"),
    ("ident",
     "host    all     all      10.0.0.0/8            ident map=devmap",
     "通过 ident 协议询问对方 OS 用户，需要客户端机器跑 identd，几乎已废弃。"),
    ("ldap",
     "host    all     all      0.0.0.0/0             ldap ldapserver=ldap.corp ldapprefix=\"cn=\" ldapsuffix=\",ou=People,dc=corp\"",
     "对接公司 AD/LDAP，统一身份。"),
    ("reject",
     "host    all     all      192.168.99.0/24       reject",
     "显式拒绝，匹配后立即 ERROR，不再向下匹配。"),
]


def line(t: str) -> None:
    print("\n" + "=" * 60 + "\n" + t + "\n" + "=" * 60)


def show_current_rules(conn: psycopg.Connection) -> None:
    """从 pg_hba_file_rules 视图读取当前生效的规则。"""
    sql = """
        SELECT line_number, type, database, user_name,
               address, netmask, auth_method, options, error
          FROM pg_hba_file_rules
         ORDER BY line_number;
    """
    with conn.cursor() as cur:
        cur.execute(sql)
        rows = cur.fetchall()

    print(f"  pg_hba.conf 当前共 {len(rows)} 条规则（按文件顺序匹配）：")
    print(f"  {'#':<4}{'TYPE':<8}{'DB':<10}{'USER':<10}{'ADDR':<22}{'METHOD':<18}ERROR")
    for r in rows:
        addr = r[4] or '-'
        print(f"  {r[0]:<4}{r[1]:<8}{','.join(r[2]):<10}{','.join(r[3]):<10}"
              f"{addr:<22}{r[6]:<18}{r[8] or ''}")


def show_security_settings(conn: psycopg.Connection) -> None:
    keys = ['password_encryption', 'ssl', 'ssl_cert_file',
            'ssl_key_file', 'authentication_timeout',
            'log_connections', 'log_disconnections']
    sql = "SELECT name, setting FROM pg_settings WHERE name = ANY(%s)"
    with conn.cursor() as cur:
        cur.execute(sql, (keys,))
        rows = cur.fetchall()
    print(f"  {'参数':<28}值")
    for k, v in rows:
        print(f"  {k:<28}{v}")


def explain_methods() -> None:
    print(f"  {'METHOD':<16}示例规则")
    print(f"  {'-'*16}{'-'*60}")
    for name, example, desc in EXAMPLES:
        print(f"  {name:<16}{example}")
        print(f"  {'':<16}↳ {desc}\n")


def try_sslmodes() -> None:
    """尝试不同 sslmode 连接，观察成功/失败。仅做现象展示。"""
    print("  注意：本地 PG 若未开 ssl，require/verify-* 会失败；这是预期。")
    for mode in ("disable", "prefer", "require", "verify-ca", "verify-full"):
        dsn = f"host=127.0.0.1 port=5432 dbname=learn_pg user=postgres sslmode={mode}"
        try:
            with psycopg.connect(dsn, connect_timeout=3) as c, c.cursor() as cur:
                cur.execute("SELECT current_setting('ssl')")
                ssl_on = cur.fetchone()[0]
                # 是否真用 SSL：psycopg v3 暴露 connection.info.ssl_in_use
                in_use = c.info.ssl_in_use if hasattr(c.info, "ssl_in_use") else "?"
                print(f"  sslmode={mode:<12} → ✅ 连上，ssl_in_use={in_use} (server ssl={ssl_on})")
        except Exception as e:
            print(f"  sslmode={mode:<12} → ❌ {type(e).__name__}: {str(e).splitlines()[0]}")


def main() -> None:
    with psycopg.connect(DSN) as conn:
        line("Step 1：当前 pg_hba.conf 规则一览")
        show_current_rules(conn)

        line("Step 2：与认证/SSL 相关的 GUC 参数")
        show_security_settings(conn)

    line("Step 3：常见 METHOD 速查 + 示例")
    explain_methods()

    line("Step 4：实测不同 sslmode 的连接行为")
    try_sslmodes()

    line("生产建议总结")
    print("""
  ① 公网/办公网入口的规则一律用 hostssl + scram-sha-256；
  ② 本地 socket 用 peer + map 文件，禁止 trust；
  ③ 复制账号单独开 type=replication，IP 限定为对端从库；
  ④ password_encryption 设 scram-sha-256；老用户必须重设一次密码；
  ⑤ 修改 pg_hba.conf 后只需 reload，不必 restart：
       SELECT pg_reload_conf();
""")


if __name__ == "__main__":
    main()