Aurora PostgreSQL に既存DBのダンプをリストアしたらちょっとハマったので対処法メモ

オンプレミスの PostgreSQL でダンプを取って、Aurora PostgreSQL にカジュアルにダンプ＆リストアしようとしたら失敗した。

ALTER ROLE が失敗した

まずユーザ情報を持ってこうと思ったら ALTER ROLE が失敗した。

$ pg_dumpall --globals-only -U postgres | ssh bastion-ec2 psql -U postgres
You are now connected to database "postgres".
SET
SET
SET
CREATE ROLE
ERROR:  must be superuser to alter superusers

原因

原因は pg_dumpall で吐かれたSQLに NOSUPERUSER が含まれてたせいだった。

CREATE ROLE user1;
ALTER ROLE user1 WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGINPASSWORD 'xxx';

RDS や Aurora 環境ではマスターユーザといえども SUPERUSER 権限を持つことが出来ないので ALETR ROLEでは SUPERUSER はもちろん NOSUPERUSER も指定が出来ない。

対応

とりあえず問題のSQLを以下のように NOSUPERUSER をとっぱらう修正をしてやれば通る。

CREATE ROLE user1;
ALTER ROLE user1 WITH INHERIT NOCREATEROLE NOCREATEDB LOGINPASSWORD 'xxx';

最初のワンライナーでいうと以下のように修正してやったらキレイに通った。

$ pg_dumpall --globals-only -U postgres | perl -pe's/^(ALTER ROLE.*) (?:NO)?SUPERUSER (.*)/$1 $2/' | ssh bastion-ec2 psql -U postgres
You are now connected to database "postgres".
SET
SET
SET
CREATE ROLE

CREATE DATABASE が失敗した

次にスキーマをリストアしてみる。案の定ここでもエラーが出る。

$ pg_dumpall --schema-only -U postgres | ssh bastion-ec2 psql -U postgres
:
ERROR:  must be member of role "user1"

原因

どうやら以下のSQLで失敗したようだ。

postgres=> CREATE DATABASE user1 WITH TEMPLATE = template0 OWNER = user1;
ERROR:  must be member of role "user1"

これは「接続中のユーザ(=postgres) が user1 のロールの権限を持ってないから user1 としてデータベースを作れません」ということのようだ。
自分でセットアップした PostgreSQL とかでは postgres が SUPERUSER 権原を持っていることが多いから忘れがちだが、Aurora環境ではマスターユーザ(=postgres)が SUPERUSER 権原を持っていないのでこういう事が起こる。

対応

user1 ロールの権限を持ってないのが原因なのでつけてやれば良い。

-- 複数ある場合は全部のロールのメンバーになっておけば良い
GRANT user1 TO postgres;
GRANT user2 TO postgres;
-- 複数ある場合はカンマ区切りでまとめて実行もできる
GRANT user1,user2,user3,user4 TO postgres;

ちなみに元の方のDBで先に明示的にこれを実行しておけば pg_dumpall で CREATE ROLE / ALTER ROLE したあとに GRANT も吐いてくれるので何度も dump & restore をするようであれば元の方でやって置くほうが楽だと思う。

Aurora 側で pg_dumpall する時にも注意点

素直に pg_dumpall を実行するとロールのダンプのところで爆死する。

$ pg_dumpall -h dbhost -U postgres
--
-- PostgreSQL database cluster dump
--

SET default_transaction_read_only = off;

SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;

pg_dumpall: 問い合わせが失敗しました: ERROR:  permission denied for relation pg_authid
pg_dumpall: 問い合わせ: SELECT oid, rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolconnlimit, rolpassword, rolvaliduntil, rolreplication, rolbypassrls, pg_catalog.shobj_description(oid, 'pg_authid') as rolcomment, rolname = current_user AS is_current_user FROM pg_authid WHERE rolname !~ '^pg_' ORDER BY 2

対応

これは SUPERUSER を権限持ってないのに他のロールのパスワード見ようとしたから怒られてるので以下のように --no-role-password オプションを付けてやれば解決する。

pg_dumpall -h dbhost -U postgres --no-role-password