PostgreSQL: 非特権ユーザーによる pg_dump と pg_restore

データベース

前提条件

  • ホスト palau の 50001 番ポートで PostgreSQL サーバが稼働している。
  • このサーバの非特権ユーザー alice がデータベース db0 と db1 を所有している。
  • alice のパスワードは bravo である。
  • db0 には複数のテーブルがある。ただし、スキーマは public のみ。
  • db1 は空である。

「スキーマ」という用語については、後述。

pg_dump

db0 のダンプを custom 形式で ~/db0.dump に作成する。

$ PGPASSWORD=bravo pg_dump \
--host=palau --port=50001 --user=alice \
--format=custom --no-privileges --schema=public --blobs \
--dbname=db0 --file ~/db0.dump

pg_restore

~/db0.dump db0 の内容をデータベース db1 に リストアする。

$ PGPASSWORD=bravo pg_restore \
--host=palau --port=50001 --user=alice \
--dbname=db1 ~/db0.dump

考察

--no-privileges オプションを付けないと revoke 文や grant 文がダンプファイルに書き込まれる。これらの文は、非特権ユーザーでは実行できないため、次のようなエラーを招く：

pg_restore: WARNING:  no privileges could be revoked for "public"
pg_restore: WARNING:  no privileges could be revoked for "public"
pg_restore: WARNING:  no privileges were granted for "public"
pg_restore: WARNING:  no privileges were granted for "public"

--schema=public オプションを付けないと、次のようなエラーが出る：

pg_restore: [archiver (db)] could not execute query: ERROR:  must be owner of extension plpgsql
    Command was: COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language';

--schema=public オプションを付けたときは、--blobs オプションを付けないとラージオブジェクトのデータがダンプファイルに書き込まれない。

ただし、pg_restore はリストアの途中で出たエラーを無視するので、以上のオプションを付けなくても実害はない。

--schema=public オプションについての注記

PostgreSQL にはデータベース内のオブジェクトをグループ化するための「スキーマ」という概念がある。データベースの構造を意味する一般用語の「スキーマ」とは無関係。

public はデフォルトのスキーマであり、スキーマを指定せずに作成されたオブジェクトがそこに分類される。

データベース db0 に public スキーマしか存在しないのであれば、本来 --schema=public というオプションは意味を持たない。しかし、このオプションを指定しないと pg_dump コマンドは plgpsql エクステンションに関するコメントをダンプファイルに書き込む。このエクステンションの所有者は特権ユーザーであるため、リストア時にエラーを引き起こす。

参考資料