本記事では、オンプレミス、または、EC2にインストールされているPostgreSQLからRDS for PostgreSQLにユーザを移行する際に発生するエラーについて記載します。この権限エラーは、移行元のPostgreSQLに存在するユーザの設定次第で発生し、そのままだと移行することができません。
ユーザ移行時に発生する権限エラー
pg_dumpall等を用いてユーザを移行するためのSQL文を発行し、それをそのままRDSで実行すると、次のような権限エラーが発生することがあります。本記事では、このエラーに対応するのが目的です。
ユーザ移行時の権限エラー例
ERROR: must be superuser to alter superusers
ERROR: must be superuser to alter replication usersご参考までに、今回検証に利用したユーザの情報を記載致します。
- test_superuser{Superuser}
- test_repuser{Replication}
参考:検証用ユーザ(移行元)
postgres=# create user test_superuser with superuser password 'password';
CREATE ROLE
postgres=# create user test_repuser with replication password 'password';
CREATE ROLE
postgres=# \du
List of roles
Role name | Attributes | Member of
----------------+------------------------------------------------+-----------
postgres | Superuser, Create role, Create DB, Replication | {}
test_repuser | | {}
test_superuser | Superuser | {}参考:移行用SQL文例
-bash-4.2$ pg_dumpall --roles-only
--
-- PostgreSQL database cluster dump
--
SET default_transaction_read_only = off;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
--
-- Roles
--
CREATE ROLE postgres;
ALTER ROLE postgres WITH SUPERUSER INHERIT CREATEROLE CREATEDB LOGIN REPLICATION;
CREATE ROLE test_repuser;
ALTER ROLE test_repuser WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN REPLICATION PASSWORD 'md56063c92b21c1aa4fc63a8dd9dde8a617';
CREATE ROLE test_superuser;
ALTER ROLE test_superuser WITH SUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN NOREPLICATION PASSWORD 'md52a52fb0807d3bfdc833db8158def1d4a';
--
-- PostgreSQL database cluster dump complete
--ユーザ移行時の権限エラー対策
次の対応を行うことでユーザを移行することができます。
- 移行不可の権限を外す
SUPERUSERの権限を外すNOSUPERUSERの権限を外すREPLICATIONの権限を外すNOREPLICATIONの権限を外す
- 代替ロールを付与する
SUPERUSERの権限をrds_superuserロールに置き換えるREPLICATIONの権限をrds_replicationロールに置き換える
移行不可の権限を外す
上述の通り、次の権限についてはSQL文に記載することができません。記載した場合、上述のエラーが発生します。したがって、ユーザの移行時には必ずSQL文から削除する必要があります。
- SUPERUSER
- NOSUPERUSER
- REPLICATION
- NOREPLICATION
例:変更前のユーザ変更文
ALTER ROLE test_repuser WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN REPLICATION PASSWORD 'md56063c92b21c1aa4fc63a8dd9dde8a617';
ALTER ROLE test_superuser WITH SUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN NOREPLICATION PASSWORD 'md52a52fb0807d3bfdc833db8158def1d4a';例:変更後のユーザ変更文
ALTER ROLE test_repuser WITH INHERIT NOCREATEROLE NOCREATEDB LOGIN PASSWORD 'md56063c92b21c1aa4fc63a8dd9dde8a617';
ALTER ROLE test_superuser WITH INHERIT NOCREATEROLE NOCREATEDB LOGIN PASSWORD 'md52a52fb0807d3bfdc833db8158def1d4a';なお、SUPERUSERおよびREPLICATIONだけでなく、それらを打ち消すNOSUPERUSERおよびNOREPLICATIONもSQL文に含めることができない点に注意する必要があります。
例:NO系のオプション指定エラー
postgres=> alter role foobarbaz with noreplication;
ERROR: must be superuser to alter replication users
postgres=> alter role foobarbaz with nosuperuser;
ERROR: must be superuser to alter superusersここまで実施すれば、該当のユーザを利用してRDSに接続する部分までは進むことができます。しかしながら、次の記載通り、作成したユーザは特別な権限を持っていません。次のステップで代替となるロールを付与する必要があります。
postgres=> \du
List of roles
Role name | Attributes | Member of
---------------------------+------------------------------------------------+----------------------------------------------------------
----
foobarbaz | Cannot login | {}
pg_execute_server_program | Cannot login | {}
pg_monitor | Cannot login | {pg_read_all_settings,pg_read_all_stats,pg_stat_scan_tabl
es}
pg_read_all_settings | Cannot login | {}
pg_read_all_stats | Cannot login | {}
pg_read_server_files | Cannot login | {}
pg_signal_backend | Cannot login | {}
pg_stat_scan_tables | Cannot login | {}
pg_write_server_files | Cannot login | {}
postgres | Create role, Create DB +| {rds_superuser}
| Password valid until infinity |
rds_ad | Cannot login | {}
rds_iam | Cannot login | {}
rds_password | Cannot login | {}
rds_replication | Cannot login | {}
rds_superuser | Cannot login | {pg_monitor,pg_signal_backend,rds_replication,rds_passwor
d}
rdsadmin | Superuser, Create role, Create DB, Replication+| {}
| Password valid until infinity |
rdsrepladmin | No inheritance, Cannot login, Replication | {}
test_repuser | | {}
test_superuser | | {}代替ロールを付与する
そもそもRDSでは機能が制限されており、本来の意味でのSUPERUSERを利用することができないため、完全な代替とはなり得ませんが、RDSが用意しているロールを付与することで、ある程度は代替することが可能です。
なお、執筆時点でRDSはストリーミングレプリケーションに対応していませんので、レプリケーショ用の代替ロールと言っても、ロジカルレプリケーションとなる点にも注意が必要です。
SUPERUSER⇒rds_superuserREPLICATION⇒rds_replication
例:代替ロールの付与
postgres=> grant rds_superuser to test_superuser;
GRANT ROLE
postgres=> grant rds_replication to test_repuser;
GRANT ROLE上記でRDSの用意するロールのメンバーとなることができました。
postgres=> \du
List of roles
Role name | Attributes | Member of
---------------------------+------------------------------------------------+----------------------------------------------------------
----
foobarbaz | Cannot login | {}
pg_execute_server_program | Cannot login | {}
pg_monitor | Cannot login | {pg_read_all_settings,pg_read_all_stats,pg_stat_scan_tabl
es}
pg_read_all_settings | Cannot login | {}
pg_read_all_stats | Cannot login | {}
pg_read_server_files | Cannot login | {}
pg_signal_backend | Cannot login | {}
pg_stat_scan_tables | Cannot login | {}
pg_write_server_files | Cannot login | {}
postgres | Create role, Create DB +| {rds_superuser}
| Password valid until infinity |
rds_ad | Cannot login | {}
rds_iam | Cannot login | {}
rds_password | Cannot login | {}
rds_replication | Cannot login | {}
rds_superuser | Cannot login | {pg_monitor,pg_signal_backend,rds_replication,rds_passwor
d}
rdsadmin | Superuser, Create role, Create DB, Replication+| {}
| Password valid until infinity |
rdsrepladmin | No inheritance, Cannot login, Replication | {}
test_repuser | | {rds_replication}
test_superuser | | {rds_superuser}以上の対応を行うことで、RDSの制限上完全に一致とはいきませんが、RDSの制限に即した形でユーザを移行することができました。
おまけ(SQL文書き換えコマンド例)
おまけとして、pg_dumpallで出力したユーザ移行用のSQL文を書き換えるコマンド例を記載致します。数ユーザ程度だったら手動対応も良いのですが、ユーザ数が増えると厳しい面もありますからね。
ユーザ移行SQL文書き換えコマンド例
# 環境変数
incompatible_users_list=<そのままだと移行できないユーザのリスト(任意の場所)>
# 対象ユーザ洗い出し
users_sql=<pg_dumpallで出力したユーザ移行SQLファイル>
super_users_list=<SUPERUSERのユーザリスト(任意の場所)>
grep "^ALTER ROLE .* SUPERUSER " "${users_sql}" \
| awk -F " " '{print $3}' \
| tee "${super_users_list}"
replication_users_list=<REPLICATIONのユーザリスト(任意の場所)>
grep "^ALTER ROLE .* REPLICATION " "${users_sql}" \
| awk -F " " '{print $3}' \
| tee "${replication_users_list}"
# 非対応部分の削除
cp -p "${users_sql}" "${users_sql}.bk"
sed -i -e 's/NOSUPERUSER//g' \
-e 's/SUPERUSER//g' \
-e 's/NOREPLICATION//g' \
-e 's/REPLICATION//g' \
"${users_sql}"
# 代替ロール付与文の追加
cat <<'_description_' >>"${users_sql}"
---
--- Grant RDS role to users
---
_description_
while read superuser; do
echo "grant rds_superuser to ${superuser};"
done <"${super_users_list}" >>"${users_sql}"
while read replication; do
echo "grant rds_replication to ${replication};"
done <"${replication_users_list}" >>"${users_sql}"最後に
今回は、記事内でも触れましたが、SUPERUSERおよびREPLICATIONだけでなく、それらを打ち消すNOSUPERUSERおよびNOREPLICATIONも記載できないという部分で少しだけ詰まったため、記事にしました。
「打ち消す指定なら良いのでは？」とも一瞬思いましたが、おそらく、RDSに最初から存在しているrdsadminやrdsrepladminの権限を剥奪されると内部的に影響が出かねないため、このような制限をしているのだと思います。
RDSは非常に便利なサービスではありますが、通常のサーバにインストールしたデータベースとは挙動が異なる点もありますので、また、何か詰まった点等があれば、記事にしていきたいと思います。
コメント