現在利用中のKeycloakに関して、「特定のレルム・権限に係るユーザーの情報をDBから直接取得してみたい」という場面に遭遇しました。
その際に調べた内容についての雑記・メモとなります。
まずは関連しそうな部分のデータ構造を観察
keycloakのDBがどのような構造をしているかを把握していない状態からスタートしましたので、
まずはそちらの観察から始めてみる事にしてみました。
テーブル・カラムの名称、中身から軽くアタリを付けていく形で探りを入れてみます
※当記事の各種SQL文は全てAurora Serverless V1(MySQL互換5.7)に対してクエリエディタを利用する形で実行しています🙏
-- どんなテーブルが有るか確認
SELECT * FROM information_schema.tables WHERE TABLE_SCHEMA = 'keycloak';
-- (例：USER_ENTITY)テーブルがどんな構造をしているか確認
SHOW CREATE TABLE USER_ENTITY
SELECT * FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = 'keycloak' AND TABLE_NAME = 'USER_ENTITY';
-- 実際にどんな値が入っているかお試しで見てみる
SELECT * FROM USER_ENTITY LIMIT 3
簡易ER図(のようなもの)
観察した結果を一旦整理のために軽くまとめてみます。
Keycloak DBは広大だわ...(少佐 全93テーブル！)となりましたので、今回必要となりそうだと感じた部分のみ、ごくごく一部を調査・抜粋しています。
観察を進める過程で外部キー制約こそ無いものの、名称や値の雰囲気からして結合に利用出来そうなカラム(?)が幾つか見受けられましたので、それらは点線での仮のメモを行ってみました。
今回やりたかったことは以下の4テーブルの主キー及び外部キー(＋ようなカラム)などを活用することで達成出来そうです。
USER_ENTITY(ユーザー)
CREATE TABLE `USER_ENTITY` (
`ID` varchar (36) NOT NULL
, `EMAIL` varchar (255) DEFAULT NULL
, `EMAIL_CONSTRAINT` varchar (255) DEFAULT NULL
, `EMAIL_VERIFIED` bit (1) NOT NULL DEFAULT b '0'
, `ENABLED` bit (1) NOT NULL DEFAULT b '0'
, `FEDERATION_LINK` varchar (255) DEFAULT NULL
, `FIRST_NAME` varchar (255) DEFAULT NULL
, `LAST_NAME` varchar (255) DEFAULT NULL
, `REALM_ID` varchar (255) DEFAULT NULL
, `USERNAME` varchar (255) DEFAULT NULL
, `CREATED_TIMESTAMP` bigint(20) DEFAULT NULL
, `SERVICE_ACCOUNT_CLIENT_LINK` varchar (255) DEFAULT NULL
, `NOT_BEFORE` int (11) NOT NULL DEFAULT '0'
, PRIMARY KEY (`ID`)
, UNIQUE KEY `UK_DYKN684SL8UP1CRFEI6ECKHD7` (`REALM_ID`, `EMAIL_CONSTRAINT`)
, UNIQUE KEY `UK_RU8TT6T700S9V50BU18WS5HA6` (`REALM_ID`, `USERNAME`)
, KEY `IDX_USER_EMAIL` (`EMAIL`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8
KEYCLOAK_ROLE(権限)
CREATE TABLE `KEYCLOAK_ROLE` (
`ID` varchar (36) NOT NULL
, `CLIENT_REALM_CONSTRAINT` varchar (255) DEFAULT NULL
, `CLIENT_ROLE` bit (1) DEFAULT NULL
, `DESCRIPTION` varchar (255) DEFAULT NULL
, `NAME` varchar (255) DEFAULT NULL
, `REALM_ID` varchar (255) DEFAULT NULL
, `CLIENT` varchar (36) DEFAULT NULL
, `REALM` varchar (36) DEFAULT NULL
, PRIMARY KEY (`ID`)
, UNIQUE KEY `UK_J3RWUVD56ONTGSUHOGM184WW2-2` (`NAME`, `CLIENT_REALM_CONSTRAINT`)
, KEY `IDX_KEYCLOAK_ROLE_CLIENT` (`CLIENT`)
, KEY `IDX_KEYCLOAK_ROLE_REALM` (`REALM`)
, CONSTRAINT `FK_6VYQFE4CN4WLQ8R6KT5VDSJ5C` FOREIGN KEY (`REALM`) REFERENCES `REALM` (`ID`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8
USER_ROLE_MAPPING(ユーザーと権限間の連関エンティティ)
CREATE TABLE `USER_ROLE_MAPPING` (
`ROLE_ID` varchar (255) NOT NULL
, `USER_ID` varchar (36) NOT NULL
, PRIMARY KEY (`ROLE_ID`, `USER_ID`)
, KEY `IDX_USER_ROLE_MAPPING` (`USER_ID`)
, CONSTRAINT `FK_C4FQV34P1MBYLLOXANG7B1Q3L` FOREIGN KEY (`USER_ID`) REFERENCES `USER_ENTITY` (`ID`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8
REALM(レルム)
CREATE TABLE `REALM` (
`ID` varchar (36) NOT NULL
, `ACCESS_CODE_LIFESPAN` int (11) DEFAULT NULL
, `USER_ACTION_LIFESPAN` int (11) DEFAULT NULL
, `ACCESS_TOKEN_LIFESPAN` int (11) DEFAULT NULL
, `ACCOUNT_THEME` varchar (255) DEFAULT NULL
, `ADMIN_THEME` varchar (255) DEFAULT NULL
, `EMAIL_THEME` varchar (255) DEFAULT NULL
, `ENABLED` bit (1) NOT NULL DEFAULT b '0'
, `EVENTS_ENABLED` bit (1) NOT NULL DEFAULT b '0'
, `EVENTS_EXPIRATION` bigint(20) DEFAULT NULL
, `LOGIN_THEME` varchar (255) DEFAULT NULL
, `NAME` varchar (255) DEFAULT NULL
, `NOT_BEFORE` int (11) DEFAULT NULL
, `PASSWORD_POLICY` varchar (2550) DEFAULT NULL
, `REGISTRATION_ALLOWED` bit (1) NOT NULL DEFAULT b '0'
, `REMEMBER_ME` bit (1) NOT NULL DEFAULT b '0'
, `RESET_PASSWORD_ALLOWED` bit (1) NOT NULL DEFAULT b '0'
, `SOCIAL` bit (1) NOT NULL DEFAULT b '0'
, `SSL_REQUIRED` varchar (255) DEFAULT NULL
, `SSO_IDLE_TIMEOUT` int (11) DEFAULT NULL
, `SSO_MAX_LIFESPAN` int (11) DEFAULT NULL
, `UPDATE_PROFILE_ON_SOC_LOGIN` bit (1) NOT NULL DEFAULT b '0'
, `VERIFY_EMAIL` bit (1) NOT NULL DEFAULT b '0'
, `MASTER_ADMIN_CLIENT` varchar (36) DEFAULT NULL
, `LOGIN_LIFESPAN` int (11) DEFAULT NULL
, `INTERNATIONALIZATION_ENABLED` bit (1) NOT NULL DEFAULT b '0'
, `DEFAULT_LOCALE` varchar (255) DEFAULT NULL
, `REG_EMAIL_AS_USERNAME` bit (1) NOT NULL DEFAULT b '0'
, `ADMIN_EVENTS_ENABLED` bit (1) NOT NULL DEFAULT b '0'
, `ADMIN_EVENTS_DETAILS_ENABLED` bit (1) NOT NULL DEFAULT b '0'
, `EDIT_USERNAME_ALLOWED` bit (1) NOT NULL DEFAULT b '0'
, `OTP_POLICY_COUNTER` int (11) DEFAULT '0'
, `OTP_POLICY_WINDOW` int (11) DEFAULT '1'
, `OTP_POLICY_PERIOD` int (11) DEFAULT '30'
, `OTP_POLICY_DIGITS` int (11) DEFAULT '6'
, `OTP_POLICY_ALG` varchar (36) DEFAULT 'HmacSHA1'
, `OTP_POLICY_TYPE` varchar (36) DEFAULT 'totp'
, `BROWSER_FLOW` varchar (36) DEFAULT NULL
, `REGISTRATION_FLOW` varchar (36) DEFAULT NULL
, `DIRECT_GRANT_FLOW` varchar (36) DEFAULT NULL
, `RESET_CREDENTIALS_FLOW` varchar (36) DEFAULT NULL
, `CLIENT_AUTH_FLOW` varchar (36) DEFAULT NULL
, `OFFLINE_SESSION_IDLE_TIMEOUT` int (11) DEFAULT '0'
, `REVOKE_REFRESH_TOKEN` bit (1) NOT NULL DEFAULT b '0'
, `ACCESS_TOKEN_LIFE_IMPLICIT` int (11) DEFAULT '0'
, `LOGIN_WITH_EMAIL_ALLOWED` bit (1) NOT NULL DEFAULT b '1'
, `DUPLICATE_EMAILS_ALLOWED` bit (1) NOT NULL DEFAULT b '0'
, `DOCKER_AUTH_FLOW` varchar (36) DEFAULT NULL
, `REFRESH_TOKEN_MAX_REUSE` int (11) DEFAULT '0'
, `ALLOW_USER_MANAGED_ACCESS` bit (1) NOT NULL DEFAULT b '0'
, `SSO_MAX_LIFESPAN_REMEMBER_ME` int (11) NOT NULL
, `SSO_IDLE_TIMEOUT_REMEMBER_ME` int (11) NOT NULL
, `DEFAULT_ROLE` varchar (255) DEFAULT NULL
, PRIMARY KEY (`ID`)
, UNIQUE KEY `UK_ORVSDMLA56612EAEFIQ6WL5OI` (`NAME`)
, KEY `IDX_REALM_MASTER_ADM_CLI` (`MASTER_ADMIN_CLIENT`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8
データの取得
念のために事前に対象テーブルのデータ量などを調べたところ、小規模利用ということもあり非常に小さいものでした。
そのためシンプルに3表を結合して、LIMIT及びOFFSET句なども使用せずに一度に全データを取得する方針としました。
REALMテーブル・KEYCLOAK_ROLEテーブルを基に今回対象となる権限のIDを調べた上で、
以下のようなSQL文を作成したところ...無事にお目当てのデータを取得する事が出来ました🍀
select a.* from USER_ENTITY a join USER_ROLE_MAPPING b
on a.ID = b.USER_ID join KEYCLOAK_ROLE c
on b.ROLE_ID = c.ID
where
c.ID = 'xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx'
-- 現在の利用法ではKEYCLOAK_ROLEのIDが事実上「どのレルムであるか」という情報を内包していたため、
-- AND a.REALM_ID = 'xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx' といった指定は意外にも不要となりました
番外編：パフォーマンスについて少し考えてみる練習
個人的なSQLのパフォーマンスについての学習や仮説立ての練習として、
作成中のSQL文について自分で色々考えてみる
↓↓
オプティマイザ大先生による実行計画と比較してみる
という取り組みをお試しで行ってみています。
言うまでもなく私よりもオプティマイザ大先生の方が圧倒的に強いため、大先生の見解を「正解」ということにしています
実行計画はSQLの頭に EXPLAIN を付けることで簡単に確認する事が出来ます。
今回は以下のようなものをぼんやりと考えてみました(結論から言うと間違っています...😢)
-
1. USER_ENTITY(IDカラム)とUSER_ROLE_MAPPING(USER_IDカラム)間の内部結合
- USER_ENTITYテーブルのIDカラム
- 主キーである
- ⇒主キーに対してデフォルトで作成されるインデックスが適用される🍀
- 主キーである
- USER_ROLE_MAPPINGのUSER_ID
- ⇒PRIMARY KEY (
ROLE_ID,USER_ID)と定義されている複合主キーの一部である- この場合、ROLE_IDのみ若しくはROLE_ID⇒USER_IDという順序でアクセスされる場合にしか、デフォルトのインデックスは作用しない...😢
- しかし別インデックス KEY
IDX_USER_ROLE_MAPPING(USER_ID)が作成されているため、こちらが適用されそう🍀(個人的にこのIDX作成はなるほどな～～！！と感じました😲)
- ⇒PRIMARY KEY (
- ⇒良さそう🌸
- USER_ENTITYテーブルのIDカラム
-
2. USER_ROLE_MAPPING(ROLE_IDカラム)とKEYCLOAK_ROLE(IDカラム)間の内部結合
- USER_ROLE_MAPPINGのROLE_ID
- ⇒PRIMARY KEY (
ROLE_ID,USER_ID)と定義されている複合主キーの一部である- ⇒ROLE_IDのみ若しくはROLE_ID⇒USER_IDという順序でアクセスされる場合、デフォルトのインデックスが適用されるため◎🍀(先ほどの名誉回復㊗)
- ⇒PRIMARY KEY (
- KEYCLOAK_ROLEのID
- 主キーである
- ⇒主キーに対してデフォルトで作成されるインデックスが適用される🍀
- 主キーである
- ⇒良さそう🌸
- USER_ROLE_MAPPINGのROLE_ID
-
3. KEYCLOAK_ROLEに対しての、IDカラムを条件とした選択(WHERE句)
- 主キーに対してデフォルトで作成されるインデックスが適用される🍀
- ⇒良さそう🌸
オプティマイザ大先生の見解
結論から言うと全体的に順番が「逆」になっていました😢...というのも今回のSQLを改めて観察してみると...
EXPLAIN select a.* from USER_ENTITY a join USER_ROLE_MAPPING b
on a.ID = b.USER_ID join KEYCLOAK_ROLE c
on b.ROLE_ID = c.ID
where
c.ID = 'xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx'
最後段のWHERE句から実行してKEYCLOAK_ROLEとUSER_ROLE_MAPPINGを結合、更にUSER_ENTITYを結合...とSQL文を後ろから先頭へと処理した方が何だか効率が良さそうですよね😲
(※KEYCLOAK_ROLEテーブルの主キーに対するWHERE句、さらに条件は文字列となると、初段はインデックスの効いた検索でさらに結果行も1件となりそうな気配がしますね😲)
また、逆順となるため全ての段階がPKのデフォルトインデックスでカバーできることになるようです...(まさかここまで読んで複合PKをあの順に？😲)
オプティマイザ大先生による実行計画
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | SIMPLE | c | NULL | const | PRIMARY | PRIMARY | 110 | const | 1 | 100 | Using index |
| 1 | SIMPLE | b | NULL | ref | PRIMARY,IDX_USER_ROLE_MAPPING | PRIMARY | 767 | const | 33 | 100 | Using index |
| 1 | SIMPLE | a | NULL | eq_ref | PRIMARY | PRIMARY | 110 | keycloak.b.USER_ID | 1 | 100 | NULL |
実行計画について教えて課金ChatGPT師匠のコーナー
※こちらは私の理解・時間不足で内容をしっかりと検証出来ておりませんこと予めご了承ください🙏
-
テーブル c (
KEYCLOAK_ROLE):- id: 1 - これはクエリの最初の部分を示しています。
- select_type: SIMPLE - サブクエリや複雑なクエリの一部ではないことを示します。
-
table: c -
KEYCLOAK_ROLEテーブルを指します。 - type: const - このタイプは、クエリがIDで一意の行（または少数の行）を選択していることを示しています。効率的なアクセス方法です。
- possible_keys: PRIMARY - クエリ実行時に使用可能なインデックス（ここではプライマリキー）。
- key: PRIMARY - 実際に使用されるプライマリキーインデックス。
- key_len: 110 - 使用されるインデックスの長さ。
- ref: const - 固定値（ここでは特定のID）を使用して行を選択しています。
- rows: 1 - このテーブルから1行だけが選択されると推定されています。
- filtered: 100 - 選択された行の100％が結果に使用されます。
- Extra: Using index - インデックスのみを使用してこの部分のクエリを解決していることを意味します。
-
テーブル b (
USER_ROLE_MAPPING):- table: b
- type: ref - これは、前のテーブルからの結果（または定数）を使用して、このテーブルから行を参照することを意味します。
- possible_keys: "PRIMARY, IDX_USER_ROLE_MAPPING" - 使用可能なインデックス。
- key: PRIMARY - プライマリキーインデックスが使用されています。
- rows: 33 - 33行がこのテーブルから選択されると推定されています。
- Extra: Using index - 再び、インデックスのみを使用しています。
-
テーブル a (
USER_ENTITY):- table: a
- type: eq_ref - 各結合行について、一意の行がテーブルから選択されることを示しています。これは非常に効率的なアクセス方法です。
- possible_keys: PRIMARY - 使用可能なプライマリキー。
- key: PRIMARY - プライマリキーが使用されています。
-
ref: keycloak.b.USER_ID - 前のテーブルの
USER_ID列を参照しています。 - rows: 1 - このテーブルから1行だけが選択されると推定されています。
この実行計画からわかることは、MySQLが非常に効率的な方法で各テーブルにアクセスしており、主にプライマリキーを使用して高速な検索を行っているということです。また、各テーブルからの行数が少ないことから、このクエリは高速に実行される可能性が高いです。
補足として、後段のwhere句を抜いた形であれば最初に立てた仮説に近い実行計画を立てている様子が見受けられました🍀(SQL文を上から下に処理していってIDX_USER_ROLE_MAPPINGを活用するスタイル)
EXPLAIN select a.* from USER_ENTITY a join USER_ROLE_MAPPING b
on a.ID = b.USER_ID join KEYCLOAK_ROLE c
on b.ROLE_ID = c.ID
しかし、最初にUSER_ENTITYを持ってくるフェーズで主キーのインデックスを使用せずに全件走査しているという違いがあり、修行不足によりこれが何故だかが分かりませんでした...(行選択ではなく単純に全部持ってくるだけだから とか 件数が少ないから などです..かね...?😢)
オプティマイザ大先生による実行計画(where無しver)
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | SIMPLE | a | NULL | ALL | PRIMARY | NULL | NULL | NULL | 66 | 100 | NULL |
| 1 | SIMPLE | b | NULL | ref | PRIMARY,IDX_USER_ROLE_MAPPING | IDX_USER_ROLE_MAPPING | 110 | keycloak.a.ID | 1 | 100 | Using index |
| 1 | SIMPLE | c | NULL | eq_ref | PRIMARY | PRIMARY | 110 | keycloak.b.ROLE_ID | 1 | 100 | Using where; Using index |
教えて課金ChatGPT師匠のコーナー(where無しver)
※こちらは私の理解・時間不足で内容をしっかりと検証出来ておりませんこと予めご了承ください🙏
このSQLクエリの実行計画を解析することで、どのようにMySQLがデータにアクセスしているかを理解することができます。ここでも、それぞれの行がクエリの異なる部分を表しています。それぞれのステップについて解説します：
-
テーブル a (
USER_ENTITY):- id: 1 - これはクエリの最初の部分を示しています。
- select_type: SIMPLE - サブクエリや複雑なクエリの一部ではないことを示します。
-
table: a -
USER_ENTITYテーブルを指します。 - type: ALL - このタイプは、フルテーブルスキャンを意味します。つまり、インデックスを使用せずにテーブルの全行をスキャンします。
- possible_keys: PRIMARY - クエリ実行時に使用可能なインデックス（ここではプライマリキー）が示されていますが、実際には使用されていません。
- rows: 66 - このテーブルから66行がスキャンされると推定されています。
- Extra: NULL - 特に追加情報はありません。
-
テーブル b (
USER_ROLE_MAPPING):- table: b
- type: ref - これは、前のテーブルからの結果（または定数）を使用して、このテーブルから行を参照することを意味します。
- possible_keys: "PRIMARY, IDX_USER_ROLE_MAPPING" - 使用可能なインデックス。
-
key: IDX_USER_ROLE_MAPPING -
IDX_USER_ROLE_MAPPINGインデックスが使用されています。 - key_len: 110 - 使用されるインデックスの長さ。
-
ref: keycloak.a.ID - 前のテーブルの
ID列を参照しています。 - rows: 1 - このテーブルから1行が選択されると推定されています。
- Extra: Using index - 再び、インデックスのみを使用しています。
-
テーブル c (
KEYCLOAK_ROLE):- table: c
- type: eq_ref - 各結合行について、一意の行がテーブルから選択されることを示しています。これは非常に効率的なアクセス方法です。
- possible_keys: PRIMARY - 使用可能なプライマリキー。
- key: PRIMARY - プライマリキーが使用されています。
-
ref: keycloak.b.ROLE_ID - 前のテーブルの
ROLE_ID列を参照しています。 - rows: 1 - このテーブルから1行だけが選択されると推定されています。
- Extra: Using where; Using index - このテーブルはインデックスを使用しており、さらにWHERE句の条件も使用してフィルタリングしています。
この実行計画の重要な点は、最初のテーブルaでフルテーブルスキャンが行われていることです。これはパフォーマンスに影響を与える可能性があり、インデックスの追加やクエリの改善を検討する余地があります。他の2つのテーブルではインデックスが効率的に使用されており、特に問題はないようです。
感想・気付きなど
- これまでDBと言われるとRailsでの使用を前提とした形(自動生成されたschema.rbの中身のような..)がパッと思い浮かぶ状態でした。そのため今回のDB観察を通して、例えば複合主キーの実物を初めて目の当たりにするなど多くの学びを得られた気がします。
- Railsにおいて初歩的・一般的な形(?)ではあまり遭遇したことのない、「外部キー制約はないが、明らかに結合用と思われるカラムが散見される」点も個人的に興味深く感じられました。
- FK制約が「欠けているように感じる」点についてはパフォーマンスやデータ登録の順序・構造の柔軟性などを、私には想像もつかないような高いレベルで意識して設計されている為かとは予想するのですが、Rails生まれRails育ち(？)で修行中の身としては「なんでついてないんだろう」といった気持ち・感覚が先行してきました(批判的な意図は全くなくシンプルに気付きとしてです🙏)。keycloakのコード部分とこれらのテーブル群がどう噛み合って動いているのかなど非常に興味が湧きましたので、今後機会が有れば追っていきたいと感じました。
- 課金ChatGPT師匠 & オプティマイザ大先生バケモノすぎ問題
- 与えられた条件をもとに最適に近いSQL文を作成出来る課金ChatGPT師匠 と
- 与えられたSQLを最適な形で実行しようとするオプティマイザ大先生 が肩を組む事で
- 修行中の身では本当に全く歯が立たない、ありえないほどに強いので
もうぼくは要らんのではないかと思う😢