【PostgreSQL】外部からデータベースへ接続するためのアクセス制御設定

PostgreSQLをDBサーバとして使用する場合、外部のAPサーバからデータベースへ接続したいというケースがあるかと思います。
PostgreSQLは、初期状態では外部からの接続が許可されておらず、その一方でローカル接続は認証不要で行えるという、セキュリティ的にあまりイケていない設定になっています。
今回は、ローカル接続と外部接続の両方に対してパスワード認証を行う設定を検証してみました。

前提条件

以下の前提条件で検証しています。

• ハードウェア: VirtualBox上の仮想マシン
• OS: CentOS 8.1
• ミドルウェア:
  • PostgreSQL: 12.1 (OS同梱版)
• 各手順はroot権限を持つユーザで実施

OSのバージョン情報は以下の通りです。

# cat /etc/redhat-release
CentOS Linux release 8.1.1911 (Core) 

# uname -r
4.18.0-147.el8.x86_64

postgresロールのパスワード設定

PostgreSQLには、DBユーザに相当する概念としてロールがあります。ロールの中に、自身の属性や、アクセス権限が格納されています。
初期ロールとして、OSユーザと同名のpostgresが存在します。postgresロールにはパスワードが設定されていないので、アクセス制御の設定を施す前に、先にパスワードの設定を行います。

postgresOSユーザの権限で、postgresデータベースへ接続します。

# sudo -u postgres psql

ロール情報が収められているpg_shadowテーブルの内容を参照します。PostgreSQLの初期設定では、postgresロールのpasswd列が空になっています。

=# SELECT * FROM pg_shadow;
   usename   | usesysid | usecreatedb | usesuper | userepl | usebypassrls |               passwd       
         | valuntil | useconfig 
-------------+----------+-------------+----------+---------+--------------+----------------------------
---------+----------+-----------
 postgres    |       10 | t           | t        | t       | t            | 
a567b4a4 |          | 
 sample_user |    16387 | f           | f        | f       | f            | md5c809dc7893336b5fe8553926
d0565d03 |          | 
(2 rows)

postgresロールにパスワードを設定します。
以下の例では、P@sswordという文字列をパスワードとして指定しています。

=# ALTER ROLE postgres PASSWORD 'P@ssword';
ALTER ROLE

再度pg_shadowテーブルを確認してみます。postgresロールのpasswd列に、先ほど指定したパスワード文字列のMD5ハッシュ値が表示されています。これで、postgresロールへのパスワード設定が完了しました。

=# SELECT * FROM pg_shadow;
 usename  | usesysid | usecreatedb | usesuper | userepl | usebypassrls |               passwd
                | valuntil | useconfig 
----------+----------+-------------+----------+---------+--------------+---------------------
----------------+----------+-----------
 postgres    |       10 | t           | t        | t       | t            | md57df363c1845f664494829cb5
a567b4a4 |          | 
 sample_user |    16387 | f           | f        | f       | f            | md5c809dc7893336b5fe8553926
d0565d03 |          | 
(2 rows)
=# \q

設定ファイルの変更

postgres.conf

外部からのDB接続を受け付けるために、リッスンIPアドレスを設定します。
vi等のテキストエディタで、以下のファイルを開きます。

# vi /var/lib/pgsql/data/postgresql.conf

変更箇所は以下の通りです。listen_addressesにリッスンする自サーバのIPアドレスを指定します。
基本的にはワイルドカード(*)で問題ないですが、サーバが複数のインターフェースを持っていて、意図的に特定のインターフェースでのみDB接続を受け付けたい場合は、該当インターフェースのIPアドレスを指定します。

(略)
#------------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#------------------------------------------------------------------------------

# - Connection Settings -

#listen_addresses = 'localhost'          # what IP address(es) to listen on;
↓
listen_addresses = '*'          # what IP address(es) to listen on;
(略)

pg_hba.conf

アクセス制御設定ファイルは、postgresql.confと分離されていて、pg_hba.confという名前のファイルが使われます。
vi等のテキストエディタで、以下のファイルを開きます。

# vi /var/lib/pgsql/data/pg_hba.conf

まずは、ローカル接続用の認証設定を行います。
ファイル下部に以下パラメータが存在するので、各行のtrustを全てmd5に変更します。

# TYPE  DATABASE        USER            ADDRESS                 METHOD

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

変更後は以下のようになります。

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     md5
# 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                                     md5
host    replication     all             127.0.0.1/32            md5
host    replication     all             ::1/128                 md5

次に、外部接続用のアクセス制御設定を行います。
外部から接続を許可するロール、接続先データベース、送信元IPアドレスを指定したパラメータ行をファイルの末尾に追記します。
以下の例では、192.168.33.0/24のセグメントに存在するクライアントからsample_userロールでsample_dbデータベースへの接続を許可しています。

host    sample_db       sample_user     192.168.56.0/24          md5

設定の反映

設定ファイルの変更が終わったら、サービスをリロードして設定を反映します。

# systemctl reload postgresql.service

OSファイアウォール設定

firewalldを有効にしている場合、PostgreSQL用のポート番号へのアクセスを許可する設定を行います。

# firewall-cmd --add-service=postgresql --permanent
success
# firewall-cmd --reload
success

データベース接続確認

PostgreSQLをインストールしたサーバとは別に、もう1台サーバを用意してデータベースの外部接続を確認してみます。
psqlコマンドの引数に-U(ロール名)、-h(接続先IPアドレス)、-d(接続先データベース名)を指定し実行します。データベースに接続されると、md5認証によりパスワードが求められます。パスワードを入力すると、<データベース名>=>のプロンプト文字が表示され、DB操作が可能になります。

# psql -U sample_user -h 192.168.56.30 -p 5432 -d sample_db
ユーザ sample_user のパスワード:  ← パスワードを入力しEnter
psql (12.1)
"help"でヘルプを表示します。

sample_db=>