スプレッドシート+GAS+GCPで簡易的なSSL証明書期限監視システムを構築してみました

You are currently viewing スプレッドシート+GAS+GCPで簡易的なSSL証明書期限監視システムを構築してみました
  • 投稿カテゴリー:-- Web
  • 投稿の最終変更日:2022-01-15

昨今はLet’sさんやらホスティングサービスで勝手に更新してくれることの多いSSL証明書ですが、意外と手動で更新しなくてはいけないこともあります。

そして、更新ができないとサイトにアクセス時にエラーが表示されてしまい良くないです。

そこで、ちゃんと証明書の更新ができているかを定期的に確認する必要があるのですが、手動ではどうしても忘れやすく、とてもめんどくさいです。

というわけで、 今回は簡易的に証明書の有効期限を確認する仕組みを構築してみようと思います。今回はGCPのCloud RunでfastAPIの処理を動かし、監視するサイトリストをスプレッドシートで管理できるように作ってみようと思います。

指定サイト証明書の有効期限を返すAPIを作成する

まずは指定されたサイトURLの証明書有効期限を返すAPIを作成します。

この工程ではGCPのCloud Run上に確認用の処理をデプロイします。基本的には無料で何とかなる範囲ですがクレジットカードなど課金情報の登録が必要になります。もし課金情報の入力が嫌な場合は、有志が作成しているAPIを使ってみたり、各サイトの証明書期限を手動で入力するようにすれば、この工程は不要になります。

さて、では早速やっていきたいと思います。今回は必要最低限のところだけ書いていこうと思います。

処理の準備

まずはDockerfileの準備です。基本的に動作に必要なものがインストールできれば問題ないのですが、CloudRunの仕様上8080ポートでアクセスできるようにする必要があります。また今回はpipでインストールものをまとめておけるようにrequirements.txtに記述して一括でインストールするようにしておきました。

FROM python:3.7.12

COPY ./apps /apps
WORKDIR /apps

COPY ./requirements.txt ./
RUN pip install -r requirements.txt

CMD ["uvicorn", "main:app", "--reload", "--host", "0.0.0.0", "--port", "8080"]
fastapi==0.63.0
uvicorn==0.13.3
pyOpenSSL==21.0.0
pytz==2018.9

次にfastAPIの処理を実装していきます。今回はfastAPIについては全く触れず処理のみ掲載します。とりあえずコピペしていただければ動くと思います…。

#coding: utf-8
import uvicorn
from fastapi import FastAPI
from routers import CertificateExpiration

app = FastAPI()

@app.get("/")
def get_root():
    return {"message": "Hello World"}

app.include_router(CertificateExpiration.router)

#coding: utf-8
from urllib.parse import urlparse
from fastapi import APIRouter
from pydantic import BaseModel
import datetime
import pytz
import OpenSSL
import ssl

router = APIRouter()
    

@router.get("/certificate-expiration")
async def get_certificate_expiration(url: str):
    try:
        # URLからドメインを抜き出す
        parsed_url = urlparse(url)
        domain = parsed_url.netloc

        cert = ssl.get_server_certificate((domain, 443))
        x509 = OpenSSL.crypto.load_certificate(OpenSSL.crypto.FILETYPE_PEM, cert)
        expiration = x509.get_notAfter()

        # 表記を調整
        expiration = expiration.decode('utf-8')
        expiration = f'{expiration[0:4]}-{expiration[4:6]}-{expiration[6:8]}'

        return {"expiration": expiration}
    except:
        # エラーが発生したので今日の日付を返す
        return {"expiration": datetime.datetime.now(pytz.timezone('Asia/Tokyo')).strftime("%Y-%m-%d")}

ファイル構造はこのようになります。

/
│  Dockerfile
│  requirements.txt
│
└─ /apps
   │  main.py
   └─ /routers
            CertificateExpiration.py

