PostgreSQL 15 : PostgreSQL over SSL/TLS2023/07/05

	PostgreSQL での SSL/TLS による暗号化通信の設定です。
[1]	デフォルトで SSL/TLS は有効となっているため、特別な要件がない限りは、設定変更の必要はありません。よって、TCP 接続する場合は、自動的に SSL/TLS による暗号化通信となります。

# SSL/TLS の設定
# 証明書は OS バンドルを使用

root@www:~#

grep -n ^ssl /etc/postgresql/15/main/postgresql.conf

105:ssl = on
107:ssl_cert_file = '/etc/ssl/certs/ssl-cert-snakeoil.pem'
110:ssl_key_file = '/etc/ssl/private/ssl-cert-snakeoil.key'

# 接続方式の設定

root@www:~#

grep -v -E '^#|^$' /etc/postgresql/15/main/pg_hba.conf

local   all             postgres                                peer
local   all             all                                     peer
host    all             all             127.0.0.1/32            scram-sha-256
host    all             all             ::1/128                 scram-sha-256
local   replication     all                                     peer
host    replication     all             127.0.0.1/32            scram-sha-256
host    replication     all             ::1/128                 scram-sha-256

# デフォルトのソケット通信で接続する場合は非暗号化

debian@www:~$

psql testdb

psql (15.3 (Debian 15.3-0+deb12u1))
Type "help" for help.

testdb=> \q

# TCP 接続する場合は暗号化

debian@www:~$

psql -h 127.0.0.1 -d testdb -U debian

Password for user debian:
psql (15.3 (Debian 15.3-0+deb12u1))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off)
Type "help" for help.

testdb=> select name as "Parameter name", setting as value, short_desc from pg_settings where name like '%ssl%';
             Parameter name             |                 value                  |                               short_desc     
----------------------------------------+----------------------------------------+-------------------------------------------------------------------------
 ssl                                    | on                                     | Enables SSL connections.
 ssl_ca_file                            |                                        | Location of the SSL certificate authority file.
 ssl_cert_file                          | /etc/ssl/certs/ssl-cert-snakeoil.pem   | Location of the SSL server certificate file.
 ssl_crl_dir                            |                                        | Location of the SSL certificate revocation list directory.
 ssl_crl_file                           |                                        | Location of the SSL certificate revocation list file.
 ssl_key_file                           | /etc/ssl/private/ssl-cert-snakeoil.key | Location of the SSL server private key file.
 ssl_library                            | OpenSSL                                | Shows the name of the SSL library.
 ssl_passphrase_command_supports_reload | off                                    | Controls whether ssl_passphrase_command is called during server reload.
 ssl_prefer_server_ciphers              | on                                     | Give priority to server ciphersuite order.
(9 rows)