UbuntuでPostgreSQLを起動してアプリケーションで使える状態にする
前提として本記事は、コチラの記事の後を想定したものです。
つまり、Ubuntuサーバーには、アプリケーションを利用するユーザーが追加されていることを想定しています。
また、Ubuntuサーバーのローカル環境でのPostgreSQLの使用を想定しています。
1. version
- Ubuntu 22.04
- PostgreSQL 14.4
2. UbuntuにPostgreSQLをインストールする
PostgreSQLのAptのwikiに記載の通りにインストールする。
以下、Ubuntuにsshログインしている前提。
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 '@@@@';
