PostgreSQL にリモートホストから安全に接続するには

PostgreSQL はデフォルトではローカルホストからしか接続できません。PostgreSQL の動作するホストとは別のリモートホストから接続するには設定が必要です。ただ接続するだけでよければ設定は簡単ですが、ここでは、なるべく安全に接続するのに必要な設定について説明します。

説明は基本的に PostgreSQL 11 を CentOS 7 にセットアップした状態を前提にしますが、バージョンで異なる部分はなるべく補足します。PostgreSQL のインストールについては [こちらの記事](https://qiita.com/tom-sato/items/e1903cb974fb6c6d5664) を参照してください。また、設定に伴う操作はとくに断わりのない限り `postgres` ユーザで行うものとします。

# パスワード暗号化アルゴリズムの設定

ユーザのパスワードはデフォルトでは MD5 で暗号化して格納されます。PostgreSQL 10 以降ではより安全な SCRAM-SHA-256 という暗号化アルゴリズムを使えます。SCRAM-SHA-256 に未対応の古いクライアントからの接続が不要であれば、変更したほうがいいでしょう。ここでは、暗号化アルゴリズムを SCRAM-SHA-256 に設定します。

パスワード暗号化アルゴリズムの設定は `postgresql.conf` ファイル内の `password_encryption` パラメータで行います。

`postgresql.conf` ファイルは PostgreSQL の設定ファイルで、データベースクラスタ内にあります。`postgresql.conf` ファイルは 1 行に 1 つの設定を `パラメータ名 = 値` という書式で書きます。`#` から行末まではコメントを表します。ほとんどのパラメータはコメントになっていて、コメントになった最初の状態は一部のパラメータを除いてデフォルト値を表します。設定を変更する際にはパラメータ名の前の `#` を外すのを忘れないようにしてください。また、コメントに `(change requires restart)` と書いてあるパラメータは設定の反映にデータベースサーバの再起動が必要です。書いてないパラメータは設定ファイルの再読み込みで反映できます。

`postgresql.conf` ファイルをエディタで編集し、`password_encryption` パラメータの設定を `scram-sha-256` に変更します。

```config:$PGDATA/postgresql.conf
password_encryption = scram-sha-256             # md5 or scram-sha-256
```

`pg_ctl` コマンドの `reload` モードで設定ファイルの再読み込みを行い、`password_encryption` パラメータの設定を反映します。現在の設定は `postgres` コマンドの `-C` オプションで確認できます。

```shell-session
$ pg_ctl reload
サーバにシグナルを送信しました
$ postgres -C password_encryption
scram-sha-256
```

# 接続用ユーザの作成

リモートホストからの接続はデフォルトで存在する `postgres` ユーザでもできますが、強い権限をもつスーパーユーザができるのはあまりよくありません。ここでは、スーパーユーザとは別に接続用ユーザを作成します。

`psql` コマンドでデータベースに接続し、`CREATE ROLE` 文でログイン権限のみをもつ `test` という名前のユーザを作成します。ユーザの一覧は `\du` コマンドで確認できます。

```shell-session
$ psql
psql (11.4)
"help" でヘルプを表示します。

postgres=# CREATE ROLE test LOGIN;
CREATE ROLE
postgres=# \du
                                              ロール一覧
 ロール名 |                                    属性                                    | 所属グループ
----------+----------------------------------------------------------------------------+--------------
 postgres | スーパーユーザ, ロール作成可, DB作成可, レプリケーション可, RLS のバイパス | {}
 test     |                                                                            | {}
```

`psql` コマンドは `\q` コマンドで終了できますが、このあとの設定もデータベースに接続した状態で行うので、終了しないでください。

# パスワードの設定

ユーザのパスワードはデフォルトでは設定されておらず、パスワード認証が行えません。ここでは、作成した接続用ユーザと設定してなければスーパーユーザのパスワードを設定します。

まず、`\password` コマンドで `test` ユーザのパスワードを設定します。

```shell-session
postgres=# \password test
新しいパスワードを入力してください: (パスワードを入力)
もう一度入力してください: (パスワードを再入力)
```

次に、`postgres` ユーザのパスワードを設定します。ユーザ名を指定しなければ自分のパスワードが設定されます。

```shell-session
postgres=# \password
新しいパスワードを入力してください: (パスワードを入力)
もう一度入力してください: (パスワードを再入力)
```

パスワードの設定は `CREATE ROLE` や `ALTER ROLE` 文でもできますが、パスワードが画面に表示されてしまい、設定によってはログにも出力されてしまうので、`\password` コマンドを使ったほうがいいでしょう。

# 接続用データベースの作成

リモートホストからの接続先はデフォルトで存在する `template1` や `postgres` データベースにできなくもないですが、`template1` データベースは新たにデータベースを作成するときのデフォルトのテンプレートで、`postgres` データベースもアプリケーションのデフォルトの接続先を意図したものなので、いずれも通常のデータベースとして使うのにはよくありません。ここでは、ユーザと同じく接続用データベースを作成します。

`CREATE DATABASE` 文で `test` ユーザを所有者にしてユーザ名と同じ `test` という名前のデータベースを作成します。データベースの一覧は `\l` コマンドで確認できます。

```shell-session
postgres=# CREATE DATABASE test OWNER test;
CREATE DATABASE
postgres=# \l
                                        データベース一覧
   名前    |  所有者  | エンコーディング | 照合順序 | Ctype(変換演算子) |     アクセス権限
-----------+----------+------------------+----------+-------------------+-----------------------
 postgres  | postgres | UTF8             | C        | C                 |
 template0 | postgres | UTF8             | C        | C                 | =c/postgres          +
           |          |                  |          |                   | postgres=CTc/postgres
 template1 | postgres | UTF8             | C        | C                 | =c/postgres          +
           |          |                  |          |                   | postgres=CTc/postgres
 test      | test     | UTF8             | C        | C                 |
(4 行)
```

# `public` スキーマの設定

スキーマとはデータベース内の名前空間のことで、デフォルトで `public` という名前のスキーマが存在します。

`public` スキーマはデフォルトですべてのユーザがデータベースオブジェクトを作成でき、`search_path` パラメータで指定されるスキーマの検索パスにも含まれています。そのため、やろうと思えば `public` スキーマにデフォルトで存在する関数名と同じ名前の関数を作成し、ほかのユーザに実行させたりできてしまいます。それを防ぐ方法はいくつかありますが、ここでは、`public` スキーマから不要な権限を取り除き、スキーマの検索パスで `public` スキーマより優先されるユーザ名と同じ名前のスキーマを作成します。

`\c` コマンドで `test` データベースに接続しなおし、`REVOKE ROLE` 文で `public` スキーマからすべてのユーザのデータベースオブジェクト作成権限を取り除き、`CREATE SCHMEA` 文で `test` ユーザを所有者としてユーザ名と同じ `test` という名前のスキーマを作成します。権限の情報を含む詳細なスキーマの一覧は `\dn+` で確認できます。

```shell-session
postgres=# \c test
データベース "test" にユーザ "postgres" として接続しました。
test=# REVOKE CREATE ON SCHEMA public FROM PUBLIC;
REVOKE
test=# CREATE SCHEMA AUTHORIZATION test;
CREATE SCHEMA
test=# \dn+
                           スキーマ一覧
  名前  |  所有者  |     アクセス権限     |          説明
--------+----------+----------------------+------------------------
 public | postgres | postgres=UC/postgres+| standard public schema
        |          | =U/postgres          |
 test   | test     |                      |
(2 行)
test=# \q
```

# Unix ドメインソケット作成先の設定

Unix ドメインソケットとは同じホスト上で動作するプロセス間の通信方法のことで、基本的に Linux などの Unix 系 OS のみで使えます。TCP/IP を使った接続をホスト接続と呼ぶのに対し、Unix ドメインソケットを使った接続をローカル接続と呼びます。

Unix ドメインソケットの作成先はデフォルトでは、パッケージでインストールした場合には `/var/run/postgresql` と `/tmp` ディレクトリ、ソースコードからインストールした場合には `/tmp` ディレクトリです。`/tmp` ディレクトリはすべてのユーザがファイルを作成できるので、やろうと思えばデータベースサーバの停止中に別のサーバを起動し、なりすますことができてしまいます。それを防ぐには Unix ドメインソケットの作成先を `postgres` ユーザのみが書き込み権限をもつディレクトリ、ここでは `/var/run/postgresql` ディレクトリに設定します。Windows では Unix ドメインソケットを使えないのでディレクトリの設定は必要ありません。

ソースコードからインストールした場合には、`/var/run/postgresql` ディレクトリが存在しないので、`root` ユーザに切り替わり、`/var/run/postgresql` ディレクトリを作成し、所有者を `postgres` ユーザに変更し、所有者以外の権限を取り除きます。

```shell-session
$ su -
パスワード: (パスワードを入力)
最終ログイン: 2019/07/26 (金) 11:25:37 JST日時 pts/0
# mkdir /var/run/postgresql
# chown postgres:postgres /var/run/postgresql
# chmod go-rwx /var/run/postgresql
# exit
ログアウト
```

`psql` などのクライアントプログラムはデフォルトでローカル接続を行いますが、ソースコードからインストールした場合には、`/tmp` ディレクトリ内の Unix ドメインソケットに接続しようとします。そのため、Unix ドメインソケットの作成先を変更した場合には、そのパスを `-h` または `--host` オプションで明示的に指定する必要があります。その都度、オプションを指定するのが面倒であれば、`~/.pgsql_profile` ファイルで環境変数 `PGHOST` を設定するといいでしょう。

環境変数の設定は、通常、設定ファイル `~/.bash_profile` で行いますが、`postgres` ユーザの `~/.bash_profile` ファイルはパッケージのインストール時に上書きされてしまいます。そのため、`~/.bash_profile` ファイルから `~/.pgsql_profile` ファイルを読み込むようになっており、環境変数の設定は `~/.pgsql_profile` ファイルで行います。

```bash:~/.pgsql_profile
export PGHOST=/var/run/postgresql
```

`~/.pgsql_profile` ファイルを編集し、すぐに設定を反映したければ、ファイルの再読み込みを行ってください。

```shell-session
$ . ~/.bash_profile
```

最後に、Unix ドメインソケットの作成先を `unix_socket_directories` パラメータで `/var/run/postgresql` のみに変更します。

```config:$PGDATA/postgresql.conf
unix_socket_directories = '/var/run/postgresql' # comma-separated list of directories
                                        # (change requires restart)
```

`unix_socket_directories` パラメータはコメントに `(change requires restart)` と書いてあるので、設定の反映にデータベースサーバの再起動が必要です。ここでは、あとでまとめて行うので省略します。

# SSL による通信暗号化の設定

クライアントとデータベースサーバ間の通信はデフォルトでは平文で行われるので、やろうと思えばネットワーク上をやり取りされるパケットをキャプチャし、中身のデータを盗み見ることができてしまいます。それを防ぐには SSL で通信を暗号化するか、SSH などで暗号化したトンネルを介して通信します。ここでは、SSL による通信暗号化の設定を行います。

SSL (Secure Socket Layer) とはネットワーク上の通信を暗号化するプロトコルのことです。SSL を使うにはビルド時に有効にしておく必要があります。パッケージでインストールした場合には有効になっています。有効になっているかは `pg_config` コマンドでビルド時のオプションに `--with-openssl` が含まれているかを見れば分かります。

```shell-session
$ pg_config --configure | grep -o -- --with-openssl
--with-openssl
```

SSL による通信暗号化の設定を行うには、まず、データベースサーバの証明書と秘密鍵を作成します。

サーバの証明書と秘密鍵の作成には `openssl` コマンドを使います。ここでは、データベースクラスタ内にサーバの証明書 `server.crt` と秘密鍵 `server.key` を作成します。サーバ証明書は自己署名、有効期間は 3650 日 (約 10 年)、ホスト名は `dbhost.example.com` に指定しています。`dbhost.example.com` はデータベースサーバのホスト名に合わせて変更してください。サーバの証明書と秘密鍵の作成方法について詳しくは OpenSSL のドキュメントを参照してください。

```shell-session
$ openssl req -new -x509 -days 3650 -nodes -text -out $PGDATA/server.crt -keyout $PGDATA/server.key -subj /CN=dbhost.example.com
Generating a 2048 bit RSA private key
..................+++
......................+++
writing new private key to '/var/lib/pgsql/11/data/server.key'
-----
```

サーバ秘密鍵は所有者以外がアクセスできると使えないので、所有者以外の権限を取り除きます。

```shell-session
$ chmod go-rwx $PGDATA/server.key
```

ルート証明書は自己署名なのでサーバ証明書のコピーで済ませます。

```shell-session
$ cp $PGDATA/server.crt $PGDATA/root.crt
```

次に、SSL モードを有効にします。

SSL モードを有効にするには、`postgresql.conf` ファイル内の `ssl` パラメータを `on` に変更します。

```config:$PGDATA/postgresql.conf
ssl = on
```

`ssl` パラメータの設定を反映するには、設定ファイルの再読み込みが必要です。ここでは、あとでまとめて行うので省略します。

# 接続監視アドレスの設定

クライアントからの接続はデフォルトではローカルホスト宛てしか監視していないので、リモートホストからデータベースサーバのアドレス宛てには接続できません。ローカルホスト宛て以外の接続を監視するには、監視対象に宛て先のアドレスを設定します。

接続監視アドレスの設定は `listen_addresses` パラメータで行います。パラメータには接続先のホスト名または IP アドレスをカンマ区切りで指定します。デフォルトは `localhost` で、Unix ドメインソケットとローカルホスト宛ての接続を監視します。たまに勘違いしている人をみかけますが、接続元のアドレスを指定するパラメータではありません。どのクライアントからの接続を許可するかは後述の [クライアント認証](#クライアント認証の設定) で設定します。

複数のネットワークインタフェースを備えたホストでは、各インタフェースに割り当てられたアドレスを指定する代わりに、すべてのインタフェースを表す `*` を指定することもできますが、接続を受けつけるアドレスが限られるのであれば、必要なアドレスのみを指定したほうがいいでしょう。

ここでは、ホスト `dbhost.example.com` 宛てにも接続できるように、`postgresql.conf` ファイル内の `listen_addresses` パラメータに `dbhost.example.com` を追加します。`dbhost.example.com` はデータベースサーバのホスト名に合わせて変更してください。

```config:$PGDATA/postgresql.conf
listen_addresses = 'localhost,dbhost.example.com'               # what IP address(es) to listen on;
                                        # comma-separated list of addresses;
                                        # defaults to 'localhost'; use '*' for all
                                        # (change requires restart)
```

`listen_addresses` パラメータの設定を反映するには、データベースサーバの再起動が必要です。ここでは、あとでまとめて行うので省略します。

# クライアント認証の設定

クライアントからの接続はデフォルトではローカルホストからしか許可されていないので、リモートホストから接続できません。ローカルホスト以外からの接続を許可するには、クライアント認証の設定を行います。

クライアント認証の設定はデータベースクラスタ内の `pg_hba.conf` ファイルで行います。ファイル名に含まれる hba は host-based authentication の略で、ホストに基づく認証を表します。`pg_hba.conf` ファイルは 1 行につき 1 つレコードを書きます。`postgresql.conf` ファイルと同じく `#` から行末まではコメントを表します。ファイルの最初はコメントが続き、最後の数行がレコードです。

レコードは 1 つ以上の空白またはタブで区切られた複数のフィールドからなります。フィールドには接続の条件とその条件に一致した場合に適用する認証方式を指定します。認証はファイル内で最初に一致したレコードのみで行われます。一致するレコードがなければ接続は拒否されます。デフォルトではローカル接続に対してデータベースクラスタの作成時に指定した認証方式で認証を行う設定になっています。

ここでは、ローカル接続の認証方式を `scram-sha-256` 認証に変更し、ホストが直接接続しているサブネット内から `test` ユーザによる `test` データベースへの SSL によるホスト接続に対して同じく `scram-sha-256` 認証を行うレコードを追加します。

```config:$PGDATA/pg_hba.conf
# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     scram-sha-256
# IPv4 local connections:
host    all             all             127.0.0.1/32            scram-sha-256
# IPv6 local connections:
host    all             all             ::1/128                 scram-sha-256
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     scram-sha-256
host    replication     all             127.0.0.1/32            scram-sha-256
host    replication     all             ::1/128                 scram-sha-256
# Allow SSL connections from any address in any subnet that the server
# is directly connected to.
hostssl test            test            samenet                 scram-sha-256
```

1 つ目の `TYPE` フィールドには接続方式を指定します。`local` はローカル接続、`host` は SSL かどうかを問わないホスト接続、`hostssl` は SSL によるホスト接続を表します。

2 つ目の `DATABASE` フィールドには接続先のデータベース名をカンマ区切りで指定します。`all` はすべてのデータベース、`replication` はデータベース名でなく、レプリケーションの接続を表します。レプリケーションとはデータを別のノードに複製する機能のことです。

3 つ目の `USER` フィールドには接続を行うユーザ名をカンマ区切りで指定します。`all` はデータベースと同じくすべてのユーザを表します。

4 つ目の `ADDRESS` フィールドにはホスト接続の場合に接続元のアドレスをホスト名または IP アドレス範囲で指定します。`samenet` はホストが直接接続しているサブネット内のすべてのアドレスを表します。より安全を求めるなら接続を許可するクライアントのアドレスのみを指定したほうがいいでしょう。

5 つ目の `METHOD` フィールドには条件に一致した接続に適用する認証方式を指定します。`scram-sha-256` は SCRAM-SHA-256 暗号化によるパスワード認証を表します。`scram-sha-256` 認証はバージョン 10 で追加された認証方式で、以前からある `md5` 認証よりも安全ですが、古いバージョンのクライアントでは対応していません。

クライアント認証の設定を反映するには、設定ファイルの再読み込みが必要です。ここでは、あとでまとめて行うので省略します。

# ファイアウォールの設定

PostgreSQL のポート番号はデフォルトでは 5432 ですが、5432 ポートへのアクセスはデフォルトではファイアウォールで許可されていないので、リモートホストから接続できません。5432 ポートへのアクセスを許可するには、ファイアウォールの設定を行います。

まず、`root` ユーザに切り替わります。

```shell-session
$ su -
パスワード: (パスワードを入力)
最終ログイン: 2019/07/26 (金) 11:25:41 JST日時 pts/0
```

次に、5432 ポートへのアクセスを許可します。

```shell-session:CentOS&nbsp;7&nbsp;の場合
# firewall-cmd --permanent --add-port=5432/tcp
success
# firewall-cmd --reload
success
```

```shell-session:CentOS&nbsp;6&nbsp;の場合
# lokkit -p 5432:tcp
```

`root` ユーザからは `exit` コマンドで戻れますが、このあとの設定も `root` ユーザで行うので、戻らないでください。

# データベースサーバの再起動

パラメータやクライアント認証の設定を反映するため、データベースサーバを再起動します。

```shell-session:CentOS&nbsp;7&nbsp;の場合
# systemctl restart postgresql-11.service
```

```shell-session:CentOS&nbsp;6&nbsp;の場合
# service postgresql-11 restart
postgresql-11 サービスを停止中:                            [  OK  ]
postgresql-11 サービスを開始中:                            [  OK  ]
```

これで、データベースサーバ側の設定は完了です。

# クライアントの設定と接続確認

ここからはデータベースサーバへの接続を行うクライアント上での操作になります。

データベースサーバへの接続時にサーバが本物かどうかはデフォルトでは検査しません。そのため、やろうと思えばデータベースサーバの停止中に同じアドレスでサーバを起動し、なりすますことができてしまいます。それを防ぐにはサーバ証明書を検査するようにします。

サーバ証明書を検査するには、まず、ルート証明書をインストールします。ルート証明書は [SSL による通信暗号化の設定](#ssl-による通信暗号化の設定) で作成した `root.crt` ファイルです。

```shell-session
$ mkdir ~/.postgresql
$ scp root@dbhost.example.com:/var/lib/pgsql/11/data/root.crt ~/.postgresql
root@dbhost.example.com's password: (パスワードを入力)
root.crt                          100%  977     1.0KB/s   00:00
```

次に、サーバ証明書を検査するように指定し、データベースサーバに接続します。

データベースサーバへの接続時にどのように SSL 接続を行うかは `sslmode` 接続パラメータで指定します。デフォルトは `prefer` で、最初に SSL 接続を行い、失敗したら非 SSL 接続を行います。SSL 接続のみを行い、サーバ証明書を完全に検査するには `verify-full` を指定します。`psql` コマンドで接続パラメータを指定するには、接続先のデータベース名の代わりに `接続パラメータ名=値` を空白で区切った文字列を指定します。

```shell-session
$ psql -h dbhost.example.com -U test "dbname=test sslmode=verify-full"
ユーザ test のパスワード: (パスワードを入力)
psql (11.4)
SSL 接続 (プロトコル: TLSv1.2、暗号化方式: ECDHE-RSA-AES256-GCM-SHA384、ビット長: 256、圧縮: オフ)
"help" でヘルプを表示します。

test=>
```

「SSL 接続」と出力されれば、SSL 接続が行えています。

その都度、接続文字列を指定するのが面倒であれば、`~/.pgsql_profile` ファイルで環境変数 `PGSSLMODE` を設定するといいでしょう。

```bash:~/.pgsql_profile
export PGSSLMODE=verify-full
```

PostgreSQL にリモートホストから安全に接続するのに最低限必要な設定はこれで完了です。