From 34981c30a78e8bbd7791131059a9210f9928b62c Mon Sep 17 00:00:00 2001
From: 刘光辉 <347230014@qq.com>
Date: 星期四, 17 九月 2026 09:24:09 +0800
Subject: [PATCH] Merge remote-tracking branch 'origin/master' into master
---
docker/postgres/migration/create_app_roles.sql | 150 ++++++++++++++++++++++++++++++++++++++++++++++++++
1 files changed, 150 insertions(+), 0 deletions(-)
diff --git a/docker/postgres/migration/create_app_roles.sql b/docker/postgres/migration/create_app_roles.sql
new file mode 100644
index 0000000..d123207
--- /dev/null
+++ b/docker/postgres/migration/create_app_roles.sql
@@ -0,0 +1,150 @@
+-- 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 锛圢acos 鏈嶅姟绔紝compose env NACOS_DB_* 娑堣垂锛�
+--
+-- 鍔ㄦ満锛圠029锛夛細涓氬姟鐢� postgres 瓒呯杩炲簱浣� superuser_reserved_connections=3 褰㈠悓铏氳锛�
+-- 杩炴帴鎵撴弧鏃惰繍缁存棤娉曟晳鐏紱涓旀潈闄愯繃澶с��
+--
+-- 鎵ц鍏ュ彛锛氬悓鐩綍 create-app-roles.sh锛堜粠浠撳簱鏍� .env 璇诲瘑鐮侊紝docker exec 杩涘鍣ㄦ墽琛岋級銆�
+-- 骞傜瓑锛氬彲閲嶅鎵ц锛涙瘡娆℃墽琛岄兘浼氭寜 .env 閲嶇疆瑙掕壊瀵嗙爜銆�
+--
+-- psql 鍙橀噺锛堢敱 wrapper 浼犲叆锛夛細
+-- nacos_enabled锛歵rue/false 鈥斺�� 琛ヤ竵 #8 鍚庢柊鏍堥粯璁� false锛�.env 鏈 JNPF_NACOS_DB_PASSWORD锛夛紝
+-- nacos_app 娈垫暣浣撹烦杩囷紱鏃у疄渚嬪吋瀹规椂 wrapper 浼氫紶 true锛岃涓轰笌鏈枃浠跺巻鍙茬増鏈竴鑷�
+-- 瀵嗙爜锛歛pp_pw / flow_pw / nacos_pw锛坣acos_enabled=false 鏃� nacos_pw 涓嶄細琚敤鍒帮級
+-- 杩炴帴閰嶉锛歛pp_conn_limit / flow_conn_limit / nacos_conn_limit锛堟暣鏁帮紝鐩存帴鍐呮彃锛寃rapper 宸叉牎楠屼负闈炶礋鏁存暟锛�
+--
+-- 璁捐瑕佺偣锛�
+-- 1. 瀛橀噺瀵硅薄鎵�鏈夋潈绉讳氦缁欏搴旇鑹� 鈥斺�� 鍦ㄧ嚎寮�鍙戯紙visualdev锛夎繍琛屾椂浼氬鏃㈡湁琛� ALTER/DROP锛�
+-- Flowable/Nacos 鍗囩骇涔熷彲鑳芥敼 schema锛涗粎 GRANT 涓嶅銆�
+-- 瑕嗙洊 relkind r/p/S/v/m锛堣〃/鍒嗗尯琛�/搴忓垪/瑙嗗浘/鐗╁寲瑙嗗浘锛夛紱濡傛湭鏉ュ紩鍏� FDW 澶栭儴琛�(f)鎴�
+-- 鑷畾涔夊鍚堢被鍨�(c)闇�鍦ㄥ惊鐜噷琛ュ厖澶勭悊
+-- 2. 鎺掗櫎鎵╁睍瀵硅薄锛坧g_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
+
+-- 瑙掕壊鍙兘杩炲悇鑷殑搴撱�俷acos_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;
--
Gitblit v1.8.0