Playbook：AWS Aurora 公网入口收紧的渐进路径——从 0.0.0.0/0 到零信任

#!/bin/bash
# audit-rds-sg.sh - 枚举指定 region 所有 RDS/Aurora 及其 SG 入站规则
# 用法：./audit-rds-sg.sh <region>
# 示例：./audit-rds-sg.sh us-west-2

set -euo pipefail

REGION="${1:-us-west-2}"
OUT_DIR="./audit-${REGION}-$(date +%Y%m%d)"
mkdir -p "$OUT_DIR"

command -v aws >/dev/null || { echo "需要 aws cli"; exit 1; }
command -v jq  >/dev/null || { echo "需要 jq"; exit 1; }

echo "[1/3] 列出所有 RDS DB instance ..."
aws rds describe-db-instances --region "$REGION" \
  --query 'DBInstances[].{
    id:DBInstanceIdentifier,
    engine:Engine,
    publicly:PubliclyAccessible,
    endpoint:Endpoint.Address,
    sgs:VpcSecurityGroups[].VpcSecurityGroupId
  }' --output json > "$OUT_DIR/rds-instances.json"

echo "[2/3] 列出所有 Aurora cluster ..."
aws rds describe-db-clusters --region "$REGION" \
  --query 'DBClusters[].{
    id:DBClusterIdentifier,
    engine:Engine,
    endpoint:Endpoint,
    reader:ReaderEndpoint,
    sgs:VpcSecurityGroups[].VpcSecurityGroupId
  }' --output json > "$OUT_DIR/aurora-clusters.json"

echo "[3/3] 抽取所有 SG 并 dump 入站规则 ..."
jq -r '.[].sgs[]' "$OUT_DIR/rds-instances.json" "$OUT_DIR/aurora-clusters.json" \
  | sort -u > "$OUT_DIR/sg-list.txt"

while read -r SG; do
  [[ -z "$SG" ]] && continue
  aws ec2 describe-security-groups --group-ids "$SG" --region "$REGION" \
    --query 'SecurityGroups[0].IpPermissions' --output json \
    > "$OUT_DIR/${SG}.json"
done < "$OUT_DIR/sg-list.txt"

echo
echo "==== 含 0.0.0.0/0 的 SG ===="
for f in "$OUT_DIR"/sg-*.json; do
  SG=$(basename "$f" .json)
  if jq -e '.[] | select(.IpRanges[]?.CidrIp=="0.0.0.0/0")' "$f" >/dev/null 2>&1; then
    echo "  [!] $SG"
  fi
done

echo
echo "完成。结果在 $OUT_DIR/"

[1/3] 列出所有 RDS DB instance ...
[2/3] 列出所有 Aurora cluster ...
[3/3] 抽取所有 SG 并 dump 入站规则 ...

==== 含 0.0.0.0/0 的 SG ====
  [!] sg-xxxxxxxxxxxxx
  [!] sg-yyyyyyyyyyyyy

完成。结果在 ./audit-us-west-2-20260430/

aws ec2 create-flow-logs \
  --resource-type VPC --resource-ids vpc-xxxxxxxxxxxxx \
  --traffic-type ACCEPT --log-destination-type s3 \
  --log-destination arn:aws:s3:::my-flow-logs-bucket/prod-vpc/ \
  --max-aggregation-interval 60 \
  --region us-west-2

-- 假设 Aurora 私有 IP 是 10.3.32.150，prod VPC CIDR 是 10.3.0.0/18
-- 找 7 天内来自 VPC 外的访问
SELECT
  srcaddr,
  COUNT(*) AS pkts,
  SUM(bytes) AS total_bytes,
  MIN(from_unixtime(start)) AS first_seen,
  MAX(from_unixtime("end"))  AS last_seen
FROM vpc_flow_logs
WHERE date >= date_format(date_add('day', -7, current_date), '%Y/%m/%d')
  AND dstaddr = '10.3.32.150'
  AND dstport IN (3306, 5432)
  AND action  = 'ACCEPT'
  AND NOT regexp_like(srcaddr, '^10\.3\.|^10\.2\.|^10\.52\.')
GROUP BY srcaddr
ORDER BY pkts DESC
LIMIT 50;

srcaddr         pkts    total_bytes  first_seen           last_seen
139.x.x.x       18432   2841029      2026-04-23 02:11     2026-04-29 23:48
47.x.x.x        9214    1102881      2026-04-23 09:01     2026-04-29 22:30
44.238.x.x      512      65300       2026-04-28 14:22     2026-04-29 18:11

aws ec2 delete-flow-logs --flow-log-ids fl-xxxxxxxxxxxxx --region us-west-2

