Lazy Diary @ Hatena Blog

PowerShell / Java / miscellaneous things about software development, Tips & Gochas. CC BY-SA 4.0/Apache License 2.0

組織内でのAPI認証にOAuth2 Client Credential flowを使うメリット募集

内閣官房が政府CIOポータルで公開してる標準ガイドライン群では、APIテクニカルガイドブックで、API認証をする際は「APIキー又はOpenID Connect」による認証を推奨しています。

ただ、調達の受注者としては「政府CIOポータルで推奨されている方式に従いました」というだけでは、「ベンダーが余計な仕様を追加して値増しを狙ってきたぞ」と思われかねません。「このような脅威が想定されるので、それに対してこうします」が筋なわけです。脅威の明確化をしましょう、とNIST SP800-18とかにも書かれてます。

組織間システム連携用のAPIなんかを設計する場合、

  • リソースサーバとIdPが上位組織(本社システムとか)、RPが下位組織(支社システムとか)にある
  • IdPはこのAPI専用
  • システム連携用のAPIだから、クライアントIDとシークレットはAPサーバの上にあって、人間が参照することはほとんどない
  • クライアントIDとシークレットを使うのは限られた職員のみ(不特定多数ではない)
  • 上位組織と下位組織の間の通信は、専用線またはTLSで保護されている

みたいな状況だと、「それってわざわざIdPにTokenもらいに行く必要あるの?Basic認証でユーザID+パスワードで認証するみたいに、クライアントIDとシークレットのみで認証すればよいのでは?」という疑問が上がることがあるわけです。

もちろん、認証処理を任せられるライブラリがあるから開発が楽チン、みたいな評価項目もあります。ただそれとは別に、セキュリティ上のメリットがないか調べていたところ、以下のQ&Aが見つかりました。

stackoverflow.com

  1. シークレットがAPI呼出の度に送られなくなる。トークンには有効期限(たとえば15分)が付いているから、MITMなどでトークンが漏洩しても15分後には無効になる。
  2. トークンに認可のスコープ情報などを付けて返せる
  3. トークンが正当か、いちいちIdPに問い合わせなくても、リソースサーバで検証できる(トークンの署名などのことと思われる)ので性能面で有利

といった点が挙げられてます。

状況にもよりますが、1.は専用線なら構内に入られた時点でOAuth2を使ってようがやりたい放題だし、2.はIdPがこのAPI専用だから関係なし、3.はRPが認証時にJSESSIONIDを発行するのと同じで済むのでは……と考えると、いずれもメリットにはならない気がします。

ID生成方法の評価観点(主にエンタープライズアプリケーションの視点から)

発端

https://www.asahi.com/articles/ASN5N5VR4N5NULFA01F.htmlwww.asahi.com

こんな話がありました。

アプリの開発者に対して「こうやってIDを採番したら、処理が競合したときにIDが重複しますよ」という説明をしたときに、「そんなことが起こる確率は低いから大丈夫だよ、UUIDやSHA-256だって『重複する確率は低いから大丈夫』なんでしょ」と返されるケースは、かなり頻繁に経験します。特に、JavaSystem.currentTimeMillis()を使って取得したミリ秒単位の時刻を使おうとするケースが多かったですね。

satob.hatenablog.com

動機

IDを採番する方法として、Webで探せる記事としては以下がよくまとまっています。

qiita.com

エンタープライズアプリケーションの分野で使うIDは、大きく分けて以下の2つに分けられるのではないかと思います。また、上記の記事で評価に使っている「生成の速度」「推測困難性」「順序性」の3つの観点も、必要性の度合いが異なります。

  • 主に人間が見る、永続的で機密性のない番号(例:裁判所の事件記録符号)……主に「生成の速度」「順序性」の2つが重要
  • 主に人間が見ることのない、一時的で機密性のない番号(例:一時ファイルのファイル名)……主に「生成の速度」「推測困難性」の2つが重要

観点

評価観点は上記の他にも以下のようなものが考えられます。選択の決め手がない場合に考慮に入れるとよいでしょう。

