PostgreSQL

PostgreSQL SSL通信設定

2023.07.17

PostgreSQLをデフォルトでインストールした状態だと、クライアントとPostgreSQLサーバ間は非SSLでの接続になっていますが、これをSSL接続できるようにします。

環境

  • CentOS Linux 8
  • OpenSSL 1.1.1k
  • PostgreSQL 13.9

処理の概要

  1. OpenSSLで自己証明書を作成する
  2. 作成した証明書と秘密鍵をサーバへ配備
  3. postgresql.confの編集
  4. PostgreSQLの再起動
  5. SSLで接続できる事の確認

OpenSSLで自己証明書を作成する

PostgreSQLをセットアップするサーバの適当なフォルダにて、rootユーザで以下コマンドを実行するとserver.crt（サーバ証明書）、server.key（秘密鍵）が作成できます。（今回は自己証明書なので証明書著名要求ファイル(CDR)は作成しません）

自己証明書作成コマンド
openssl req -new -x509 -days 365 -nodes -out server.crt -keyout server.key
コマンドオプション解説
  • req -new ：証明書リクエストを新規に作成要求
  • -x509：自己署名証明書を作成する
  • -days 3650：証明書の有効期間365×10=10年間
  • -nodes：秘密鍵の暗号化を行わない。（これにより、秘密鍵ファイルの使用時にパスフレーズを入力する必要がなくなる。これではセキュリティ上のリスクがあるため、実際の運用環境ではパスフレーズを使用すべき）
  • -out server.crt：証明書の出力先ファイル名
  • -keyout server.key：秘密鍵の出力先ファイル名
