Google Cloud blog が公開しているユーザーアカウント、認証、パスワード管理に関する 13 のベストプラクティス2021 年版（以下、元記事）を参考にして、特にデータベースに特化した実装案を考えてみる。

認証要素の分離と内部グローバル ID の活用

元記事では、データベース設計のベストプラクティスとして「柔軟なユーザー管理のために認証要素を分離すること」と「そのためには内部グローバル ID を活用すること」が紹介されている。

上記の要素について言及している箇所を抜粋する。

ユーザー ID とユーザーアカウントのコンセプトを分離する。（中略）適切に設計されたユーザー管理システムでは、ユーザーのプロファイルの各部分間の結合度が低く、凝集度が高くなる。（中略）各ユーザーに抽象的な内部グローバル ID を設定して、その ID を介してユーザーのプロファイルと1つ以上の認証データセットを関連付ける

満たすべき要素を具体的に箇条書きするとこうなる。

1人のユーザー（= 人間）に users テーブルのレコードが1つ対応する
users テーブルは内部グローバルな ID をカラムとして持つ
認証要素はこの ID を通して users テーブルと紐づく

ここでいう認証要素とは一例としてメールアドレス/電話番号/SNS連携のこと。さらに、個々の認証要素は下記のような柔軟性があるべきと書かれている。

メールアドレスをユーザーの認証要素と切り離し、変更可能にする
電話番号やユーザー名も同様に変更可能にする
複数の ID を単一のユーザーアカウントにリンク可能にする

カラムが増えていく設計は避ける

「すべてを1つのレコード内に積み重ねること」については否定的に書かれている。つまり、データベースを適切に正規化すべきで、下記のようなテーブル設計は避けろということだと思う。

悪いテーブル設計の例

Table users {
  id bigint [primary key]
  name varchar
  email varchar
  phone varchar
  encrypted_password varchar
  zip_code varchar
  country varchar
  city varchar
  ...
  created_at timestamp
  updated_at timestamp
}

ベストプラクティスに沿ってテーブルを設計する

ここまでの意図を踏まえてテーブルを設計していく。要点を一言でまとめると「内部グローバルな ID でユーザーと認証要素を紐づける」というだけなのでやることはとても単純。

注：すべてのテーブルにサロゲートキーとしての id カラム、作成/更新の日時としての created_at カラム、updated_at カラムがあるが、これは作者の好みであり元記事で紹介されているベストプラクティスとは関係が無い。

users テーブル

Table users {
  id bigint [primary key]
  uid varchar [unique]
  created_at timestamp
  updated_at timestamp
}

users テーブルは内部グローバルな ID として uid カラムを持つ。bc85be のような独自の小さい文字列で十分なのか、UUIDであるべきなのかはこの ID の利用のされ方による。

names テーブル

Table names {
  id bigint [primary key]
  user_uid varchar
  name varchar
  primary boolean
  created_at timestamp
  updated_at timestamp
}

Ref: users.uid < names.user_uid

users テーブルと names テーブルは一対多の関係になり、そのリレーションは user_uid カラムで実現されている。

ユーザー名は users テーブルの1つのカラムとして実装する事例が多いと思うが、1人のユーザーに複数のユーザー名を持たせたい場合はユーザー名のみを別テーブルに分離することになる。

元記事では「複数のユーザー名の中から、メインで使うものを選択できること」についても言及されており、それを実現するために primary カラムを追加している。

不正アクセス対策として「誰かが使ったユーザー名は再利用不可」を機能追加するなら、一例として used_names テーブルで過去に利用されたユーザー名と期間を記録することで実現できる。また、「ユーザー名の変更回数の制限」を機能追加するなら、一例として activities テーブルでユーザー名変更イベントを記録することで実現できる。

emails テーブル

Table emails {
  id bigint [primary key]
  user_uid varchar
  email varchar
  confirmation_token varchar
  confirmation_sent_at timestamp
  confirmed_at timestamp
  created_at timestamp
  updated_at timestamp
}

Ref: users.uid < emails.user_uid

users テーブルと emails テーブルは一対多の関係になり、そのリレーションは user_uid カラムで実現されている。

この emails テーブルもこれ以降にでてくるテーブルも、基本的な考え方は names テーブルとほぼ同じになる。

phones テーブル

Table phones {
  id bigint [primary key]
  user_uid varchar
  phone varchar
  confirmation_token varchar
  confirmation_sent_at timestamp
  confirmed_at timestamp
  created_at timestamp
  updated_at timestamp
}

Ref: users.uid < phones.user_uid

users テーブルと phones テーブルは一対多の関係になり、そのリレーションは user_uid カラムで実現されている。

passwords テーブル

Table passwords {
  id bigint [primary key]
  user_uid varchar [unique]
  encrypted_password varchar
  reset_token varchar
  reset_sent_at timestamp
  created_at timestamp
  updated_at timestamp
}

Ref: users.uid - passwords.user_uid

パスワードは users テーブルの1つのカラムとして実装する事例が多いと思うが、セキュリティ上の理由でパスワード情報のみを別ホストのデータベースに分離することまで想定すると、passwords テーブルを独立して用意すると都合がよい。

別の観点として、SNS連携ログインのみを利用するユーザーはパスワードを登録しないことがありうる。こういう場合も別テーブルにしておいた方が null だらけのカラムを無くすという意味ですっきりした実装になる。

注：encrypted_password カラムにはハッシュ化されたパスワードとソルトを保存する。私がよく利用する devise と Rails の組み合わせであれば標準でこのような形式になる。ペッパーの設定も config.pepper として用意されている。

providers テーブル

Table providers {
  id bigint [primary key]
  user_uid varchar
  name varchar // Google, Facebook, ...
  auth_result json // {user_info: {profile: ...}, credential: {access_token: ..., secret: ...}}
  created_at timestamp
  updated_at timestamp
}

Ref: users.uid < providers.user_uid

SNS連携ログインのためのテーブル。

まとめ

Google Cloud blog で提案されているベストプラクティスを採用してデータベースを設計してみた。特に大事な点として「認証要素の分離と内部グローバル ID の活用」を取り上げ、これに焦点を当てたテーブル設計になっている。

所感

先を見越したテーブル設計になっている反面、人によってはオーバーエンジニアリングという印象を持ちそう。

小さなアプリケーションで最初からこの設計を採用するのは少しハードルが高い気がするが、規模が大きくなるにつれ似たような変更をしていくことはよくある話なので、そこまで想定しているのであれば最初からこの設計にしてもよいかも。