More than 1 year has passed since last update.

Postgresの公開サーバーを作った結果

Posted at 2023-01-03

これはなに？

　初心者の私がVPSを契約してPostgreSQLのサーバーを立てて公開した結果、いろいろと学ぶことができたのでまとめます。
　本記事ではサーバー構築の手順は割愛し、私個人の想定外だった点や調べるのが不十分だった点を記載します。

今回使用した環境

VPS : KAGOYA VPS
OS : Ubuntu 22.04.1 LTS
PG : PostgreSQL 15.1

きっかけ

　自身のデータベースに関するスキルを上げたいと思ったものの、なかなかそのような機会がなくてモヤモヤしていた矢先。
　そうだ、トラフィックを生み出せるコンテンツを作れないなら他人様に生み出してもらえばいい。と思い立ち、善は急げと取り組むことに。

結果、どうなったか

PostgreSQLに対して大量のログイン試行

　ログイン試行のログが大量発生。改めてpg_hba.confの設定が重要であることを再認識。

ファイヤーウォールの設定が不適切であることが判明

　ufwで行ったデフォルト設定が不適切であったことが原因で、上記のログイン試行を許してしまったことが発覚。

PostgreSQLへのログイン試行

　ネットの大海原へ公開した12月31日の夜から元日の夕方までの間に6,239回のログイン失敗のログがありました。
　これはすべてpostgresロール、すなわち標準設定のスーパーユーザーへのアクセスであります。
　元日の夕方からはpg_hba.confの設定を見直し、リモートからpostgresロールへのアクセスを拒否するよう明示しました。
　なお、設定したpg_hba.confは以下の通りです。

#IPv4
host    all    all        127.0.0.1/32    trust
host    all    postgres   0.0.0.0/0       reject
#IPv6
host    all    all        ::1/128         trust
host    all    postgres   ::0/0           reject

　今後、postgresロールへのログインはローカル環境からに限られます。
　また実際には公開に向けて他の設定も必要です。例えば、ロール名と同じ名前データベースしかアクセスを許可しない場合は以下の通り設定します。

host    sameuser    all    0.0.0.0/0      scram-sha-256

ufwで行った不適切なデフォルト設定

　サーバーを公開するならば、ファイヤーウォールは必須だろうとufwを用いて設定を行いました
　しかし、 ufw default denyとすべきところをufw default allowとしたためにすべての通信が通過することに。
　設定時に？？？となっていたものの、深く検討しないまま設定。
　ufwのログを見て初めてこの誤りに気付くことに。
　本当に必要だったのは以下の手順での設定です。

ufw default deny
ufw limit postgres

今後の課題

低い？ログレベル

　今回、ufwのデフォルトログレベルでは誰が認証に失敗した、ブロックされたかといった記録を残すことはできましたが、その逆の「誰が成功したか」についての記録を最初から残すことはできませんでした。
　招かれざる客を門前払いするには、どういった条件で侵入されたかを検知する必要があるため明らかに収集不足です。
　そのため、ufwのログレベルは以下の通り設定すべきでした。

ufwのログレベル設定

ufw logging medium

　なお、PostgreSQLは以下の設定とすることでクライアント認証の成否をログに残すことができます。

PostgreSQL (`postgresql.conf`)のログレベル設定

logging_collector = true
log_connection = ture

　なお、PostgreSQLにおけるlog_min_messagesはデフォルトです。

最後に公開サーバーについて

　非常に手前味噌で恐縮ですが、公開PostgreSQLサーバーについての詳細は以下をご覧ください。