Proxmox やってますか？
タイトルと違いますがProxmoxは便利ですね。こういったアプリの trial and error するときに環境まるっと消したり復元したり簡単でいいです。あと LXC（Linux Containers）使うとディスク容量もメモリもマシンに優しいですね。
最終的に目指すこと
PostgreSQL をインストールしてお終い。とするのは、わりとどこの記事でもありそうなので、すこし本番運用を目指しチューニングしてみることにします。
お題目は下記としてみます。
・PostgreSQL16 導入
・通信経路をSSL接続化
・A5:SQL Mk-2 から接続
・ODBCドライバからの接続
・publicスキーマ接続禁止
・ログローテーションの変更
PostgreSQL16 導入
「PostgreSQL 16」正式版がリリースされたので最新版を導入してみます！
yumリポジトリーを使用し AlmaLinx9 に PostgreSQL16をインストールしてみます。公式サイトに手順があるのでそちらをベースに進めます。
下記のように選択するとインストールコマンドを教えてくれます。
手順に従って yumリポジトリのインストールをします。
$ sudo dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-9-x86_64/pgdg-redhat-repo-latest.noarch.rpm
PostgreSQLモジュール無効化および本体のインストール
$ sudo dnf -qy module disable postgresql
$ sudo dnf install -y postgresql16-server
デフォルト文字コードをUTF-8、デフォルトロケールをCとして、データベースクラスタを作成します。PostgreSQLの有名な仕様としてロケールによって漢字ソートが想定とおりにならないという現象があるようですので、ロケールを無効にします。
$ sudo PGSETUP_INITDB_OPTIONS='--encoding=UTF-8 --no-locale' /usr/pgsql-16/bin/postgresql-16-setup initdb
データベース格納場所について
デフォルト設定は /var/lib/pgsql/16/data となりますので initdb 時に --pgdata パラメータで格納場所を変更することも可能です
接続ユーザを作成
次章以降で使用するPostgreSQLに接続するユーザを作成します（OSアカウントではありません）。
$ su - postgres
Last login: Thu Sep 21 01:43:59 2023
$ psql
psql (16.0)
Type "help" for help.
postgres=#
テスト接続用、hogeuserユーザーを作成しておきましょう
postgres=# create user hogeuser password 'hogeuser';
CREATE ROLE
postgres=# create database hogedb owner hogeuser;
CREATE DATABASE
とりあえずlocalhostでログインしてみる
[root@postgres ~]# psql -h localhost -U hogeuser hogedb
Password for user hogeuser:
psql (16.0)
Type "help" for help.
hogedb=>
パスワードの保存暗号化形式
PostgreSQLに接続する場合の認証方式は、パスワード認証やLDAP,SSL証明書などありますが、ここでは比較的設定が楽なパスワード認証で進めます。パスワード認証は、以下３種類ありますが業務で使用する場合は、scram-sha-256 一択になるのではないでしょうか？
ちなみに PostgreSQL 16では、パスワードを保存する場合にデフォルトで scram-sha-256 となるようです。
| パスワード認証方式 | 意味 |
|---|---|
| password | パスワードを平文で送信 |
| md5 | MD5ハッシュアルゴリズムで送信 |
| scram-sha-256 | RFC 7677に記述された方法でSCRAM-SHA-256 認証方式で送信 |
他の端末からログインしてみる
PostgreSQL は、アクセス元制限を行える機能がありデフォルトの設定では、他の端末から接続できまん。
listen_addresses = '*' # what IP address(es) to listen on;
#listen_addresses = 'localhost' # what IP address(es) to listen on;
接続するネットワーク範囲もしくは、特定IPを指定する。
host all all 100.100.0.0/24 scram-sha-256
セキュア SSL 接続を有効にする
PostgreSQLの通信経路をSSL化します。個人的に非武装地帯（DMZ）にDBあれば通信経路はSSL化しなくてもいいのでは？って偉い人に言ったら怒られましたｗ。ですので今回前向きに対応してみます。
まずオレオレ証明書を用意します。とりあえず証明書の有効期間は3650日にしてみて作成します。
証明書ペアを置いておくため場所と権限を吟味する
$ sudo openssl req -new -x509 -days 3650 -nodes -text -out /var/lib/pgsql/16/data/server.crt -keyout /var/lib/pgsql/16/data/server.key
$ sudo chown postgres:postgres /var/lib/pgsql/16/data/server.{crt,key}
$ sudo chmod og-rwx /var/lib/pgsql/16/data/server.key
セキュア SSL 接続を有効にする
ssl = on
ssl_cert_file = 'server.crt'
ssl_key_file = 'server.key'
接続するネットワーク範囲もしくは、特定IPの接続でSSL指定する。
※公式サイトの説明が判りずらいですが、hostssl指定します
hostssl all all 100.100.0.0/24 scram-sha-256
SSL通信できているか確認する
$ psql -h 100.100.0.100 -U hogeuser -d hogedb
Password for user hogeuser:
psql (16.0)
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off)
Type "help" for help.
hogedb=>
A5:SQL Mk-2 から接続
象さん(pgAdmin)ツールは、テーブル定義やDB周りをGUIで操作できるので便利なのですが個人的にSQLクライアントとして使用するなら A5:SQL Mk-2 が便利と思っています。ですのでインストールしましょう。
ところがこの A5:SQL Mk-2 から PostgreSQL に セキュア SSL 接続する場合、下記のエラーが発生する場合があります。公式の A5:SQL MK-2 掲示板にも書かれているように自身で使用しているSSH/SSLライブラリ仕様の問題だとか。
というわけで、2.17.4 以前の版だと下記のエラーもしくはライブラリが。。というエラーダイアログが表示されます。※著者もそうでした。
そんなわけで 2.18.4 以降のバージョンは問題なくセキュア SSL 接続することができます。
ODBC/JDBCドライバからの接続
ODBC/JDBCドライバからセキュア SSL に接続する場合は、SSLMode= で指定する。自身でためしたのは、AccessからODBC経由で Postgresセキュア SSL 接続する場合のパラメタは、SSLMode=require でした。ちなみにJAVAの場合、MyBatis を使用したときどうやらデフォルト？でセキュア SSL 接続するかチャレンジレスポンスするのか指定は不要だった。
ちなみに Postgres の ODBCドライバーは、別途インストールが必要です。
publicスキーマ接続禁止
PostgreSQL14以前は、全てのユーザーはデフォルトで Public ロールに属する らしいです。というか少しわかりずらいのが下記となります（少し強引ですが分かりやすいように説明）。
・データベースには必ず Publicスキーマが存在する。
・Publicスキーマロール権限というのがありデフォルトで許可されている。
結局どうなるかというとユーザ権限とデータベースと紐づけ下記のように権限をつけたとした場合、hoge1ユーザはhoge2dbのスキーマに対してアクセスできてしまうということになります。
| ユーザ | データベース |
|---|---|
| hoge1 | hoge1db |
| hoge2 | hoge2db |
Oracle, SQLServer を使用したことがあるのであれば へ？ ってなりそうな感覚ですね。
このことからPostgreSQLデータベースエンジニアに対して「今回の業務ではPublicスキーマを使ってますが？」っていうと、かなりの角度で こいつデータベースのなんたるか知らないやつ って嫌味言われます（実際私も何度か経験あり）。
もう少し踏み込んで。
この「Publicスキーマロール権限というのがありデフォルトで許可されている」は、PostgreSQL14以前の話であり15以降では対処されています。ではPostgreSQL14以前の場合どのようにすればよいかというと簡単です。
・Publicスキーマロール権限を無効に設定。
・全ユーザのデータベースへの接続(CONNECT)権限を無効に設定。
・該当ユーザのデータベースへの接続(CONNECT)権限を許可に設定。
コマンドで示すと下記となります。
postgres=# revoke connect on database postgres from public;
postgres=# revoke connect on database hoge1db from public;
postgres=# revoke connect on database hoge2db from public;
postgres=# grant connect on database hoge1db to hoge1;
postgres=# grant connect on database hoge2db to hoge2;
当たり前ですが PostgreSQL の adminユーザである postgres はどのデータベース、スキーマにもアクセスできます。
ちなみに。PostgreSQL15以降はこの考えは不要です。
ユーザ権限
上記でデータベースとユーザの関係は、簡単ながらも理解できた。次に新たにユーザを作成し既存のデータベースに接続したいケースは？という場合を想定してみる。
| ユーザ | データベース | 備考 |
|---|---|---|
| hoge1 | hoge1db | DBオーナ |
| hoge1app | hoge1db |
答えから言うと、hoge1ユーザ権限を継承しhoge1appユーザに付与するだけです。
postgres=# create user hoge1app with login password 'hoge1app';
postgres=# grant connect on database hoge1db to hoge1app;
postgres=# grant hoge1app to hoge1;
SQLServer 経験者だと、あれ？ データベースの権限にユーザを紐づけるんじゃね？となりますよね（割と大雑把に思ってください）。
ログ出力の設定および、ログローテーションの変更
PostgreSQL が出力するログは週単位（月～日の頭文字）で上書きされるためいいっちゃいいような気もするんですが（だって周単位で上書きされ、ログが溢れることはないから）、割と推奨があるので最低限でも下記としておきます。
この設定でYMD単位で出力されますが、ローテーションするファイル数を設定できないので注意が必要です。
log_filename = 'postgresql.log'
log_line_prefix = '[%t]%u %d %p[%l]'
log_min_duration_statement = 5000
じゃあ溢れるログファイルどうするの？ってことで２つあります。
・自作のログローテーションshを作成する。
・PostgreSQL ログを固定名にして logrotate を設定する。
・syslogにログを出力して logrotate を設定する。
どれも運用を見据えて設計するのでしょうが、個人的は自作で作成したほうがなにかとよいかなと思っています。
最後に
PostgreSQLは、無料のオープンソースのリレーショナルデータベース管理システム (RDBMS) 気軽に導入しやすい面があります。私もそういう形で使用しています。
ただ何でもそうですが、業務として使用する場合はセキュリティ面やバックアップなど当たり前ですが、そういう考慮は別途必要です。