SSHトンネリングによる外部DB接続

PostgreSQLインストールメモ その3 ～CentOS編②～ はタイトル上はインストールメモなのだけれど、実際のところはVirtualBoxゲストOS上に用意したPostgreSQLサーバーへの接続を目標としていた。この続きみたいな感じのメモ。

上記記事でのネットワーク設定はブリッジアダプターとなっているため同一LAN内に存在する外部端末からの接続を想定したような内容であったのだが、シンプルな疑問として「じゃあインターネットに存在するDBサーバのアクセスどうやんねん？」という話がある（クラウド上にサーバがあるとかいくらでも想定しうる）。

まあ要はVirtualBoxでのネットワーク設定をブリッジアダプターからNATネットワークに変更したような状況で外部DB接続をやってみよう、というのが今度の趣旨。

SSHトンネリング

天下りになるがまずSSHトンネリングについてのお勉強から入る。
SSHトンネリングとはSSHポートフォワーディングとも呼ばれる（SSH接続が可能なサーバを踏み台にして）直接アクセスできないサーバーにアクセスするSSHサーバの機能である。

参考記事
sshポートフォワーディング とは
SSHトンネルを抜けるとそこはイントラネットだった
Tera Termでポートフォワーディングをやってみた

引用元：https://qiita.com/miyuki_samitani/items/07c8b05f74dca25b33d0
図のパクリ芸毎回申し訳なく思うけどパクらせてもらう。

図の通りで、左のPCから右側のサーバに接続したいわけだけどサーバは外部LANに存在しており直接アクセスすることはできない。そこで踏み台サーバにクライアントからSSH接続を行って、アクセスを目的地に流してもらう（SSHサーバがそういう機能を持っている）。

sshコマンドにもこのオプション（-L, -R）が存在するし、

ssh -L [PCのローカルポート番号]:[接続先サーバのアドレス]:[接続先サーバのポート番号] [踏み台サーバのアドレス]

TeraTermから[設定]>[SSH転送]>[追加]と進むとポート転送設定画面を開くことができる。

検証準備

postgresユーザでゲストOSにログインして検証用のDB「testDB01」、ロール「testROLE01」を作成しておく。

$ psql
>psql (13.5)
>"help"でヘルプを表示します。
>postgres=# create database testDB01;
 >CREATE DATABASE
>postgres=# \q

$ psql -d testdb01 
>psql (13.5)
>"help"でヘルプを表示します。
>testdb01=# CREATE USER testROLE01 WITH PASSWORD 'testROLE01';
 >CREATE ROLE

作成したロールでテーブル作成を行うべくログインを試みたら怒られた。

$ psql -U testrole01 -d testdb01
>psql: エラー: FATAL:  ユーザ"testrole01"で対向(peer)認証に失敗しました

原因は pg_hba.conf でローカルホストからの接続要求に対する認証方式がpeerになっていることだったので、認証方式をmd5に変更して再度ログイン試行したら上手くいった。
※初期設定で行う手順として追記。↓
PostgreSQLインストールメモ その2 ～CentOS編①～

$ psql -U testrole01 -d testdb01
>ユーザ testrole01 のパスワード:
>psql (13.5)
>"help"でヘルプを表示します。
>testdb01=> CREATE TABLE SSH_FORWARD (FIELD VARCHAR(1));
>CREATE TABLE
>testdb01=> INSERT INTO SSH_FORWARD VALUES('X');
>INSERT 0 1

テーブル "SSH_FORWARD" の作成とテキトーなデータ挿入を行った。

NATネットワーク設定時のDB接続

前提：
・NATネットワーク設定のVirtualBox仮想マシン上にDBサーバが用意されており、かつポートフォワーディング設定が行われていること
・仮想マシンに固定プライベートIPアドレスを割り振っていること（自分の場合は10.0.2.4）

SSHトンネリングのお話を踏まえて今回の目標を図示してみた。ゲストOS上のDBサーバにアクセスするには

①適当な踏み台サーバの適当なポートにSSH接続
②踏み台サーバへのSSH接続を10.0.2.4:5432にトンネリング（ポートフォワーディング）

という2つのステップを踏む必要があるわけだが、適当な踏み台サーバとは何だろうか（適当なポートはまあ22だろう）。

…結論から言うとそれは「ゲストOSのSSHサーバ」ということになると思う。図示するとこんな感じ。

ゲストOSを踏み台サーバにするとして 10.0.2.4:22 へのアクセスはどうするかという問題に当たるわけだがそこはポートフォワーディングだろう（自分の場合は localhost:22221 へのアクセスを 10.0.2.4:22 に流すようにVirtualBoxで設定している）。

詳細に描くとこんな感じ。

サーバ側の設定

/var/lib/pgsql/13/data 下の postgresql.conf, pg_hba.conf を編集する。編集内容は明らかで、ゲストOSのIPアドレス（10.0.2.4）からのアクセスを通すような設定を書いてやれば良い。

# postgresql.conf の listen_addresses を下記のように編集
listen_addresses = '10.0.2.4'           # what IP address(es) to listen on;

# pg_hba.conf に下記を追記
host    all             all             10.0.2.4/32             md5​

ホストPC側の設定

ここではA5M2を使う。[データベース]>[データベースの追加と削除]>[追加]>[PostgreSQL（直接接続）]>[基本]を開き、基本的な接続内容を記入する。

サーバー名：10.0.2.4（※DBサーバ）
ポート番号：5421
データベース名：testdb01（検証用に作成）
ユーザーID：testrole01（検証用に作成）

残るはSSHトンネリングの設定だけである。これについては[SSH2トンネル]タブで設定することができる。

ホスト名：localhost（SSHサーバ）
ポート番号：22221
ユーザーID：ゲストOSのユーザー

※今回のケースはSSHサーバとDBサーバが同一マシン上に存在するためか、ユーザーIDはOS上のユーザーであればなんでもいい感じだった（rootでもpostgresでも接続成功した）。

テスト接続を行うと「localhostのホストキーを受け入れますか？」というポップアップが現れるので、「はい」を選択して接続完了と出ればOK。

作成したDB・テーブル、投入したデータも確認することができた。