JTNの作佐部ブログ

このブログは、スーパードライが僕の脳細胞を破壊する事を気にならないようにする為の手段です。

UbuntuでPostgreSQLを起動してアプリケーションで使える状態にする

前提として本記事は、コチラの記事の後を想定したものです。
つまり、Ubuntuサーバーには、アプリケーションを利用するユーザーが追加されていることを想定しています。

また、Ubuntuサーバーのローカル環境でのPostgreSQLの使用を想定しています。

1. version

2. UbuntuPostgreSQLをインストールする

PostgreSQLのAptのwikiに記載の通りにインストールする。
以下、Ubuntusshログインしている前提。

2.1 curl, ca-certificates, gnupgをインストールする

$ sudo apt install curl ca-certificates gnupg

2.2 Apt Repository keyをダウンロードする

$ curl https://www.postgresql.org/media/keys/ACCC4CF8.asc | gpg --dearmor | sudo tee /etc/apt/trusted.gpg.d/apt.postgresql.org.gpg >/dev/null

2.3 etc/apt/sources.list.d ディレクトリに pgdg.list ファイルを作成する

下記のコマンドでUbuntuのCodenameを出力する。

$ lsb_release -c

↓出力

Codename:  jammy

下記ののコマンドでviを起動する。

$ sudo vi /etc/apt/sources.list.d/pgdg.list

viを利用して下記の内容の pgdg.list ファイルを作成する。
****は、上記で出力したUbuntuのCodenameの値(例: jammy)が入る。

deb http://apt.postgresql.org/pub/repos/apt ****-pgdg main

2.4 PostgreSQLをインストールする

$ sudo apt update
$ sudo apt install postgresql-14

2.5 userを確認する

aptでPostgreSQLをインストールすると postgres というユーザーも作ってくれる。
下記コマンドでuser一覧を確認出来る。

$ less /etc/passwd

参考

2.5 参考

3. データベースクラスタの確認

3-1. postgres userのpasswordを変更する

下記のコマンドで postgres ユーザーのpasswordを変更する。

$ sudo passwd postgres

3-2. userを postgres に切り替える。

$ su postgres

上記のコマンドを入力すると、パスワードを求められるので上記 3-1 で決めたpasswordを入力する。
これにより postgres でログイン出来る。

3-3. データベースクラスタの確認

aptでPostgreSQLをインストールするとデータベースクラスタも作ってくれる。
ディレクトリのパスは下記の通り。

/var/lib/postgresql/14/main

データベースクラスタディレクトリは、postgresユーザーの所有となっているため、postgres ユーザーでログインすることにより確認することが出来る。

$ cd /var/lib/postgresql/14/main

4. postgres userとしてssh接続出来るようにする

4-1. 既に存在する ~/.ssh/authorized_keysの内容をコピーする

4-2. userを postgres に切り替える

上記3-2と同様に切り替える。

4-2. homeに飛ぶ

$ cd


カレントディレクトリがhomeに飛ぶ。

~/

4-3. .ssh ディレクトリを作成する

$ mkdir .ssh

4-4. .ssh ディレクトリに authorized_keys を作成する。

# .sshに移動する
$ cd .ssh

# ファイルを作成する
$ vi authorized_keys

上記 4-1 でコピーしたauthorized_keysの内容を vi で貼り付ける。

postgres user のhomeディレクトリに .ssh/authorized_keys があるのでssh接続が出来るようになる。

4-5. userのhomeディレクトリの確認方法

下記のコマンドで確認出来る。

$ less /etc/passwd

参考

4-6. 参考

5. サーバーの起動を確認する

5-1. systemctl用のファイル

aptでPostgreSQLをインストールすると、 systemdで起動するための設定ファイルが /lib/systemd/system/postgresql.service に作成される。
systemdで火使うファイルは、/lib/systemd/systemの他に /etc/systemd/system がある。
aptなどのパッケージマネジャーでインストールした際に作成されるsystemdのファイルは、/lib/systemd/systemに配置され、それ以外は /etc/systemd/systemに配置される模様。
参考

/lib/systemd/system/postgresql.service ファイルの中身は下記の通り。

# systemd service for managing all PostgreSQL clusters on the system. This
# service is actually a systemd target, but we are using a service since
# targets cannot be reloaded.

[Unit]
Description=PostgreSQL RDBMS

[Service]
Type=oneshot
ExecStart=/bin/true
ExecReload=/bin/true
RemainAfterExit=on

[Install]
WantedBy=multi-user.target

5-2. Postgresサーバーの起動の確認

下記のコマンドでPostgresサーバーの起動を確認する。

$ systemctl status postgresql.service

↓出力

postgresql.service - PostgreSQL RDBMS
     Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled)
     Active: active (exited) since Wed 2022-08-03 01:32:31 JST; 1 day 20h ago
    Process: 157764 ExecStart=/bin/true (code=exited, status=0/SUCCESS)
   Main PID: 157764 (code=exited, status=0/SUCCESS)
        CPU: 2ms

