RDS for PostgreSQLにユーザを移行する際の権限エラーについて

AWS
AWS PostgreSQL RDS データベース

本記事では、オンプレミス、または、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はストリーミングレプリケーションに対応していませんので、レプリケーショ用の代替ロールと言っても、ロジカルレプリケーションとなる点にも注意が必要です。

  • SUPERUSERrds_superuser
  • REPLICATIONrds_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に最初から存在しているrdsadminrdsrepladminの権限を剥奪されると内部的に影響が出かねないため、このような制限をしているのだと思います。

RDSは非常に便利なサービスではありますが、通常のサーバにインストールしたデータベースとは挙動が異なる点もありますので、また、何か詰まった点等があれば、記事にしていきたいと思います。

コメント

タイトルとURLをコピーしました