PostgreSQL データベースクラスタ (インストレーション) には、1 つ以上の名前付きデータベースが含まれます。 ユーザおよびユーザのグループはクラスタ全体で共有されますが、他のデータは複数のデータベース間で共有されません。 サーバに接続しているクライアントは、単一のデータベース、つまり接続要求で指定したデータベース内のデータにしかアクセスできません。
Note: クラスタのユーザは、クラスタ内のすべてのデータベースへのアクセス権限を持っているとは限りません。 ユーザ名を共有するということは、たとえば joe という同じユーザ名を持つ異なるユーザが同じクラスタ内の 2 つのデータベースに存在することはできないということです。しかし、joe が一部のデータベースにのみアクセスできるようにシステムを構成することはできます。
データベースには、1 つ以上の名前付きスキーマが含まれ、スキーマにはテーブルが含まれます。 スキーマには、データ型、関数および演算子などの他の名前付きオブジェクトも含まれます。 同じオブジェクト名を複数のスキーマで使用しても矛盾は起こりません。たとえば、schema1 と myschema の両方のスキーマに mytable というテーブルが含まれていても構いません。 スキーマはデータベースとは異なり厳格に分離されていないので、ユーザは、権限さえ持っていれば接続しているデータベース内のどのスキーマのオブジェクトにでもアクセスすることができます。
スキーマを使用する理由はいくつかあります。
1 つのデータベースを多数のユーザが互いに干渉することなく使用できるようにするため。
管理しやすくなるよう、データベースオブジェクトを論理グループに編成するため。
サードパーティーのアプリケーションを別々のスキーマに入れることにより、他のオブジェクトの名前と競合しないようにするため。
スキーマは、ネストできないという点を除き、オペレーティングシステムのディレクトリーと似ています。
個別のスキーマを作成するには、CREATE SCHEMA コマンドを使用します。 スキーマに自由に名前を付けます。例を示します。
CREATE SCHEMA myschema;
スキーマ内にオブジェクトを作成したりこれにアクセスするには、スキーマ名とテーブル名をドットで区切った修飾名を書きます。
schema.table
実際には、より一般的な以下の構文
database.schema.table
を使用することもできますが、現在ではこの構文は SQL に準拠するためにのみ存在しています。記述されるデータベース名は、接続しているデータベースと同じ名前でなければなりません。
ですから、新しいスキーマにテーブルを作成するには次のようにします。
CREATE TABLE myschema.mytable ( ... );
この方法は、後で説明するテーブル変更コマンドやデータアクセスコマンドなど、テーブル名を必要とする場合すべてに使用できます。
空のスキーマ (すべてのオブジェクトがドロップされたスキーマ) をドロップするには次のようにします。
DROP SCHEMA myschema;
スキーマを、含まれているすべてのオブジェクトと一緒にドロップするには次のようにします。
DROP SCHEMA myschema CASCADE;
この構文がどのように機能するのかについての詳細は Section 2.10 を参照してください。
他のユーザが所有するためのスキーマを作成したい場合があります (これは他のユーザの活動を明確に定義された名前空間内に制限する方法の 1 つです) 。 そのための構文は次のとおりです。
CREATE SCHEMA schemaname AUTHORIZATION username;
スキーマ名は省略することもでき、その場合スキーマ名はユーザ名と同じになります。 この構文の便利な使用方法は Section 2.8.6 に記載されています。
pg_ で始まるスキーマ名は、システム上の使用のため予約されており、ユーザが作成することはできません。
これまでの節ではスキーマ名を指定せずにテーブルを作成してきました。 デフォルトでは、このようなテーブル (および他のオブジェクト) は自動的に "public" というスキーマに入れられます。 新しいデータベースにはすべてこのようなスキーマが含まれています。 そのため、以下の 2 つの構文は同等です。
CREATE TABLE products ( ... );
と
CREATE TABLE public.products ( ... );
修飾名を書くのは手間がかかりますし、どちらにしても、アプリケーションに特定のスキーマ名を書き込まないほうが良いことも多いのです。 そのため、テーブルは多くの場合、テーブル名しかもたない非修飾名として参照されます。 システムは、検索するスキーマのリストである検索パスに従って、どのテーブルを指しているのかを判別します。検索パスで最初に一致したテーブルが、該当テーブルだと解釈されます。 検索パス内に一致するテーブルがないと、データベースの他のスキーマ内に一致するテーブルがある場合でもエラーが報告されます。
検索パスの最初にリストされているスキーマは、現行スキーマと呼ばれます。 現行スキーマは、検索される最初のスキーマであると同時に、スキーマ名を指定せずに CREATE TABLE コマンドでテーブルを作成した場合に新しいテーブルが作成されるスキーマでもあります。
現行の検索パスを示すには次のコマンドを使用します。
SHOW search_path;
デフォルトの設定では次のように返されます。
search_path -------------- $user,public
最初の要素は、現行ユーザと同じ名前のスキーマを検索することを指定しています。 そのようなスキーマはまだ存在していないため、このエントリは無視されます。 2 番目の要素は、先ほど説明した public スキーマを参照しています。
検索パス内に存在する最初のスキーマは、新規オブジェクトが作成されるデフォルトの場所になります。 これが、デフォルトでオブジェクトが public スキーマに作成される理由です。 オブジェクトが、スキーマ修飾なしで別のコンテキストで参照される場合 (デーブル変更、データ変更、あるいは問い合わせコマンドなど)、一致するオブジェクトが見つかるまで検索パス内で探索されます。 そのためデフォルト構成では、非修飾のアクセスは public スキーマしか参照できません。
新しいスキーマをパスに追加するには次のようにします。
SET search_path TO myschema,public;
($user はまだ必要ないので、ここでは省略しています。) そして、次のようにしてスキーマ修飾なしでテーブルにアクセスします。
DROP TABLE mytable;
また、myschema はパス内の最初の要素なので、新しいオブジェクトはデフォルトでここに作成されます。
以下のように書くこともできます。
SET search_path TO myschema;
このようにすると、今後は修飾名なしで public スキーマにアクセスすることができなくなります。 public スキーマはデフォルトで存在するということ以外に特別な意味はありません。 他のスキーマと同様にドロップすることもできます。
スキーマ検索パスにアクセスする他の方法については Section 6.13 を参照してください。
検索パスはデータ型名、関数名、演算子名についても、テーブル名の場合と同じように機能します。 データ型および関数の名前は、テーブル名とまったく同じように修飾することができます。 式で修飾演算子名を書く場合には、特別な決まりがあります。 それは以下のとおりです。
OPERATOR(schema.operator)
この規則は構文があいまいになることを防ぐためのものです。 たとえば、
SELECT 3 OPERATOR(pg_catalog.+) 4;
実際の場面ではこのような見づらい構文を書かなくてすむように、演算子についても検索パスが使用されています。
ユーザは、デフォルトでは所有していないスキーマのオブジェクトを見ることはできません。 これらのオブジェクトを見るには、そのスキーマの所有者からスキーマの USAGE 権限を付与してもらう必要があります。 そのスキーマ内のオブジェクトに対して操作を行うには、そのオブジェクトの種類に応じて、さらに追加の権限が必要となる場合があります。
他のユーザのスキーマ内でオブジェクトを作成することも可能です。 それには、CREATE 権限が必要です。 public スキーマに関してはすべてのユーザが CREATE 権限を持っていることに注意してください。 つまり、すべてのユーザは、そのユーザが接続できる任意のデータベース上にオブジェクトを作成できるということです。 これが好ましくない場合は、CREATE 権限を取り消すことができます。
REVOKE CREATE ON public FROM PUBLIC;
(最初の "public" はスキーマです。2 番目の "PUBLIC" は "すべてのユーザ" を意味します。 最初の public は識別子で、2番目の PUBLIC は予約語なので、それぞれ小文字、大文字になっています。Section 1.1.1 の説明を参照してください。)
各データベースには、public およびユーザ作成のスキーマの他に pg_catalog が含まれています。pg_catalog スキーマにはシステムテーブルとすべての組み込みデータ型、関数および演算子が含まれており、常に検索パスに含まれています。 パスに明示的にリストされていない場合は、パスのスキーマを検索する前に暗黙的に検索されます。 これにより組み込みの名前が常に検索されることが保証されます。 しかし、ユーザ定義の名前で組み込みの名前をオーバーライドする場合は、pg_catalog を明示的にパスの最後に置くことができます。
PostgreSQL の 7.3 より前のバージョンでは、pg_ で始まるテーブル名は予約されていました。 しかし現在では、システムスキーマ以外のスキーマにも pg_ で始まる名前を付けられるようになりました。 ただし、このような名前は使用しないのが得策といえます。今後のバージョンでユーザのテーブルと同じ名前のシステムカタログが定義され、競合する事態を避けるためです。 (デフォルトの検索パスでは、ユーザのテーブル名への非修飾の参照は、システムカタログとして解決されることになります) システムカタログは今後も pg_ で始まる規則に従うので、ユーザが pg_ を使わない限り、非修飾のユーザ定義テーブル名がシステムカタログと競合することはありません。
スキーマはさまざまな方法でデータの編成に使用できます。 デフォルト構成で簡単にサポートできるお勧めの使用パターンがいくつかあります。
スキーマを作成しない場合は、すべてのユーザが暗黙に public スキーマにアクセスします。 これはスキーマがまったく使用できない状況と同じです。 このセットアップは主に、データベースで作業するのが 1人または 2、3人しかいない場合に推奨されます。 またこのセットアップでは、スキーマを認識しないセットップからの移行を容易に行えます。
それぞれのユーザに、ユーザ名と同じ名前のスキーマを作成することができます。 デフォルトの検索パスが $user で始まることを思い出してください。これはユーザ名に解決されます。 つまり、各ユーザが個別のスキーマを持っていると、デフォルトでそれぞれのスキーマにアクセスすることになります。
このセットアップを使用する場合は、public スキーマへのアクセス権を取り消して (またはスキーマをドロップして) 、ユーザが完全に自分のスキーマにしかアクセスできないようにすることもできます。
共有アプリケーション (共有で使うテーブル、サードパーティー提供の追加関数など) をインストールする際には、それぞれ別のスキーマに入れるようにしてください。 また他のユーザがこれらにアクセスできるように適切な権限を付与することを忘れないようにしてください。 それによって他のユーザは、追加のオブジェクトをスキーマ名で修飾することによって参照したり、スキーマをそれぞれのパスに追加したりすることができます。
SQL 標準では、複数のユーザが所有する 1 つのスキーマに入っているオブジェクトという概念は存在しません。 それどころか、実装によっては所有者と異なる名前のスキーマを作成することが許可されていない場合もあります。 実際、SQL 標準で指定されている基本スキーマサポートのみを実装しているデータベースシステムでは、スキーマという概念とユーザという概念はほとんど同じなのです。 そのため、修飾名とは username.tablename のことであると思っているユーザは沢山います。 PostgreSQL においても、ユーザごとに 1 つのスキーマを作成すると、このようになります。
また SQL標準には、public スキーマという概念もありません。 標準に最大限従うためには、public スキーマは使用しないか、あるいは削除してしまうことをお勧めします。
もちろん、スキーマをまったく実装していなかったり、または、データベース間アクセスを (場合によっては制限付きで) 許可することによって名前空間の使用をサポートしている SQL データベースもあります。 このようなシステムで作業する必要がある場合は、スキーマをまったく使わないようにすることで最大限の移植性を実現できます。