What's？

タイトルどおり、AlmaLinx 9にPostgreSQLのyumリポジトリーを使用してPostgreSQL 15をインストールしてみます。

この時に、SSL接続もできるようにしてみようと思います。

環境

今回の環境は、こちらです。

$ cat /etc/redhat-release
AlmaLinux release 9.1 (Lime Lynx)


$ uname -srvmpio
Linux 5.14.0-162.23.1.el9_1.x86_64 #1 SMP PREEMPT_DYNAMIC Tue Apr 11 10:43:28 EDT 2023 x86_64 x86_64 x86_64 GNU/Linux


$ openssl version
OpenSSL 3.0.1 14 Dec 2021 (Library: OpenSSL 3.0.1 14 Dec 2021)

インストール方法を確認する

インストール方法を確認しましょう。

こちらのページで「Linux」を選択。

Linuxディストリビューションの選択肢が現れるので、「Red Hat/Rocky/CentOS」を選択。

すると、こちらのページに移ります。

このページで対象としているLinuxディストリビューションは以下ですが、AlmaLinuxでもまあいいでしょうということで続けてみます。

Red Hat Enterprise Linux
Rocky Linux
CentOS 6または7
Fedora
Oracle Linux

RPMファイルを直接使う場合はこちら。

PostgreSQL 15をyumリポジトリからインストールする

では、PostgreSQL 15をインストールしていきます。

以下のページで、「Select version」を「15」に、「Select platform」を「Red Hat Enterprise, Rocky, or Oracle version9」に、「Select architecture」を「x86_64」にして、表示されたコマンドを実行していきます。

yumリポジトリのインストール。

$ sudo dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-9-x86_64/pgdg-redhat-repo-latest.noarch.rpm

このリポジトリを初めて使う時には、GPG鍵のインポート確認を求められます。

ドキュメントでは、デフォルトのPostgreSQLモジュールを無効化するように書かれていますが、これを実行するとエラーになります。

$ sudo dnf -qy module disable postgresql
引数 postgresql を解決できません
エラー: リクエスト中の問題:
足りないグループまたはモジュール: postgresql

$ dnf module list postgresql
Extra Packages for Enterprise Linux 9 - x86_64
エラー: 表示する一致モジュールはありません

ちなみに、AlmaLinux 8ではありました。

$ dnf module list postgresql
Failed to set locale, defaulting to C.UTF-8
AlmaLinux 8 - BaseOS                                                                                                                                     3.4 MB/s | 6.3 MB     00:01
AlmaLinux 8 - AppStream                                                                                                                                  5.3 MB/s |  12 MB     00:02
AlmaLinux 8 - Extras                                                                                                                                      21 kB/s |  19 kB     00:00
Extra Packages for Enterprise Linux 8 - x86_64                                                                                                           5.4 MB/s |  14 MB     00:02
Last metadata expiration check: 0:00:01 ago on Sat Apr 15 07:17:58 2023.
AlmaLinux 8 - AppStream
Name                                    Stream                             Profiles                                       Summary
postgresql                              9.6                                client, server [d]                             PostgreSQL server and client module
postgresql                              10 [d]                             client, server [d]                             PostgreSQL server and client module
postgresql                              12                                 client, server [d]                             PostgreSQL server and client module
postgresql                              13                                 client, server [d]                             PostgreSQL server and client module

Hint: [d]efault, [e]nabled, [x]disabled, [i]nstalled

ということで、PostgreSQL 15をインストール。

$ sudo dnf install postgresql15-server

今回インストールされたのは、PostgreSQL 15.2でした。

$ psql --version
psql (PostgreSQL) 15.2

パッケージの情報。

