[！] この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。
取り組みの背景
Apache Guacamoleは、ブラウザでのリモートデスクトップ接続を実現するオープンソースソフトウェアです。ユーザー情報（ユーザー名と接続先ホスト名）はデータベースで管理できるのですが、GuacamoleのWeb画面から一括で登録する機能がありません。大規模な利用で接続情報が多い場合、Web画面からひとつずつ設定するのは骨が折れます。
そこで、GuacamoleのWeb画面を使うことなく、複数のユーザー情報のデータをファイルからデータベースに同期する仕組みを作りました。
前提・要件
システムを構築するにあたって、以下の点を考慮します。
- Guacamoleは稼働中とする
- ユーザー名・接続先ホスト名から成るユーザー情報を、データベース（Maria DB）に接続情報として同期する
- ユーザー1人に対し、1台から3台の接続先ホスト名が存在する
- ユーザー情報は不定期に変化する
- ユーザー情報の変化に対応するため、システムは定期実行を想定
- 定期実行が前提のため、システムは全て自動化する
ユーザー情報は次のような書式のCSVファイルとして保存されており、ファイル名はuser_conf.csvとします。
| ユーザー名 | 接続先ホスト名1 | 接続先ホスト名2 | 接続先ホスト名3 |
|---|---|---|---|
| alpha | host-a.example.com | - | - |
| beta | host-b.example.com | host-c.example.com | - |
| gamma | host-a.example.com | host-d.example.com | host-e.example.com |
| delta | host-f.example.com | - | - |
[ユーザー名, 1台目の接続先ホスト名, 2台目の接続先ホスト名, 3台目の接続先ホスト名]として、1行に1ユーザーずつユーザー情報を記載します。なお、各ユーザーに対し必ず3台分の接続先ホスト名を定義し、データが存在しない場合は「-」とします。
接続情報の追加・変更・削除への対応
システムは大きく2段階で構成されます。
- ユーザー名・接続先ホスト名をデータベースに接続情報として登録
- 不要な接続情報をデータベースから削除
1では、ファイルに存在するユーザー情報を登録します。これは、ファイルに新規登録した、あるいは情報を更新したユーザー情報を登録するため、ユーザー情報の「追加」「変更」には対応できます。しかし、過去に存在し、現在は存在しないユーザー情報がデータベースに含まれたままとなります。すなわち、前回の登録処理時はファイルに記載されていたが、現在のファイルには記載がないというユーザー情報の「削除」に対応できません。そこで2では、データベースに登録されているユーザー情報とファイルに存在するユーザー情報を比較し、データベースにのみ存在するユーザー情報を削除します。この両段階を経ることで、データの同期を行うことができます。
スクリプトの実装
仕様・方針
まず、データを登録するデータベースのテーブルについて述べます。Guacamoleが定義しているテーブルは公式マニュアル ⧉から確認できますが、接続情報を登録するテーブルは以下になります。
| テーブル名 | 説明 |
|---|---|
| guacamole_entity | ユーザー名登録 |
| guacamole_user | ユーザーのパスワードを設定 |
| guacamole_connection | Guacamole認証後に表示される識別用の接続名とプロトコルの設定 |
| guacamole_connection_parameter | 接続名に対し接続先ホスト名を設定 |
| guacamole_connection_permission | 接続権限の設定 |
構成で述べた2段階の処理をシェルスクリプトで実装します。具体的には、次の手順から成ります。
- ファイルを読み込み、ユーザー名・接続先ホスト名を取得
- テーブル
guacamole_entityにユーザー名設定 - テーブル
guacamole_userにユーザーパスワードを設定- 手順2と3で設定したものを合わせてユーザーデータとする
- テーブル
guacamole_connectionguacamole_connection_parameterに接続データの設定 - テーブル
guacamole_connection_permissionに接続権限の設定 - テーブル
guacamole_entityguacamole_userguacamole_connection_permissionから不要なユーザーデータを削除 - テーブル
guacamole_connectionguacamole_connection_permissionguacamole_connection_parameterから不要な接続データを削除 - テーブル
guacamole_connection_permissionから不要な接続権限の削除
手順1～5がデータの登録、手順6～8がデータの削除に当たります。ファイルの書式を活かすため、データの登録処理はファイルの各行、つまり各ユーザー単位でのwhile文で行います。さらに、各ユーザーに対し複数の接続先ホスト名を扱うので、接続先ホスト名が絡む登録処理をfor文内で行います。
登録処理完了後、データの削除を行うのですが、この際の削除対象は「ユーザーデータ」「接続データ」「ユーザーデータと接続データの組」の3つです。これは、手順5の接続権限の設定にて、ユーザーデータと接続データを紐づけて登録するからです。削除処理については、登録処理完了後のデータベース内に存在するユーザー名・接続先ホスト名と元ファイルに存在するユーザー名・接続先ホスト名を比較し、データベースにのみ存在するデータを削除します。
サンプルコード
コード中の［SQLによる処理ブロック］は、それぞれ後述します。
#!/bin/bash
#作業ディレクトリの作成
tmp_dir=$(mktemp -d)
#一時ファイル変数
tmp_hosts=$(mktemp $tmp_dir/XXXXXX) #元ファイルに存在するユーザー名リスト
new_user_id=$(mktemp $tmp_dir/XXXXXX) #元ファイルに存在するユーザー名を対応するentity_idに変換出力したリスト
new_host_id=$(mktemp $tmp_dir/XXXXXX) #元ファイルに存在するホスト名を対応するconnection_idに変換出力したリスト
tmp_users=$(mktemp $tmp_dir/XXXXXX) #元ファイルに存在するホスト名リスト
user_host_id=$(mktemp $tmp_dir/XXXXXX) #元ファイルに存在する[ユーザー,ホスト名]を対応する[entity_id,connection_id]に変換したリスト
new_users=$(mktemp $tmp_dir/XXXXXX) #整理した、元ファイルに存在するユーザー名リスト
new_hosts=$(mktemp $tmp_dir/XXXXXX) #整理した、元ファイルに存在するホスト名リスト
old_users=$(mktemp $tmp_dir/XXXXXX) #DBに存在するユーザー名リスト
old_hosts=$(mktemp $tmp_dir/XXXXXX) #DBに存在するホスト名リスト
old_user_host_id=$(mktemp $tmp_dir/XXXXXX) #DBに存在する[entity_id,connection_id]のリスト
delete_users=$(mktemp $tmp_dir/XXXXXX) #削除するユーザー名リスト
delete_hosts=$(mktemp $tmp_dir/XXXXXX) #削除するホスト名リスト
delete_user_host_id=$(mktemp $tmp_dir/XXXXXX) #削除する[entity_id,connection_id]のリスト
#ファイルを行ごとに読み込む（IFSでカンマ区切り)
#ファイルが読み込みできた場合のみデータベースのへの登録処理
while IFS=',' read -r user host1 host2 host3
do
mysql -u "$MYSQL_USER" -p"$MYSQL_PASSWORD" "$MYSQL_DATABASE" <<EOF
# ユーザーの登録
[SQL文による処理ブロック #1]
# パスワードの設定
[SQL文による処理ブロック #2]
EOF
#ホスト名登録のループ
for host_name in $host1 $host2 $host3
do
#ホスト名が空ならばループを抜け、次の行（ユーザー）へ
if [ "$host_name" = "-" ]; then
break
else
#ホスト名を「.」で区切ると、先頭がそのまま PC名になる
PC_name=$(echo $host_name|cut -d "." -f 1)
mysql -u "$MYSQL_USER" -p"$MYSQL_PASSWORD" "$MYSQL_DATABASE" <<EOF
#接続の設定
[SQL文による処理ブロック #3]
#接続権限の設定
[SQL文による処理ブロック #4]
EOF
echo $host_name >> $tmp_hosts.csv
#削除処理用 entity_id の取得
mysql -u "$MYSQL_USER" -p"$MYSQL_PASSWORD" "$MYSQL_DATABASE" -e "SELECT entity_id FROM guacamole_entity WHERE name = '$user';" | sed -e 's/\t/","/g' | sed '1d' >> $new_user_id.csv
#削除処理用 connection_id の取得
mysql -u "$MYSQL_USER" -p"$MYSQL_PASSWORD" "$MYSQL_DATABASE" -e "SELECT connection_id FROM guacamole_connection_parameter WHERE parameter_value = '$host_name' ORDER BY connection_id DESC LIMIT 1;" | sed -e 's/\t/","/g' | sed '1d' >> $new_host_id.csv
fi
done
echo $user >> $tmp_users.csv
#最初のwhileで読み込む際、ファイル形式に応じた区切り文字を指定
done < <(cut -d "," $tmp_dir/user_conf.csv)
#元のユーザー情報をID変換した一時ファイルを作成
paste -d , $new_user_id.csv $new_host_id.csv > $user_host_id.csv
#ユーザー名リスト・ホスト名リストをソート&重複排除
sort $tmp_users.csv | uniq > $new_users.csv
sort $tmp_hosts.csv | uniq > $new_hosts.csv
#ユーザー名、ホスト名、[ユーザー,接続名]の組の順に、DB登録済データと元のファイルデータの差分から削除するデータリストを出力
mysql -u "$MYSQL_USER" -p"$MYSQL_PASSWORD" "$MYSQL_DATABASE" -e "SELECT name FROM guacamole_entity;" | sed -e 's/\t/","/g' | sed '1d' | sort | uniq > $old_users.csv
comm -23 --nocheck-order $old_users.csv $new_users.csv > $delete_users.csv
mysql -u "$MYSQL_USER" -p"$MYSQL_PASSWORD" "$MYSQL_DATABASE" -e "SELECT parameter_value FROM guacamole_connection_parameter;" | sed -e 's/\t/","/g' | sed '1d' | sort | uniq > $old_hosts.csv
comm -23 --nocheck-order $old_hosts.csv $new_hosts.csv > $delete_hosts.csv
mysql -u "$MYSQL_USER" -p"$MYSQL_PASSWORD" "$MYSQL_DATABASE" -e "SELECT entity_id, connection_id FROM guacamole_connection_permission;" | sed -e 's/\t/","/g' | sed -e 's/"//g' | sed '1d' > $old_user_host_id.csv
comm -23 --nocheck-order $old_user_host_id.csv $user_host_id.csv > $delete_user_host_id.csv
while read -r delete_user
do
mysql -u "$MYSQL_USER" -p"$MYSQL_PASSWORD" "$MYSQL_DATABASE" <<EOF
#作成した削除リストに従ってユーザーデータの削除
[SQL文による処理ブロック #5]
EOF
done < $delete_users.csv
while read -r delete_host
do
mysql -u "$MYSQL_USER" -p"$MYSQL_PASSWORD" "$MYSQL_DATABASE" <<EOF
#作成した削除リストに従って接続データの削除
[SQL文による処理ブロック #6]
EOF
done < $delete_hosts.csv
while IFS=',' read -r delete_entity_id delete_connection_id
do
mysql -u "$MYSQL_USER" -p"$MYSQL_PASSWORD" "$MYSQL_DATABASE" <<EOF
#作成した削除リストに従って[ユーザーデータ, 接続データ]の組データの削除
[SQL文による処理ブロック #7]
EOF
done < $delete_user_host_id.csv
#作業ディレクトリの削除
rm -rf $tmp_dir
ユーザー名にはentity_id、接続名にはconnection_idとそれぞれ一意のIDが割り当てられるので、それらを使ってホスト名や接続権限の設定、または削除処理を行います。
各処理ブロックをSQL文と共に説明します。見出しの番号はサンプルコード内のブロック番号と対応しています。
1. ユーザー名の登録
テーブルguacamole_entityにユーザー名を登録します。登録された各ユーザー名に対し、entity_idが自動で割り当てられます。
構文はREPLACE INTOを使うことで、データの新規登録・変更に対応します。
REPLACE INTO guacamole_entity (name, type)
VALUES ('$user', 'USER');
2. パスワードの設定
テーブルguacamole_userでは、Guacamole認証用のパスワードをユーザーに対して設定します。具体的には、現在登録処理中のユーザー名に割り当てられたentity_idに対し、ハッシュ化したパスワードの設定します。
OpenID Connect等に対応した認証基盤と連携している場合、ここで設定するパスワードは必要ありません。ただし、ハッシュ化されたパスワードを格納するカラムpassword_hashがNOT NULLなため、全ユーザーとも空文字列をハッシュ化したものを格納しています。
REPLACE INTO guacamole_user (
entity_id,
password_hash,
password_date
)
SELECT
entity_id,
UNHEX(SHA2('', 256)),
CURRENT_TIMESTAMP
FROM guacamole_entity
WHERE
name = '$user'
AND type = 'USER'
3. 接続の設定
テーブルguacamole_connectionでは、Guacamole認証後に表示される接続名と、接続のプロトコルを設定します。この接続名に対して、connection_idが自動で割り当てられます。テーブルguacamole_connection_parameterでは、そのconnection_idに対して接続先ホスト名を設定します。この両テーブルへのデータ登録により、端末への接続設定が完了します。
REPLACE INTO guacamole_connection (
connection_name,
protocol
)
VALUES ('$PC_name', 'rdp');
SELECT @connection_id := connection_id
FROM
guacamole_connection
WHERE
connection_name = '$PC_name'
AND
parent_id IS NULL;
REPLACE INTO
guacamole_connection_parameter
VALUES (
@connection_id,
'hostname',
'$host_name'
);
4. 接続権限の設定
テーブルguacamole_connection_permissionではentity_idで特定されるユーザーに対し、3で設定した接続データへのアクセス許可を与えます。READ権限ではconnection_id、つまり接続に関するデータを読み取り、テーブルguacamole_connection_parameterで設定した端末へ接続が行えるようになります。すなわち、entity_idとconnection_idをREAD権限で紐づけることで、entity_idに対応するユーザーにconnection_idに対応する端末への接続権限を設定することができます。
SELECT @entity_id := entity_id
FROM
guacamole_entity
WHERE
name = '$user';
REPLACE INTO guacamole_connection_permission (
entity_id,
connection_id,
permission
)
VALUES (
@entity_id,
@connection_id,
'READ'
);
5. ユーザーデータの削除
ユーザーデータはentity_idに関連付けられており、entity_idを削除することで不要なユーザーデータを一括削除することができます。entity_idは、テーブルguacamole_entityでユーザー名に対応しているため、削除対象のユーザーデータをユーザー名でリスト化し、各ユーザー名に対応するentity_idをWHERE句で削除します。
削除対象のリストファイル$delete_users.csvには、データベースに存在するユーザー名リスト$old_users.csvとwhile文内でファイルから登録したユーザー名のリスト$new_users.csvを比較し、$old_users.csvにのみ存在するユーザー名を出力しています。
DELETE FROM guacamole_user
WHERE
entity_id IN (
SELECT entity_id FROM guacamole_entity WHERE name = '$delete_user'
);
DELETE FROM guacamole_connection_permission
WHERE
entity_id IN (
SELECT entity_id FROM guacamole_entity WHERE name = '$delete_user'
);
DELETE FROM guacamole_entity WHERE name = '$delete_user';
6. 接続データの削除
接続データはconnection_idに関連付けられており、connection_idを削除することで不要な接続データを一括削除することができます。connection_idはテーブルguacamole_connection_parameterにて接続先ホスト名との対応関係があるため、削除対象の接続データを接続先ホスト名でリスト化し、各接続先ホスト名に対応するconnection_idをWHERE句で削除します。
削除対象のリストファイル$delete_hosts.csvには、データベースに存在するユーザー名リスト$old_hosts.csvとwhile文内でファイルから登録したユーザー名のリスト$new_hosts.csvを比較し、$old_hosts.csvにのみ存在するユーザー名を出力しています。
DELETE FROM guacamole_connection
WHERE
connection_id IN (
SELECT connection_id FROM guacamole_connection_parameter WHERE parameter_value = '$delete_host'
);
DELETE FROM guacamole_connection_permission
WHERE
connection_id IN (
SELECT connection_id FROM guacamole_connection_parameter WHERE parameter_value = '$delete_host'
);
DELETE FROM guacamole_connection_parameter WHERE parameter_value = '$delete_host';
7. [ユーザーデータ, 接続データ]の組データの削除
最後は接続権限について、テーブルguacamole_connection_permissionから[ユーザーデータ, 接続データ]の組データ、すなわち[entity_id, connection_id]の組データを削除します。接続権限のデータについては、ユーザーデータに対するentity_idや接続データに対するconnection_idのように、データ登録時に関連付けられるIDがないため、データの削除には登録に使ったentity_id connection_idを直接指定します。ユーザーデータの削除、接続データの削除でもユーザー名、接続先ホスト名それぞれを比較して、テーブルguacamole_connection_permissionからentity_id connection_idの削除は行っています。しかし、これだけでは以下のようにユーザー名と接続先ホスト名の組み合わせのみが変化した場合に削除処理が発生しません。
更新前
| ユーザー名 | 接続先ホスト名1 | 接続先ホスト名2 | 接続先ホスト名3 |
|---|---|---|---|
| alpha | host-a.example.com | - | - |
| beta | host-b.example.com | host-c.example.com | - |
| gamma | host-a.example.com | host-d.example.com | host-e.example.com |
| delta | host-f.example.com | - | - |
更新後
| ユーザー名 | 接続先ホスト名1 | 接続先ホスト名2 | 接続先ホスト名3 |
|---|---|---|---|
| alpha | host-f.example.com | - | - |
| beta | host-b.example.com | host-c.example.com | - |
| gamma | host-a.example.com | host-d.example.com | host-e.example.com |
| delta | host-a.example.com | - | - |
したがって、entity_idとconnection_idの組み合わせが一致するレコードをテーブルguacamole_connection_permissionから削除しなければなりません。削除対象の[entity_id, connection_id]を$delete_user_host_id.csvのリストアップするには、データベース内の[entity_id, connection_id]の組リスト$old_user_host_id.csvとwhile文内でファイルから登録したデータに対応する[entity_id, connection_id]の組リスト$user_host_id.csvを比較し、$old_user_host_id.csvのみに存在する組を出力します。
$old_user_host_id.csvはSQLで簡単に取得できるのですが、$user_host_id.csvの取得については一工夫必要になります。というのも、ファイルに記載するのは[ユーザー名, 接続先ホスト名]の組であり、これを対応する[entity_id, connection_id]の組に変換しなければならないからです。entity_id connection_idはそれぞれテーブルguacamole_entityテーブルguacamole_connectionへデータ登録後に取得できるので、接続先ホスト名1台分の登録処理が完了する、for文の最後にて対応するIDをそれぞれ一時ファイルに出力しています。つまり、$new_user_id.csvには元ファイルのユーザー名が対応するentity_id表記で出力されており、$new_host_id.csvには元ファイルの接続先ホスト名が対応するconnection_id表記で出力されています。この両ファイルをpasteコマンドで結合することにより、元ファイルに存在するユーザー情報を対応するID変換した$user_host_id.csvを取得することができます。
DELETE FROM guacamole_connection_permission
WHERE entity_id = '$delete_entity_id'
AND connection_id = '$delete_connection_id';
あとがき
Apache GuacamoleのWeb画面を使わずに、ファイルに記載したユーザー情報をGuacamoleのデータベースに同期できました。シェルスクリプトを定期実行することで自動で同期処理が完了するので、Guacamoleへのユーザー登録に手間を取られることはなくなります。
なお、上述のサンプルコードはご覧の通り（一気にスクロールされた方も多いかもしれませんが）、大部分がSQLによる力技です。実は、Guacamoleには公式ドキュメントで言及されていないREST API ⧉があるようです。APIの挙動が十分に確認できれば、こちらを利用してコンパクトなスクリプトにできたかもしれません。