#!/bin/bash
# sg-history.sh - 找 SG 上每条 ingress 规则的添加时间和操作人
# 用法：./sg-history.sh <sg-id> <region>

set -euo pipefail
SG="${1:?用法: $0 <sg-id> <region>}"
REGION="${2:-us-west-2}"

aws cloudtrail lookup-events \
  --region "$REGION" \
  --lookup-attributes AttributeKey=ResourceName,AttributeValue="$SG" \
  --max-results 50 \
  --query 'Events[?EventName==`AuthorizeSecurityGroupIngress` || EventName==`RevokeSecurityGroupIngress`].{
    Time:EventTime,
    User:Username,
    Event:EventName,
    Source:SourceIPAddress
  }' --output table

-----------------------------------------------------------------------------
|                              LookupEvents                                 |
+---------------------+-----------+--------------------------+--------------+
|  Time               |  User     |  Event                   |  Source      |
+---------------------+-----------+--------------------------+--------------+
|  2024-09-12T03:11Z  |  alice    |  AuthorizeSecurityGroupI |  AWS CLI     |
|  2025-01-08T08:42Z  |  former-x |  AuthorizeSecurityGroupI |  Console     |
+---------------------+-----------+--------------------------+--------------+

#!/bin/bash
# find-aurora-deps.sh - 在导出的 Nacos / K8s Secret 中找连 Aurora 的服务
# 前置：先把目标命名空间所有配置导出到本地（Nacos OpenAPI 或 nacos-cli）
# 用法：./find-aurora-deps.sh <配置目录> <数据库 hostname 关键字>

set -euo pipefail
DIR="${1:?用法: $0 <dir> <keyword>}"
KEY="${2:-rds.amazonaws.com}"

[[ -d "$DIR" ]] || { echo "目录不存在: $DIR"; exit 1; }

echo "==== 配置文件命中清单 ===="
grep -rEl "$KEY" "$DIR" 2>/dev/null \
  | sort -u \
  | tee /tmp/aurora-deps.txt

echo
echo "==== 命中行（脱敏前）===="
grep -rE "$KEY" "$DIR" 2>/dev/null \
  | sed 's/password=[^&[:space:]]*/password=***/g'

echo
echo "==== 涉及的 dataId 数量：$(wc -l < /tmp/aurora-deps.txt) ===="

#!/bin/bash
# scan-k8s-aurora-secrets.sh
# 用法：./scan-k8s-aurora-secrets.sh <kubeconfig context>

set -euo pipefail
CTX="${1:?用法: $0 <context>}"
KEY="rds.amazonaws.com"

command -v kubectl >/dev/null || { echo "需要 kubectl"; exit 1; }
command -v jq      >/dev/null || { echo "需要 jq"; exit 1; }

