LIFULL Creators Blog

LIFULL Creators Blogとは、株式会社LIFULLの社員が記事を共有するブログです。自分の役立つ経験や知識を広めることで世界をもっとFULLにしていきます。

トップ > DBエンジン移行に伴うSQLの移行漏れ検出の取り組み

DBエンジン移行に伴うSQLの移行漏れ検出の取り組み

こんにちは。テクノロジー本部基盤開発ユニット改善推進グループの花岡です。
改善推進グループでは技術的負債解消の一環としてLIFULL HOME'S で使用されているDBエンジンプロジェクトに取り組んでいます。

今回はDBエンジン移行プロジェクトにおけるSQL改修漏れを防ぐための取り組みについて紹介したいと思います。

DBエンジン移行プロジェクトの概要

LIFULLでは運用コストの削減や開発効率やパフォーマンス改善のためにOracle DatabaseからPostgreSQLへの移行を進めています。 両DBのデータを同期させつつアプリケーションのSQL改修を進めています。
※ 以下ではOracle DatabaseをOracle, PostgreSQLをPostgresと呼びます。

プロジェクトの内容については以下の弊社ブログでも紹介されていますのでそちらもご覧いただければと思います。

www.LIFULL.blog www.lifull.blog

DBエンジン移行の進め方について

