104 lines
5.4 KiB
Bash
104 lines
5.4 KiB
Bash
#!/usr/bin/env bash
|
||
# ==============================================================================
|
||
# 🔴 2026-09-23 已作废，⛔ 不得执行。
|
||
# 原因：PG 显式禁止从函数内执行 CREATE DATABASE（SECURITY DEFINER 路径无效）。
|
||
# 替代件：交付物/建库权限-D档被证伪与修正判定-20260923.md
|
||
# ==============================================================================
|
||
# D 档一次性落地：建库函数 + 7 条验收 + 锁管理（幂等 · 可重复跑）
|
||
# 用法： bash "E:/ProgramData/AIProject/aliyun-dsh-server/交付物/插件建库权限-一次性执行.sh"
|
||
# 设计目标：**把占锁窗口压到 ~40 秒**（抢不到就立刻退出，不空转）
|
||
# 退出码： 0=全绿 2=DDL/验收失败 3=抢锁失败（别的会话在跑）
|
||
# ⚠️ 本脚本会 `su postgres` 建 SECURITY DEFINER 函数 —— 已由用户 2026-09-22 19:3x 拍板 D
|
||
# ==============================================================================
|
||
set -u
|
||
GUARD="D:/github/dsh_shenxian/dsh-server-docs/scripts/handoff-guard.sh"
|
||
HOST="[email protected]"
|
||
ME="插件投放与分库线-执行棒(抢间隙)"
|
||
|
||
# ── 1 抢全局执行锁（抢不到立刻走人）─────────────────────────────────────────
|
||
CLAIM_OUT="$(bash "$GUARD" --claim-exec "$ME" 2>&1)"
|
||
if printf '%s' "$CLAIM_OUT" | grep -q "✗"; then
|
||
echo "LOCK_BUSY :: $(printf '%s' "$CLAIM_OUT" | grep -m1 '占用者' | cut -c1-80)"
|
||
echo "→ 停手（R9：不得删锁 / 不得接管）。过一会儿再跑本脚本即可。"
|
||
exit 3
|
||
fi
|
||
echo "LOCK_HELD=yes"
|
||
# 无论成败都放锁（这是抢间隙策略的前提）
|
||
trap 'bash "$GUARD" --release-exec >/dev/null 2>&1; echo "LOCK_RELEASED=yes"' EXIT
|
||
|
||
# ── 2 落地 DDL（幂等）────────────────────────────────────────────────────────
|
||
echo "--- 2 落地 DDL ---"
|
||
ssh -o ConnectTimeout=8 -o BatchMode=yes "$HOST" 'bash -s' <<'REMOTE'
|
||
set -u
|
||
cat > /tmp/.pdl-d.sql <<'SQL'
|
||
CREATE SCHEMA IF NOT EXISTS dshs_int AUTHORIZATION postgres;
|
||
REVOKE ALL ON SCHEMA dshs_int FROM PUBLIC;
|
||
GRANT USAGE ON SCHEMA dshs_int TO dshs;
|
||
|
||
CREATE OR REPLACE FUNCTION dshs_int.create_plugin_database(p_name text)
|
||
RETURNS text
|
||
LANGUAGE plpgsql
|
||
SECURITY DEFINER
|
||
SET search_path = pg_catalog
|
||
AS $fn$
|
||
DECLARE
|
||
v_owner constant text := 'dshs';
|
||
BEGIN
|
||
IF p_name IS NULL OR p_name !~ '^dshs_pl_[a-z][a-z0-9_]{0,40}$' THEN
|
||
RAISE EXCEPTION 'invalid plugin database name: %', p_name USING ERRCODE = '22023';
|
||
END IF;
|
||
IF EXISTS (SELECT 1 FROM pg_database WHERE datname = p_name) THEN
|
||
RETURN 'exists';
|
||
END IF;
|
||
EXECUTE format('CREATE DATABASE %I OWNER %I ENCODING %L TEMPLATE template0',
|
||
p_name, v_owner, 'UTF8');
|
||
RAISE LOG 'dshs plugin db created: %', p_name;
|
||
RETURN 'created';
|
||
EXCEPTION
|
||
WHEN duplicate_database THEN
|
||
RETURN 'exists';
|
||
END;
|
||
$fn$;
|
||
|
||
REVOKE ALL ON FUNCTION dshs_int.create_plugin_database(text) FROM PUBLIC;
|
||
GRANT EXECUTE ON FUNCTION dshs_int.create_plugin_database(text) TO dshs;
|
||
SQL
|
||
su postgres -c 'cd /tmp && psql -h /var/run/postgresql -p 15432 -d dshs -X -v ON_ERROR_STOP=1 -q -f /tmp/.pdl-d.sql' 2>&1 | head -10
|
||
echo "DDL_RC=$?"
|
||
rm -f /tmp/.pdl-d.sql
|
||
REMOTE
|
||
[ $? -ne 0 ] && { echo "DDL_FAILED"; exit 2; }
|
||
|
||
# ── 3 验收 7 条 ──────────────────────────────────────────────────────────────
|
||
echo "--- 3 验收 ---"
|
||
ssh -o ConnectTimeout=8 -o BatchMode=yes "$HOST" 'bash -s' <<'REMOTE'
|
||
P() { su postgres -c "cd /tmp && psql -h /var/run/postgresql -p 15432 -d dshs -X -A -t -q -c \"$1\"" 2>&1 | head -2; }
|
||
U() { python3 - <<'PY'
|
||
import glob,re,os
|
||
url=""
|
||
for f in ["/etc/dshs.env"]+glob.glob("/etc/systemd/system/dshs.service.d/*.conf"):
|
||
try:
|
||
for line in open(f,encoding="utf-8",errors="replace"):
|
||
m=re.search(r'DSHS_DB_URL\s*=\s*"?([^"\s]+)"?',line)
|
||
if m: url=m.group(1)
|
||
except Exception: pass
|
||
import urllib.parse as up
|
||
print(up.urlparse(url).password or "")
|
||
PY
|
||
}
|
||
export PGPASSWORD="$(U)"
|
||
D() { psql -h 127.0.0.1 -p 15432 -U dshs -d dshs -X -A -t -q -c "$1" 2>&1 | head -2; }
|
||
|
||
echo "① create = $(D "select dshs_int.create_plugin_database('dshs_pl_selftest')")"
|
||
echo "② 幂等 = $(D "select dshs_int.create_plugin_database('dshs_pl_selftest')")"
|
||
echo "③ 库与属主 = $(P "select datname||'/'||pg_get_userbyid(datdba) from pg_database where datname='dshs_pl_selftest'")"
|
||
echo "④ 白名单(evil) = $(D "select dshs_int.create_plugin_database('evil')" | head -1)"
|
||
echo "④' 注入串 = $(D "select dshs_int.create_plugin_database('dshs_pl_x; DROP DATABASE dshs')" | head -1)"
|
||
echo "⑤ dshs 权限(须 f|f) = $(P "select rolsuper||'|'||rolcreatedb from pg_roles where rolname='dshs'")"
|
||
echo "⑥ 无旁路 = $(D 'create database pdl_should_fail' | head -1)"
|
||
echo "⑦ 可连新库+建schema = $(PGPASSWORD="$PGPASSWORD" psql -h 127.0.0.1 -p 15432 -U dshs -d dshs_pl_selftest -X -A -t -q -c 'create schema s_test' 2>&1 | head -1)"
|
||
echo "→ 清理 = $(P 'DROP DATABASE dshs_pl_selftest' >/dev/null 2>&1 && echo dropped)"
|
||
echo "→ 残留库数(须0) = $(P "select count(*) from pg_database where datname like 'dshs_pl_%'")"
|
||
REMOTE
|
||
echo "DONE"
|