echo "Scanning context=$CTX, keyword=$KEY ..."
kubectl --context "$CTX" get secret -A -o json \
  | jq -r --arg key "$KEY" '
      .items[]
      | . as $s
      | (.data // {}) | to_entries[]
      | .value as $v
      | ($v | @base64d) as $decoded
      | select($decoded | test($key))
      | "\($s.metadata.namespace)/\($s.metadata.name)\t\(.key)"
    ' 2>/dev/null | sort -u

ns-foo/db-secret           connection-string
ns-bar/app-config          DATABASE_URL
ns-baz/legacy-secret       MYSQL_HOST

#!/bin/bash
# create-peering.sh - 创建跨 Region VPC Peering 并等待 active
set -euo pipefail

REQ_VPC="vpc-xxxxxxxxxxxxx"     # us-west-2 prod
REQ_REGION="us-west-2"
ACC_VPC="vpc-yyyyyyyyyyyyy"     # ap-southeast-1 pre
ACC_REGION="ap-southeast-1"
ACC_ACCOUNT="<ACCOUNT_ID>"

echo "[1/4] 创建 Peering 请求 ..."
PCX=$(aws ec2 create-vpc-peering-connection \
  --vpc-id "$REQ_VPC" \
  --peer-vpc-id "$ACC_VPC" \
  --peer-region "$ACC_REGION" \
  --peer-owner-id "$ACC_ACCOUNT" \
  --region "$REQ_REGION" \
  --query 'VpcPeeringConnection.VpcPeeringConnectionId' --output text)
echo "  PCX=$PCX"

echo "[2/4] 等待对端进入 pending-acceptance ..."
for i in {1..30}; do
  STATUS=$(aws ec2 describe-vpc-peering-connections \
    --vpc-peering-connection-ids "$PCX" \
    --region "$ACC_REGION" \
    --query 'VpcPeeringConnections[0].Status.Code' --output text 2>/dev/null || echo "")
  [[ "$STATUS" == "pending-acceptance" ]] && break
  sleep 2
done
echo "  status=$STATUS"

echo "[3/4] 接受 Peering ..."
aws ec2 accept-vpc-peering-connection \
  --vpc-peering-connection-id "$PCX" \
  --region "$ACC_REGION" >/dev/null

echo "[4/4] 等待 active ..."
until [[ "$(aws ec2 describe-vpc-peering-connections \
  --vpc-peering-connection-ids "$PCX" --region "$REQ_REGION" \
  --query 'VpcPeeringConnections[0].Status.Code' --output text)" == "active" ]]; do
  sleep 2
done
echo "  Peering active: $PCX"

echo
echo "现在两侧路由表加路由（手动确认 RTB ID）："
echo "  aws ec2 create-route --route-table-id <RTB-A> --destination-cidr-block 10.x.0.0/16 \\"
echo "    --vpc-peering-connection-id $PCX --region $REQ_REGION"
echo "  aws ec2 create-route --route-table-id <RTB-B> --destination-cidr-block 10.3.0.0/18 \\"
echo "    --vpc-peering-connection-id $PCX --region $ACC_REGION"

aws ec2 delete-vpc-peering-connection \
  --vpc-peering-connection-id <PCX> \
  --region us-west-2

# 进 prod VPC 的 SSM 跳板（见第 3 步）执行
dig +short prod-aurora.example.aws.com
# 期望输出：10.3.32.150

#!/bin/bash
# setup-phz.sh - 在 ap-southeast-1 创建 PHZ 并指向 us-west-2 Aurora 私有 IP
set -euo pipefail

ZONE_NAME="rds.internal"
PEER_VPC="vpc-yyyyyyyyyyyyy"
PEER_REGION="ap-southeast-1"
RECORD_NAME="aurora-prod.rds.internal"
AURORA_PRIVATE_IP="10.3.32.150"

echo "[1/3] 创建 PHZ ..."
HZ_ID=$(aws route53 create-hosted-zone \
  --name "$ZONE_NAME" \
  --vpc "VPCRegion=$PEER_REGION,VPCId=$PEER_VPC" \
  --hosted-zone-config "Comment=cross-region aurora,PrivateZone=true" \
  --caller-reference "$(date +%s)" \
  --query 'HostedZone.Id' --output text | sed 's|/hostedzone/||')
echo "  HZ_ID=$HZ_ID"

echo "[2/3] 加 A 记录 ..."
cat > /tmp/rrset.json <<EOF
{
  "Changes": [{
    "Action": "CREATE",
    "ResourceRecordSet": {
      "Name": "$RECORD_NAME",
      "Type": "A",
      "TTL": 60,
      "ResourceRecords": [{"Value": "$AURORA_PRIVATE_IP"}]
    }
  }]
}
EOF
aws route53 change-resource-record-sets \
  --hosted-zone-id "$HZ_ID" \
  --change-batch file:///tmp/rrset.json >/dev/null

echo "[3/3] 关联其他 VPC（如 staging/qa）..."
echo "  aws route53 associate-vpc-with-hosted-zone --hosted-zone-id $HZ_ID \\"
echo "    --vpc VPCRegion=ap-southeast-1,VPCId=<other-vpc-id>"

# 在 ap-southeast-1 VPC 的 EC2 上
dig +short aurora-prod.rds.internal
# 期望：10.3.32.150

aws route53 change-resource-record-sets --hosted-zone-id "$HZ_ID" \
  --change-batch '{"Changes":[{"Action":"DELETE","ResourceRecordSet":{...}}]}'
aws route53 delete-hosted-zone --id "$HZ_ID"

aws ec2 authorize-security-group-ingress \
  --group-id sg-xxxxxxxxxxxxx \
  --protocol tcp --port 3306 \
  --cidr 10.x.0.0/16 \
  --region us-west-2 \
  --tag-specifications 'ResourceType=security-group-rule,Tags=[{Key=owner,Value=infra},{Key=ticket,Value=SEC-2026-04-30}]'

#!/usr/bin/env python3
# nacos-host-rewrite.py - 批量替换 Nacos 配置中的 Aurora hostname
# 前置：pip install nacos-sdk-python>=2.0
# 用法：
#   python3 nacos-host-rewrite.py --dry-run
#   python3 nacos-host-rewrite.py --apply
#   python3 nacos-host-rewrite.py --rollback ./backup-20260430.json

import argparse, json, os, re, sys, time
from pathlib import Path
import nacos

NACOS_SERVER  = os.environ["NACOS_SERVER"]   # mse-xxx.nacos-ans.mse.aliyuncs.com:8848
NACOS_NS      = os.environ["NACOS_NS"]       # us-prod namespace id
NACOS_USER    = os.environ["NACOS_USER"]
NACOS_PASS    = os.environ["NACOS_PASS"]

OLD_HOST = "prod-aurora.example.aws.com"
NEW_HOST = "aurora-prod.rds.internal"

def list_all_configs(client):
    items, page = [], 1
    while True:
        resp = client.get_configs(page_no=page, page_size=200, no_snapshot=True)
        items.extend(resp.get("pageItems", []))
        if page * 200 >= resp.get("totalCount", 0):
            break
        page += 1
    return items

def main():
    p = argparse.ArgumentParser()
    g = p.add_mutually_exclusive_group(required=True)
    g.add_argument("--dry-run", action="store_true")
    g.add_argument("--apply",   action="store_true")
    g.add_argument("--rollback", metavar="BACKUP.json")
    args = p.parse_args()

    client = nacos.NacosClient(
        NACOS_SERVER, namespace=NACOS_NS,
        username=NACOS_USER, password=NACOS_PASS,
    )

    if args.rollback:
        backup = json.loads(Path(args.rollback).read_text())
        for it in backup:
            print(f"[rollback] {it['group']}/{it['dataId']}")
            client.publish_config(it["dataId"], it["group"], it["content"])
        return

    matched = []
    for it in list_all_configs(client):
        content = client.get_config(it["dataId"], it["group"], no_snapshot=True) or ""
        if OLD_HOST in content:
            matched.append({**it, "content": content})

    print(f"匹配到 {len(matched)} 条配置含 {OLD_HOST}")
    for m in matched:
        print(f"  - {m['group']}/{m['dataId']}")

    if args.dry_run:
        return

    backup_path = f"./backup-{time.strftime('%Y%m%d-%H%M%S')}.json"
    Path(backup_path).write_text(json.dumps(matched, ensure_ascii=False, indent=2))
    print(f"\n备份已写入 {backup_path}")
    confirm = input("\n确认替换？输入 YES 回车继续：")
    if confirm != "YES":
        sys.exit(1)

    for m in matched:
        new_content = m["content"].replace(OLD_HOST, NEW_HOST)
        ok = client.publish_config(m["dataId"], m["group"], new_content)
        print(f"  [{'OK' if ok else 'FAIL'}] {m['group']}/{m['dataId']}")

if __name__ == "__main__":
    main()

# 1. 先 dry-run
python3 nacos-host-rewrite.py --dry-run
# 输出：匹配到 4 条配置含 prod-aurora.example.aws.com

# 2. 实际替换（会写备份并提示输入 YES）
python3 nacos-host-rewrite.py --apply

# 3. rollout 业务 Pod，看日志
kubectl --context prod rollout restart deploy/service-foo -n ap-pre
kubectl --context prod logs -f deploy/service-foo -n ap-pre | grep -i "mysql connected\|host="
# 期望看到：MySQL connected to host: aurora-prod.rds.internal

# 4. 出问题立刻回滚
python3 nacos-host-rewrite.py --rollback ./backup-20260430-153022.json

# 在跨 Region 服务的 Pod 内
kubectl --context prod -n ap-pre exec -it deploy/service-foo -- sh -c \
  'getent hosts aurora-prod.rds.internal && nc -vz aurora-prod.rds.internal 3306'
# 期望：10.3.32.150 + Connection succeeded

{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Sid": "SSMCoreManaged",
      "Effect": "Allow",
      "Action": [
        "ssm:UpdateInstanceInformation",
        "ssmmessages:CreateControlChannel",
        "ssmmessages:CreateDataChannel",
        "ssmmessages:OpenControlChannel",
        "ssmmessages:OpenDataChannel",
        "ec2messages:GetMessages",
        "ec2messages:AcknowledgeMessage",
        "ec2messages:SendReply"
      ],
      "Resource": "*"
    }
  ]
}