自己証明書作成例
[root@vm022 tmp]# openssl req -new -x509 -days 3650 -nodes -out server.crt -keyout server.key
Generating a RSA private key
......................................................................+++++
..........................+++++
writing new private key to 'server.key'
-----
You are about to be asked to enter information that will be incorporated
into your certificate request.
What you are about to enter is what is called a Distinguished Name or a DN.
There are quite a few fields but you can leave some blank
For some fields there will be a default value,
If you enter '.', the field will be left blank.
-----
Country Name (2 letter code) [XX]:jp
State or Province Name (full name) []:sooni
Locality Name (eg, city) [Default City]:tokyo
Organization Name (eg, company) [Default Company Ltd]:My Company
Organizational Unit Name (eg, section) []:IT Department
Common Name (eg, your name or your server's hostname) []:vm022
Email Address []:sooni@example.com
[root@vm022 tmp]#

[root@vm022 tmp]# ls -lt
合計 8
-rw-r--r--. 1 root root 1440  7月 16 07:58 server.crt
-rw-------. 1 root root 1704  7月 16 07:55 server.key
-- 
-- オーナーをPostgreSQL実行ユーザに変更する
--
[root@vm022 tmp]# chown postgres:postgres server.key
[root@vm022 tmp]# chown postgres:postgres server.crt
[root@vm022 tmp]# ls -lt
合計 8
-rw-r--r--. 1 postgres postgres 1440  7月 16 07:58 server.crt
-rw-------. 1 postgres postgres 1704  7月 16 07:55 server.key
証明書と秘密鍵のオーナを変更する

rootユーザで作成しているので、PostgreSQLセットアップユーザ（ここではpostgres）にファイルの所有者を変更する

[root@vm022 tmp]# chown postgres:postgres server.key
[root@vm022 tmp]# chown postgres:postgres server.crt
[root@vm022 tmp]# ls -lt
合計 8
-rw-r--r--. 1 postgres postgres 1440  7月 16 07:58 server.crt
-rw-------. 1 postgres postgres 1704  7月 16 07:55 server.key

作成した証明書と秘密鍵をサーバへ配備

$PGDATA直下に配備する例もよく見ますが個人的にはそれぞれ以下フォルダへ配備するのが好みです。（/etc/ssl/privateフォルダはデフォルトでは存在しないので今回新規に作成します）

--
-- 証明書と秘密鍵の配備先
--
/etc/ssl/certs/server.crt
/etc/ssl/private/server.key
[root@vm022 tmp]# mv server.crt /etc/ssl/certs/
[root@vm022 tmp]# mkdir /etc/ssl/private/
[root@vm022 tmp]# mv server.key /etc/ssl/private/
[root@vm022 tmp]# ls -lt /etc/ssl/certs/server.crt
-rw-------. 1 postgres postgres 1440  7月 16 08:05 /etc/ssl/certs/server.crt
[root@vm022 tmp]# ls -lt /etc/ssl/private/server.key
-rw-------. 1 postgres postgres 1704  7月 16 08:06 /etc/ssl/private/server.key
[root@vm022 tmp]#

postgresql.confの編集

今回編集するのは以下赤字３か所です。

# - SSL -

ssl = on
#ssl_ca_file = ''
ssl_cert_file = '/etc/ssl/certs/server.crt'
#ssl_crl_file = ''
ssl_key_file = '/etc/ssl/private/server.key'
#ssl_ciphers = 'HIGH:MEDIUM:+3DES:!aNULL' # allowed SSL ciphers
#ssl_prefer_server_ciphers = on
#ssl_ecdh_curve = 'prime256v1'
#ssl_min_protocol_version = 'TLSv1.2'
#ssl_max_protocol_version = ''
#ssl_dh_params_file = ''
#ssl_passphrase_command = ''

postgresqlの再起動

postgresql.confの編集内容を反映させるためにPostgreSQLを再起動します。

[root@vm022 ~]# systemctl stop postgresql-13
[root@vm022 ~]# systemctl start postgresql-13

SSLで接続できる事の確認

以下の通り、psqlで接続するとSSL接続が行われたメッセージが出るようになるのですぐに確認できます。また、pg_stat_activityビューを参照してもSSL接続は確認できます。

sooni >> psql -h vm022 -d myposdb -U sooni
psql (14.7、サーバー 13.9)
SSL 接続 (プロトコル: TLSv1.3、暗号化方式: TLS_AES_256_GCM_SHA384、ビット長: 256、圧縮: オフ)
"help"でヘルプを表示します。
myposdb=#
myposdb=# select a.pid,a.datname, a.usename,a.application_name, s.ssl
myposdb-# --, client_addr
myposdb-# ,to_char(a.backend_start, 'YY/MM/DD HH24:MI:SS') as backend_start
myposdb-# ,to_char(a.xact_start, 'YY/MM/DD HH24:MI:SS') as xact_start
myposdb-# --,a.state,a.wait_event,a.wait_event_type
myposdb-# from pg_stat_activity a left outer join pg_stat_ssl s
myposdb-# on s.pid = a.pid
myposdb-# where 1=1 and datname is not null
myposdb-# order by backend_start
myposdb-# ;
  pid  | datname | usename  |     application_name     | ssl |   backend_start   |    xact_start
-------+---------+----------+--------------------------+-----+-------------------+-------------------
 36708 | myposdb | postgres | pgAdmin 4 - CONN:6723353 | t   | 23/07/16 12:34:50 |
 38474 | myposdb | sooni    | psql                     | t   | 23/07/16 14:44:46 | 23/07/16 14:45:17
(2 行)


myposdb=#

設定後に非SSLで接続する場合

psqlにて非SSLでつなぐ場合

SSL接続の設定をした後、やはり非SSLで接続したくなった場合は環境変数にPGSSLMODE=”disable”としてやれば非SSLで接続できます。（Linux環境ではexport PGSSLMODE=disable）

System32 >> $env:PGSSLMODE="disable"
System32 >> psql -h vm022 -d myposdb -U sooni
psql (14.7、サーバー 13.9)
"help"でヘルプを表示します。

myposdb=#
myposdb=# select a.pid,a.datname, a.usename,a.application_name, s.ssl
myposdb-# --, client_addr
myposdb-# ,to_char(a.backend_start, 'YY/MM/DD HH24:MI:SS') as backend_start
myposdb-# ,to_char(a.xact_start, 'YY/MM/DD HH24:MI:SS') as xact_start
myposdb-# --,a.state,a.wait_event,a.wait_event_type
myposdb-# from pg_stat_activity a left outer join pg_stat_ssl s
myposdb-# on s.pid = a.pid
myposdb-# where 1=1 and datname is not null
myposdb-# order by backend_start
myposdb-# ;
  pid  | datname | usename  |     application_name     | ssl |   backend_start   |    xact_start
-------+---------+----------+--------------------------+-----+-------------------+-------------------
 36708 | myposdb | postgres | pgAdmin 4 - CONN:6723353 | t   | 23/07/16 12:34:50 |
 38474 | myposdb | sooni    | psql                     | t   | 23/07/16 14:44:46 |
 38551 | myposdb | sooni    | psql                     | f   | 23/07/16 14:51:17 | 23/07/16 14:53:18
(3 行)


myposdb=#
jdbcにて非SSLでつなぐ場合

以下のように接続URLのパラメータに設定する事で非SSLで接続する事可能です。

 final String URL  = "jdbc:postgresql://vm022:5432/myposdb?sslmode=disable";
myposdb=# \g
  pid  | datname | usename  |     application_name     | ssl |   backend_start   |    xact_start
-------+---------+----------+--------------------------+-----+-------------------+-------------------
 36708 | myposdb | postgres | pgAdmin 4 - CONN:6723353 | t   | 23/07/16 12:34:50 |
 38474 | myposdb | sooni    | psql                     | t   | 23/07/16 14:44:46 | 23/07/16 14:59:35
 38551 | myposdb | sooni    | psql                     | f   | 23/07/16 14:51:17 |
 38653 | myposdb | sooni    | PostgreSQL JDBC Driver   | f   | 23/07/16 14:59:28 |
(4 行)


myposdb=#

クライアントからの接続をSSLに限定するには

これまでの手順のようにpostgresql.confにssl = onと記載しても、クライアント側の設定により非SSLで接続する事ができましたが、これを「かならずSSL接続に限定」するには、ここまでの手順に加え、pg_hba.confのTYPEフィールドにhostsslと記載する事で可能となります。具体的には以下のように記載した場合、192.168.3.1/24からの接続は全てSSLが求められます。この時TLSのバージョンについてはpostgresql.conf内のssl_min_protocol_versionで制御されます。

# TYPE  DATABASE        USER            ADDRESS                 METHOD
hostssl    all           all             192.168.3.1/24          md5

今回接続を確認したSQLはこちら

PostgreSQL セッション一覧を確認する
pg_stat_activity pg_stat_activityビューはOracleでいうv$sessionに相当すると思います。セッション一覧と記載していますが、正確には次の通りです。PostgreSQLのインスタンスではクライアントか...
oha-yo.com
2023.06.23
スポンサーリンク
sooni
タイトルとURLをコピーしました