$ dnf info postgresql15-server
Extra Packages for Enterprise Linux 9 - x86_64                                                                                                           8.9 kB/s | 3.8 kB     00:00
PostgreSQL common RPMs for RHEL / Rocky 9 - x86_64                                                                                                        89  B/s | 195  B     00:02
PostgreSQL 15 for RHEL / Rocky 9 - x86_64                                                                                                                 96  B/s | 195  B     00:02
PostgreSQL 14 for RHEL / Rocky 9 - x86_64                                                                                                                 91  B/s | 195  B     00:02
PostgreSQL 13 for RHEL / Rocky 9 - x86_64                                                                                                                100  B/s | 195  B     00:01
PostgreSQL 12 for RHEL / Rocky 9 - x86_64                                                                                                                 87  B/s | 195  B     00:02
PostgreSQL 11 for RHEL / Rocky 9 - x86_64                                                                                                                 97  B/s | 195  B     00:02
インストール済みパッケージ
名前         : postgresql15-server
バージョン   : 15.2
リリース     : 1PGDG.rhel9
Arch         : x86_64
サイズ       : 24 M
ソース       : postgresql15-15.2-1PGDG.rhel9.src.rpm
リポジトリー : @System
repo から    : pgdg15
概要         : The programs needed to create and run a PostgreSQL server
URL          : https://www.postgresql.org/
ライセンス   : PostgreSQL
説明         : PostgreSQL is an advanced Object-Relational database management system (DBMS).
             : The postgresql15-server package contains the programs needed to create
             : and run a PostgreSQL server, which will in turn allow you to create
             : and maintain PostgreSQL databases.

新しいデータベースクラスタの作成。

$ sudo PGSETUP_INITDB_OPTIONS='--encoding=UTF-8 --no-locale' postgresql-15-setup initdb

環境変数PGSETUP_INITDB_OPTIONSを使って、エンコーディングをUTF-8、ロケールをC（なし）で作成。

ロケールがCとなっていることを、設定ファイル上で確認。

$ sudo grep lc_ /var/lib/pgsql/15/data/postgresql.conf
lc_messages = 'C'                       # locale for system error message
lc_monetary = 'C'                       # locale for monetary formatting
lc_numeric = 'C'                        # locale for number formatting
lc_time = 'C'                           # locale for time formatting

これを忘れると、現在のロケールで設定されてしまいます。

$ sudo grep lc_ /var/lib/pgsql/15/data/postgresql.conf
lc_messages = 'ja_JP.utf8'                      # locale for system error message
lc_monetary = 'ja_JP.utf8'                      # locale for monetary formatting
lc_numeric = 'ja_JP.utf8'                       # locale for number formatting
lc_time = 'ja_JP.utf8'                          # locale for time formatting

自動起動の有効化。

$ sudo systemctl enable postgresql-15

ステータス。

$ sudo systemctl status postgresql-15
○ postgresql-15.service - PostgreSQL 15 database server
     Loaded: loaded (/usr/lib/systemd/system/postgresql-15.service; enabled; vendor preset: disabled)
     Active: inactive (dead)
       Docs: https://www.postgresql.org/docs/15/static/

起動。

$ sudo systemctl start postgresql-15

ステータスを再度確認。

$ sudo systemctl status postgresql-15
● postgresql-15.service - PostgreSQL 15 database server
     Loaded: loaded (/usr/lib/systemd/system/postgresql-15.service; enabled; vendor preset: disabled)
     Active: active (running) since Sat 2023-04-15 16:30:19 JST; 20s ago
       Docs: https://www.postgresql.org/docs/15/static/
    Process: 3400 ExecStartPre=/usr/pgsql-15/bin/postgresql-15-check-db-dir ${PGDATA} (code=exited, status=0/SUCCESS)
   Main PID: 3405 (postmaster)
      Tasks: 7 (limit: 11077)
     Memory: 17.5M
        CPU: 81ms
     CGroup: /system.slice/postgresql-15.service
             ├─3405 /usr/pgsql-15/bin/postmaster -D /var/lib/pgsql/15/data/
             ├─3406 "postgres: logger "
             ├─3407 "postgres: checkpointer "
             ├─3408 "postgres: background writer "
             ├─3410 "postgres: walwriter "
             ├─3411 "postgres: autovacuum launcher "
             └─3412 "postgres: logical replication launcher "

 4月 15 16:30:19 alma9.localdomain systemd[1]: Starting PostgreSQL 15 database server...
 4月 15 16:30:19 alma9.localdomain postmaster[3405]: 2023-04-15 16:30:19.747 JST [3405] LOG:  redirecting log output to logging collector process
 4月 15 16:30:19 alma9.localdomain postmaster[3405]: 2023-04-15 16:30:19.747 JST [3405] HINT:  Future log output will appear in directory "log".
 4月 15 16:30:19 alma9.localdomain systemd[1]: Started PostgreSQL 15 database server.