aws ssm describe-instance-information --region us-west-2 \
  --filters "Key=InstanceIds,Values=i-xxxxxxxxxxxxx" \
  --query 'InstanceInformationList[0].PingStatus' --output text
# 期望：Online

#!/bin/bash
# db-tunnel.sh - 通过 SSM Port Forwarding 建立 Aurora 隧道
# 用法：./db-tunnel.sh [prod|staging|qa] [local_port]
# 示例：./db-tunnel.sh prod 13306
# 前置：
#   - aws cli v2.x 已配置 profile（默认 profile 或 AWS_PROFILE 环境变量）
#   - Session Manager Plugin 已装：https://docs.aws.amazon.com/systems-manager/latest/userguide/session-manager-working-with-install-plugin.html

set -euo pipefail

ENV="${1:-prod}"
LOCAL_PORT="${2:-13306}"
VERBOSE="${VERBOSE:-0}"

log()  { echo "[$(date +%H:%M:%S)] $*"; }
fail() { echo "ERROR: $*" >&2; exit 1; }

# 依赖检查
command -v aws       >/dev/null || fail "aws cli 未安装"
session-manager-plugin --version >/dev/null 2>&1 \
  || fail "Session Manager Plugin 未安装：https://docs.aws.amazon.com/systems-manager/latest/userguide/session-manager-working-with-install-plugin.html"

