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.