EC2からAurora（PostgreSQL互換）を利用する際にはまったポイント

### 背景
AWS基盤で構築中のシステムにてデータストアとしてAurora（PostgreSQL互換）というRDBのサービスを利用することになった。
Auroraのテーブル作成やデータ投入などの開発作業のために、SQLクライアントとして、Cloud9というマネージドのEC2（仮想サーバ）をホストできるサービスを用意した。

Cloud9はWeb IDEのサービスであるが、Terminalを使ってEC2上でコマンド実行でき、複数メンバーで共用も可能であり、料金面でもEC2インスタンス利用料金以外はかからず、しかもしばらく使わなければEC2を自動停止してくれることから、筆者は踏み台サーバのような使い方をよくしている。
Cloud9の詳細は[こちら](https://aws.amazon.com/jp/cloud9/)。

システム構成は以下の通り。
Auroraは、PostgreSQL互換のv14.6 を利用した。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/271327/e64ad349-f803-7a62-b6e6-44160d31fc3b.png)


### やろうとしたこと
インフラ担当として、Cloud9からAuroraへの接続確認を行った上で、開発メンバー向けのDBユーザーを作成・認証情報を配布しようとした。
その際にはまったポイントについて記述する。

### はまったポイント①  Cloud9へのPostgreSQLのクライアントのインストール

まずはCloud9にてPostgreSQLクライアントがインストール済みかを確認したが、デフォルトではインストールされていなかった。
```
$ psql -V
bash: psql: command not found
```

#### 正しい手順

結論から言うと、[AWSのマニュアル](
https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_ConnectToPostgreSQLInstance.html
)通りに以下のコマンドを実行すればよい。
```
$ sudo amazon-linux-extras install postgresql14
```

インストールのコマンド実行後に再度インストール状況を確認すると、無事インストールされていた。
```
$ psql -V
psql (PostgreSQL) 14.3
```

インストールされたパッケージを使って、以下のコマンドでAuroraに接続できた。
```
$ psql --host=<Auroraのホスト名> --port=5432 --username=<DBユーザー名> --password
```


#### 正しい手順に辿りつくまでに試したこと
以上のようにシンプルに解決可能だが、当初はAWSのマニュアルのリンクを見つけられずつまづいてしまった。
試したことは、以下の通り。

まずはインストール可能なパッケージを探した。

```
$ yum list | grep postgre

calligra-kexi-driver-postgresql.x86_64  2.9.10-2.el7                  epel      								
check_postgres.noarch                   2.25.0-1.el7                  epel      								
collectd-postgresql.x86_64              5.8.1-1.el7                   epel      								
freeradius-postgresql.x86_64            3.0.26-1.amzn2.0.1            amzn2-core								
libgda-postgres.x86_64                  1:5.2.2-7.el7                 epel      								
opendbx-postgresql.x86_64               1.4.6-6.el7                   epel      								
opensips-postgresql.x86_64              1.10.5-4.el7                  epel      								
pcp-pmda-postgresql.x86_64              4.3.2-12.amzn2.0.1            amzn2-core								
pdns-backend-postgresql.x86_64          4.1.14-1.el7                  epel      								
perdition-postgresql.x86_64             2.2-2.el7                     epel      								
postgresql.x86_64                       9.2.24-8.amzn2.0.1            amzn2-core								
postgresql-contrib.x86_64               9.2.24-8.amzn2.0.1            amzn2-core								
postgresql-devel.x86_64                 9.2.24-8.amzn2.0.1            amzn2-core								
postgresql-docs.x86_64                  9.2.24-8.amzn2.0.1            amzn2-core								
postgresql-jdbc.noarch                  9.2.1002-8.amzn2              amzn2-core								
postgresql-jdbc-javadoc.noarch          9.2.1002-8.amzn2              amzn2-core								
postgresql-libs.i686                    9.2.24-8.amzn2.0.1            amzn2-core								
postgresql-libs.x86_64                  9.2.24-8.amzn2.0.1            amzn2-core								
postgresql-odbc.x86_64                  09.03.0100-2.amzn2.0.2        amzn2-core								
postgresql-pgpool-II.x86_64             3.4.6-1.el7                   epel      								
postgresql-pgpool-II-devel.x86_64       3.4.6-1.el7                   epel      								
postgresql-pgpool-II-extensions.x86_64  3.4.6-1.el7                   epel      								
postgresql-plperl.x86_64                9.2.24-8.amzn2.0.1            amzn2-core								
postgresql-plpython.x86_64              9.2.24-8.amzn2.0.1            amzn2-core								
postgresql-plruby.x86_64                0.5.3-13.el7                  epel      								
postgresql-plruby-doc.x86_64            0.5.3-13.el7                  epel      								
postgresql-pltcl.x86_64                 9.2.24-8.amzn2.0.1            amzn2-core								
postgresql-server.x86_64                9.2.24-8.amzn2.0.1            amzn2-core								
postgresql-static.x86_64                9.2.24-8.amzn2.0.1            amzn2-core								
postgresql-test.x86_64                  9.2.24-8.amzn2.0.1            amzn2-core								
postgresql-upgrade.x86_64               9.2.24-8.amzn2.0.1            amzn2-core								
postgresqltuner.noarch                  1.0.1-4.el7                   epel      								
postgrey.noarch                         1.37-13.el7                   epel      								
proftpd-postgresql.x86_64               1.3.5e-12.el7                 epel      								
python-testing.postgresql.noarch        1.1.0-3.el7                   epel      								
qt-postgresql.i686                      1:4.8.5-15.amzn2.0.5          amzn2-core								
qt-postgresql.x86_64                    1:4.8.5-15.amzn2.0.5          amzn2-core								
qt5-qtbase-postgresql.i686              5.9.2-3.amzn2.0.6             amzn2-core								
qt5-qtbase-postgresql.x86_64            5.9.2-3.amzn2.0.6             amzn2-core								
soci-postgresql.x86_64                  4.0.0-4.el7                   epel      								
soci-postgresql-devel.x86_64            4.0.0-4.el7                   epel      								
wt-dbo-postgres.x86_64                  3.3.9-1.el7                   epel      								

```