# 端口占用检查
if command -v lsof >/dev/null && lsof -iTCP:"$LOCAL_PORT" -sTCP:LISTEN >/dev/null 2>&1; then
  fail "本地端口 $LOCAL_PORT 已被占用，请换端口或先 kill 占用进程"
fi

# 环境配置
case "$ENV" in
  prod)
    TARGET="i-xxxxxxxxxxxxx"
    REGION="us-west-2"
    DB_HOST="aurora-prod.rds.internal"
    DB_PORT="3306"
    ;;
  staging)
    TARGET="i-xxxxxxxxxxxxx"
    REGION="us-west-2"
    DB_HOST="aurora-staging.rds.internal"
    DB_PORT="3306"
    ;;
  qa)
    TARGET="i-yyyyyyyyyyyyy"
    REGION="us-west-2"
    DB_HOST="aurora-qa.rds.internal"
    DB_PORT="3306"
    ;;
  *)
    fail "未知环境 $ENV，支持 prod | staging | qa"
    ;;
esac

# 鉴权检查
CALLER=$(aws sts get-caller-identity --query Arn --output text 2>&1) \
  || fail "AWS 凭据无效：$CALLER"
log "Caller: $CALLER"

# 连通性预检：SSM Agent 是否 online
PING=$(aws ssm describe-instance-information --region "$REGION" \
        --filters "Key=InstanceIds,Values=$TARGET" \
        --query 'InstanceInformationList[0].PingStatus' \
        --output text 2>/dev/null || echo "")
[[ "$PING" == "Online" ]] || fail "跳板 $TARGET 不在线（PingStatus=$PING）"

cat <<EOF

=========================================
  DB Tunnel: $ENV → 127.0.0.1:$LOCAL_PORT
  via SSM($TARGET) → $DB_HOST:$DB_PORT
=========================================

新开终端连接：
  mysql -h 127.0.0.1 -P $LOCAL_PORT -u <user> -p
  或 DBeaver/TablePlus 连 127.0.0.1:$LOCAL_PORT

按 Ctrl+C 断开隧道。

EOF

[[ "$VERBOSE" == "1" ]] && set -x

aws ssm start-session \
  --region "$REGION" \
  --target "$TARGET" \
  --document-name AWS-StartPortForwardingSessionToRemoteHost \
  --parameters "{\"host\":[\"$DB_HOST\"],\"portNumber\":[\"$DB_PORT\"],\"localPortNumber\":[\"$LOCAL_PORT\"]}"

[15:02:11] Caller: arn:aws:iam::<ACCOUNT_ID>:user/alice

=========================================
  DB Tunnel: prod → 127.0.0.1:13306
  via SSM(i-xxxxxxxxxxxxx) → aurora-prod.rds.internal:3306
=========================================

Starting session with SessionId: alice-0a1b2c3d4e
Port 13306 opened for sessionId alice-0a1b2c3d4e.
Waiting for connections...

{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Sid": "AllowStartPortForwardingDocument",
      "Effect": "Allow",
      "Action": "ssm:StartSession",
      "Resource": [
        "arn:aws:ssm:us-west-2::document/AWS-StartPortForwardingSessionToRemoteHost"
      ]
    },
    {
      "Sid": "AllowStartSessionToBastionsOnly",
      "Effect": "Allow",
      "Action": "ssm:StartSession",
      "Resource": [
        "arn:aws:ec2:us-west-2:<ACCOUNT_ID>:instance/i-xxxxxxxxxxxxx",
        "arn:aws:ec2:us-west-2:<ACCOUNT_ID>:instance/i-yyyyyyyyyyyyy"
      ]
    },
    {
      "Sid": "AllowSelfTerminate",
      "Effect": "Allow",
      "Action": [
        "ssm:TerminateSession",
        "ssm:ResumeSession"
      ],
      "Resource": "arn:aws:ssm:*:<ACCOUNT_ID>:session/${aws:username}-*"
    },
    {
      "Sid": "AllowDescribeInstanceInfo",
      "Effect": "Allow",
      "Action": "ssm:DescribeInstanceInformation",
      "Resource": "*"
    },
    {
      "Sid": "DenyEverythingElseOnSSM",
      "Effect": "Deny",
      "Action": [
        "ssm:SendCommand",
        "ssm:StartAutomationExecution",
        "ssm:GetParameter*",
        "ssm:PutParameter"
      ],
      "Resource": "*"
    }
  ]
}