飛び番を防止できるか?

「順序性」のさらに強いケースとして「意図的に削除したデータがない限りIDが連番になっていること」が必要とされるケースがあります。「末番を見ただけで、その時点のデータの総件数がすぐに分かるようにしたい」という業務要件があるケースが該当します。たとえば脆弱性のCVE番号がこの要件に近いのではと思います。これを可能にするには、ビジネスロジック中で発行されたトランザクションの中で採番処理を行う必要があります(飛び番を許容する場合と比較して、同時実行性は下がります)。

完全な一意性(絶対に重複しないこと)を容易に説明できるか?

一般的なレベルの開発者に「ミリ秒が衝突しないここと、UUIDが衝突しないことが実用上等価でないことがすんなり受け入れられる」とか、システムについて詳しくない顧客に対して「UUIDは確率的に衝突しないという説明をすんなり受け入れられる」というケースはそう多くありません。なので、絶対にIDが重複しない方法、しかもそれが容易に理解可能なレベルで説明できる方法が必要となるケースがあります。

1つの仕組みで複数の採番系列を作れるか?

「2020年と2021年では別の系列を採番する必要がある(脆弱性のCVE番号みたいなイメージ)」「一般入会の場合と特別入会の場合とでIDを別々に採番したい」のように、業務中で採番の系列が複数必要になる場合があります。採番の系列ごとにサーバが増える、みたいなのはやってられないので、1つの仕組みで複数の採番系列を管理できることが望ましいです。

開発中にDDL等の発行なしに新しい番号の系列を作れるか?

開発作業中に、DBのテーブル構成を管理する担当者(DBA)を設けるケースがあります。アプリの開発者が兼任の場合はまぁいいのですが、アプリの開発チームが複数存在する場合など、たとえば構成がチーム間で矛盾するのを避けるため等の理由で、専任者を設ける場合があります。新しいテーブルやシーケンスを作るたびにDBAへ連絡が必要になるのですが、これが正直面倒くさい!という場合は、DDLの発行なしに新しい番号の系列を作れた方が楽です。

実行中にDDLの発行なしに新しい番号の系列を作れるか?

「2020年と2021年では別の系列を採番する」という処理をシーケンスで実装する場合、新しい系列が必要になるたびにDDLを発行する必要があります。DBAが実行環境上のテーブルやシーケンスの一覧を管理していたりする場合には、勝手にシーケンスが増えるのは避けたいところです。

まとまったIDを一度に採番できるか?

バッチ処理なんかで、レコードごとに採番処理なんかやってたら性能が出ない!という場合に、末番をいきなり1000番から1050番に変更する(1001~1050番は採番したプロセスが自由に利用可能)ことで、採番処理の回数を減らすことがあります。OracleのシーケンスのINCREMENT BYと似ていますが、INCREMENT BY 10なら固定で10ずつ番号が増えるのに対し、こちらは増分を採番のたびに変更可能にする必要があります。原理的に、飛び番を許容する場合のみ選択可能なオプションです。

DB上で採番する場合、DBMS間での移植性があるか?

複数のDBMSに対応するアプリケーションで必要になる要件です。たとえばMySQLではシーケンスが使えません(特定カラムに紐付くAUTO_INCREMENTのみ)。

標準的な採番体系が言語処理系の機能でサポートされているか?

採番の体系がRFCなどで定められていれば、採番に使用しているライブラリがEOLを迎えた場合にも別の選択肢へ容易に移行ができます。 また特に必要がなければ、追加のコンポーネントは避けたいところです。たとえばUUIDv4はJavaの標準APIで採番できるのでお手軽に使えます。

採番にAPサーバ上のアプリケーションとDBMS以外の構成要素が要るか?

snowflakeは採番用のサーバプロセスが必要です(合ってる?)。システムの実行に必要なプロセスが増えるということは、それだけリソース設計作業も増えますし、実行するサーバに関する知識も必要ですし、自動テストの制御なんかも大変になります。

まとめ

