-- 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;
|