# 一次性建组（已建过则跳过）
aws iam create-group --group-name db-access
aws iam create-policy \
  --policy-name db-tunnel-ssm-access \
  --policy-document file://db-tunnel-policy.json
aws iam attach-group-policy \
  --group-name db-access \
  --policy-arn arn:aws:iam::<ACCOUNT_ID>:policy/db-tunnel-ssm-access

# 给某个用户开通
aws iam add-user-to-group --group-name db-access --user-name alice

# 离职回收
aws iam remove-user-from-group --group-name db-access --user-name former-alice

AWS_PROFILE=alice ./db-tunnel.sh prod 13306 &
mysql -h 127.0.0.1 -P 13306 -u readonly_user -p -e "SELECT VERSION();"
# 期望：返回 Aurora MySQL 版本号

# 上传到私有桶
aws s3 cp db-tunnel.sh s3://my-internal-tools/db-tunnel.sh \
  --acl private --sse AES256

# 给开发者发预签名 URL（24 小时有效）
aws s3 presign s3://my-internal-tools/db-tunnel.sh --expires-in 86400
# 输出 https URL，发给本人

# 开发者本机
curl -fsSL "<presigned-url>" -o ~/db-tunnel.sh && chmod +x ~/db-tunnel.sh

SG="sg-xxxxxxxxxxxxx"
REGION="us-west-2"
TS=$(date +%Y%m%d-%H%M%S)

aws ec2 describe-security-groups --group-ids "$SG" --region "$REGION" \
  --query 'SecurityGroups[0].IpPermissions' \
  --output json > "sg-backup-${SG}-${TS}.json"

echo "备份已写入 sg-backup-${SG}-${TS}.json，行数：$(jq '. | length' sg-backup-${SG}-${TS}.json)"

#!/bin/bash
# sg-add-vpc-cidrs.sh - 加白 VPC 内网 CIDR
set -euo pipefail
SG="sg-xxxxxxxxxxxxx"
REGION="us-west-2"
TICKET="SEC-2026-04-30"

CIDRS=(
  "10.3.0.0/18    prod-vpc"
  "10.2.0.0/18    staging-vpc"
  "10.x.0.0/16   ap-pre-vpc-peering"
)

for line in "${CIDRS[@]}"; do
  cidr=$(echo "$line" | awk '{print $1}')
  desc=$(echo "$line" | awk '{print $2}')
  echo "+ $cidr ($desc)"
  aws ec2 authorize-security-group-ingress \
    --group-id "$SG" --region "$REGION" \
    --ip-permissions "IpProtocol=tcp,FromPort=3306,ToPort=3306,IpRanges=[{CidrIp=$cidr,Description=\"$desc-$TICKET\"}]"
done

#!/bin/bash
# verify-aurora-reach.sh - 多源验证连通性
set -e

# 1. 同 VPC EC2
aws ssm send-command \
  --document-name AWS-RunShellScript \
  --targets "Key=tag:role,Values=k8s-node" \
  --comment "verify aurora reach from prod VPC" \
  --parameters 'commands=["timeout 5 nc -vz aurora-prod.rds.internal 3306"]' \
  --region us-west-2

# 2. Peering 对端
kubectl --context ap-pre -n default run -i --rm verify-db --image=alpine:3.20 --restart=Never \
  -- sh -c 'apk add --no-cache mariadb-client >/dev/null && \
            mysql -h aurora-prod.rds.internal -u readonly_user -p<readonly_pwd> -e "SELECT 1"'

# 3. SSM 隧道
./db-tunnel.sh prod 13306 &
PID=$!
sleep 3
mysql -h 127.0.0.1 -P 13306 -u readonly_user -p<readonly_pwd> -e "SELECT 1"
kill $PID

SG="sg-xxxxxxxxxxxxx"
REGION="us-west-2"

# 删全协议 0.0.0.0/0
aws ec2 revoke-security-group-ingress \
  --group-id "$SG" --region "$REGION" \
  --ip-permissions 'IpProtocol=-1,IpRanges=[{CidrIp=0.0.0.0/0}]'

# 立刻验证
aws ec2 describe-security-groups --group-ids "$SG" --region "$REGION" \
  --query 'SecurityGroups[0].IpPermissions[?IpRanges[?CidrIp==`0.0.0.0/0`]]' \
  --output json
# 期望：[]