起動したようです。

データ等は、/var/lib/pgsql/15ディレクトリにあります。

$ sudo ls -l /var/lib/pgsql/15
合計 8
drwx------.  2 postgres postgres    6  2月  9 21:30 backups
drwx------. 20 postgres postgres 4096  4月 15 16:30 data
-rw-------.  1 postgres postgres 1246  4月 15 16:27 initdb.log

接続確認

とりあえず、この状態でpsqlで接続してみます。

$ sudo su - postgres
[postgres@alma9 ~]$ psql
psql (15.2)
"help"でヘルプを表示します。

postgres=# select version();
                                                 version
----------------------------------------------------------------------------------------------------------
 PostgreSQL 15.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 11.3.1 20220421 (Red Hat 11.3.1-2), 64-bit
(1 行)

OKですね。

ユーザーも作成しておきましょう。

postgres=# create user myuser password 'password';
CREATE ROLE
postgres=# create database example owner myuser;
CREATE DATABASE

接続確認。

$ psql -h localhost -U myuser example
ユーザー myuser のパスワード:
psql (15.2)
"help"でヘルプを表示します。

SSL接続を有効にする

続いて、SSL接続を有効にしてみましょう。

ドキュメントはこちら。

SSLを有効にするには、OpenSSL 1.0.1以上がインストールされていればOKです。

今回は、SSL証明書を自己署名証明書で作成し、sslmode=requireとするところまでをゴールにします。

SSL証明書の作成。

$ sudo openssl req -new -x509 -days 365 -nodes -text -out /var/lib/pgsql/15/data/server.crt -keyout /var/lib/pgsql/15/data/server.key

ドキュメントのサンプルのままですが、証明書の有効期間は365日ですね。

また、少なくともserver.keyはpostgresユーザー以外からのアクセスは禁じる必要があります。

On Unix systems, the permissions on server.key must disallow any access to world or group; achieve this by the command chmod 0600 server.key. Alternatively, the file can be owned by root and have group read access (that is, 0640 permissions).

とりあえず、server.keyとserver.crtの所有者をpostgresに変更。

$ sudo chown postgres:postgres /var/lib/pgsql/15/data/server.{crt,key}

server.key（秘密鍵）の権限変更。

$ sudo chmod og-rwx /var/lib/pgsql/15/data/server.key

/var/lib/pgsql/15/data/postgresql.confの、以下の箇所を変更します。

# - SSL -

#ssl = off
#ssl_ca_file = ''
#ssl_cert_file = 'server.crt'
#ssl_crl_file = ''
#ssl_crl_dir = ''
#ssl_key_file = '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 = ''
#ssl_passphrase_command_supports_reload = off

最低限の設定変更。

# - SSL -

ssl = on
#ssl_ca_file = ''
ssl_cert_file = '/var/lib/pgsql/15/data/server.crt'
#ssl_crl_file = ''
#ssl_crl_dir = ''
ssl_key_file = '/var/lib/pgsql/15/data/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 = ''
#ssl_passphrase_command_supports_reload = off

あとはPostgreSQLを再起動します。

$ sudo systemctl restart postgresql-15