https://www.LIFULL.blog/entry/2021/03/24/151447 でも紹介されていますが、本プロジェクトでは以下のようにDBエンジン移行を進めています。現在は参照系SQLの移行を進めている最中です。

  • スキーマオブジェクトとアプリケーションの依存関係の洗い出し
  • Postgresに移行先のテーブルを作成(AWS SCTを使用
  • OracleとPostgresのデータ同期(AWS DMSを使用
  • 参照系SQLを移行

上記のブログでも言及されていますが、移行が必要なSQLの調査が不十分なことによるアプリケーションの障害が課題の一つとなっていました。 そこで今回は抜け漏れ検知のためのDBアクセス監視のしくみを構築することで移行漏れを早く検知できる様にしました。

DBアクセス監視のしくみ

DBアクセス監視のしくみは以下の通りです。 Oracleにアクセスがあった際に検知してChatWorkへ送信して開発者が確認できる様にします。

Oracleの対象テーブルに監査ログ(以下Audit Log)を設定してアクセス状況をログ出力する。
⬇︎
CloudWatch Logsのサブスクリプションフィルタで監視対象スキーマオブジェクトへのアクセスを検知してLambdaを起動する。監視対象スキーマオブジェクトはJSONで管理する。
⬇︎
Lambdaでデータを整形してChatWorkへ送信する。
⬇︎
ChatWorkでアクセス情報を表示する。

全体の構成は以下の通りです。

f:id:LIFULL-hanaokat:20210930175929p:plain

ここからはそれぞれの設定や処理内容についてもう少し詳細に説明していきたいと思います。

Oracle

OracleへのSQLの実行を検知するために、Audit Logを出力する設定をします。

監査ログにはアクセス対象のスキーマオブジェクトや実行されたSQLの種類を出力できます。

対象テーブルの参照系SQLがすべてPostgres用に改修された段階で、以下のコマンドで対象テーブルに監査ログの設定を追加します。
AUDIT SELECT ON <対象テーブル>;

この設定により監査ログが出力される様になります。
ログの内容についての詳しい説明は省きますが、このログでどのスキーマオブジェクトがアクセスされているかやスキーマオブジェクトに対してどのような操作があったのかを調査できます。
今回は監査ログからアクセスされたスキーマオブジェクト名を抽出します。

監査ログについて詳しく知りたい方はこちらも参照してください。
docs.oracle.com

OracleでAudit Logを出力できる様にした後、Amazon RDS側でCloudWatchにログを出力する設定をすれば監査ログの設定は完了です。

CloudWatch Logs

Audit Logを記録しているCloudWatchLogsのロググループにサブスクリプションフィルタをつけて、ロググループへのログの追加をトリガにしてLambdaを実行する様にします。 これで、ログのデータをLabmdaで受け取ることも可能になります。

サブスクリプションフィルタは以下のコマンドで追加します。

$ aws logs put-subscription-filter \
    --log-group-name $LOGGROUP_NAME \
    --filter-name ora2pos-notification-filter --filter-pattern "$FILTER_PATTERN" \
    --destination-arn arn:aws:lambda:ap-northeast-1:$AWS_ACCOUNT:function:$FUNCTION_NAME \
    --profile $AWS_PROFILE --region ap-northeast-1

サブスクリプションフィルタのfilter patternは監視対象のスキーマオブジェクトリストから抽出して作成します。 監視対象のスキーマオブジェクトリストは以下のようにJSONで管理しています。

{
    "objects":[
        {
            "schema":"HOMES",
            "object":"object1"
        },
        {
            "schema":"HOMES",
            "object":"object2"
        },
        {
            "schema":"HOMES",
            "object":"object3"
        },
    ]
}

サブスクリプションフィルタでLambdaに送信するデータはBase64でエンコードされ、gzip形式に圧縮されます。

複合化したデータは以下のようになります。

{
    "awslogs": {
        "data": {
            "owner": "xxxx",
            "logGroup": "xxxx",
            "logStream": "xxxx",
            "subscriptionFilters": [
                "xxxx"
            ],
            "messageType": "DATA_MESSAGE",
            "logEvents": [
            {
               "id": "xxxx",
               "timestamp": 000000000000,
               "message": "Audit Logの内容"
            },
            ]
        }
    }
  }

サブスクリプションフィルタの設定方法について詳しく知りたい方はこちらも参照してください。
docs.aws.amazon.com

Lambda

Lambda側で受け取ったログから必要な情報を抽出して整形し、ChatWorkへ送信します。

Lambdaはgzip形式で圧縮されたデータを受け取るため、以下のようにしてログ情報を取得します。

const zlib = require('zlib');
var payload = Buffer.from(event.awslogs.data,'base64');
zlib.gunzip(payload,function(error,result){
  if(error){
    console.log(error);
  }else{
        result = JSON.parse(result.toString('ascii'));
        const logs = result.logEvents;
        const logGroup = result.logGroup;
        const logStream = result.logStream;
...

SQLでアクセスされたスキーマオブジェクトを監視対象のスキーマオブジェクトリストと照合します。このリストはサブスクリプションフィルタのfilter patternに使用したのと同じ物を使用します。

照合が終わったら受け取ったデータを以下の形式に整形します。
これでアクセスされたスキーマオブジェクトを確認し、ログのURLから原因調査できる様にします。

oracle異常アクセスログ検知
異常アクセスがあるオブジェクト一覧:
...
...
...
以下のLogStreamを参照してください:
<ログのURL>

データを整形したらChatwork APIでChatworkに通知メッセージを送信します。 APIの形式は以下の通りです。

  • URL
https://api.chatwork.com/v2/rooms/$CHATWORK_ROOM_ID/messages
  • ヘッダ
'X-ChatWorkToken': $CHATWORK_TOKEN

ChatWork APIについては、以下のドキュメントを参照ください。
developer.chatwork.com

ChatWork

ChatWorkでAPIを受け取ると以下のようなメッセージが表示されます。
モザイクで隠していますが、アクセスのあったスキーマオブジェクトとログのURLが表示される様になっています。 これでOracleのアクセスをChatWorkで確認するできるようになりました。

f:id:LIFULL-hanaokat:20210928201336p:plain

移行漏れ検出時の運用フロー

ChatWorkでアクセスを検知した場合は以下のような運用フローで問題を把握して対処します。

ChatWorkでSQLの移行漏れを検出する。
⬇︎
監査ログの詳細（ログが発生した原因）を調査する。 ユーザーやアクセス対象のオブジェクト、どのサーバからアクセスされているか？SQLの種類は何か？を把握する。SQLの種類はSQL Command Codesを参考にする
⬇︎
ログ発生の原因や状況に応じて緊急度を判断して対応する。

原因・状況 緊急度 対応内容
手作業によるSQL実行など、移行漏れが原因でない場合 対応の必要なし -
移行漏れだが障害につながらない場合 緊急度低 ・対象のSQLを確認
・移行計画を立て、対象のSQLを移行する
・移行後、ログが出なくなった場合はOKとする
移行もれによる障害が発生した場合(もしくは発生する可能性が高い場合)　 緊急度高 ・至急で障害対応を実施する
・障害対応後、ログが出なくなった場合はOKとする

これでSQLの移行漏れがあった場合に早く検知して対処するしくみを作り上げることができました。

まとめ

今回はCloudWatchやLambdaを使ったSQL検知システムについて紹介しました。 DBエンジン移行を行っている方にとって少しでもお役に立てれば幸いです。

まだDBエンジン移行プロジェクトは道半ばですが、一つ一つの課題に対して解決方法を模索しつつ進めています。
ここで紹介したのはその取り組みの一つですが、 これから発信できる様な取り組みがあれば引き続きブログで紹介していきたいと思います。ぜひ楽しみにしてください。

LIFULLではともに成長できるような仲間を募っています。 よろしければこちらのページもご覧ください。

hrmos.co hrmos.co