クライアント証明書について
一言で言うなら「クライアント証明書自体がないとシステムを利用できないよ！」といった機能です。
Nginx等のWebでの利用は検索すると沢山出てくると思います。
今回はDB(Postgresql)でもクライアント証明書認証が可能であると知り、試行錯誤したのでそちらについて記述しておく。
参考文献
Postgresqlにおけるクライアント証明書認証について
Postgresqlのクライアント証明書について調べると大きく２つの種類があるようだ。それぞれの特徴を説明するとこんな感じ。
- 「１．クライアント証明書自体をユーザIDやパスワードとして扱う！」
- こちらはクライアント証明書の”CN”がDBでユーザIDと一致するユーザがあればパスワードの入力無しでログインを完了とする認証方式だ。
- したがって絶対に他人に渡して（漏洩して）はならない。
- 「２．互いに信頼の証を持っていないと通信してやんない！」
- こちらはクライアント証明書がサーバが信頼している証明書で署名されており、別途パスワード認証を行う認証方式だ。
- したがってクライアント証明書は必要だが、どのDBユーザにログインするかは別途指定が必要であり、パスワードの入力も必要となる。
- どう運用するかにもよるが、一つだけクライアント証明書を作成し、ログインを許可するDB利用者に共有するような運用も可能だ。
動作環境はDockerのPostgresql
今回の動作環境は、Docker Hubのpostgresで、バージョン16.3とします。
このイメージは初期化スクリプトがある場合は、”/docker-entrypoint-initdb.d/”に配置してあるスクリプトやSQLファイルを実行します。
サーバ側の証明書でSSL通信
本題ではないが、DBとクライアント間の通信をサーバの証明書で暗号化したかったので、まずはそっちの手順について書き留めておく。
サーバ側の証明書を作成する
証明書の作成については以下の通りだ。こちらが参考になるだろう。今回は自己署名とします。
ここの”CN”にはホスト名やドメイン名を入力してください。クライアント側で”CN”の値を名前解決できなければエラーになると思います。
ちなみに中間証明書を含むチェーンを構築するならこちらの最下部を参考にすると良いと思う。
# サーバ証明書（ルート証明書）の作成
openssl req -x509 -new -subj "/C=JP/CN=host" -keyout server.key -out server.crt -days 365
通信のSSL暗号化における設定ファイル
SSL設定用の初期化スクリプトを作成してみた。
前提としてDocker Hub postgres(ver16.3)のDockerイメージを使うこととする。加えて先ほど用意した証明書やパスフレーズ用スクリプトを用意すること。
#!/bin/sh
# ssl communication settings.
echo "####################"
echo "start of $0 shell script"
echo "####################"
# setting for next.
POSTGRE_SSL_DIR="$PGDATA/ssl"
SSL_CRT_FILE="server.crt"
SSL_KEY_FILE="server.key"
#SSL_CA_FILE="CA.key"
#SSL_CRL_FILE="crl.key"
mkdir $POSTGRE_SSL_DIR
cd $POSTGRE_SSL_DIR
cp /docker-entrypoint-initdb.d/ssl/$SSL_CRT_FILE $POSTGRE_SSL_DIR
cp /docker-entrypoint-initdb.d/ssl/$SSL_KEY_FILE $POSTGRE_SSL_DIR
cp /docker-entrypoint-initdb.d/ssl/passphrase.sh $POSTGRE_SSL_DIR
chmod 0600 $POSTGRE_SSL_DIR/*
chmod 0700 $POSTGRE_SSL_DIR/passphrase.sh
# create cert files.
# openssl req -x509 -new -subj "/C=JP/CN=postgresql" \
# -keyout $SSL_KEY_FILE -out $SSL_CRT_FILE -days 365
# set postgresql.conf file.
cat << EOF >> $PGDATA/postgresql.conf
# - SSL -
ssl = on
ssl_cert_file = '$POSTGRE_SSL_DIR/$SSL_CRT_FILE'
ssl_key_file = '$POSTGRE_SSL_DIR/$SSL_KEY_FILE'
ssl_passphrase_command = '$POSTGRE_SSL_DIR/passphrase.sh'
# bellow, for client cert.
#ssl_ca_file = '$POSTGRE_SSL_DIR/$SSL_CA_FILE'
#ssl_crl_file = '$POSTGRE_SSL_DIR/$SSL_CRL_FILE'
EOF
# set pg_hba.conf file.(force SSL communication)
sed -i 's/^host all all all/#&/g' $PGDATA/pg_hba.conf
cat << EOF >> $PGDATA/pg_hba.conf
# - Server SSL -
hostssl all all all scram-sha-256
EOF
echo "####################"
echo "end of $0 shell script"
echo "####################"
psqlコマンドで接続確認
上手く設定できるとpsqlコマンドで外部ホストから接続すると以下のような出力が得られる。
“SSL connection…”といったような出力があれば成功だ！
他の確認方法が良ければWireSharkを使う方法もある。
psql -U User -h ip_addr Database
Password for user User:
psql (16.3 (Ubuntu 16.3-0ubuntu0.24.04.1))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off)
Type "help" for help.
Database=#さらにセキュアなSSL通信をしたい
よりセキュアな設定として、「SSL接続を強要して、DBのサーバ証明書がクライアントが信用しているCAから発行されていて、サーバ証明書のCNとリクエストしているホスト名（ドメイン名）と同一か確認する」オプションの認証設定が存在する。
一言で言うなら、「お前ホンマに本人(DB)か？信用できんのか？」って事ですね。
psqlコマンドで接続確認
以下のコマンドで接続できた。
CA証明書の場所：
クライアントが信用するCAは事前に入手してクライアントのローカルに保存しなくてはならない。
デフォルトで”~/.postgresql/root.crt”の証明書を利用する。ただし環境変数”PGSSLROOTCERT”や、恐らく別途オプション(多分”sslrootcert”)でもデフォルト値の変更が可能です。
# リクエスト時に"sslmode"オプションでリクエスト
psql "postgres://user@host/database?sslmode=verify-full"「１．クライアント証明書自体をユーザIDやパスワードとして扱う！」を設定してみる
クライアント側の証明書を作成する
先ほどのサーバ証明書で著名したクライアント証明書を作成します。下記のコマンド実行後入力する”CN”については下記記述について注意して入力してください。
ログインするユーザID：
証明書作成時に指定した”CN”がデータベースログイン時のユーザIDに紐づく。ただし“clientname”で”CN”からデフォルト値を変更可能です。
# クライアント証明書を作成
openssl req -x509 -nodes -new -keyout ./client.key -out ./client.crt -CAkey ./server.key -CA ./server.crt -days 365
クライアント証明認証における設定ファイル
これまた今回の設定対応を初期化スクリプトに落とし込んでみた。
先ほどのスクリプトとは別ファイルに記述しおり先ほどのファイル名が接頭辞”01_”で、今回のファイルは”02_”なため順にスクリプトが実行されることになる。
#!/bin/sh
# 前提として"01_ssl-setup.sh"を実行完了済みであるとする。
echo "####################"
echo "start of $0 shell script"
echo "####################"
# setting for next.
POSTGRE_CLICERT_DIR="$PGDATA/ssl"
SSL_CA_FILE="server.crt"
# SSL_CRL_FILE="root.crl"
mkdir $POSTGRE_CLICERT_DIR
cd $POSTGRE_CLICERT_DIR
# 01_ssl-setup.shで利用した証明書を以下でも利用している想定。
# 以下の成果物2つと01_ssl-setup.shで利用した証明書2つは、クライアント側の".postgresql/"に配置すること
# openssl req -x509 -nodes -new -keyout ./client.key -out ./client.crt -CAkey ./server.key -CA ./server.crt
# set postgresql.conf file.
cat << EOF >> $PGDATA/postgresql.conf
# - Client SSL -
ssl_ca_file = '$POSTGRE_CLICERT_DIR/$SSL_CA_FILE'
# ssl_crl_file = '$POSTGRE_CLICERT_DIR/$SSL_CRL_FILE'
EOF
# set pg_hba.conf file.
cat << EOF >> $PGDATA/pg_hba.conf
# - Client SSL -
hostssl all uredmine 0.0.0.0/0 cert
# hostssl all all all scram-sha-256 clientcert=verify-full
# hostssl all all all scram-sha-256 clientcert=verify-full clientname=ST
EOF
echo "####################"
echo "end of $0 shell script"
echo "####################"
注意事項
設定ファイルの評価準？優先順位については別途調査の必要あり。
どういうことかというと以下のような設定を施した場合にクライアント証明書による認証が行われず、通常のパスワード認証が行われた。したがって先ほど（サーバ側の証明書でSSL通信）のシェルスクリプトの”hostssl”行をコメントアウトしなければならない。
多分一番最初に当てはまった認証設定を採用する、のかな？どうだろう？
まぁいずれにせよクライアント証明書以外の設定に目を向けておらずクライアント認証が無視されていたから、かなり数日困惑していた（笑）
# 以下のような設定が施されていると、クライアント証明が無効となった
host all all all scram-sha-256
hostssl all all all scram-sha-256
# 上記記述の下にクライアント証明書を記述
hostssl all uredmine 0.0.0.0/0 cert
psqlコマンドで接続確認
psql "postgres://user@host/Database"
# よりセキュアにアクセスしたければ（試してない）
psql "postgres://user@host/Database?sslmode=verify-full"「２．互いに信頼の証を持っていないと通信してやんない！」を設定してみる
こちら、ここまででほぼ準備が完了しているためほとんど作業は必要ない。
裏を返せばここまで使ってきた各種証明書やスクリプトは全て必要になります。
クライアント証明認証における設定ファイル
認証方法は”cert”以外を任意で指定してください。今回の場合は “clientcert”認証オプションの指定が必要になります。
# こちらはコメントアウトしてください
# hostssl all uredmine all cert
# こちらを有効にしてください
hostssl all all all scram-sha-256 clientcert=verify-full
psqlコマンドで接続確認
psql "postgres://user@host/Database"
# よりセキュアにアクセスしたければ（試してない）
psql "postgres://user@host/Database?sslmode=verify-full"Pythonでの実装方法
ちらちら触れてもいますがPythonなどのプログラムで実装するには、DBリクエスト前に環境変数で指定するか、リクエスト時のオプションで指定するかの2択になると思います。（試してはいない）
参考文献
PostgreSQL 16.3 ドキュメント
21.12. Certificate Authentication
19.9. Secure TCP/IP Connections with SSL(postgresql.confで必要な設定項目について知れる)
20.3.4. SSL(postgresql.confで設定可能なSSLに関する項目)
21.1.pg_hba.confファイル(認証手法”cert”または認証オプション”clientcert”について調べると良い)
30.1. Database Connection Control Functions(クライアントアプリで指定できるオプションについて知れる)
PostgreSQLにSSL接続する
PostgreSQLのSSLサポートの構成
SSL証明書接続が必要な PostgresQL サーバーを docker-compose で立ち上げる
PostgreSQL でクライアント証明書認証を行うには
MySQLでクライアント証明（特に調べてない）
事前知識・準備
まぁ環境変数やリクエストオプションで指定することは変わらないだろう。参考になりそうな資料をいくつか添付しておく。