この状態でpsqlでアクセスすると、SSL接続が有効になっていることが確認できます。

$ psql -h localhost -U myuser example
ユーザー myuser のパスワード:
psql (15.2)
SSL接続(プロトコル: TLSv1.3、暗号化方式: TLS_AES_256_GCM_SHA384、圧縮: オフ)
"help"でヘルプを表示します。

example=>

psqlは、SSL接続が利用可能であればSSLで接続するので。

SSL接続を行っているかを統計情報のビューで見るには、pg_stat_activityとpg_stat_sslを結合すれば良さそうですね。

select
  psa.datname, psa.pid, psa.usename, psa.application_name, psa.client_addr, psa.client_port, psa.query, pss.ssl, pss.version, pss.cipher, pss.bits, pss.client_dn, pss.client_serial, pss.issuer_dn
from
  pg_stat_activity psa
inner join
  pg_stat_ssl pss
on
  psa.pid = pss.pid

example=> select psa.datname, psa.pid, psa.usename, psa.application_name, psa.client_addr, psa.client_port, psa.query, pss.ssl, pss.version, pss.cipher, pss.bits, pss.client_dn, pss.client_serial, pss.issuer_dn from pg_stat_activity psa inner join pg_stat_ssl pss on psa.pid = pss.pid;
 datname | pid  | usename | application_name | client_addr | client_port |
                         query                                                                                                                                        | ssl | version |
       cipher         | bits | client_dn | client_serial | issuer_dn
---------+------+---------+------------------+-------------+-------------+---------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----+---------+--
----------------------+------+-----------+---------------+-----------
 example | 4895 | myuser  | psql             | 127.0.0.1   |       38844 | select psa.datname, psa.pid, psa.usename, psa.application_name, psa.client_addr, psa.client_port, psa.query, p
ss.ssl, pss.version, pss.cipher, pss.bits, pss.client_dn, pss.client_serial, pss.issuer_dn from pg_stat_activity psa inner join pg_stat_ssl pss on psa.pid = pss.pid; | t   | TLSv1.3 | T
LS_AES_256_GCM_SHA384 |  256 |           |               |
(1 行)

もしくは、拡張モジュールsslinfoを使います。

ただ、今回のインストール手順を実行した状態で拡張モジュールを有効にしようとすると失敗します。

postgres=# create extension sslinfo;
ERROR:  extension "sslinfo" is not available
DETAIL:  Could not open extension control file "/usr/pgsql-15/share/extension/sslinfo.control": No such file or directory.
HINT:  The extension must first be installed on the system where PostgreSQL is running.

拡張モジュールを使うには、postgresql15-contribをインストールしておく必要があります。

$ sudo dnf install postgresql15-contrib

これで、管理ユーザーであれば拡張モジュールの有効化ができます。

対象のデータベースで、sslinfoもジューを有効化。

$ psql example
psql (15.2)
"help"でヘルプを表示します。

example=# create extension sslinfo;
CREATE EXTENSION

一般ユーザーだとNGですね。

example=> create extension sslinfo;
ERROR:  permission denied to create extension "sslinfo"
HINT:  Must be superuser to create this extension.

確認。

# Unixドメインソケット
$ psql example
psql (15.2)
"help"でヘルプを表示します。

example=# select ssl_is_used();
 ssl_is_used
-------------
 f
(1 行)


# TCP/IP with SSL
$ psql -h localhost -U myuser example
ユーザー myuser のパスワード:
psql (15.2)
SSL接続(プロトコル: TLSv1.3、暗号化方式: TLS_AES_256_GCM_SHA384、圧縮: オフ)
"help"でヘルプを表示します。

example=> select ssl_is_used();
 ssl_is_used
-------------
 t
(1 行)

OKですね。

なお、sslinfoモジュールで取得できる情報は、pg_stat_sslに含まれているものもあるようです。

Some of the information available through this module can also be obtained using the built-in system view pg_stat_ssl.