評価をまとめると以下のようになります。個人的には、人間が見る永続的な番号なら昔ながらの採番テーブル、人間が見ない一時的な番号ならUUIDv4を使うことが多いです。

# 採番方法 bit数 速度 推測困難 順序 飛び番防止 一意性説明 複数系列対応 開発中DDL不要 実行中DDL不要 一括採番 DBMS移植性 標準API 追加サーバ不要
1 データベースの採番テーブル 任意 × × × ×
2 データベースのシーケンス(またはIDENTITY) 32 or 64 × × × × × × ×
3 UUID version1 128 × × × ×
4 Snowflake 63 × × × × ×
5 Flake 128 × × × × ×
6 ULID 128 × × × ×
7 Firebase PushID 120 × × × ×
8 Instagram ID 64 × × × ×
9 UUID version4 128 × × × ×

Read from .xlsx in PowerShell (x64) without using Excel

This article shows how to read .xlsx in PowerShell without Excel. In Windows 10 (x64), you will have to do additional work.

  • You have to install Microsoft Access Database Engine 2010 Redistributable (AccessDatabaseEngine_X64.exe) when you use Windows PowerShell (x64). You will have to install AccessDatabaseEngine.exe when you use Windows PowerShell (x86).
  • You have to use Provider=Microsoft.ACE.OLEDB.12.0 instead of Provider=Microsoft.Jet.OLEDB.4.0.
  • You have to use Extended Properties=Excel 12.0 instead of Extended Properties=Excel 8.0.
  • ADO can handle unicode characters in .xlsx files. Note that PowerShell ISE, cmd.exe and powershell.exe has some limitations:
    • The console in PowerShell ISE cannot display the characters not included in the default locale.
    • The terminal window for cmd.exe and powershell.exe cannot display the surrogate pairs.
$fileName = "C:\tmp\UnicodeTest.xlsx"
$sheetName = "sheet1$"
$provider = "Provider=Microsoft.ACE.OLEDB.12.0"
$dataSource = "Data Source = $fileName"
$extend = "Extended Properties=Excel 12.0"
$query = "Select * from [$sheetName]"

$conn = New-Object System.Data.OleDb.OleDbConnection("$provider;$dataSource;$extend")
$sqlCommand = New-Object System.Data.OleDb.OleDbCommand($query)
$sqlCommand.Connection = $conn
$conn.open()
$dataReader = $sqlCommand.ExecuteReader()

$list = New-Object System.Collections.ArrayList
While ($dataReader.read())
{
    $list.Add($dataReader[1].ToString()) > $null
}
$list | % { $_ } | Out-File -Encoding UTF8 c:\tmp\UnicodeTest.txt

$dataReader.close()
$conn.close()

Launch PowerShell process in non-default culture (locale)

In PowerShell on Windows 10, you cannot change the current culture (locale) with chcp command.

If you want to launch a new PowerShell process in another culture, once run cmd.exe from PowerShell, execute chcp, and launch powershell.exe from cmd.exe.

This is useful when you want to get an error message in en-US culture to ask questions in forums like Stack Overflow.

PS C:\> [Threading.Thread]::CurrentThread.CurrentUICulture.Name
ja-JP
PS C:\> cmd.exe
Microsoft Windows [Version 10.0.18362.836]
(c) 2019 Microsoft Corporation. All rights reserved.

C:\>chcp 437
Active code page: 437

C:\>powershell.exe
Windows PowerShell
Copyright (C) Microsoft Corporation. All rights reserved.

Try the new cross-platform PowerShell https://aka.ms/pscore6

PS C:\> [Threading.Thread]::CurrentThread.CurrentUICulture.Name
en-US
PS C:\>

pgbenchでINSERT中心のトランザクションのTPSをできるだけ高くする

動機

DBのチューニング方法なんかの書籍を見てみても、性能向上方法として語られているのはSELECTのチューニングが主で、INSERTに関する記載はあまり見当たりませんでした。ですが、大量のデバイスから飛んできたデータをMQTTやらHTTPやらで受けて、それを生のままDBMSに登録する……というワークロードの場合、INSERTがトランザクションの中心になるので、DBMSベンチマークもINSERT中心のワークロードにする必要があります。