# CloudWatch 看 Aurora 连接数曲线
aws cloudwatch get-metric-statistics --region us-west-2 \
  --namespace AWS/RDS --metric-name DatabaseConnections \
  --dimensions Name=DBClusterIdentifier,Value=prod-aurora-cluster \
  --start-time "$(date -u -d '10 minutes ago' +%Y-%m-%dT%H:%M:%S)" \
  --end-time "$(date -u +%Y-%m-%dT%H:%M:%S)" \
  --period 60 --statistics Average

# 看应用日志有无 connection refused / timeout 突增（loki 或 cloudwatch logs insights）

#!/bin/bash
# sg-rollback.sh - 从备份还原 SG 全部入站规则
# 用法：./sg-rollback.sh <sg-id> <region> <backup.json>
set -euo pipefail

SG="${1:?用法: $0 <sg-id> <region> <backup.json>}"
REGION="${2:?}"
BACKUP="${3:?}"

[[ -f "$BACKUP" ]] || { echo "备份文件不存在：$BACKUP"; exit 1; }

echo "[1/3] 删除当前所有入站规则 ..."
CURRENT=$(aws ec2 describe-security-groups --group-ids "$SG" --region "$REGION" \
  --query 'SecurityGroups[0].IpPermissions' --output json)
if [[ "$CURRENT" != "[]" ]]; then
  aws ec2 revoke-security-group-ingress --group-id "$SG" --region "$REGION" \
    --ip-permissions "$CURRENT"
fi

echo "[2/3] 还原备份规则 ..."
aws ec2 authorize-security-group-ingress --group-id "$SG" --region "$REGION" \
  --ip-permissions "$(cat "$BACKUP")"

echo "[3/3] 校验 ..."
aws ec2 describe-security-groups --group-ids "$SG" --region "$REGION" \
  --query 'SecurityGroups[0].IpPermissions' --output json | jq '. | length'

#!/bin/bash
# audit-leftover-rules.sh - 列出 SG 上所有 /32 规则并 whois
set -euo pipefail
SG="${1:?用法: $0 <sg-id> [region]}"
REGION="${2:-us-west-2}"

aws ec2 describe-security-group-rules --region "$REGION" \
  --filters "Name=group-id,Values=$SG" \
  --query 'SecurityGroupRules[?IsEgress==`false` && CidrIpv4!=`null`].[CidrIpv4, FromPort, ToPort, Description, SecurityGroupRuleId]' \
  --output json | jq -c '.[]' | while read -r row; do
    cidr=$(echo "$row" | jq -r '.[0]')
    [[ "$cidr" == *"/32" ]] || continue
    ip=${cidr%/32}
    asn=$(whois "$ip" 2>/dev/null | grep -E '^(OrgName|netname|owner|Organization)' | head -1)
    echo "$row    => $asn"
done

["139.x.x.x/32",3306,3306,"",sgr-aaa]    => OrgName: Linode
["47.x.x.x/32",3306,3306,"former-dev",sgr-bbb]    => OrgName: Aliyun (US) LLC

1. WHOIS / IP 反查 ASN，初步判断归属
2. 在团队 IM 里贴出来询问 24-48 小时
3. 无人认领则 description 改为 "pending-delete-YYYY-MM-DD"，先保留 1 周
4. 一周后用 Flow Logs 确认无该 IP 流量后删除

aws ec2 modify-security-group-rules --region us-west-2 --group-id sg-xxxxxxxxxxxxx \
  --security-group-rules 'SecurityGroupRuleId=sgr-aaa,SecurityGroupRule={IpProtocol=tcp,FromPort=3306,ToPort=3306,CidrIpv4=139.x.x.x/32,Description="pending-delete-2026-05-07-no-owner"}'

aws ec2 revoke-security-group-ingress --region us-west-2 \
  --group-id sg-xxxxxxxxxxxxx \
  --security-group-rule-ids sgr-aaa

#!/bin/bash
# scan-all-rds-public.sh - 扫描所有 region 所有 RDS 实例的公网 SG
set -euo pipefail
REGION="${1:-us-west-2}"

aws rds describe-db-instances --region "$REGION" \
  --query 'DBInstances[].[DBInstanceIdentifier, Engine, PubliclyAccessible, VpcSecurityGroups[].VpcSecurityGroupId]' \
  --output json \
  | jq -r '.[] | @tsv' \
  | while IFS=$'\t' read -r id engine public sgs; do
      for sg in $(echo "$sgs" | tr -d '[],"'); do
        open=$(aws ec2 describe-security-groups --group-ids "$sg" --region "$REGION" \
          --query 'SecurityGroups[0].IpPermissions[?IpRanges[?CidrIp==`0.0.0.0/0`]]' \
          --output json 2>/dev/null || echo "[]")
        if [[ "$open" != "[]" ]]; then
          echo "[!] $id ($engine) PubliclyAccessible=$public, SG $sg has 0.0.0.0/0"
        fi
      done
    done

