Database Schema Recommendations for an Application/ja

原文最終更新日：22 February 2013

アプリケーションのためのデータベーススキーマ推奨設定

PostgreSQLデータベースクラスタの範囲において、オブジェクトの分割と名前空間の基本的な手法はデータベースの管理とスキーマデータ定義にて解説されています。

複数命名されたスキーマを伴った単一のデータベースを作成することを推奨します。これは、複数データベースを作成し、"public"スキーマ内にオブジェクトを格納する一般的な（そしてより古い）事例と相違があります。加えて、publicスキーマを削除することを推奨します。

この推奨設定に倣うことでいくつかの優位点があります：

• スキーマを跨ったオブジェクトへのアクセスが単一のデータベース接続にて可能となります。
• スキーマへのアクセスの権限付与はpg_hba.confファイルの再設定ではなくGRANT文を通じて実行されます。
• スキーマのオブジェクト分割と名前空間がANSI標準となります。
• 単一のサーバ（PostgreSQLクラスタ）内の一つのデータベースのみ管理します。

データベースを分割する方法を利用する優位点は、データベースに接続されたセッションが、データベースの境界を越えられないことです。オブジェクトの分割はより完璧です。しかしながら、もっとオブジェクトを分割したい（例えば違う環境変数を使う）場合は、新たなサーバを作成することを推奨します。

注記：データベースレベルの分割は、db_user_namespace設定パラメータを用いて結合された複数テナントホスティングのために使用することができます。スキーマの再分割は、それぞれのホスティングされたデータベースにおいてデータベース構成要素の分割のために重要な機能となります。

テーブルスペースを作成する

それぞれのアプリケーションに最適なテーブルスペースのセットを作成してください。これはアプリケーションごとに記憶媒体の物理的な分割を提供します。アプリケーションの範囲において記憶媒体の分割も提供可能です。テーブルとインデックスの記憶媒体の分離は、管理の柔軟性の向上と性能向上を提供することができます。

PostgreSQLは、ホストOSがデバイス管理を提供することを要求します。テーブルスペースの記憶媒体を分割し、制御するためにそれぞれのテーブルスペースごとにファイルシステムを作成してください。以下は、pgdatapoolとpgindexpoolという名称の記憶媒体プールを伴ったZFS利用例です。2つの10GBのテーブルスペースはとあるアプリケーションで、1つはテーブル用、もう1つはインデックス用に使用されます。

Unixコマンド：

  zfs create -o mountpoint=/pgdata/app1_data pgdatapool/app1_data
  zfs set refquota=10G pgdatapool/app1_data
  zfs set refreference=10G pgdatapool/app1_data
  zfs create -o mountpoint=/pgdata/app1_index pgindexpool/app1_index
  zfs set refquota=10G pgindexpool/app1_index
  zfs set refreference=10G pgindexpool/app1_index

SQLコマンド：

  create tablespace app1_data location '/pgdata/app1_data';
  create tablespace app1_index location '/pgdata/app1_index';

アカウントとロールを作成する

PostgreSQLデータベースオブジェクトは、アカウント（ロール）の所有権を持っています。オブジェクトの空間に名前付けるスキーマと同意のアカウントを作成してください。このアカウントはオブジェクトの所有者として使用されます。セキュリティ目的のために、アカウントが所有するアプリケーションのデータベースオブジェクトへのanyoneの接続権限を許可しないでください。このアカウントは、以前に作成されたテーブルスペースへのアクセスが必要となります。アプリケーションオブジェクトが作成された時に、所有権が正しく設定されていることを確かめてください。

  create user app1 nologin;
  grant create on tablespace app1_data to app1;
  grant create on tablespace app1_index to app1;

ユーザもしくはアプリケーションアカウントにデータベースオブジェクトの権限を付与することは、ロールの使用を通じて行います。アプリケーション用に適切なロールを作成してください。簡潔にするには、少なくとも2つのロールを作成し、一つはアプリケーション用の読み書き権限に使用し、一つは問い合わせ用に使用します。データベースとしてアプリケーション用に作成されたオブジェクトには適切な権限付与をロールに適用します。

  create role app1_role;
  create role app1_query_role;

アプリケーションは一般的に、アプリケーションサーバからプール（共有）されたアカウントを使用してデータベースに接続します。これらのアカウントの認識と資格情報を制限してください。可能であれば、これらのアカウントが定義済みアプリケーションサーバからのみデータベースに接続できるようにpg_hba.confの定義を使用してください。

  create user app1_pool password 'app1_pool';
  grant app1_role to app1_pool;

アプリケーションスキーマを作成する

アプリケーションスキーマを作成し、アプリケーションユーザとロールに適切な権限を付与してください。定義済みスキーマ内のアプリケーションオブジェクトの作成と適切なオブジェクト所有権の設定を確認してください。

  create schema app1 authorization app1;
  grant usage on schema app1 to app1_role;
  grant usage on schema app1 to app1_query_role;

免責事項

これらはDBAの経験からの推奨設定です。これらを倣うことも、拒否することもできます。個人的には、アプリケーションスキーマを管理するために、これらいくつかの単純な方法に倣うことによって、人生を簡単にできます。