DBMSPostgreSQLの場合、性能単価の測定にはpgbenchを使うと思いますが、ここで設定を誤っていたりすると、PostgreSQLの実力値を見誤ったりします(結果、DBサーバにオーバースペックなマシンを用意してしまったりする)。ハードウェアのリソース設計はそれはそれで行うとして、ここでは主にpgbenchによる性能単価測定の際にTPSをできるだけ高くする方法(=pgbenchの設定がマズくてPostgreSQLの性能を過少に見積らない方法)をまとめておきます。

前提とするワークロード

  • テーブルはpgbench組込みのトランザクションで使われるpgbench_historyを使います。
  • 1回のトランザクションで8レコードをINSERTします。
  • INSERT以外のトランザクションは実行しません(無風測定です)。
  • 同時接続クライアント数は20、実行時間20秒で確認しました。
  • ここではオプションの有効性の確認を目的として、pgbenchとPostgreSQLを同じマシンで実行しています。正確なTPSを知りたい場合はpgbenchはPostgreSQLとは別マシンで実行してください(後述)。
  • OSはWindows 10です。
  • CPUはIntel(R) Core(TM) i7-8750H CPU @ 2.20GHz(6コア12スレッド)、ストレージはADATA SU800NS38を使っています。

何もしない場合

以下のようなスクリプトファイルを作成して実行します。

\set nbranches 1 * :scale
\set ntellers 10 * :scale
\set naccounts 100000 * :scale
\set aid random(1,:naccounts)
\set bid random(1,:nbranches)
\set tid random(1,:ntellers)
\set delta random(-5000,5000)
BEGIN;
INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);
INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta+1, CURRENT_TIMESTAMP);
INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta+2, CURRENT_TIMESTAMP);
INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta+3, CURRENT_TIMESTAMP);
INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta+4, CURRENT_TIMESTAMP);
INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta+5, CURRENT_TIMESTAMP);
INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta+6, CURRENT_TIMESTAMP);
INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta+7, CURRENT_TIMESTAMP);
END;

pgbenchの実行オプションは以下の通りです。

pgbench.exe -U postgres -c 20 -T 20 -f C:\tmp\insert.pgbench test

この場合のTPSは3863TPSでした。

transaction type: C:\tmp\insert.pgbench
scaling factor: 1
query mode: simple
number of clients: 20
number of threads: 1
duration: 20 s
number of transactions actually processed: 77323
latency average = 5.177 ms
tps = 3863.290450 (including connections establishing)
tps = 3873.891699 (excluding connections establishing)

いろいろ対策をほどこす

CPUの割り当てコア数を調整する(プロセッサアフィニティを設定している場合・DBと同じマシンでpgbenchを実行する場合)

PostgreSQLインスタンスに対するCPUの割り当てに応じて、TPSが高くなります。プロセッサアフィニティを設定している場合、PostgreSQLに割り当てるCPUコアを増やします。ただし、DBと同じマシンでpgbenchを実行する場合、すべてのCPUをPostgreSQLに割り当てると逆にTPSが下がるようです(pgbenchに割り当てるCPUがなくなるせい?)。そのためCPUアフィニティを調整して、いちばん高い性能が出るポイントを探ります。

Windowsの場合、サービスプロセスにはプロセッサアフィニティは設定できないので、PostgreSQLサービスは停止し、ユーザプロセスとしてPostgreSQLインスタンスを実行します。以下のようなPowerShellスクリプトを作成し、割り当てるCPUのスレッド数を1~12まで変化させて実行しました。