見つかった中で適当なパッケージをインストール。

```
$ sudo yum install postgresql.x86_64
<中略>

Installed:			
  postgresql.x86_64 0:9.2.24-8.amzn2.0.1                                                       			
			
Dependency Installed:			
  postgresql-libs.x86_64 0:9.2.24-8.amzn2.0.1                                                                                   			
			
Complete!			
```

パッケージのバージョンを確認。
```
$ psql -V
psql (PostgreSQL) 9.2.24
```
Aurora側はPostgreSQL v14.6なのでバージョンがかなり離れているが、いったんAurora接続を試してみる。
```
$ psql --host=<Auroraのホスト名> --port=5432 --username=<DBユーザー名> --password
Password for user postgres: 
psql: SCRAM authentication requires libpq version 10 or above
```
しかし、`SCRAM authentication requires libpq version 10 or above` というメッセージが出て接続できなかった。

[SCRAM Authentication in RDS for PostgreSQL 13](https://aws.amazon.com/jp/blogs/database/scram-authentication-in-rds-for-postgresql-13/)というAWSのブログには以下のようなことが書いてあった。
> PostgreSQL 14以上では、password_encryption というパスワード暗号化方式を指定するパラメーターのデフォルト値が変わり、SCRUMパスワードハッシングアルゴリズムがデフォルトで有効になっている。
エラーを回避するには、クライアントライブラリのバージョンを SCRUM をサポートするレベルに上げるか、password_encryption の設定値を md5 という以前までのデフォルト値に修正するかのやり方がある。
既存パスワードについてはパスワード変更をかけるまで元の暗号化方式で保管される。

Auroraの接続がうまく行っていないので「password_encryption の設定値変更＆パスワード変更」のやり方は取れず、「クライアントライブラリのバージョンを SCRUM をサポートするレベルに上げる」方向を試してみることに。

インストールしていた古いバージョンのパッケージを削除。
```
$ sudo yum -y remove postgresql.x86_64		
<中略>

Running transaction		
  Erasing    : postgresql-9.2.24-8.amzn2.0.1.x86_64                                                                                                                                                                                                                1/1 		
  Verifying  : postgresql-9.2.24-8.amzn2.0.1.x86_64                                                                                                                                                                                                                1/1 		
		
Removed:		
  postgresql.x86_64 0:9.2.24-8.amzn2.0.1                                                                                                                                                                                                                               		
		
Complete!		
```

続いて新しいバージョンのパッケージをインストール。

```
$ sudo yum -y install postgresql14.x86_64				
<中略>			
Running transaction				
  Installing : postgresql14-libs-14.8-1PGDG.rhel7.x86_64                                                                                                                                                                                                           1/2 				
  Installing : postgresql14-14.8-1PGDG.rhel7.x86_64                                                                                                                                                                                                                2/2 				
failed to link /usr/bin/psql -> /etc/alternatives/pgsql-psql: /usr/bin/psql exists and it is not a symlink				
failed to link /usr/bin/clusterdb -> /etc/alternatives/pgsql-clusterdb: /usr/bin/clusterdb exists and it is not a symlink				
failed to link /usr/bin/createdb -> /etc/alternatives/pgsql-createdb: /usr/bin/createdb exists and it is not a symlink				
failed to link /usr/bin/createuser -> /etc/alternatives/pgsql-createuser: /usr/bin/createuser exists and it is not a symlink				
failed to link /usr/bin/dropdb -> /etc/alternatives/pgsql-dropdb: /usr/bin/dropdb exists and it is not a symlink				
failed to link /usr/bin/dropuser -> /etc/alternatives/pgsql-dropuser: /usr/bin/dropuser exists and it is not a symlink				
failed to link /usr/bin/pg_dump -> /etc/alternatives/pgsql-pg_dump: /usr/bin/pg_dump exists and it is not a symlink				
failed to link /usr/bin/pg_dumpall -> /etc/alternatives/pgsql-pg_dumpall: /usr/bin/pg_dumpall exists and it is not a symlink				
failed to link /usr/bin/pg_restore -> /etc/alternatives/pgsql-pg_restore: /usr/bin/pg_restore exists and it is not a symlink				
failed to link /usr/bin/reindexdb -> /etc/alternatives/pgsql-reindexdb: /usr/bin/reindexdb exists and it is not a symlink				
failed to link /usr/bin/vacuumdb -> /etc/alternatives/pgsql-vacuumdb: /usr/bin/vacuumdb exists and it is not a symlink				
  Verifying  : postgresql14-14.8-1PGDG.rhel7.x86_64                                                                                                                                                                                                                1/2 				
  Verifying  : postgresql14-libs-14.8-1PGDG.rhel7.x86_64                                                                                                                                                                                                           2/2 				
				
Installed:				
  postgresql14.x86_64 0:14.8-1PGDG.rhel7                                                                                                                                                                                                                               				
				
Dependency Installed:				
  postgresql14-libs.x86_64 0:14.8-1PGDG.rhel7                                                                                                                                                                                                                          				
				
Complete!
```

パッケージのバージョンを確認。
```
$ psql -V
bash: /usr/bin/psql: No such file or directory
```
しかし、バージョンが表示されず、インストールが上手く行っていなかった。
ここからさらに調べながら試行錯誤したが、結果的に正しい手順に書いた [AWSのマニュアル](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_ConnectToPostgreSQLInstance.html) の方法で上手く行った。


### はまったポイント② DBユーザーの作成
続いて、開発メンバー向けに、マスターユーザー `postgres` よりも権限の弱いDBユーザーを追加で作成しようとした。
権限については、Auroraへログイン、インフラ側で作ったデータベースへの接続、データベース内でのスキーマやテーブルの作成ができる権限を付与することにした。

ちなみに、PostgreSQLにおけるデータベース、スキーマ、DBユーザーの関係についてはQiita記事 [PostgreSQL にて複数のスキーマを作り、適切なユーザー権限設定を行う](https://qiita.com/jun1s/items/952993386f4aef7375cf) が参考になった。

#### 正しい手順

また結論から言うと、以下の手順で上手くいく。

まずDBユーザー `app_user` を作成。初期パスワードは `password1` とする。
```
postgres=> create user app_user with password 'password1';
```

続いて、`app_user` に対して、事前に作成したデータベース `test` に対する全権限を付与。
```
postgres=> grant all on database test to app_user;
```

最後に `app_user` という名前のデータベースを作成。これは`app_user` でAuroraにログインできるようにするための手順。
```
postgres=> create database app_user;
```


#### 正しい手順に辿りつくまでに困ったこと

`app_user` にデータベース `test` への権限を付与しただけの状態だとログインがエラーになってしまった。
```
$ psql --host=<Auroraのホスト名> --port=5432 --username=app_user --password                                                                    
Password: 
psql: error: connection to server at "・・・" , port 5432 failed: FATAL:  database "app_user" does not exist
```
データベース `app_user` が存在しないというエラーになるので、データベース `app_user` を作成した上でAuroraへ再度ログインしてみると成功した。

もしかしたらユーザー名と同じ名前のデータベースを作成する以外にも対応方法があるのかもしれないが、調べた限りでは見つけられなかった。


以上。