AthenaでWAFログからHTTP headerを取得しJWTの内容も取得する

前提

WAF のログを Kinesis Data Firehose 経由で S3 に記録していること

Athena に WAF ログのテーブルを作成ずみであること

まとめると、大体ここらへん

HTTP headerの内容を取得したい

Stack Overflowの記事の様に、UNNESTとMAP_AGGで、header['name'] = valueで扱える様にする。

例えば、header['user-agent']でUser-Agentが取得できる

JWTをデコードし内容を取得したい

Amazon Athena は Presto/Trino (Presto SQL is now Trino) ベースなので、 Trino の Issue に SQL で JWT アクセスしたいというのがあがってる。JWT自体をデコードする関数等はないが、とりあえず次の方法で取得できる（暗号化されてないもの)

1. AuthorizationヘッダーからBearerを削除してJWTだけにする
2. ドットをデリミタとして分割してペイロード部分を抜き出し
3. Base64デコードし
4. UTF-8文字列化し
5. JSONとして返す
6. JSONPathで目的の値を取り出し（例: subクレーム=ユーザーの一意識別子)
7. JSONから文字列にキャスト

クエリー例

SQL例

LOWER(f.name)なのはクライアント実装によって異なるってのもあるけど、なぜかWAFのログ上同じブラウザーでもUser-Agentだったりuser-agentだったり(原因は追及してない)なので同一で扱うため。

WITH access_log AS (
  SELECT
    timestamp,
    action,
    httprequest.clientip,
    httprequest.country,
    MAP_AGG(LOWER(f.name), f.value) AS header
  FROM waf_logs,
  UNNEST(httprequest.headers) AS t(f)
  GROUP BY 1, 2, 3, 4
)
SELECT
  FROM_UNIXTIME(timestamp/1000) AS timestamp,
  header['user-agent'] AS ua,
  CAST(
    JSON_EXTRACT(
      JSON_PARSE(
        FROM_UTF8(
          FROM_BASE64(
            SPLIT_PART(REPLACE(header['authorization'], 'Bearer '), '.', 2)
          )
        )
      ),
      '$.sub'
    )
    AS VARCHAR
  ) AS subject
FROM access_log;

結果例

timestamp	ua	subject
2021-09-01 01:23:45.000	Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/92.0.4515.159 Safari/537.36	248289761001