@('1','3','7','F','1F','3F','7F','FF','1FF','3FF','7FF','FFF') | ForEach-Object {
  Write-Host "`nCPU Affinity:" $_
  cmd.exe /c "start /affinity $_ /B c:\opt\PostgreSQL\12\bin\pg_ctl.exe start -w -s -D C:\opt\PostgreSQL\12\data"
  $env:PGPASSWORD='postgres'
  c:\opt\PostgreSQL\12\bin\pgbench.exe -U postgres -c 20 -T 20 -f C:\tmp\insert2.pgbench test
  c:\opt\PostgreSQL\12\bin\pg_ctl.exe stop -D "C:\opt\PostgreSQL\12\data"
}

結果は以下のグラフのようになりました。以下、設定を変えながら上記のスクリプトを実行していきます。 何もしない場合、いちばん性能がよいのは全6コア・12スレッドを使った場合で、3863TPSでした。

f:id:satob:20200518033138p:plain
設定ごとのpgbench TPS数

バルクインサート

スクリプトファイルを修正し、レコードごとにINSERTを実行していたところを、バルクインサートを行うよう修正します。

\set nbranches 1 * :scale
\set ntellers 10 * :scale
\set naccounts 100000 * :scale
\set aid random(1,:naccounts)
\set bid random(1,:nbranches)
\set tid random(1,:ntellers)
\set delta random(-5000,5000)
BEGIN;
INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP),
                                                                 (:tid, :bid, :aid, :delta+1, CURRENT_TIMESTAMP),
                                                                 (:tid, :bid, :aid, :delta+2, CURRENT_TIMESTAMP),
                                                                 (:tid, :bid, :aid, :delta+3, CURRENT_TIMESTAMP),
                                                                 (:tid, :bid, :aid, :delta+4, CURRENT_TIMESTAMP),
                                                                 (:tid, :bid, :aid, :delta+5, CURRENT_TIMESTAMP),
                                                                 (:tid, :bid, :aid, :delta+6, CURRENT_TIMESTAMP),
                                                                 (:tid, :bid, :aid, :delta+7, CURRENT_TIMESTAMP);
END;

PostgreSQLに3コア・6スレッドを使った場合が最も性能が高く、9375TPSになりました。

pgbenchのオプションに--protocol=preparedを指定

pgbenchとPostgreSQLの間の通信プロトコルは、デフォルトでは簡易問い合わせプロトコルになっています。ここで--protocol=extendedを設定すると拡張問い合わせプロトコルが、--protocol=preparedを指定するとプリペアドステートメントを伴う拡張問い合わせプロトコルが使用されます。ここではもっとも性能が高くなる--protocol=preparedを指定します。

バルクインサートと合わせて指定した場合、PostgreSQLに3コア・6スレッドを使った場合が最も性能が高く、11823TPSになりました。

postgresql.confでsynchronous_commit = offを指定

postgresql.confでsynchronous_commit = offを指定することで、DBがクラッシュした際に直近のコミット分のデータがロストするのと引き換えに、PostgreSQLの性能を上げることができます(データ破壊のリスクはありません)。

バルクインサート・--protocol=preparedと合わせて指定した場合、PostgreSQLに4コア・7スレッドを使った場合が最も性能が高く、13349TPSになりました。

pgbenchのオプションに-j nを指定

pgbenchはデフォルトではシングルスレッドで動作します。-jオプションで同時実行スレッド数を変更できます。 pgbenchのスレッド数を変更しながら試した結果、pgbenchに4スレッド、PostgreSQLに4コア・8スレッドを割り当てた場合が最も性能が高く、24007TPSになりました。

pgbenchをDBと別マシン(または別のCPUコア)で実行する

pgbenchでは10000TPS程度で1CPUを1スレッド使いきってしまうようです(パフォーマンスモニタでpgbenchのProcessor %が100程度になる)。上記の通りINSERTの性能はCPU-boundなようです。pgbenchがCPUを食うとそれだけベンチマークの結果に影響するので、(今回は行えませんでしたが)pgbenchはDBとは別マシン(または別のCPUコア)で実行した方がよいTPSが出るようです。上記のグラフが8スレッド以降で伸びていないのは、pgbenchの実行にまわせるCPU数が少なくなってしまっているためと思われます。