What's？
タイトルどおり、Ubuntu Linux 22.04 LTSにPostgreSQLのaptリポジトリーからPostgreSQL 16をインストールしてみます。
この時に、SSL接続についても確認しておきたいと思います。
環境
今回の環境はこちら。
$ lsb_release -a
No LSB modules are available.
Distributor ID: Ubuntu
Description: Ubuntu 22.04.4 LTS
Release: 22.04
Codename: jammy
$ uname -srvmpio
Linux 5.15.0-112-generic #122-Ubuntu SMP Thu May 23 07:48:21 UTC 2024 x86_64 x86_64 x86_64 GNU/Linux
$ openssl version
OpenSSL 3.0.2 15 Mar 2022 (Library: OpenSSL 3.0.2 15 Mar 2022)
インストール方法を確認する
まずはインストール方法を確認します。
こちらのページで「Linux」を選択。
Linuxディストリビューションの選択肢が現れるので、「Ubuntu」を選択。
こちらのページに移ります。
最初にOS標準パッケージのインストール方法が書かれています。
$ sudo apt install postgresql
もしインストールした場合、どのバージョンが入るのか確認してみましょう。
$ apt show postgresql
Package: postgresql
Version: 14+238
Priority: optional
Section: database
Source: postgresql-common (238)
Origin: Ubuntu
Maintainer: Ubuntu Developers <ubuntu-devel-discuss@lists.ubuntu.com>
Original-Maintainer: Debian PostgreSQL Maintainers <team+postgresql@tracker.debian.org>
Bugs: https://bugs.launchpad.net/ubuntu/+filebug
Installed-Size: 71.7 kB
Depends: postgresql-14
Suggests: postgresql-doc
Task: postgresql-server
Download-Size: 3,288 B
APT-Sources: https://mirrors.edge.kernel.org/ubuntu jammy/main amd64 Packages
Description: オブジェクトリレーショナル SQL データベース (サポート版)
本メタパッケージはサポートされる最新版の PostgreSQL データベースサーバに常 に依存しています。
.
PostgreSQL はフル機能を備えたオブジェクトリレーショナルデータベース管理シス テムです。SQL
標準の大部分をサポートし、多くの点でユーザが拡張できるよう設 計されています。機能の一部を以下に示します: ACID
トランザクション、外部キー、ビュー、シーケンス、サブクエリ、トリガ、 ユーザ定義型および関数、外部結合、多版型同時実行制御。グラフィカルユーザイ
ンターフェースや多くのプログラム言語用のバインディングも入手できます。
PostgreSQL 14がインストールされるようですね。ちょっと古いです。
というわけで、今回はPostgreSQL 16をインストールするべくPostgreSQLのaptリポジトリーを使うことにします。
PostgreSQL 16をaptリポジトリーからインストールする
では、ドキュメントに従ってUbuntu Linux 22.04 LTSにPostgreSQLのaptリポジトリーを追加します。
aptリポジトリー自体についての詳しい情報は、こちらのWikiに書かれているようです。
このaptリポジトリーの提供範囲は、現時点で以下のようです。
- Debian
- buster (10), bullseye (11), bookworm (12), trixie (testing/13) and sid (unstable)
- Ubuntu
- focal (20.04), jammy (22.04), mantic (23.10, amd64 only), noble (24.04)
- Architectures
- amd64 (64-bit x86), i386 (32-bit x86, being phased out), arm64 (64-bit ARM), ppc64el (little-endian 64-bit POWER), s390x (IBM z-Series)
- PostgreSQL
- 10, 11, 12, 13, 14, 15, 16, 17（開発中）
- Server extensions
- Slony-I, various PL languages, and datatypes
- Applications
- like omnidb, pgbouncer, and pgpool-II
インストール方法は、シェルスクリプトを使って自動化されたものと
$ sudo apt install -y postgresql-common
$ sudo /usr/share/postgresql-common/pgdg/apt.postgresql.org.sh
GPG鍵をインポートしたりなど、手動で行う方法があるようです。
今回は手動で行ってみます。
$ sudo apt install curl ca-certificates
$ sudo install -d /usr/share/postgresql-common/pgdg
$ sudo curl -o /usr/share/postgresql-common/pgdg/apt.postgresql.org.asc --fail https://www.postgresql.org/media/keys/ACCC4CF8.asc
$ sudo sh -c 'echo "deb [signed-by=/usr/share/postgresql-common/pgdg/apt.postgresql.org.asc] https://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
$ sudo apt update
ここまで実行すると、パッケージpostgresqlの内容がaptリポジトリーで提供されるものに変わります。
$ apt show postgresql
Package: postgresql
Version: 16+260.pgdg22.04+1
Priority: optional
Section: database
Source: postgresql-common (260.pgdg22.04+1)
Maintainer: Debian PostgreSQL Maintainers <team+postgresql@tracker.debian.org>
Installed-Size: 74.8 kB
Depends: postgresql-16
Suggests: postgresql-doc
Download-Size: 69.4 kB
APT-Sources: https://apt.postgresql.org/pub/repos/apt jammy-pgdg/main amd64 Packages
Description: オブジェクトリレーショナル SQL データベース (サポート版)
本メタパッケージはサポートされる最新版の PostgreSQL データベースサーバに常 に依存しています。
.
PostgreSQL はフル機能を備えたオブジェクトリレーショナルデータベース管理シス テムです。SQL
標準の大部分をサポートし、多くの点でユーザが拡張できるよう設 計されています。機能の一部を以下に示します: ACID
トランザクション、外部キー、ビュー、シーケンス、サブクエリ、トリガ、 ユーザ定義型および関数、外部結合、多版型同時実行制御。グラフィカルユーザイ
ンターフェースや多くのプログラム言語用のバインディングも入手できます。
N: 追加レコードが 1 件あります。表示するには '-a' スイッチを付けてください。
PostgreSQL 16を指していますね。
特定のバージョンを指定してインストールする場合は、
このくらいパッケージがあります。
$ apt search postgresql-1[0-9]$
ソート中... 完了
全文検索... 完了
postgresql-10/jammy-pgdg 10.23-3.pgdg22.04+1 amd64
The World's Most Advanced Open Source Relational Database
postgresql-10-dbgsym/jammy-pgdg 10.23-3.pgdg22.04+1 amd64
debug symbols for postgresql-10
postgresql-11/jammy-pgdg 11.22-4.pgdg22.04+1 amd64
The World's Most Advanced Open Source Relational Database
postgresql-11-dbgsym/jammy-pgdg 11.22-4.pgdg22.04+1 amd64
debug symbols for postgresql-11
postgresql-12/jammy-pgdg 12.19-1.pgdg22.04+1 amd64
The World's Most Advanced Open Source Relational Database
postgresql-12-dbgsym/jammy-pgdg 12.19-1.pgdg22.04+1 amd64
debug symbols for postgresql-12
postgresql-13/jammy-pgdg 13.15-1.pgdg22.04+1 amd64
The World's Most Advanced Open Source Relational Database
postgresql-13-dbgsym/jammy-pgdg 13.15-1.pgdg22.04+1 amd64
debug symbols for postgresql-13
postgresql-14/jammy-pgdg 14.12-1.pgdg22.04+1 amd64
The World's Most Advanced Open Source Relational Database
postgresql-14-dbgsym/jammy-pgdg 14.12-1.pgdg22.04+1 amd64
debug symbols for postgresql-14
postgresql-15/jammy-pgdg 15.7-1.pgdg22.04+1 amd64
The World's Most Advanced Open Source Relational Database
postgresql-15-dbgsym/jammy-pgdg 15.7-1.pgdg22.04+1 amd64
debug symbols for postgresql-15
postgresql-16/jammy-pgdg 16.3-1.pgdg22.04+1 amd64
The World's Most Advanced Open Source Relational Database
postgresql-16-dbgsym/jammy-pgdg 16.3-1.pgdg22.04+1 amd64
debug symbols for postgresql-16
今回は、PostgreSQL 16をインストールします。なんか、postgresqlパッケージと表示される情報が違う気がしますが…。
$ apt show postgresql-16
Package: postgresql-16
Version: 16.3-1.pgdg22.04+1
Priority: optional
Section: database
Maintainer: Debian PostgreSQL Maintainers <team+postgresql@tracker.debian.org>
Installed-Size: 59.8 MB
Provides: postgresql-16-jit-llvm (= 15), postgresql-contrib-16
Depends: locales | locales-all, postgresql-client-16, postgresql-common (>= 252~), ssl-cert, tzdata, debconf (>= 0.5) | debconf-2.0, libc6 (>= 2.34), libgcc-s1 (>= 3.3.1), libgssapi-krb5-2 (>= 1.14+dfsg), libicu70 (>= 70.1-1~), libldap-2.5-0 (>= 2.5.4), libllvm15, liblz4-1 (>= 0.0~r130), libpam0g (>= 0.99.7.1), libpq5 (>= 16~~), libselinux1 (>= 3.1~), libssl3 (>= 3.0.0~~alpha1), libstdc++6 (>= 11), libsystemd0, libuuid1 (>= 2.16), libxml2 (>= 2.7.4), libxslt1.1 (>= 1.1.25), libzstd1 (>= 1.4.0), zlib1g (>= 1:1.1.4)
Recommends: sysstat
Breaks: dbconfig-common (<< 2.0.22~)
Homepage: http://www.postgresql.org/
Postgresql-Catversion: 202307071
Download-Size: 18.1 MB
APT-Sources: https://apt.postgresql.org/pub/repos/apt jammy-pgdg/main amd64 Packages
Description: The World's Most Advanced Open Source Relational Database
PostgreSQL, also known as Postgres, is a free and open-source relational
database management system (RDBMS) emphasizing extensibility and SQL
compliance. It features transactions with Atomicity, Consistency, Isolation,
Durability (ACID) properties, automatically updatable views, materialized
views, triggers, foreign keys, and stored procedures. It is designed to handle
a range of workloads, from single machines to data warehouses or Web services
with many concurrent users.
.
This package provides the database server for PostgreSQL 16.
インストール。
$ sudo apt install postgresql-16
インストールされたpsqlのバージョンを確認してみます。16.3がインストールされました。
$ psql --version
psql (PostgreSQL) 16.3 (Ubuntu 16.3-1.pgdg22.04+1)
ところで、インストール中にちょっと気になるメッセージが表示されていました。
データベースクラスタはロケール"ja_JP.UTF-8"で初期化されます。
デフォルトのデータベース符号化方式はそれに対応してUTF8に設定されました。
initdb: ロケール"ja_JP.UTF-8"用の適切なテキスト検索設定が見つかりませんでした
デフォルトのテキスト検索構成は simple に設定されます。
ロケールがja_JP.UTF-8になってしまったようです。
設定ファイルで確認してみると、確かにja_JP.UTF-8になっています。
$ grep lc_ /etc/postgresql/16/main/postgresql.conf
lc_messages = 'ja_JP.UTF-8' # locale for system error message
lc_monetary = 'ja_JP.UTF-8' # locale for monetary formatting
lc_numeric = 'ja_JP.UTF-8' # locale for number formatting
lc_time = 'ja_JP.UTF-8' # locale for time formatting
ロケールはCにしたいのですが、どうやらインストール時にロケールを指定することはできないようです。
いったんデータベースクラスタを削除。
$ sudo pg_dropcluster 16 main --stop
このコマンドを実行すると/var/lib/postgresqlディレクトリ配下のデータベースクラスタのファイルおよび/etc/postgresql/16/main配下の設定ファイルがなくなります。
そしてデータベースクラスタの作成。
$ sudo pg_createcluster --locale=C 16 main
pg_createclusterおよびpg_dropclusterはUbuntu Linux固有のコマンドらしいです。
ロケールがCになりました。
$ grep lc_ /etc/postgresql/16/main/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
ちなみに、この時点でPostgreSQLは起動しています。
$ sudo systemctl status postgresql@16-main
● postgresql@16-main.service - PostgreSQL Cluster 16-main
Loaded: loaded (/lib/systemd/system/postgresql@.service; enabled-runtime; vendor preset: enabled)
Active: active (running) since Sun 2024-06-23 16:28:26 JST; 24s ago
Process: 613 ExecStart=/usr/bin/pg_ctlcluster --skip-systemctl-redirect 16-main start (code=exited, status=0/SUCCESS)
Main PID: 765 (postgres)
Tasks: 6 (limit: 2219)
Memory: 40.9M
CPU: 247ms
CGroup: /system.slice/system-postgresql.slice/postgresql@16-main.service
├─765 /usr/lib/postgresql/16/bin/postgres -D /var/lib/postgresql/16/main -c config_file=/etc/postgresql/16/main/postgresql.conf
├─777 "postgres: 16/main: checkpointer " "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "">
├─778 "postgres: 16/main: background writer " "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" >
├─784 "postgres: 16/main: walwriter " "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "">
├─785 "postgres: 16/main: autovacuum launcher " "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" ">
└─786 "postgres: 16/main: logical replication launcher " "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" ">
6月 23 16:28:16 myserver systemd[1]: Starting PostgreSQL Cluster 16-main...
6月 23 16:28:26 myserver systemd[1]: Started PostgreSQL Cluster 16-main.
自動起動は有効化しておきましょう。
$ sudo systemctl enable postgresql@16-main
ここまでで少し登場していますが、データなどは/var/lib/postgresql/16/mainディレクトリに、設定ファイルは/etc/postgresql/16/mainディレクトリにあります。
接続確認
接続確認。
$ sudo -u postgres psql
psql (16.3 (Ubuntu 16.3-1.pgdg22.04+1))
"help"でヘルプを表示します。
postgres=# select version();
version
-----------------------------------------------------------------------------------------------------------------------------------
PostgreSQL 16.3 (Ubuntu 16.3-1.pgdg22.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 11.4.0-1ubuntu1~22.04) 11.4.0, 64-bit
(1 行)
ユーザーとデータベースも作成しておきましょう。
postgres=# create user myuser password 'password';
CREATE ROLE
postgres=# create database example owner myuser;
CREATE DATABASE
接続確認。
$ psql -h localhost -U myuser example
ユーザー myuser のパスワード:
psql (16.3 (Ubuntu 16.3-1.pgdg22.04+1))
SSL接続(プロトコル: TLSv1.3、暗号化方式: TLS_AES_256_GCM_SHA384、圧縮: オフ)
"help"でヘルプを表示します。
OKですね。
SSL接続について
ところで先ほどの接続確認結果をよく見ると、TCP接続の時はSSL/TLSが有効になっているように見えます。
$ psql -h localhost -U myuser example
ユーザー myuser のパスワード:
psql (16.3 (Ubuntu 16.3-1.pgdg22.04+1))
SSL接続(プロトコル: TLSv1.3、暗号化方式: TLS_AES_256_GCM_SHA384、圧縮: オフ)
"help"でヘルプを表示します。
設定を確認してみます。
$ grep ssl /etc/postgresql/16/main/postgresql.conf | grep -v '#'
ssl = on
ssl_cert_file = '/etc/ssl/certs/ssl-cert-snakeoil.pem'
ssl_key_file = '/etc/ssl/private/ssl-cert-snakeoil.key'
これは、ssl-certによって作成されたサンプルの（自己署名）SSL証明書らしいです。
# make-ssl-cert generate-default-snakeoil
このままでもいいといえばいいのですが、せっかくなので自分でもSSL証明書を作ってみましょう。
結局、自己署名証明書ですが。有効期限は365日です。
$ sudo openssl req -new -x509 -days 365 -nodes -text -out /etc/postgresql/16/main/server.crt -keyout /etc/postgresql/16/main/server.key
また、ドキュメントに習ってserver.keyとserver.crtの所有者をpostgresに変更してserver.keyのパーミッションを0600にしておきます。
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).
$ sudo chown postgres:postgres /etc/postgresql/16/main/server.{crt,key}
$ sudo chmod og-rwx /etc/postgresql/16/main/server.{crt,key}
ssl_cert_fileとssl_key_fileを以下のように変更。
$ grep ssl /etc/postgresql/16/main/postgresql.conf | grep -v '#'
ssl = on
ssl_cert_file = '/etc/postgresql/16/main/server.crt'
ssl_key_file = '/etc/postgresql/16/main/server.key'
PostgreSQLを再起動。
$ sudo systemctl restart postgresql@16-main
接続確認。見た目上は変化はわかりませんが。
$ psql -h localhost -U myuser example
ユーザー myuser のパスワード:
psql (16.3 (Ubuntu 16.3-1.pgdg22.04+1))
SSL接続(プロトコル: TLSv1.3、暗号化方式: TLS_AES_256_GCM_SHA384、圧縮: オフ)
"help"でヘルプを表示します。
設定の確認もしておきましょう。
example=> show ssl_cert_file;
ssl_cert_file
------------------------------------
/etc/postgresql/16/main/server.crt
(1 行)
example=> show ssl_key_file;
ssl_key_file
------------------------------------
/etc/postgresql/16/main/server.key
(1 行)
SQLでも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
結果。
datname | pid | usename | application_name | client_addr | client_port | query | ssl | version | cipher | bits | client_dn | client_serial | issuer_dn
---------+------+---------+------------------+-------------+-------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----+---------+------------------------+------+-----------+---------------+-----------
example | 1408 | myuser | psql | 127.0.0.1 | 36730 | select +| t | TLSv1.3 | TLS_AES_256_GCM_SHA384 | 256 | | |
| | | | | | 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; | | | | | | |
(1 行)
これでOKでしょう。