-- Backlog A4（2026-07-17）：业务连库账号与超管分离 —— 三库三角色
--   jnpf_app      → jnpf_init    （9 个业务服务，Nacos datasource.yaml 消费）
--   jnpf_flow_app → jnpf_flow    （独立流程引擎，compose env FLOW_DB_* 消费）
--   nacos_app     → nacos_config （Nacos 服务端，compose env NACOS_DB_* 消费）
--
-- 动机（L029）：业务用 postgres 超管连库使 superuser_reserved_connections=3 形同虚设，
-- 连接打满时运维无法救火；且权限过大。
--
-- 执行入口：同目录 create-app-roles.sh（从仓库根 .env 读密码，docker exec 进容器执行）。
-- 幂等：可重复执行；每次执行都会按 .env 重置角色密码。
--
-- psql 变量（由 wrapper 传入）：
--   nacos_enabled：true/false —— 补丁 #8 后新栈默认 false（.env 未设 JNPF_NACOS_DB_PASSWORD），
--     nacos_app 段整体跳过；旧实例兼容时 wrapper 会传 true，行为与本文件历史版本一致
--   密码：app_pw / flow_pw / nacos_pw（nacos_enabled=false 时 nacos_pw 不会被用到）
--   连接配额：app_conn_limit / flow_conn_limit / nacos_conn_limit（整数，直接内插，wrapper 已校验为非负整数）
--
-- 设计要点：
--   1. 存量对象所有权移交给对应角色 —— 在线开发（visualdev）运行时会对既有表 ALTER/DROP，
--      Flowable/Nacos 升级也可能改 schema；仅 GRANT 不够。
--      覆盖 relkind r/p/S/v/m（表/分区表/序列/视图/物化视图）；如未来引入 FDW 外部表(f)或
--      自定义复合类型(c)需在循环里补充处理
--   2. 排除扩展对象（pg_depend deptype='e'，如 pg_stat_statements 的视图），不动扩展归属
--   3. REVOKE PUBLIC 的默认 CONNECT —— 角色只能连自己的库
--   4. postgres 建的新对象通过 ALTER DEFAULT PRIVILEGES 自动授予（运维补丁场景）

\set ON_ERROR_STOP on

-- ========== 角色（幂等创建 + 每次重置密码） ==========
SELECT 'CREATE ROLE jnpf_app LOGIN NOSUPERUSER NOCREATEDB NOCREATEROLE NOREPLICATION'
 WHERE NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'jnpf_app') \gexec
SELECT 'CREATE ROLE jnpf_flow_app LOGIN NOSUPERUSER NOCREATEDB NOCREATEROLE NOREPLICATION'
 WHERE NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'jnpf_flow_app') \gexec
\if :nacos_enabled
SELECT 'CREATE ROLE nacos_app LOGIN NOSUPERUSER NOCREATEDB NOCREATEROLE NOREPLICATION'
 WHERE NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'nacos_app') \gexec
\else
\echo '跳过 nacos_app（补丁 #8 后栈不再使用；旧实例兼容需 wrapper 传 nacos_enabled=true）'
\endif

ALTER ROLE jnpf_app      PASSWORD :'app_pw';
ALTER ROLE jnpf_flow_app PASSWORD :'flow_pw';
\if :nacos_enabled
ALTER ROLE nacos_app     PASSWORD :'nacos_pw';
\endif

-- ========== 连接数配额（L029 纵深防御：防单角色占满连接槽饿死其他角色，如流程引擎） ==========
-- 值由 wrapper 从 .env 传入（未设用脚本默认 150/30/10）。铁律：三者之和须 <
-- (max_connections − superuser_reserved_connections)，否则等于没预留。整数由 wrapper 校验后直接内插。
ALTER ROLE jnpf_app      CONNECTION LIMIT :app_conn_limit;
ALTER ROLE jnpf_flow_app CONNECTION LIMIT :flow_conn_limit;
\if :nacos_enabled
ALTER ROLE nacos_app     CONNECTION LIMIT :nacos_conn_limit;
\endif

-- 角色只能连各自的库。nacos_config 的 REVOKE 不放进 \if：即便本次跳过 nacos_app，
-- 该库也不该继续留着默认的 PUBLIC CONNECT——只有随后的 GRANT（把连接权还给 nacos_app）才需要条件跳过。
REVOKE CONNECT ON DATABASE jnpf_init    FROM PUBLIC;
REVOKE CONNECT ON DATABASE jnpf_flow    FROM PUBLIC;
REVOKE CONNECT ON DATABASE nacos_config FROM PUBLIC;
GRANT  CONNECT ON DATABASE jnpf_init    TO jnpf_app;
GRANT  CONNECT ON DATABASE jnpf_flow    TO jnpf_flow_app;
\if :nacos_enabled
GRANT  CONNECT ON DATABASE nacos_config TO nacos_app;
\endif

