PostgreSQL 基礎講座 #9 ロール・権限・バックアップ基礎 — 運用の最小規律

インフラ PostgreSQL データベース
読了 5分

基礎の最終回は、華やかではないけれど事故を防ぐ 2 つ、権限とバックアップです。「開発の都合でアプリに superuser アカウントを渡していたら」と「バックアップはあるはずだったのに復元できなかった」は、DB 事故の体験談の二大定番です。どちらも最小限の規律を立てれば避けられます。

ロール: ユーザーもグループも全部 role です #

PostgreSQL の権限の主体はロール(role)1 つに統一されています。ログインできるロールを慣習的に「ユーザー」と呼び、権限のまとまりとして使うロールを「グループ」と呼ぶだけで、同じものです。第 1 回から使ってきた postgres はすべてが許可された superuser ロールで、学習では便利でしたが、アプリケーションに渡すのは禁物です。SQL インジェクションでもコードのバグでも、破られたときの被害は「権限が許す範囲」で決まるからです。

実務の手順: 最小権限アカウントを作る #

権限のまとまりのロールと、ログインするロールを分けるのが定石です。

最小権限ロールの作成 
-- 1) 権限のまとまり(グループ)ロール: ログイン不可
CREATE ROLE app_readwrite NOLOGIN;
GRANT USAGE ON SCHEMA public TO app_readwrite;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_readwrite;
GRANT USAGE ON ALL SEQUENCES IN SCHEMA public TO app_readwrite;

-- これから作るテーブルにも自動で適用
ALTER DEFAULT PRIVILEGES IN SCHEMA public
    GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_readwrite;

-- 2) ログイン(ユーザー)ロール: 権限はグループから継承
CREATE ROLE app_server LOGIN PASSWORD 'strong-password' IN ROLE app_readwrite;

読み取っておくポイントが 3 つあります。1 つ目、GRANT ... ON ALL TABLES は今あるテーブルにだけ適用されるので、未来のテーブルは ALTER DEFAULT PRIVILEGES が受け持ちます(これを忘れるとデプロイのたびに権限エラーにぶつかります)。2 つ目、IDENTITY カラムの採番は内部的にシーケンスを使うので、ON ALL SEQUENCES の USAGE が必要です。3 つ目、アプリケーション用とは別に人間用の読み取り専用ロール(app_readonly、SELECT のみ)を 1 つ置いておくと、「調べに入ったのにうっかり UPDATE」を構造的に防げます。ちなみに PostgreSQL 15 からは public スキーマに誰でもテーブルを作れないよう既定値が締められましたが、上のように明示的に GRANT する習慣をつければ、バージョンと無関係に安全です。

権限の確認は psql のメタコマンド \du(ロール一覧)と \dp(テーブル権限)でします。第 1 回の観察ツール一覧に、この 2 つを追加しておいてください。

バックアップ基礎: pg_dump と復元リハーサル #

バックアップの基礎は論理バックアップ、つまり「DB を作り直せる SQL/アーカイブを取り出しておくこと」です。

ダンプと復元リハーサル 
# カスタム形式でダンプ(圧縮 + 選択的な復元が可能)
pg_dump -U postgres -Fc -f lab.dump lab

# 復元: 新しい DB にリハーサル
createdb -U postgres lab_restore
pg_restore -U postgres -d lab_restore lab.dump

-Fc(カスタム形式)を基本にする理由は、圧縮が効き、pg_restore で特定のテーブルだけ選んで復元できるからです(平文の SQL ダンプは全か無かです)。そしてバックアップで一番重要な文はこれです。復元してみたことのないバックアップはバックアップではありません。 上のように別の DB へ復元するリハーサルを定期的に回してこそ、「バックアップファイルは積もっているのに開けない」事故を防げます。

pg_dump は「ダンプ開始時点の一貫したスナップショット」を取ります(第 6 回の MVCC がここでも働いています)。ただし論理バックアップの限界もはっきりしています。ダンプ以降の変更は消え(昨日未明のバックアップなら今日のデータはありません)、大容量 DB ではダンプ・復元の時間が伸びます。「障害直前の時点に戻る」(PITR)と物理バックアップは、実践編の最終章の主題です。マネージドサービス(RDS など)を使えばこの層をかなり任せられるのは、RDS vs 自前運用で扱ったとおりです。

基礎 9 回の振り返り、そして実践編 #

ここまでが基礎です。インストールと psql(1 回)、データ型と制約(2 回)、結合・集計(3 回)、インデックス(4 回)、EXPLAIN(5 回)、トランザクション・MVCC(6 回)、JSONB(7 回)、ビュー・CTE・ウィンドウ関数(8 回)、そして権限・バックアップ(今回)まで。「クエリは書くが DB を学んだことはない」状態から出発して、DB を観察して判断する基本を身につけました。続く実践編は舞台を「成長するサービス」に移します。無停止のスキーマ変更、コネクションプール、性能診断のルーチン、VACUUM、ロック、パーティショニング、レプリケーション、そして PITR まで、運用で実際にぶつかる順に扱います。

まとめ #

  • 権限の主体はロール 1 つです。ログイン可否と権限の継承の組み合わせで、ユーザーとグループを作ります。
  • アプリケーションに superuser は禁物です。権限のまとまりのロール + ログインロールの構造で最小権限を作ります。
  • ON ALL TABLES は現在まで、未来は ALTER DEFAULT PRIVILEGES です。シーケンスの USAGE も忘れやすい必需品です。
  • バックアップの基礎は pg_dump のカスタム形式で、復元リハーサルのないバックアップはバックアップではありません。PITR は実践編で扱います。
  • 基礎 9 回が終わりました。実践編では、成長するサービスの運用の問題に順にぶつかっていきます。
X