dial tcp: lookup prod-aurora.example.aws.com on 10.52.0.2:53: no such host
... (DNS 仍然能解析的话变成)
dial tcp 52.x.x.x:3306: i/o timeout

#!/bin/bash
# emergency-restore.sh
set -e
SG="sg-xxxxxxxxxxxxx"
REGION="us-west-2"
echo "重新加 0.0.0.0/0 ..."
aws ec2 authorize-security-group-ingress --group-id "$SG" --region "$REGION" \
  --ip-permissions 'IpProtocol=-1,IpRanges=[{CidrIp=0.0.0.0/0,Description="EMERGENCY-RESTORE-DELETE-ASAP"}]'
echo "✓ 公网已恢复，立刻按 Playbook 第 2 步重新走流程"

#!/bin/bash
# expire-orphan-rules.sh - 把无主规则改名为 pending-delete 并设到期日期
set -euo pipefail
SG="$1"
RULE_ID="$2"
DAYS="${3:-7}"
REGION="${4:-us-west-2}"
EXPIRE=$(date -d "+$DAYS days" +%Y-%m-%d)

# 先取出当前规则
RULE=$(aws ec2 describe-security-group-rules --region "$REGION" \
  --filters "Name=group-id,Values=$SG" \
  --query "SecurityGroupRules[?SecurityGroupRuleId=='$RULE_ID']" --output json)

CIDR=$(echo "$RULE" | jq -r '.[0].CidrIpv4')
PORT=$(echo "$RULE" | jq -r '.[0].FromPort')

aws ec2 modify-security-group-rules --region "$REGION" --group-id "$SG" \
  --security-group-rules "SecurityGroupRuleId=$RULE_ID,SecurityGroupRule={IpProtocol=tcp,FromPort=$PORT,ToPort=$PORT,CidrIpv4=$CIDR,Description=pending-delete-$EXPIRE-orphan}"

echo "✓ 规则 $RULE_ID 已标记为 pending-delete-$EXPIRE"
echo "  $DAYS 天后跑：aws ec2 revoke-security-group-ingress --security-group-rule-ids $RULE_ID"

#!/bin/bash
# scan-all-public-rds.sh - 全 region 全 engine 扫公网开放
set -euo pipefail
for r in us-west-2 us-east-1 ap-southeast-1 eu-west-1; do
  echo "==== $r ===="
  aws rds describe-db-instances --region "$r" \
    --query 'DBInstances[].VpcSecurityGroups[].VpcSecurityGroupId' \
    --output text 2>/dev/null | tr '\t' '\n' | sort -u | while read sg; do
      [[ -z "$sg" ]] && continue
      open=$(aws ec2 describe-security-groups --group-ids "$sg" --region "$r" \
        --query 'SecurityGroups[0].IpPermissions[?IpRanges[?CidrIp==`0.0.0.0/0`]]' \
        --output json 2>/dev/null)
      [[ "$open" != "[]" ]] && echo "  [!] $r/$sg has 0.0.0.0/0"
    done
done

#!/bin/bash
# setup-db-access-check.sh - 开发者本机环境自检
set +e

ok=0; fail=0
check() { if eval "$2"; then echo "  [OK] $1"; ((ok++)); else echo "  [FAIL] $1"; ((fail++)); fi; }

echo "==== AWS CLI ===="
check "aws cli 已安装"        'command -v aws >/dev/null'
check "Session Manager Plugin" 'session-manager-plugin --version >/dev/null 2>&1'
check "AWS 凭据有效"           'aws sts get-caller-identity >/dev/null 2>&1'

echo "==== IAM 权限 ===="
USER=$(aws sts get-caller-identity --query Arn --output text 2>/dev/null | awk -F'/' '{print $NF}')
check "在 db-access 组中"      "aws iam list-groups-for-user --user-name $USER --query 'Groups[?GroupName==\`db-access\`]' --output text | grep -q db-access"

echo "==== 跳板可达 ===="
check "prod 跳板 SSM Online"   'aws ssm describe-instance-information --region us-west-2 --filters Key=InstanceIds,Values=i-xxxxxxxxxxxxx --query "InstanceInformationList[0].PingStatus" --output text 2>/dev/null | grep -q Online'

echo "==== 本地端口 ===="
check "13306 未被占用"          '! lsof -iTCP:13306 -sTCP:LISTEN >/dev/null 2>&1'

echo
echo "通过 $ok / 失败 $fail"
[[ $fail -eq 0 ]] || exit 1