🐘

Ubuntu 24.04 へ PostgreSQL 17 + pgAdmin 4 を構築する（備忘録）

2025/05/04に公開

はじめに

米国株の配当金の為替差益を管理したかったので、OSS RDBMSで有名なPostgreSQLを構築することからはじめた。

環境

OS:Ubuntu 24.04 (LXC) on Proxmox VE 8.4.1
vCPU:1 Core
MEM:1.0 GiB
Storage:31.20 GiB

LXCへのUbuntuインストールについては割愛する。

PostgreSQL 17, pgAdmin 4 のインストール

Ubuntu標準のaptリポジトリは、PostgreSQL 16までしかなかったので、PostgreSQLのaptリポジトリを追加する。
リポジトリの追加は、シェルスクリプトで行える方法で実施。

sudo apt install -y postgresql-common
sudo /usr/share/postgresql-common/pgdg/apt.postgresql.org.sh
sudo apt install -y postgresql

PostgreSQLの管理ツールである、pgAdmin 4もインストールする。
デスクトップ環境は不要なので、WEB単体をインストール。

curl -fsS https://www.pgadmin.org/static/packages_pgadmin_org.pub | sudo gpg --dearmor -o /usr/share/keyrings/packages-pgadmin-org.gpg
sudo sh -c 'echo "deb [signed-by=/usr/share/keyrings/packages-pgadmin-org.gpg] https://ftp.postgresql.org/pub/pgadmin/pgadmin4/apt/$(lsb_release -cs) pgadmin4 main" > /etc/apt/sources.list.d/pgadmin4.list && apt update'
# Install for web mode only: 
sudo apt install pgadmin4-web 
# Configure the webserver, if you installed pgadmin4-web:
sudo /usr/pgadmin4/bin/setup-web.sh

pgAdminインストール後、初期設定としてpgAdminへのログイン用のメールアドレスとパスワードが聞かれるので、入力する。

PostgreSQL設定

postgresユーザーのパスワード設定

PostgreSQLをインストールすると、OS側、DB側両方にpostgresユーザーが作成される。
外部（pgAdmin等）からもDB側のpostgresユーザーに接続できるようにするため、パスワードを設定する。

sudo -u postgres psql
# [*****]の箇所は任意のパスワードを入力する
postgres=# ALTER ROLE postgres WITH PASSWORD '*****';
postgres=# exit;

OSとDBで対にユーザー管理するのは大変なので、ローカルでの接続をパスワード認証に変更。
同時に、TCP/IP経由での接続の認証も追加する。（今回は、192.168.2.0/24をパスワード認証にした）

/etc/postgresql/17/main/pg_hba.conf

# DO NOT DISABLE!
# If you change this first entry you will need to make sure that the
# database superuser can access the database using some other method.
# Noninteractive access to all databases is required during automatic
# maintenance (custom daily cronjobs, replication, and similar tasks).
#
# Database administrative login by Unix domain socket
- local   all             postgres                                peer
+ local   all             postgres                                scram-sha-256

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
- local   all             all                                     peer
+ local   all             all                                     scram-sha-256
# IPv4 local connections:
host    all             all             127.0.0.1/32            scram-sha-256
+ host    all             all             192.168.2.0/24          scram-sha-256
# IPv6 local connections:
host    all             all             ::1/128                 scram-sha-256
# Allow replication connections from localhost, by a user with the
# replication privilege.
- local   replication     all                                     peer
+ local   replication     all                                     scram-sha-256
host    replication     all             127.0.0.1/32            scram-sha-256
host    replication     all             ::1/128                 scram-sha-256

外部接続の許可

上記ファイルの設定だけでは、まだ接続できない。
別途、外部接続を待つアドレスを指定する必要がある。
カンマ区切りで複数指定することができるが、pg_hba.confと管理が煩雑になるので、こちらはすべて許可とする。

/etc/postgresql/17/main/postgresql.conf

