さくらVPS上のPostgreSQLにsshポートフォワーディングで接続する

なぜポートフォワーディングで接続するのか

先日MacからさくらVPSにあるPostgreSQLのデータベースに接続してみました。が、外部接続の設定が誰でも接続できる設定にした(サブネットマスクが0)のがずっと気になっていました。

izumii19.hatenablog.com

ポートフォワーディングを使うことで、安全に接続できることを知ったのでまとめてみます。

ポートフォワーディングとは

http://e-words.jp/w/%E3%83%9D%E3%83%BC%E3%83%88%E3%83%95%E3%82%A9%E3%83%AF%E3%83%BC%E3%83%87%E3%82%A3%E3%83%B3%E3%82%B0.html　にはこうあります。

ポートフォワーディングとは、IPネットワーク上のある機器が、自らのIPアドレスのTCPやUDPの特定のポート番号への通信を、別のアドレスの特定のポートへ自動的に転送すること。

これを使うことで、MacからVPSのPostgrSQLへ直接接続しているかのように操作できます。現時点で自分が知っている接続方法を挙げてみます。

VPSにSSH接続し、PostgreSQLに接続する(Mac--->VPS--->PostgreSQL)
PostgreSQLに外部からの通信を受け付ける設定をして、Macから直接PostgreSQLに接続する(Mac--->PostgreSQL)
ポートフォワーディングの設定をし、MacからPostgreSQLに接続する (Mac--->(VPS)--->PostgreSQL)

今回試したのは3つ目のポートフォワーディングを使った接続です。ポートフォワーディングはどちらかというと1のSSHに近いなとやってみて思いました。この記事のリモートフォワードのイラストがとてもわかりやすかったです。 https://qiita.com/mechamogera/items/b1bb9130273deb9426f5

localhostとは

設定ファイルでよく見るlocalhostについて調べました。 loaclhostは自身を表すホスト名のことです。loaclhostを使うとTCP/IP ソケットの場合以下のように接続できます。 psql -h localhost postgres -p 5432 なんでTCP/IPを使うかというと、ユニックスドメインソケットによる接続はPostgreSQL が動いているマシンに直接ログイン(例えばSSH)しなければならないので、今回のような外部接続の場合、TCP/IPを使うことになります。以下の記事の説明はとてもわかりやすかったです。 http://www.hizlab.net/app/pgsec.html

トンネルを掘る⛏

コマンド一本でポートフォワーディングの設定は終わります。ポートフォワーディングを設定することを、「トンネルを掘る」と呼ぶそうです。わかりやすい...! 実行したコマンドは以下の通りです。

ssh -N -L [ローカル側で転送に使用するPort）]:[PostgreSQLのHostName]:[PostgreSQLが解放しているPort(デフォルトだと5432)] -i [さくらVPSの秘密鍵]）] -p [VPSが解放しているPort] [VPSのUser]@[VPSのIPアドレス]

PostgreSQLのHostNameはlocalhostを指定します。 VPSのポート、ユーザー名、ホスト名は普段SSHで使っているのでOKです。これで接続の準備が整いました。-fオプションをつけるとバックグラウンドで実行できます。接続するには以下のコマンドを実行します。

psql -h localhost -p [ローカル側で転送に使用するPort] -U [ユーザー名] -d [DB名]

接続完了!

不要な設定を削除する

接続はできたものの、不要な設定が残っていないか見直してみました。外部接続に関するファイルは2つあります。

`/etc/postgresql/12/main/postgresql.conf`

listen_addresses = 'localhost'

listen_address = '*'は「何でもOK！どうぞどうぞ」な設定なので変更。 listen_addresについて

クライアント認証は誰がサーバにアクセス可能かをきめ細かく制御するのに対し、listen_addressesはどのインターフェイスが接続を試みるかを制御します。

listen_addressにどのインターフェースを受け付けるかのざっくりとした設定を書き、クラインアント認証(pg_hba.conf)でより詳細な設定を書くようです。

ちなみに、listen_addressで設定されていないことによって弾かれた時のメッセージと、listen_addressでは許可されているが、pg_hba.confでは設定されていない時に表示されるメッセージが違うことを確認しました。 - listen_addressで設定されていない時のメッセージ

pg_hba.confで設定されていない時のメッセージ

`/etc/postgresql/12/main/pg_hba.conf`

# Database administrative login by Unix domain socket
# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
  local   all             all                                     peer
# IPv4 local connections:
  host    all             all             127.0.0.1/32            md5
# IPv6 local connections:
 host    all             all             ::1/128                 md5
# Allow replication connections from localhost, by a user with the
# replication privilege.
 local   replication     all                                     peer
 host    replication     all             127.0.0.1/32            md5
 host    replication     all             ::1/128                 md5

hostの設定はlocalhost(自身のIPアドレス)のみとし、ポートフォワーディングで使用する接続以外は受け付けないようにしました。 psql -U ユーザー名 -d postgres -h さくらVPSのホスト名みたいな接続は受け付けません。ユニックスドメインソケットの設定と、リプレーションの設定はそのままにしておきました。

参考記事(状況が全く同じだったためとても参考になりました) https://qiita.com/rjge/items/d9ec5eb463a0ce24cb8

感想

設定ファイルの意味などを調べつつ動作確認をしたので時間がかかりましたが、自分が何をしているのか&してきたのかをだいたい理解できました。また何でpeer認証なんてあるんだ？なぜpostgresっていうユーザーがOSのユーザーとして作成されるんだという疑問も解決しました。「UNIX ドメインソケット経由の場合、アクセス制御はOSレベルに任せることで、安全に運用できるから」となりました。逆にOSレベルにユーザー管理任せないでどうするの？と考えたところ、そっか！OSに任せた方が都合いいかと納得しました。読んでてSSHとあんまり変わらないじゃん！と思った方... ポートフォワーディングの方がかっこいいと僕は思います！