PostgreSQL12以前のpg_dumpallデータを13以降に入れようとするとパスワード認証でコケる

ソフトウェア

概要

PostgreSQLでデータ移行、またはバッグアップの方法としてpg_dumpallがよく使われるが、タイトルのverまたぎでデータ復元するとコケる。原因はパスワード暗号方式の更新のせい。対処としてパスワード保護を解除してデータ投入しパスワードを更新する方法を紹介する。

現状

環境
  • Windows10 64bit 21H1
  • PostgreSQL12.7と13.3
12側で取得したpg_dumpallの出力ファイルを13側へPSQLで入れる。
すると高確率で数行目にパスワードエラーが出力される

原因

PostgreSQL13からデフォルトのパスワードの暗号化方式（postgresql.confのpassword_encryption）がMD5からSHA-256になった。暗号化方式が違うので保存されているデータも復元できなくなり、パスワードが一致しなくなる。

解決法

案として
  1. 12側の暗号化方式をSHA-256にして、全ユーザーのパスワードを設定し直したあと再度pg_dumpallを取得し直して入れる
  2. 13側で一旦パスワード認証を無効にした状態でdumpデータを投入し、全ユーザーのパスワードを再設定したあとパスワード保護を有効にする
1は稼働中のサーバーを停める場合があるので不採用。よって2を採用し、以下にやり方を示す。
  1. 13の[pg_hba.conf]を開き、[127.0.0.1]、[::1]のMETHOD列を[scram-sha-256]から[trust]にする。リモートでpsql叩いてる場合はそのIPのMETHODをいじる。
  2. PostgreSQLを再起動する。サービス画面で出来る
  3. dumpデータをpsqlで投入する。パスワードエラーが出ないはず。出たら1.でtrustにするIP等が間違ってる
  4. 投入できたらpg_AdminやSQLクライアントでユーザー[postgres]にログインする。パスワードなしで入れるはず。
  5. データ上にある全てのユーザーのパスワードを再設定する。同じパスワードで良い。新し暗号化データで上書きしたいだけ。
  6. 1.で[trust]にしたMETHOD列をすべて[scram-sha-256]に戻す
  7. PostgreSQLを再起動する。
  8. PSQL等で全ユーザーにログインし、パスワードが通るか確認して完了

補足

13以降のpg_dumpallのデータを13以降に入れても暗号化方式が一致しているので、この記事の内容が原因でコケることはない。
13以降のデータを12以前に入れるとなったら12と13の作業内容を入れ替えて行う。

0 件のコメント :

コメントを投稿