背景
AWS基盤で構築中のシステムにてデータストアとしてAurora（PostgreSQL互換）というRDBのサービスを利用することになった。
Auroraのテーブル作成やデータ投入などの開発作業のために、SQLクライアントとして、Cloud9というマネージドのEC2（仮想サーバ）をホストできるサービスを用意した。
Cloud9はWeb IDEのサービスであるが、Terminalを使ってEC2上でコマンド実行でき、複数メンバーで共用も可能であり、料金面でもEC2インスタンス利用料金以外はかからず、しかもしばらく使わなければEC2を自動停止してくれることから、筆者は踏み台サーバのような使い方をよくしている。
Cloud9の詳細はこちら。
システム構成は以下の通り。
Auroraは、PostgreSQL互換のv14.6 を利用した。
やろうとしたこと
インフラ担当として、Cloud9からAuroraへの接続確認を行った上で、開発メンバー向けのDBユーザーを作成・認証情報を配布しようとした。
その際にはまったポイントについて記述する。
はまったポイント① Cloud9へのPostgreSQLのクライアントのインストール
まずはCloud9にてPostgreSQLクライアントがインストール済みかを確認したが、デフォルトではインストールされていなかった。
$ psql -V
bash: psql: command not found
正しい手順
結論から言うと、AWSのマニュアル通りに以下のコマンドを実行すればよい。
$ 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という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のマニュアル の方法で上手く行った。
はまったポイント② DBユーザーの作成
続いて、開発メンバー向けに、マスターユーザー postgres よりも権限の弱いDBユーザーを追加で作成しようとした。
権限については、Auroraへログイン、インフラ側で作ったデータベースへの接続、データベース内でのスキーマやテーブルの作成ができる権限を付与することにした。
ちなみに、PostgreSQLにおけるデータベース、スキーマ、DBユーザーの関係についてはQiita記事 PostgreSQL にて複数のスキーマを作り、適切なユーザー権限設定を行う が参考になった。
正しい手順
また結論から言うと、以下の手順で上手くいく。
まず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へ再度ログインしてみると成功した。
もしかしたらユーザー名と同じ名前のデータベースを作成する以外にも対応方法があるのかもしれないが、調べた限りでは見つけられなかった。
以上。