今回やることだけであれば、main.pyとCertificateExpiration.pyを分ける必要もないのですが、将来的にこのシステムにほかのAPIも追加していきたいと考えているため、今後処理を追加しやすいように分割して実装しています。

Cloud Runのプロジェクトを作成

処理が書けたので次はデプロイ先であるCloudRunの準備を進めます。まずはCloudSDKのインストールです。OSによって方法がちょっと違うので、公式のマニュアルを参考にしてください。

インストールができたらGCPの管理画面へアクセスし、プロジェクトを作成します。

手順としては管理画面にログインすると画面上部のプルダウン(下記画像内では「プロジェクトの選択」)があり、その中の「新しいプロジェクト」から作成できます。作成時にプロジェクIDが表示されているのでこれをメモしておいてください。

画面イメージ

初めてCloudRunを使う場合は決済情報を登録する必要がります。これはハンバーガーメニューの「お支払い」から登録することができます。これでGCP管理画面の操作はおしまいです。

次に端末から下記のコマンドを実行しプロジェクトを選択しておきます。

$ gcloud init

あとは画面に表示される項目に合わせて登録していけば準備完了です。

デプロイ

プロジェクトの準備ができたのでいよいよデプロイです。デプロイは下記のコマンドで実行できます。プロジェクトIDの部分は、上記手順で確認したそれぞれのIDに書き換えてください。

$ gcloud builds submit \
     --tag gcr.io/[プロジェクトID]/backend-app
$ gcloud run deploy [プロジェクトID] \
     --image gcr.io/[プロジェクトID]/backend-app \
     --region asia-northeast1 \
     --platform managed \
     --allow-unauthenticated

これらのコマンドはシェルなどにまとめておくと今後デプロイするときの役に立つのでオススメです。ステージング環境にアップロードしたり自動でトラフィックの切り替えたくない場合は適宜書き換えちゃってください。

監視するサイトリストを作成する

さて、下準備は完了したので次はスプレッドシート上での作業になります。いつも通りにシートを作成してください。シートの内容は以下の通りです。証明書期限の項目は処理で自動入力されるため、事前に必要なのはサイト名とURL、通知フラグ、そして式を残日数に入力します。ちなみに通知フラグのチェックボックスはメニューの挿入→チェックボックスから入れることができます。

シート入力イメージ

残日数に入力する式は以下のようにD列の日付から証明書の期限が切れる残日数を計算します。おまけで証明書期限が入力されていない場合は空白になるようにしています。

=IF(D3="","",(IFERROR(DATEDIF(TODAY(), D3,"D"),0)))

もしシートの構造を変えたい場合はこちらの式を次に紹介する監視処理の値を修正してください。

サイトリストの期限を確認する処理を実装する

サイト監視リストができたので最後に監視を行う処理を用意して作業完了です。監視にはGASを使います。画面上メニューの拡張機能からApps Scriptを選んでください。入力する処理は以下の通りです。2箇所通知の送信先メールアドレスと上記で作成したAPIのURLはそれぞれの環境に合わせて書き換えてください。


// 通知先のメールアドレス
const NOTIFICATION_TO_ADDRESS = "[送信先メールアドレス]"

// API URL
const API_URL = '[API URL]'

// 通知を送信する残日数リスト
// ※ 最小の日付からは毎日通知する
const NOTIFICATION_EXPIRATION_DATE_LIST = [7, 30, 45];
const MIN_EXPIRATION = Math.min.apply(null, NOTIFICATION_EXPIRATION_DATE_LIST);

const ROW_ID_TABLE_START = 2; // サイトリストが始まる行数

const COLUMN_ID_SITE_NAME  = 1; // サイト名を掲載している列番号
const COLUMN_ID_SITE_URL   = 2; // サイトURLを掲載している列番号
const COLUMN_ID_EXPIRATION = 4; // 有効期限残日を算出している列番号
const COLUMN_ID_SEND_FLG   = 5; // 送信フラグを指定している列番号

/* 証明書の有効期限を確認する
 *
 */