-- ========== jnpf_init → jnpf_app ==========
\c jnpf_init
GRANT USAGE, CREATE ON SCHEMA public TO jnpf_app;
DO $$
DECLARE r record;
BEGIN
  FOR r IN
    SELECT c.oid::regclass AS obj
      FROM pg_class c
      JOIN pg_namespace n ON n.oid = c.relnamespace
     WHERE n.nspname = 'public'
       AND c.relkind IN ('r','p','S','v','m')
       AND c.relowner <> 'jnpf_app'::regrole
       AND NOT EXISTS (SELECT 1 FROM pg_depend d WHERE d.objid = c.oid AND d.deptype = 'e')
       -- 表自有序列（serial/identity）不可单独改 owner，随其表的 ALTER TABLE 自动移交
       AND NOT (c.relkind = 'S' AND EXISTS (
             SELECT 1 FROM pg_depend d2
              WHERE d2.classid = 'pg_class'::regclass AND d2.objid = c.oid
                AND d2.deptype IN ('a','i')))
  LOOP
    EXECUTE format('ALTER TABLE %s OWNER TO jnpf_app', r.obj);
  END LOOP;
END $$;
ALTER DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA public GRANT ALL ON TABLES    TO jnpf_app;
ALTER DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA public GRANT ALL ON SEQUENCES TO jnpf_app;

-- ========== jnpf_flow → jnpf_flow_app ==========
\c jnpf_flow
GRANT USAGE, CREATE ON SCHEMA public TO jnpf_flow_app;
DO $$
DECLARE r record;
BEGIN
  FOR r IN
    SELECT c.oid::regclass AS obj
      FROM pg_class c
      JOIN pg_namespace n ON n.oid = c.relnamespace
     WHERE n.nspname = 'public'
       AND c.relkind IN ('r','p','S','v','m')
       AND c.relowner <> 'jnpf_flow_app'::regrole
       AND NOT EXISTS (SELECT 1 FROM pg_depend d WHERE d.objid = c.oid AND d.deptype = 'e')
       -- 表自有序列（serial/identity）不可单独改 owner，随其表的 ALTER TABLE 自动移交
       AND NOT (c.relkind = 'S' AND EXISTS (
             SELECT 1 FROM pg_depend d2
              WHERE d2.classid = 'pg_class'::regclass AND d2.objid = c.oid
                AND d2.deptype IN ('a','i')))
  LOOP
    EXECUTE format('ALTER TABLE %s OWNER TO jnpf_flow_app', r.obj);
  END LOOP;
END $$;
ALTER DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA public GRANT ALL ON TABLES    TO jnpf_flow_app;
ALTER DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA public GRANT ALL ON SEQUENCES TO jnpf_flow_app;

-- ========== nacos_config → nacos_app ==========
\if :nacos_enabled
\c nacos_config
GRANT USAGE, CREATE ON SCHEMA public TO nacos_app;
DO $$
DECLARE r record;
BEGIN
  FOR r IN
    SELECT c.oid::regclass AS obj
      FROM pg_class c
      JOIN pg_namespace n ON n.oid = c.relnamespace
     WHERE n.nspname = 'public'
       AND c.relkind IN ('r','p','S','v','m')
       AND c.relowner <> 'nacos_app'::regrole
       AND NOT EXISTS (SELECT 1 FROM pg_depend d WHERE d.objid = c.oid AND d.deptype = 'e')
       -- 表自有序列（serial/identity）不可单独改 owner，随其表的 ALTER TABLE 自动移交
       AND NOT (c.relkind = 'S' AND EXISTS (
             SELECT 1 FROM pg_depend d2
              WHERE d2.classid = 'pg_class'::regclass AND d2.objid = c.oid
                AND d2.deptype IN ('a','i')))
  LOOP
    EXECUTE format('ALTER TABLE %s OWNER TO nacos_app', r.obj);
  END LOOP;
END $$;
ALTER DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA public GRANT ALL ON TABLES    TO nacos_app;
ALTER DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA public GRANT ALL ON SEQUENCES TO nacos_app;
\endif

-- ========== 汇总 ==========
\c postgres
SELECT rolname, rolsuper, rolcanlogin, rolconnlimit FROM pg_roles
 WHERE rolname IN ('postgres','jnpf_app','jnpf_flow_app','nacos_app') ORDER BY rolname;