📝

VPN経由の処理を自動化

2024/09/11に公開

Python

VPN

tech

背景

VPN制限のある環境でデータ取得する必要がありました

内容

最終的な実装コードだけスニペット的に貼り付けておきます。

Docker + Docker Compose + github actionsでデータ取得するようになっています。

name: ELT with VPN
on:
  workflow_dispatch:
  schedule:
    # 毎日 JST 10:30 （UTC 01:30 + 9hour）
    - cron: "30 1 * * *"

jobs:
  main:
    runs-on: ubuntu-latest
    defaults:
      run:
      working-directory: ./
      steps:
        - uses: "actions/checkout@v3"
          with:
            ref: main
        - name: Create env
          run: |
            touch .env
            echo ${{ secrets.GCP_SERVICE_ACCOUNT }} | base64 --decode >> .env
            cat .env
        - name: Create dir
          run: |
            mkdir .secrets
            mkdir .secrets/vpn
        - name: Create .secrets/vpn/myvpn.ovpn
          run: |
            touch .secrets/vpn/myvpn.ovpn
            echo ${{ secrets.OPVN_FILE }} | base64 --decode  >> .secrets/vpn/myvpn.ovpn
            cat .secrets/vpn/myvpn.ovpn
        - name: Create .secrets/vpn/myvpn_auth.txt
          run: |
            touch .secrets/vpn/myvpn_auth.txt
            echo ${{ secrets.VPN_AUTH_CREDENTIAL }} | base64 --decode  >> .secrets/vpn/myvpn_auth.txt
            cat .secrets/vpn/myvpn_auth.txt
        - name: compose up
          run: docker compose up -d
        - name: setup vpn
          run: docker compose exec app sudo openvpn --config ./vpn/myvpn.ovpn --auth-user-pass ./vpn/myvpn_auth.txt --script-security 2 --up /etc/openvpn/update-resolv-conf.sh --down /etc/openvpn/update-resolv-conf.sh --down-pre --daemon
        - name: wait
          run: sleep 10
        - name: HOGE_TABLE
          run: docker compose exec app python3 ./scripts/main.py --table=HOGE_TABLE
        - name: Retrieve current Date Time
          shell: bash
          run: echo "START_TIME=$(TZ=":Asia/Tokyo" date -R | sed 's/.....$//')" >> $GITHUB_ENV
        # https://github.com/slackapi/slack-github-action
        - name: Send a message to slack
          if: ${{ success() }}
          id: slack
          uses: slackapi/slack-github-action@v1.16.0
          with:
            channel-id: "XXX"
            payload: |
              {
                "blocks": [
                  {
                    "type": "section",
                    "text": {
                      "type": "mrkdwn",
                      "text": "データ投入完了： ${{ env.START_TIME }}"
                    }
                  },
                  {
                    "type": "context",
                    "elements": [
                      {
                        "type": "mrkdwn",
                        "text": "Author: <https://github.com/${{ github.event.sender.login }}|@${{ github.event.sender.login }}>"
                      }
                    ]
                  }
                ]
              }
          env:
            SLACK_WEBHOOK_TYPE: INCOMING_WEBHOOK
            SLACK_WEBHOOK_URL: ${{ secrets.SLACK_WATCH_WEBHOOK }}

services:
  app:
    build:
      context: ./
    cap_add:
      - NET_ADMIN
    devices:
      - /dev/net/tun:/dev/net/tun
    volumes:
      - ./.secrets/vpn:/home/circleci/vpn
      - ./scripts:/home/circleci/scripts
    env_file:
      - .env
    tty: true
    working_dir: /home/circleci

services:
  app:
    build:
      context: ./
    cap_add:
      - NET_ADMIN
    devices:
      - /dev/net/tun:/dev/net/tun
    volumes:
      - ./.secrets/vpn:/home/circleci/vpn
      - ./scripts:/home/circleci/scripts
    env_file:
      - .env
    working_dir: /home/circleci
    command: bash -c "chmod +x scripts/run.sh && bash scripts/run.sh"

FROM cimg/base:2022.03

RUN sudo apt-get update \
    && sudo apt-get install -y \
    iputils-ping \
    net-tools \
    dnsutils \
    openvpn \
    rsync \
    python3 \
    python3-pip \
    && sudo apt clean

COPY requirements.txt ./
RUN pip install --no-cache-dir --upgrade pip \
    && pip install --no-cache-dir -r requirements.txt

# openresolv のインストール
RUN wget "https://github.com/NetworkConfiguration/openresolv/releases/download/v3.13.2/openresolv-3.13.2.tar.xz" && \
    tar -xf "openresolv-3.13.2.tar.xz" && \
    pushd "openresolv-3.13.2" && \
    ./configure && \
    make && \
    sudo make install && \
    popd
RUN sudo wget "https://raw.githubusercontent.com/masterkorp/openvpn-update-resolv-conf/master/update-resolv-conf.sh" -P "/etc/openvpn" && \
    sudo chmod 744 "/etc/openvpn/update-resolv-conf.sh"

❯ ls .secrets/vpn
myvpn.ovpn      myvpn_auth.txt
❯ ls .env
.env❯ ls serviceaccount.json
serviceaccount.json

❯ docker compose -f compose.local.yaml run --rm app

dbt-bigquery==1.5.0
pandas==1.4.2
google-api-python-client==2.42.0
click==8.1.3

#!/bin/bashecho -------- VPN
sudo openvpn --config ~/vpn/myvpn.ovpn --auth-user-pass ~/vpn/myvpn_auth.txt --script-security 2 --up /etc/openvpn/update-resolv-conf.sh --down /etc/openvpn/update-resolv-conf.sh --down-pre --daemonsleep 10
echo -------- VPN 接続成功
echo -------- HOGE_TABLE
python3 scripts/main.py --table=HOGE_TABLE
echo -------- HOGE_TABLE ここまで

import os
import json
import requests
import pandas as pd
from google.cloud import bigquery
from google.oauth2 import service_account
import calendar
import datetime
ENV_JSON = os.environ.get('SA')
service_account_info = json.loads(ENV_JSON)
credentials = service_account.Credentials.from_service_account_info(service_account_info)

参考

https://memo.open-code.club/OpenVPN/はじめに/概要.html

背景

内容

参考

Discussion