6. Postgresサーバーにログインする

6-1. Postgresサーバーのテーブル

Postgresサーバーは初期化の時点で、下記の3つのテーブルが存在する。 - template0 - template1 - postgres

template0テーブルは、template1を作るために利用されるテンプレートのテーブル。
template1は、template0のテンプレートとデータベースクラスタで設定されたパラメータに従い作成されるテンプレートのテーブル。CREATE TABLEコマンドで作成されるテーブルは、このtemplate1を利用して作成される。
postgresは、外部のライブラリなどが利用するテーブル。

参考

6-2. postgresにログイン

下記のコマンドで、テンプレートであるpostgresテーブルにログインする。
-U は、ユーザーの名称である。
aptでインストールすると、Postgresサーバーに、 postgres というロールが作成される。

$ psql -U postgres postgres

7. アプリケーション用のロールを作成してログイン出来るようにする

現状、上記6-2でログインした postgres というロールはスーパーユーザーである。
スーパーユーザーは何でも出来てしまうロールなので、権限を絞ったロールを作成する。

7-1. アプリケーション用ロールを作成

上記6-2の方法によりPostgresサーバーにログインする。

$ psql -U postgres postgres

下記のコマンドでロールを作成する。
****は、ロールの名称。

$ CREATE ROLE ****;

7-2. ロール一覧

Postgresサーバーにログインしている状態で下記のSQLを入力するとロール一覧を出力出来る。

$ SELECT * FROM pg_roles;

それぞれのカラムの属性の意味は、ロールの属性を参照する。

7-3. アプリケーション用ロールに必要な属性を付与する。

アプリケーション用のロールには下記の属性が欲しい - ログイン権限(rolcanlogin) - データベース作成(rolcreatedb)

よって、下記の変更SQLを入力してこれらの属性を付与するように変更する。
****は、7-1で作成したアプリケーション用のロール名。

$ ALTER ROLE **** CREATEDB;

$ ALTER ROLE **** LOGIN;

参考

7-4. アプリケーション用ロールの認証方法を定義する

PostgreSQLの認証方法は、 pg_hba.conf ファイルで設定する。
aptでPostgreSQLをインストールすると、 /etc/postgresql/14/main/pg_hba.conf にある。
~/14/main/pg_hba.conf では無いので注意。

参考

Ubuntuローカル環境での接続のみを想定しているので、peer認証を利用する。

etc/postgresql/14/main/pg_hba.confに下記の行を追加する。
****は、アプリケーション用ロールの名前。

local   all             ****                                       peer

↓結果、下記のようなファイルになる。

# Database administrative login by Unix domain socket
local   all             postgres                                peer
local   all             ****                                       peer

7-5. ログイン確認

peer認証の設定なので、Postgresサーバーからログアウトして、Ubuntuサーバーからpostgres userとしてのログインをログアウトする。
そして、再度アプリケーションのユーザー名でssh接続をする。
ssh接続後、下記のコマンドを入力するとPostgresサーバーのpostgresDBにログイン出来る。
****は、アプリケーションのユーザー名。

psql -U **** postgres

8. 上記7で作成したロールが所有するDBを作成する

8-1. DBの作成

上記7-5で行なった方法でPostgresサーバーのpostgresDBにログインした後、下記のコマンドを入力してアプリケーション用のDBを作成する。

$ CREATE DATABASE **** TEMPLATE = template0 ENCODING = 'UTF8' LC_COLLATE='C' LC_CTYPE = 'C';

8-2. DBの確認

下記のpsqlコマンドでDBの一覧を出力する。

$ \l

9. TCP/IP接続を出来るようにする

JDBC Driverは、Unixドメインソケットを使用した接続が出来ない。
そのため、TCP/IPを使用した接続を出来るようにする。
参考

9-1. 認証方法を設定する

上述の通り、pg_hba.conf ファイルは、aptでPostgreSQLをインストールすると、 /etc/postgresql/14/main/pg_hba.conf にある。
下記のコマンドでviを起動して編集する。

$ vi /etc/postgresql/14/main/pg_hba.conf

↓下記のように追記する。
****は、DBの名前。
$$$$は、そのDBのオーナーのユーザーの名前。

# IPv4 local connections:
host    ****         $$$$             localhost            scram-sha-256

9-2. パスワードの暗号化方式を設定する

暗号化方式は、 postgresql.conf ファイルで設定する。

postgresql.conf ファイルは、aptでPostgreSQLをインストールすると、 /etc/postgresql/14/main/postgresql.conf にある。

9-3. ユーザーのパスワードを設定する

下記のコマンドで、ログインする。 ****は、DBの名前。
$$$$は、そのDBのオーナーのユーザーの名前。

$ psql -U $$$$ ****

下記のコマンドでパスワードを設定する。
$$$$は、ユーザーの名前。
@@@@は、パスワード。

$ ALTER ROLE $$$$ WITH PASSWORD '@@@@';

以上で、JDBC DriverがTCP/IP接続出来る。