PostgreSQL 14: TLS 接続
Tags: postgres, database, security, libressl
Published: 2023-05-06 02:29:25

## はじめに

外部にある [PostgreSQL](http://postgresql.org/) サーバーをデータベース・サーバーとして使用する場合、TLS/SSL 接続を使用するのが良いでしょう。本記事では、証明書を生成して、それを用いてサーバーの設定を行い、さらにその検証をするところまでをご紹介します。

手順は 3 つだけです。

1. サーバー証明書を準備する
    - 自己証明書の生成
    - (必要に応じて) CA 署名の証明書の生成 - クライアント検証用
2. サーバーの設定ファイルを編集する
    - postgresql.conf : オプションの編集 - listen_address / ssl
    - pg_hba.conf : hostssl 定義の追加
3. クライアントマシンから検証する
    - `psql` を sslmode で使用

### 環境

- [OpenBSD](https://www.openbsd.org) 7.1
- PostgreSQL 14.2

### 参考

- "ssl-tcp" に関する公式ドキュメント (英語): [14](https://www.postgresql.org/docs/14/ssl-tcp.html), [current](https://www.postgresql.org/docs/current/ssl-tcp.html)

## チュートリアル

### 1. サーバー証明書を準備する

#### 自己証明書の生成

_postgresql ユーザーで PostgreSQL の data ディレクトリにサーバー証明書を生成します。

```console
$ doas su - _postgresql
$ whoami
_postgresql

$ cd /var/postgresql/data
```

[openssl](https://man.openbsd.org/openssl.1) コマンドで自己証明書を作成します。
"36500" はもちろん変更可能です。36500 日 = 約 100 年を有効期間として指定しています。

```console
$ # ksh
$ DB_HOST_DOMAIN="(...)"
$ openssl req -new -x509 -days 36500 -nodes -text -out server.crt -keyout server.key -subj "/CN=$DB_HOST_DOMAIN"
```

出力は以下の通りでした:

```console
Generating a 2048 bit RSA private key
..............................................................................+++++
................+++++
writing new private key to 'server.key'
-----
```

以下のようになっているでしょう:

```console
$ ls -l server\.*
-rw-r--r--  1 _postgresql  _postgresql  3660 Apr 24 13:17 server.crt
-rw-r--r--  1 _postgresql  _postgresql  1704 Apr 24 13:17 server.key
```

鍵のパーミッションを更新します:

```console
$ chmod 400 server.key
```

_postgresql ユーザーで /var/postgresql/data にいる状態のままで進んでください。

#### (必要に応じて) CA 署名の証明書の生成 - クライアント検証用

もしも v3_ca エクステンションを使うために /etc/ssl/openssl.cnf を編集して問題無いのであれば、クライアントから妥当なものと認識してもらえるサーバー証明書を作成できます。ルートと中間証明書を作成します。編集内容の詳細は [公式ドキュメント (英語)](https://www.postgresql.org/docs/current/ssl-tcp.html#SSL-CLIENT-CERTIFICATES) に書かれています:

```console
$ # create a certificate signing request (CSR) and a public/private key file
$ openssl req -new -nodes -text -out root.csr -keyout root.key -subj "/CN=$ROOT_CA_DOMAIN"
$ chmod 400 root.key

$ # create a root certificate authority
$ openssl x509 -req -in root.csr -text -days 36500 -extfile /etc/ssl/openssl.cnf -extensions v3_ca -signkey root.key -out root.crt
$ # ... might be end with "Error Loading extension section v3_ca"

$ # create a server certificate signed by the new root certificate authority
$ openssl req -new -nodes -text -out server.csr -keyout server.key -subj "/CN=$DB_HOST_DOMAIN"
$ chmod 400 server.key

$ openssl x509 -req -in server.csr -text -days 36500 -CA root.crt -CAkey root.key -CAcreateserial -out server.crt
```

ここで v3_ca エクステンションに起因するエラーが発生するかもしれません。その場合は /etc/ssl/openssl.cnf を編集すれば良いです。[こちらの記事](https://obsd.solutions/ja/blog/2023/05/05/libressl-error-due-to-missing-v3_ca-in-extension/index.html) を参考にしてください。

```console
$ ls {root,server}*
root.crt   root.csr   root.key   root.srl   server.crt server.csr server.key
```

### 2. サーバーの設定ファイルを編集する

`/var/postgresql/data` に `_postgres` ユーザーでいる前提で進みます。よろしいですね ?

```console
$ whoami
_postgresql
$ pwd  
/var/postgresql/data
```

postgresql.conf を編集します:

```console
$ nvim postgresql.conf
```

外部のクライアントからのリクエストを受け付け、且つ、SSL 接続を有効にするためです:

```diff
  #listen_addresses = 'localhost' ...
+ listen_addresses = '*'
  ...
  #port = 5432
+ port = {$DB_PORT} # (optional) for security
  ...
  #ssl = off
+ ssl = on
  #ssl_cert_file = 'server.crt'
  #ssl_ca_file = ''
  #ssl_crl_file = ''
  #ssl_key_file = 'server.key'
```

なお使っているのが自己証明書では無く root.crt か何かの場合は `ssl_ca_file` で指定する必要があります。

次に pg_hba.conf を編集します:

```console
$ nvim pg_hba.conf
```

末尾に hostssl の行を追加しましょう。クライアントからの SSL 接続が許容されます:

```diff
+ hostssl all             all             0.0.0.0/0               md5
```

14 からは、自己証明書を使用する時 `clientcert` に 0 をセットする必要は無くなりました。この項目は ["auth-options" 認証オプション (英語)](https://www.postgresql.org/docs/14/auth-pg-hba-conf.html) です。妥当なクライアント証明書がある場合は `verify-ca` や `verify-full` をセットします。[13 とそれ以前では](https://obsd.solutions/ja/blog/2023/05/06/postgresql-12-tls-connection/index.html) セットできる値は 1 (デフォルトです。それに加えて)/0/no-verify でした。

余談ですが、途中で何か問題が発生することがあれば、/var/postgresql/logfile が詳細を知るためにきっと役に立つでしょう。

もとのユーザーに戻りましょう:

```console
$ # end of behavior as _postgresql
$ exit
```

データベース・サーバーを再起動します:

```console
$ doas rcctl restart postgresql
```

完了です。

### 3. クライアントマシンから検証する

クライアント・マシンで、[`psql`](https://www.postgresql.org/docs/current/app-psql.html) を "sslmode=require" にして使います:

```console
$ psql "sslmode=require host=$DB_HOST port=$DB_PORT user=$DB_USER dbname=$DB_NAME"
Password for user ...: 
```

DB ユーザーのパスワードを入力しましょう。

```console
psql (14.2)
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.

postgres=#
```

無事に "protocol: TLSv1.3" 等で構築された TLS 接続を獲得できました :)