function checkCertificateExpiration(url) {
  const requestUrl = `${API_URL}?url=${url}`
  const response = UrlFetchApp.fetch(requestUrl)
  const responseCode = response.getResponseCode()
  const responseText = response.getContentText()

  if (responseCode === 200) {
    const responseTextObject = JSON.parse(responseText); 
    return responseTextObject['expiration'];
  } else {
    // 有効期限が取得できなかったので今日の日付を入れておく
    return Utilities.formatDate( date, 'Asia/Tokyo', 'yyyy-MM-dd'); 
  }
}


/* 通知する
 * @param array 行の内容
 */
function sendNotifiication(rows) {
  notificationText = "";
  for (let i in rows) {
    const row = rows[i];
    notificationText += `サイト名: ${row[COLUMN_ID_SITE_NAME]} 残日数: ${row[COLUMN_ID_EXPIRATION]}日\nURL: ${row[COLUMN_ID_SITE_URL]}\n`;
  }

  const subject = "【重要】 SSL証明書失効警告";
  const body = `SSL証明書失効期限が迫っています!\n\n${notificationText}`;

  MailApp.sendEmail(NOTIFICATION_TO_ADDRESS, subject, body, {
    noReply: true
  });
}

/* リストの残日数を確認して指定された日付だった場合は通知する
 *
 */
function checkExpiredDate() {
  const sheet = SpreadsheetApp.getActiveSheet();
  const rows = sheet.getDataRange();
  const numRows = rows.getNumRows();
  let values = rows.getValues();

  //  証明書の有効期限を更新する
  for (let i = ROW_ID_TABLE_START; i <= numRows - 1; i++) {
    const row = values[i];

    // 通知送信フラグがONのもののみ有効期限を更新する
    if (row[COLUMN_ID_SEND_FLG]) {
      const certificateExpiration = checkCertificateExpiration(row[COLUMN_ID_SITE_URL]);
      sheet.getRange(i+1, COLUMN_ID_EXPIRATION).setValue(certificateExpiration);
    } else {
      sheet.getRange(i+1, COLUMN_ID_EXPIRATION).setValue('');
    }
  }

  // 有効期限を更新したので残日数を再計算する
  SpreadsheetApp.flush();
  values = rows.getValues();


  // 送信するサイトの列情報リスト
  let notificationRows = [];

  for (let i = ROW_ID_TABLE_START; i <= numRows - 1; i++) {
    const row = values[i];

    // 通知送信フラグがONのもののみ判定する
    if (row[COLUMN_ID_SEND_FLG]) {
      if (NOTIFICATION_EXPIRATION_DATE_LIST.indexOf(row[COLUMN_ID_EXPIRATION]) !== -1) {
        // 指定日数の場合は通知リストに追加する
        notificationRows.push(row)
      } else if (row[COLUMN_ID_EXPIRATION] <= MIN_EXPIRATION) {
        // 指定日数の最小日からは毎日通知リストに追加する
        notificationRows.push(row)
      }
    }
  }

  // 通知対象のサイトがある場合は通知を行う
  if (notificationRows.length > 0) {
    sendNotifiication(notificationRows);
  }
}

実行時にはcheckExpiredDate関数を選択して実行ボタンを押します。初回はスプレッドシートにアクセスする権限やメールを送信する権限など許可が求められます。許可画面では一度警告画面が表示されますが、詳細からすすめることができます。

最後に通知確認のため、証明書の期限残にあわせてNOTIFICATION_EXPIRATION_DATE_LISTで設定している日数を調整して実行してみてください。「【重要】 SSL証明書失効警告」というメールが送信されていれば作業完了です。

終わりに

というわけで今回は、ほぼスプレッドシートで証明書監視機能を構築してみました。これまでGASはなんとなくダサい感じがしていて食わず嫌いをしていたのですが、今回触ってみてそのお手軽さにハマってしまいそうです。今後も色々小さいアプリを作って行きたいと思います。

タグ: , , ,