"""
第 13 章 · 演示 2：ALTER DEFAULT PRIVILEGES
============================================

场景：DBA 给 ro 角色授权了「未来所有由 dba_user 创建的表」的 SELECT；
      然后 dba_user 新建一张表，验证 ro 不需要再 GRANT 就能读。

依赖：
    pip install "psycopg[binary]>=3.1"
    需先 psql -f ../init.sql 跑过初始化（init.sql 已设置好 dba_user / analyst
    与默认权限规则）。

运行：
    python 02_default_privileges.py
"""

from __future__ import annotations

import psycopg

ADMIN_DSN = "host=127.0.0.1 port=5432 dbname=learn_pg user=postgres"
DBA_DSN   = "host=127.0.0.1 port=5432 dbname=learn_pg user=dba_user  password=Dba@2025"
RO_DSN    = "host=127.0.0.1 port=5432 dbname=learn_pg user=analyst   password=Analyst@2025"


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


def show_default_privs() -> None:
    """展示当前 ALTER DEFAULT PRIVILEGES 规则。"""
    sql = """
        SELECT pg_get_userbyid(defaclrole)        AS for_role,
               nspname,
               defaclobjtype,                      -- r=table, S=sequence, f=function...
               defaclacl
          FROM pg_default_acl d
          JOIN pg_namespace n ON n.oid = d.defaclnamespace
         ORDER BY for_role, nspname;
    """
    with psycopg.connect(ADMIN_DSN) as c, c.cursor() as cur:
        cur.execute(sql)
        rows = cur.fetchall()
    print("  当前 pg_default_acl 中的规则：")
    for r in rows:
        kind = {'r': '表', 'S': '序列', 'f': '函数', 'T': '类型'}.get(r[2], r[2])
        print(f"    FOR ROLE {r[0]:<10} IN SCHEMA {r[1]:<10} ON {kind}: {r[3]}")


def main() -> None:
    line("Step 1：查看当前默认权限规则（init.sql 已设置）")
    show_default_privs()

    # ★ 关键：以 dba_user 身份新建一张表
    line("Step 2：dba_user 在 public 下新建一张表 future_table")
    with psycopg.connect(DBA_DSN) as dba, dba.cursor() as cur:
        cur.execute("DROP TABLE IF EXISTS future_table;")
        cur.execute("""
            CREATE TABLE future_table(
                id   BIGSERIAL PRIMARY KEY,
                memo TEXT
            );
        """)
        cur.execute("INSERT INTO future_table(memo) VALUES ('hello'), ('world');")
        dba.commit()
        # 立刻看一下这张表的 ACL，应该已经包含 analyst=r/dba_user
        cur.execute("""
            SELECT relname, relacl
              FROM pg_class
             WHERE relname = 'future_table';
        """)
        row = cur.fetchone()
        print(f"  新表 {row[0]} 的 ACL = {row[1]}")

    # 用 analyst 连过去，应该可以直接 SELECT，不需要再 GRANT
    line("Step 3：analyst 直接 SELECT future_table（验证默认权限生效）")
    with psycopg.connect(RO_DSN) as ro, ro.cursor() as cur:
        try:
            cur.execute("SELECT count(*) FROM future_table;")
            print(f"  ✅ analyst SELECT 成功，行数 = {cur.fetchone()[0]}")
        except psycopg.errors.InsufficientPrivilege as e:
            print(f"  ❌ analyst SELECT 失败：{e}")

    # 进一步：尝试 INSERT，应失败（默认权限只 GRANT SELECT）
    line("Step 4：analyst 尝试 INSERT，应被拒绝")
    with psycopg.connect(RO_DSN) as ro, ro.cursor() as cur:
        try:
            cur.execute("INSERT INTO future_table(memo) VALUES ('hack');")
            print("  ❌ analyst INSERT 居然成功了，规则有问题！")
        except psycopg.errors.InsufficientPrivilege:
            print("  ✅ analyst INSERT 被拒绝（默认权限只授了 SELECT）")
        ro.rollback()

    # 收尾
    line("收尾：删除 future_table")
    with psycopg.connect(DBA_DSN) as dba, dba.cursor() as cur:
        cur.execute("DROP TABLE IF EXISTS future_table;")
        dba.commit()
        print("  已清理。")


if __name__ == "__main__":
    main()