読者です 読者をやめる 読者になる 読者になる

やってみる

アウトプットすべく己を導くためのブログ。その試行錯誤すらたれ流す。

GitHubアカウントDBの正規化について考える

ワークフロー

リレーショナルDBにおけるGitHubアカウント情報の正規化について考えたログ。

テーブル

テーブル名 説明
Accounts メアド、パスワード、ユーザ名
TwoFactors 二要素認証用キー
AccessTokens WebAPI用Token
Scopes Tokenに割り当てられた権限

もしかするとScopesは作らないかもしれない。そこのところを今回考える。

テーブル親子関係

  • Accounts
    • TwoFactors
    • AccessTokens
      • Scopes

DBの正規化について

GitHubAPI version 3 時点での話。

AccessTokenのScope情報をどう保持するか。

Scopeについて

  • 24種類ある
  • 親子関係がある
  • 一部は名前にコロン:が含まれている
  • WebAPI仕様のため変更される恐れがある

上記が要点となりそう。

Scope一覧

AccessToken作成画面を見ると、以下のような親子関係があるらしい。

f:id:ytyaru:20170106161503p:plain

  • repo
    • repo:status
    • repo_deployment
    • public_repo
  • admin:org
    • read:org
    • write:org
  • admin:public_key
    • read:public_key
    • write:public_key
  • admin:repo_hook
    • read:repo_hook
    • write:repo_hook
  • admin:org_hook
  • gist
  • notifications
  • user
    • user:email
    • user:follow
  • delete_repo
  • admin:gpg_key
    • read:gpg_key
    • write:gpg_key
  • (no scope)

なんだか無駄に複雑になりそうな予感。とりあえずスコープの親子関係は無視して考える。

実装パターン

データベースの構造上、以下の実装パターンが考えられる。

  1. スコープの各値を列名にして真偽値を持たせる
  2. カンマ区切りの文字列データを持たせる
  3. ビットフラグで管理する
  4. APIでAccessTokenを新規生成した結果のjsonを保存する

それぞれのパターンについて考えてみる。

A. スコープの各値を列名にして真偽値を持たせる

今回はスコープ名にコロン:がある。こちらによると列名にはコロン記号を使えないらしい。よって今回はこのパターンを使えない。

メリット

  • SQLのselect文で検索しやすい
select * from Scopes Where repo is not NULL

上記のようにすれば、repo(リポジトリ操作権限)を持ったものだけを検索できる。

デメリット

  • Scopeが24個と多い
    • 応答速度が遅くなる可能性がある
    • ファイルサイズが大きくなる可能性がある(未知。DBの仕様次第。)
  • マイグレーションが大変そう
    • Webサービス変更によりScopeが変わった場合、テーブル作成しなおすことになる。変更内容にもよるが、真偽値をScope値に変換する必要がある

B. カンマ区切りの文字列データを持たせる

たとえばテーブルのScopes列に、repo,gistというカンマ区切りのデータを持たせる。

メリット

データ登録しやすい

GitHubAPIで新規作成したときに取得できるスコープ名をそのまま登録できる。いちいちIdやビットフラグ値など別物に変換する必要がない。

データの可読性が高い

Scope名がそのまま登録されているから一目でわかる。

DB構造はそのままでOK

GitHubAPIの仕様変更によりScope値が変更しても、DB構造はそのままでOK。ただしレコードのデータを全部変更する必要がある。メリットなのかデメリットなのか微妙。

デメリット

解析が大変

こちらによると、MySQLにはFIND_IN_SET関数とやらがあり簡単らしいが、SQLite3にはないらしい。こちらによると、(',' || column_name || ',') LIKE '%,value,%'のようにLIKE句を使って取得するらしい。

複数の状態をand条件で取得するときは、上記のLIKE句を複数書くことになるのだろうか。可読性が下がりそう。だが、複数の権限を持った基本的に最低限のスコープのみを割り振るはずだから、1Token1権限になるかもしれない。

ファイルサイズが大きくなる

他の方法に比べて。ただ、スコープ名のデータ程度だから大したことはない。AccessTokenも大量に作成するようなものではない。よって問題になるほどの差は出ないはず。

C. ビットフラグで管理する

メリット

データ量が少なくて済む。

24種類のフラグなら、224=16777216=3Byte。1レコードあたり3Byteで済む。

デメリット

  • 可読性が悪い
    • 検索が難しくなる
      • ビット計算しなければならない
        • どのScopeがどの桁のビットに該当するか知っている必要がある
          • 人為的ミスを誘発する罠になる

今回のような小規模なDBではデメリットのほうが大きい。また、現代ではHDD容量、処理速度、通信速度も改善されているが、ソフトウェアは複雑化していることから、ファイルサイズより可読性を優先したほうがよいかもしれない。

D. APIでAccessTokenを新規生成した結果のjsonを保存する

メリット

RDBMSのリレーションシップ的な制約を一切受けない。

  • 登録時にjson解析が不要
  • テーブル構造もjson列だけでいい

デメリット

メリットがそのままデメリットである。

  • select文で条件指定して絞り込めない

本末転倒。もはやリレーショナルDBではない。たとえば「repo権限を持ったレコードを抽出する」というユースケースSQL文で対応できない。1レコードずつjsonをパースして調べる羽目になる。

jsonファイル化してファイル名をAccessTokenにしたほうが早い気がするが、セキュリティ的に論外。でも、そのためだけにわざわざRDBMSjsonデータをぶち込むのもおかしい。リレーショナルな構造でないので間違っている気がする。

RDBMSでなくNoSQLなデータベースシステムを探したほうがいいか。jsonをDB化できるものとなると、MongoDBだろうか。しかし、今回は小規模のため組込用の小さなDBのほうが好都合。組込用DBは実質SQLite一択。しかしSQLitejson非対応。ふつうのリレーションシップDBである。よって、jsonデータをテキストとして登録しPythonでパースすることになる。結果、select文だけでは絞り込めない。

どれを使うか

Aはカラム名にコロンを含むため使用不可。名前を変えてしまうのも手だが自分が管理しているわけではない部分は親元に従わないと混乱しそうなので却下。

必然的にB,C,Dのどれか。C,Dはデメリットが大きすぎる。

検索のしやすさ的にBがマシか。LIKE句での検索を確認できたらBにしたい。

ここまで考える必要あるの?

ある。

もっと楽な方法があるはず、どうにか手抜きしたい、と思う。しかし、セキュリティ的に妥協できる範囲が限られるため仕方ない。

AccessTokenハードコーディングでよくね?

ダメ。以前自分で漏洩するミスを犯したから。

repoスコープ持ちTokenだけ登録して使えばよくね?

リポジトリ作成するだけならそれでOK。

しかしリポジトリ削除のときはdelete_repoスコープだけを与えたTokenを使いたい。

他にも、将来APIを使いこなせるようになったら、Tokenを使い分けたくなると思われる。セキュリティ的に。今のうちにしっかりやっておくのが吉。

全権限を持たせたToken使えばよくね?

漏洩したときが大変だからダメ。

一元管理したらセキュリティ的に意味なくね?

せっかく権限を最小化したTokenを作っても、一元管理したらセキュリティ的に意味なくね?(だったら全権限を持たせたToken使って楽すればよくね?)

確かにDBファイルもってかれたらヤバい。でも自動化&ハードコーディングによる漏洩防止することを優先したいので一元管理する。

それでも全権限をもったTokenを使わない理由は、通信時にTokenが盗まれたときの損害を最小化するため。

所感

セキュリティのせいで面倒になるのはWebAPIの宿命。自動化とセキュリティのトレードオフ。何度も同じこと言ってる気がする。