- #listen_addresses = 'localhost'         # what IP address(es) to listen on;
+ listen_address = '*'

上記設定を追加後、postgresqlを再起動しておく。

sudo systemctl restart postgresql

ロール（ユーザー）作成

調べた感じ、ログインできるロールがユーザーと呼ばれるっぽい。
Oracleと違って、1ユーザーにつき１スキーマ作られるわけではないみたい。
今回は、データベースとスキーマを作成予定なので、それぞれの管理ユーザーを作成する。（スーパーユーザーでずっと操作するのは嫌なので。）
イメージはこんな感じ。

スーパーユーザーで先に2ユーザー（assetsmgr, shinory）を作成しておく。
assetsmgrはshinory所有のオブジェクトも管理できるように、shinoryのメンバーに追加する。

$ psql -U postgres
postgres=# CREATE USER assetsmgr WITH PASSWORD 'assetsmgr';
CREATE ROLE
postgres=# CREATE USER shinory WITH PASSWORD 'shinory';
CREATE ROLE
postgres=# GRANT shinory TO assetsmgr;
GRANT ROLE
postgres=# select usename from pg_user;
  usename
-----------
 postgres
 assetsmgr
 shinory
(3 rows)

assetsmgrを所有者としたデータベース、assetsを作成。

postgres=# CREATE DATABASE assets WITH OWNER assetsmgr;
CREATE DATABASE

assetsmgrで、データベースassetsへログインしなおして、スキーマshinoryを作成。
所有者のユーザー名とスキーマ名は別々にできるらしいが、今回は一緒にしておく。
因みに、スキーマ名の指定を省略するとAUTHORIZATIONと同じ名前のスキーマが作成されるらしい。

$ psql -U assetsmgr assets
assets=> CREATE SCHEMA shinory AUTHORIZATION shinory;
CREATE SCHEMA

publicスキーマは使用しない予定なので、オブジェクトのスキーマ省略時の設定を変更する。
具体的には、ユーザーshinoryのsearch_pathを"$user"のみにしておく。
これで、shinoryで接続したときのカレントスキーマは、自身のユーザー名shinoryのみになるので、Oracleに近い使い方ができる。

$ psql -U shinory assets
assets=> ALTER USER shinory SET search_path TO "$user";
SET
assets=> show search_path;
 search_path
-------------
 "$user"
(1 row)

assets=> select current_schema();
 current_schema
----------------
 shinory
(1 row)

最後に、ユーザーshinoryで、スキーマshinoryへ、テーブルが作成できるか確認。

assets=> CREATE TABLE test (test1 integer, test2 varchar(20));
CREATE TABLE

assets=> \dt
        List of relations
 Schema  | Name | Type  |  Owner
---------+------+-------+---------
 shinory | test | table | shinory
(1 row)

問題なさそう。

pgAdmin4への接続

ここまで来れば、pgAdminでロールごとにpgAdmin上で接続可能なハズ。
http://[ホスト]/pgadmin4/browser/へアクセスし、インストール時に設定したメールアドレスとパスワードでログイン。

サーバーの追加で、最低限、「名前」、「ホスト」、「管理用データベース」、「ユーザー名」、「パスワード」さえ指定すれば接続できる。
以下の画像は、最小限のshinoryスキーマのみ管理するための接続例となっている。

もちろん、スーパーユーザーpostgresの指定や、接続データベースをデフォルトのpostgresにすることも可能。
最終的にはこんな感じで、ロールごとにプロファイルを使い分けることにした。

感想

構築したことのあるDBが、Oracleだけだったので、PostgreSQLとの違いを学ぶいい機会だった。
スキーマやロール、ユーザーについては、各DBで考えが違うことには衝撃的だった。
とりあえず任意のスキーマとオブジェクトの作成はできたので、目的はほぼ達成できたと思う。

pgAdmin単体だとテーブル内データの閲覧・編集とかがやや不便なので、クライアント側に別アプリをインストールした方がよさそう。A5M2とかかな？