導入
PostgreSQLの設計において、データベース、スキーマ、テーブルの階層関係を正しく理解することは、セキュリティと運用効率を高めるために不可欠です。多くの開発者が「とりあえずpublicスキーマにテーブルを作る」運用をしがちですが、マルチテナント環境や開発・本番環境の分離において、スキーマを適切に活用することで、権限管理の複雑さを劇的に軽減できます。本記事では、これら3つの階層の役割と、現場で役立つ活用方法を解説します。
基礎知識
PostgreSQLの構造を理解する上で、以下の3つの階層を知っておく必要があります。
1. データベース
PostgreSQLインスタンス内で物理的に分離された単位です。接続する際にはデータベースを指定する必要があり、異なるデータベース間のテーブルには直接アクセスできません。
2. スキーマ
データベース内部を論理的に分割する「名前空間」です。フォルダのような役割を果たし、異なるスキーマであれば同じ名前のテーブルを共存させることができます。
3. テーブル
実際にデータが格納される場所です。テーブルにアクセスする際は「スキーマ名.テーブル名」という形式（完全修飾名）で指定するのが最も安全です。
実装/解決策
実務では、アプリケーションごとに専用のスキーマを作成し、アクセス権限を最小限に絞るのがベストプラクティスです。デフォルトのpublicスキーマに全てを詰め込むと、他ユーザーが意図せずテーブルを更新・削除できるリスクがあるため注意が必要です。
サンプルプログラム
以下は、管理用スキーマを作成し、特定のユーザーにのみアクセスを許可する一連のSQLです。
— 1. 新しいスキーマを作成
CREATE SCHEMA sales_data;
— 2. 既存のテーブルをスキーマへ移動（または最初からスキーマを指定して作成）
— 構文: CREATE TABLE スキーマ名.テーブル名 (…);
CREATE TABLE sales_data.orders (
id SERIAL PRIMARY KEY, — 注文ID
amount INT NOT NULL, — 金額
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP — 作成日時
);
— 3. 特定のユーザーに対する権限設定
— publicスキーマの権限を剥奪（セキュリティ強化）
REVOKE ALL ON SCHEMA public FROM public;
— sales_dataスキーマへのアクセス権を特定のロールに付与
GRANT USAGE ON SCHEMA sales_data TO app_user;
GRANT SELECT, INSERT, UPDATE ON ALL TABLES IN SCHEMA sales_data TO app_user;
応用・注意点
現場でよくある失敗として「検索パス（search_path）」の罠があります。
1. search_pathの優先順位
テーブル名だけでアクセスすると、PostgreSQLはsearch_path設定順にスキーマを探します。意図しないスキーマのテーブルを参照しないよう、アプリケーションの初期化処理で明確に設定するか、コード内では必ず「スキーマ名.テーブル名」で記述することを推奨します。
2. publicスキーマの削除は慎重に
PostgreSQLのデフォルト設定ではpublicスキーマが存在することを前提とした拡張機能やツールが多いです。publicを削除するのではなく、「権限を厳しく制限する」運用が現実的な落とし所となります。
DBAとしてのアドバイス：開発初期段階でスキーマ設計を固めておけば、将来的なデータの移行や、環境ごとの分離（テスト用・本番用）が非常にスムーズになります。ぜひ、今のプロジェクトの構成を見直してみてください。
コメント