セキュリティ、バックアップ、メンテナンス

🔄 前回のおさらい: レッスン6では、レポートとダッシュボードクエリ——ビュー、時間インテリジェンス、自動レポート配信を構築しました。今回は、そのすべてのデータを適切なセキュリティ、バックアップ戦略、継続的メンテナンスで保護します。

ロールベースアクセス制御

アプリケーションやユーザーにデフォルトのスーパーユーザーロールを決して与えないでください。具体的なロールを作成します：

-- レポート用の読み取り専用ロール
CREATE ROLE reporting_role;
GRANT CONNECT ON DATABASE myapp TO reporting_role;
GRANT USAGE ON SCHEMA public TO reporting_role;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO reporting_role;

-- アプリケーションロール（特定テーブルの読み書き）
CREATE ROLE app_role;
GRANT CONNECT ON DATABASE myapp TO app_role;
GRANT USAGE ON SCHEMA public TO app_role;
GRANT SELECT, INSERT, UPDATE ON customers, orders, order_items TO app_role;
-- DELETE権限なし——アプリはソフトデリートを使用

✅ 確認クイズ: アプリケーションロールからDELETE権限を除外する理由は？（ほとんどのアプリケーションは実際の行削除ではなくソフトデリート（deleted_atタイムスタンプの設定）を使うべき。ハードデリートはデータベースレベルで不可逆。アプリロールからDELETE権限を外せば、アプリケーションコードのバグでも誤ってデータを消去できない。ハードデリートが本当に必要なら、適切な承認付きの管理者ロール経由で行う。）

SQLインジェクション防止

良いデータベース権限があっても、SQLインジェクションはコントロールをバイパスし得ます。

ルールはシンプル： ユーザー入力をSQLに結合しない。常にパラメータ化クエリを使用。

# 危険——SQLインジェクションリスク
cursor.execute(f"SELECT * FROM users WHERE email = '{user_email}'")

# 安全——パラメータ化クエリ
cursor.execute("SELECT * FROM users WHERE email = %s", (user_email,))

バックアップ戦略

重要なルール： テストしていないバックアップはバックアップではありません。月次で非本番サーバーへのリストアテストをスケジュールしてください。

一般的なバックアップスケジュール：

• 継続的： WAL（先行書き込みログ）アーカイブでポイントインタイムリカバリー
• 日次： 低トラフィック時にフル論理バックアップ（pg_dump）
• 週次： オフサイトストレージへのフル物理バックアップ
• 月次： バックアップ整合性のリストアテスト

継続的メンテナンス

データベースはメンテナンスなしでは劣化します。自動化された健全性チェックを設定：VACUUM ANALYZEスケジュール（肥大化防止、統計更新）、インデックス健全性チェック、テーブル肥大化モニタリング、接続数モニタリング、ディスク容量アラート、長時間実行クエリの検出。

✅ 確認クイズ: PostgreSQLのパフォーマンスにVACUUM ANALYZEが重要な理由は？（PostgreSQLは削除・更新された行のスペースを即座に回収しない——「デッドタプル」となりテーブルを肥大化させスキャンを遅くする。VACUUMがそのスペースを回収。ANALYZEがクエリプランナーが実行戦略を選ぶために使う統計を更新。定期的なVACUUM ANALYZEなしでは、テーブルが必要以上に大きくなり、クエリプランナーが古い統計に基づいて不適切な判断をする。）

モニタリングとアラート

問題が起きる前にプロアクティブなアラートを設定：指定秒以上のクエリ実行、max_connectionsの一定割合超えの接続数、ディスク使用率80%超え、レプリケーション遅延超過、24時間以内にバックアップ未完了、テーブル肥大化の閾値超え。

まとめ

• 最小権限の原則：すべてのロールに必要最小限の権限のみ——必要以上は決して付与しない
• SQLインジェクション防止はどこでもパラメータ化クエリ——ユーザー入力をSQLに決して結合しない
• バックアップ戦略には論理（pg_dump）と物理の両方が必要で、定期的なリストアテストを実施
• テストしていないバックアップはバックアップではない——月次のリストア検証をスケジュール
• VACUUM ANALYZEがデッドタプルと古い統計によるパフォーマンス劣化を防止
• プロアクティブなモニタリング（接続数、ディスク使用率、クエリ時間）が障害になる前に問題を検出

次のレッスン

最終レッスンでは、学んだすべてを組み合わせて完全なデータベースプロジェクトを構築します——スキーマ設計から本番デプロイまで。