PosgreSQLの構築
PosgreSQLはMySQL(MariaDB)と並ぶOSSのデータベースです。
今回はProxmoxVE 9.0.11上のdebian13にPostgreSQL17をインストールします。
1. 構築環境
- ProxmoxVE 9.0.11
- Debian13(LXC)
- CPUS数: 1
- RAM: 2GB
- ストレージ: 30GB
2. 構築手順
構築手順では以下内容を紹介します。
- インストールの方法
- 推奨する初期設定
2.1. PostgreSQLのインストール
まずは、debian13組込みのパッケージでインストールされるPostgreSQLのバージョンを確認します。
$ apt info posgresql
Package: postgresql
Version: 17+278
Priority: optional
Section: database
Source: postgresql-common (278)
Maintainer: Debian PostgreSQL Maintainers <[email protected]>
Installed-Size: 30.7 kB
Provides: postgresql-contrib (= 17)
Depends: postgresql-17, debconf (>= 0.5) | debconf-2.0
Suggests: postgresql-doc
:debian13の場合はPostgreSQL17がインストールされるようです。
次に念のためパッケージのアップデートとPostgreSQLのインストールを行います。
$ apt update -y
$ apt install postgresqlインストールされたら以下コマンドでpostgresqlのバージョン確認を行います。
$ psql --version
psql (PostgreSQL) 17.6 (Debian 17.6-0+deb13u1)無事にインストールされました。
2.2. Postgreユーザの初期設定
PostgreSQLをインストールすると、OS(debian13)とDB(PostgreSQL)上にユーザ「postgres」が自動的に作成されます。
一方でこれらのユーザにはパスワードが一切設定されていません。
運用やセキュリティの観点を考えると管理ユーザにパスワードが設定されていないのは問題です。
OSとDBの「postgres」ユーザにパスワードを設定していきます。
例として構築例では以下のパスワードで設定しました。
実際の構築ではパスワードポリシ等に合わせてパスワードを設定してください。
| アカウント | パスワード |
|---|---|
| postgres(OS) | postgres |
| postgres(DB) | postgres |
OSのpostgresユーザへの設定
rootもしくはsudo権限のあるユーザで以下コマンドを実施します。
$ passwd postgres
New password:
Retype new password:
passwd: password updated successfullyDBのpostgresユーザへの設定
まずはpostgresユーザに切り替えます。(デフォルトだとDBの操作はpostgresユーザしか許可されていません)
$ su postgrespostgreSQLに接続してパスワードを設定します。(以下例ではパスワードをpostgresで設定)
$ psql -U postgres
psql (17.6 (Debian 17.6-0+deb13u1))
Type "help" for help.
postgres=#
postgres=# ALTER ROLE postgres WITH PASSWORD 'postgres';
ALTER ROLE
postgres=# \q
$ exitこのままではDBにpostgresでログインした際にパスワードを求められないので、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 scram-sha-256
# TYPE DATABASE USER ADDRESS METHOD
# "local" is for Unix domain socket connections only
local all all scram-sha-256
# IPv4 local connections:
host all all 127.0.0.1/32 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 scram-sha-256
host replication all 127.0.0.1/32 scram-sha-256
host replication all ::1/128 scram-sha-2562.3. 外部からのアクセス許可設定
PostgreSQLはインストール直後の初期状態だと、外部のPCやサーバから接続ができないようになっています。
外部のサーバやPCからデータベースの取得や更新、作成/削除を行う場合は以下の設定が必要です。
/etc/postgresql/17/mainに移動します。
$ cd /etc/postgresql/17/mainpostgreSQLの基本的な設定ファイルであるpostgresql.confを編集します。
59行目付近にある以下の設定を書き換えます。
# listen_addresses='localhost'
listen_addresses='*'これでサーバのすべてのネットワークインターフェイスでpostgreSQLが待ち受けるようになりました。
NICが複数ある場合で、かつアクセス元IPアドレス範囲を制限したい場合は*ではなく、192.168.0.10のようにサーバ自体のIPアドレスを設定してください。
2.4. PostgreSQLの再起動
PostgreSQLのサービス登録(OS起動時自動起動)と再起動を行います。
$ systemctl enable postgresql
$ systemctl restart postgresql以下コマンドで起動状況の確認が可能です。
systemctl status postgresql3. PostgreSQLの各種確認コマンド
PostgreSQLの各種確認方法を紹介します。
いずれもPotsgreSQLに接続した状態で使用してください。
| コマンド | 説明 |
|---|---|
\du | ユーザーロール一覧を表示 |
\l | データベース一覧を表示 |
\dt | テーブル一覧を表示 |
\q | データベース接続解除 |
4. データベースへの操作
この項目では各種PostgreSQLの操作方法と設定ファイルの編集方法を紹介します。
4.1. ユーザ(ロール)の追加/削除
ユーザの追加
OSからコマンドでDBにユーザを追加する方法もありますが、安全のためPostgreSQLにログインしてから作成します。
例ではdbuserをパスワードなしで作成します。\duでユーザ一覧を確認できます。
$ psql -U postgres
psql (17.6 (Debian 17.6-0+deb13u1))
Type "help" for help.
postgres=#
postgres=# CREATE USER dbuser;
CREATE ROLE
postgres=# \du
List of roles
Role name | Attributes | Member of
--------------------------------------------------------------
dbuser | | {}
postgres | Superuser, Create role, Create DB, Replication | {}
postgres=# パスワードも一緒に設定する場合はCREATE USER dbuser WITH PASSWORD 'password';で設定を行います。
ユーザの削除
例では上記で作成したdbuserを削除します。
postgres=# DROP USER dbuser;
DROP ROLE
postgres=# \du
List of roles
Role name | Attributes | Member of
--------------------------------------------------------------
postgres | Superuser, Create role, Create DB, Replication | {}
postgres=# 4.2. データベースの作成/削除
データベースの作成
データベースの作成をします。
データベース名はdbtestにします
postgres=# CREATE DATABASE dbtest;
CREATE DATABASEデータベースの削除
postgres=# DROP DATABASE dbtest;
DROP DATABASE4.3. ユーザにデータベース権限の付与
全権限の付与
dbtestデータベースの対する全権限をdbuserユーザに付与します。
postgres=# GRANT ALL PRIVILEGES ON DATABASE dbtest TO dbuser;
GRANT4.4. データーベースのオーナの変更
PostgreSQL15からデータベースのオーナしか [public]スキーマに書き込みができなくなりました。
データベースのユーザの権限付与後に以下操作が必要になります。
postgres=# ALTER DATABASE dbtest OWNER TO dbuser;
ALTER DATABASE5. PostgreSQLの設定ファイル
5.1. postgresql.conf
postgresql.confは PostgreSQL に関する基本的な設定を記述するファイルで、データベースのチューニングなどを行う場合も使用します。
チューニングを行う際のパラメータは以下サイトが参考になるかもしれません。
5.2. pg_hba.conf
pg_hba.confは PostgreSQL に接続するクライアントの認証に関する設定を記述するファイルです。
# 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
# TYPE DATABASE USER ADDRESS METHOD
# "local" is for Unix domain socket connections only
local all all peer
# IPv4 local connections:
host all all 127.0.0.1/32 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
host replication all 127.0.0.1/32 scram-sha-256
host replication all ::1/128 scram-sha-256外部からの接続でクライアント認証を許可する場合は以下部分を編集することが多いです。
# IPv4 local connections:
host all all 127.0.0.1/32 scram-sha-256
# 以下追加例
host wiki wikijs 192.168.5.0/24 md5
host netbox netbox 192.168.5.0/24 md5