2d43751729
2026-04-18 台北時區 —— ogt + Claude Opus 4.7 (1M) 本 commit 響應本 Session 兩次憑證外洩事故 (feedback_secrets_leak_incidents_2026-04-18.md), 交付統帥可直接部署的零信任基礎設施範本. 檔案清單: 1. scripts/host-ops/awoooi-hosts-add.sh - 110 主機 /etc/hosts 白名單 wrapper - 只允許預定義主機名,idempotent,帶 IP 格式驗證 - 安裝: /usr/local/bin/awoooi-hosts-add (root:root 0755) 2. scripts/host-ops/awoooi-wrapper.sudoers - 配套 sudoers 規則 (NOPASSWD for wrapper + SIGHUP only) - 安裝: /etc/sudoers.d/awoooi-wrapper (root:root 0440) - 禁 tee / bash / sh 這類 generic shell access 3. apps/api/migrations/adr090b_awoooi_migrator_role.sql - PG 限權角色 awoooi_migrator - 只能 DDL (CREATE/ALTER/DROP/INDEX/COMMENT) - 明確 REVOKE 所有 DML + default privileges 鎖死 - 本檔由統帥執行 (需 superuser),不由 Claude 執行 4. k8s/awoooi-prod/awoooi-migrator-secret.template.yaml - K8s Secret patch 範本 - 新增 MIGRATION_DATABASE_URL key (awoooi_migrator 連線串) - 與應用 DATABASE_URL 拆開 5. .gitea/workflows/run-migration.yml - CI 自動套用新 migration (單 transaction + ON_ERROR_STOP) - 用 Gitea secret MIGRATION_DATABASE_URL,不走明碼 - 每次成功寫一筆 asset_discovery_run (audit trail) 零信任三層防線 (對應 feedback_secrets_leak_incidents): L1 對話無密碼 -> wrapper 內建白名單 L2 操作經 wrapper -> sudoers + awoooi_migrator L3 顯示強制遮蔽 -> CI 走 secret,不走 env 本 Session 發現的 3 次憑證外洩全部在 feedback_secrets_leak memory 登記,並有對應 P0 輪替計畫. Co-Authored-By: Claude Opus 4.7 (1M context) <noreply@anthropic.com>
107 lines
3.5 KiB
YAML
107 lines
3.5 KiB
YAML
# ADR-090-B: Gitea CI 自動 migration workflow
|
|
# 建立時間: 2026-04-18 台北時區
|
|
# 建立者: ogt + Claude Opus 4.7 (1M)
|
|
#
|
|
# 目的: 每次 main 分支有新 migration SQL 檔,自動:
|
|
# 1. 用 MIGRATION_DATABASE_URL (awoooi_migrator 限權帳號) 連 PG
|
|
# 2. 只跑「新增」的 migration (比對已執行列表)
|
|
# 3. 跑後寫 asset_discovery_run + automation_operation_log 記錄
|
|
# 4. 失敗自動 rollback (single transaction + ON_ERROR_STOP)
|
|
#
|
|
# 觸發: push to main,且 apps/api/migrations/ 有變更
|
|
|
|
name: run-migration
|
|
|
|
on:
|
|
push:
|
|
branches: [main]
|
|
paths:
|
|
- 'apps/api/migrations/*.sql'
|
|
|
|
jobs:
|
|
migrate:
|
|
runs-on: ubuntu-latest # 或 self-hosted runner on 110
|
|
container:
|
|
image: postgres:15-alpine # 帶 psql
|
|
|
|
steps:
|
|
- name: Checkout
|
|
uses: actions/checkout@v4
|
|
with:
|
|
fetch-depth: 2 # 需比對上一個 commit
|
|
|
|
- name: Identify new migrations
|
|
id: diff
|
|
run: |
|
|
NEW_FILES=$(git diff --name-only --diff-filter=A HEAD~1 HEAD -- 'apps/api/migrations/*.sql' || true)
|
|
echo "new_files<<EOF" >> $GITHUB_OUTPUT
|
|
echo "$NEW_FILES" >> $GITHUB_OUTPUT
|
|
echo "EOF" >> $GITHUB_OUTPUT
|
|
echo "=== New migration files ==="
|
|
echo "$NEW_FILES"
|
|
|
|
- name: Apply new migrations
|
|
if: steps.diff.outputs.new_files != ''
|
|
env:
|
|
# 從 Gitea secrets 取,不直接明碼
|
|
PGURL: ${{ secrets.MIGRATION_DATABASE_URL }}
|
|
run: |
|
|
set -euo pipefail
|
|
if [ -z "$PGURL" ]; then
|
|
echo "::error::MIGRATION_DATABASE_URL secret not set in Gitea"
|
|
exit 1
|
|
fi
|
|
|
|
# 套用每個新檔 (single transaction per file)
|
|
echo "${{ steps.diff.outputs.new_files }}" | while IFS= read -r file; do
|
|
[ -z "$file" ] && continue
|
|
echo "=== Applying: $file ==="
|
|
psql "$PGURL" \
|
|
-v ON_ERROR_STOP=1 \
|
|
--single-transaction \
|
|
-f "$file"
|
|
echo "=== OK: $file ==="
|
|
done
|
|
|
|
- name: Seed asset_discovery_run (audit)
|
|
if: steps.diff.outputs.new_files != ''
|
|
env:
|
|
PGURL: ${{ secrets.MIGRATION_DATABASE_URL }}
|
|
run: |
|
|
FILES_JSON=$(echo "${{ steps.diff.outputs.new_files }}" | jq -Rn '[inputs | select(length > 0)]')
|
|
psql "$PGURL" -c "
|
|
INSERT INTO asset_discovery_run (
|
|
run_id, triggered_by, scope, scan_depth, status,
|
|
started_at, ended_at, tools_used, summary
|
|
) VALUES (
|
|
gen_random_uuid(),
|
|
'ci:gitea',
|
|
ARRAY['schema_migration'],
|
|
'full',
|
|
'success',
|
|
NOW(),
|
|
NOW(),
|
|
'{\"psql\": 1, \"gitea_ci\": 1}'::jsonb,
|
|
jsonb_build_object(
|
|
'type', 'ci_migration',
|
|
'commit_sha', '${{ github.sha }}',
|
|
'files', $FILES_JSON
|
|
)
|
|
);
|
|
"
|
|
|
|
- name: Notify Telegram (if configured)
|
|
if: always()
|
|
env:
|
|
TG_TOKEN: ${{ secrets.TELEGRAM_BOT_TOKEN }}
|
|
TG_CHAT: ${{ secrets.TELEGRAM_OPS_CHAT_ID }}
|
|
run: |
|
|
if [ -n "$TG_TOKEN" ] && [ -n "$TG_CHAT" ]; then
|
|
STATUS="${{ job.status }}"
|
|
MSG="🗄️ Migration CI: \`${STATUS}\` — commit ${{ github.sha }}"
|
|
curl -s -X POST "https://api.telegram.org/bot${TG_TOKEN}/sendMessage" \
|
|
-d chat_id="${TG_CHAT}" \
|
|
-d parse_mode="Markdown" \
|
|
-d text="${MSG}" || true
|
|
fi
|