PostgreSQLデータベースクラスタには、複数の名前付きデータベースが含まれます。 ユーザおよびユーザのグループはクラスタ全体で共有されますが、他のデータは複数のデータベース間で共有されません。 サーバに接続しているクライアントは、単一のデータベース、つまり接続要求で指定したデータベース内のデータにしかアクセスできません。
注意: クラスタのユーザは、クラスタ内のすべてのデータベースへのアクセス権限を持っているとは限りません。 ユーザ名を共有するということは、たとえばjoeという同じユーザ名を持つ異なるユーザが同じクラスタ内の2つのデータベースに存在することはできないということです。 しかし、joeが一部のデータベースにのみアクセスできるようにシステムを構成することはできます。
データベースには、複数の名前付きスキーマが含まれ、スキーマにはテーブルが含まれます。 スキーマには、データ型、関数および演算子などの他の名前付きオブジェクトも含まれます。 同じオブジェクト名を異なるスキーマで使用しても矛盾は起こりません。 たとえば、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;
この背後にある一般的な機構についての詳細は項5.10を参照してください。
他のユーザが所有するためのスキーマを作成したい場合があります (これは他のユーザの活動を明確に定義された名前空間内に制限する方法の1つです)。 そのための構文は次のとおりです。
CREATE SCHEMA schemaname AUTHORIZATION username;
スキーマ名は省略することもでき、その場合スキーマ名はユーザ名と同じになります。 この構文の便利な使用方法は項5.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スキーマはデフォルトで存在するということ以外に特別な意味はありません。 他のスキーマと同様に削除することもできます。
スキーマ検索パスにアクセスする他の方法については項9.13を参照してください。
検索パスはデータ型名、関数名、演算子名についても、テーブル名の場合と同じように機能します。 データ型および関数の名前は、テーブル名とまったく同じように修飾することができます。 式で修飾演算子名を書く場合には、特別な決まりがあります。 それは以下のとおりです。
OPERATOR(schema.operator)
この規則は構文があいまいになることを防ぐためのものです。 以下に例を示します。
SELECT 3 OPERATOR(pg_catalog.+) 4;
実際の場面ではこのような見づらい構文を書かなくてすむように、演算子についても検索パスが使用されています。
ユーザは、デフォルトでは所有していないスキーマのオブジェクトをアクセスすることはできません。 アクセスするためには、そのスキーマの所有者からスキーマのUSAGE権限を付与してもらう必要があります。 そのスキーマ内のオブジェクトに対して操作を行うには、そのオブジェクトに応じて、さらに追加の権限が必要となる場合があります。
他のユーザのスキーマ内でオブジェクトを作成することも可能です。 それには、スキーマ上でCREATE権限が付与されていなければなりません。 デフォルトでは、public スキーマに関してはすべてのユーザがCREATEとUSAGE権限を持っていることに注意してください。 つまり、すべてのユーザは、そのユーザが接続できる任意のデータベース上のpublicスキーマにオブジェクトを作成できるということです。 これが好ましくない場合は、以下のように権限を取り消すことができます。
REVOKE CREATE ON SCHEMA public FROM PUBLIC;
(最初の"public"はスキーマです。2番目の"public"は"すべてのユーザ"を意味します。 最初のpublicは識別子で、2番目のpublicは予約語なので、それぞれ小文字、大文字を使用しています。 項4.1.1の説明を参照してください。)
各データベースには、publicおよびユーザ作成のスキーマの他にpg_catalogスキーマが含まれています。 このスキーマにはシステムテーブルとすべての組み込みデータ型、関数および演算子が含まれています。 pg_catalogは常に検索パスに含まれています。 パスに明示的にリストされていない場合は、パスのスキーマを検索する前に暗黙的に検索されます。 これにより組み込みの名前が常に検索されることが保証されます。 しかし、ユーザ定義の名前で組み込みの名前を上書きする場合は、pg_catalogを明示的にパスの最後に置くことができます。
PostgreSQLの7.3より前のバージョンでは、pg_で始まるテーブル名は予約されていました。 しかし現在では変更され、システムスキーマ以外のスキーマにもpg_で始まる名前を付けられるようになりました。 ただし、このような名前は使用しないのが得策といえます。 今後のバージョンでユーザのテーブルと同じ名前のシステムカタログが定義され、競合する事態を避けるためです。 (デフォルトの検索パスでは、ユーザのテーブル名への非修飾の参照は、システムカタログとして解決されることになります。) システムカタログは今後もpg_で始まる規則に従うので、ユーザがpg_という前置詞を使わない限り、非修飾のユーザ定義テーブル名がシステムカタログと競合することはありません。
スキーマは様々な方法でデータの編成に使用できます。 デフォルト構成で簡単にサポートできるお勧めの使用パターンがいくつかあります。
スキーマを作成しない場合は、すべてのユーザが暗黙にpublicスキーマにアクセスします。 これはスキーマがまったく使用できない状況と同じです。 このセットアップは主に、データベースで作業するのが1人または2、3人しかいない場合に推奨されます。 またこのセットアップでは、スキーマを認識しない状況からの移行を容易に行えます。
それぞれのユーザに、ユーザ名と同じ名前のスキーマを作成することができます。 デフォルトの検索パスが$userで始まることを思い出してください。 これはユーザ名に解決されます。 つまり、各ユーザが個別のスキーマを持っていると、デフォルトでそれぞれのスキーマにアクセスすることになります。
この設定を使用する場合は、publicスキーマへのアクセス権を取り消して(またはスキーマを削除して)、ユーザが完全に自分のスキーマにしかアクセスできないようにすることもできます。
共有アプリケーション(共有で使うテーブル、サードパーティー提供の追加関数など)をインストールする際には、それぞれ別のスキーマに入れるようにしてください。 また他のユーザがこれらにアクセスできるように適切な権限を付与することを忘れないようにしてください。 それによって他のユーザは、追加のオブジェクトをスキーマ名で修飾することによって参照したり、希望するならば、スキーマをそれぞれのパスに追加したりすることができます。
標準SQLでは、複数のユーザが所有する1つのスキーマに入っているオブジェクトという概念は存在しません。 それどころか、実装によっては所有者と異なる名前のスキーマを作成することが許可されていない場合もあります。 実際、標準で規定されている基本スキーマサポートのみを実装しているデータベースシステムでは、スキーマという概念とユーザという概念はほとんど同じなのです。 そのため、修飾名とはusername.tablenameのことであると思っているユーザは沢山います。 PostgreSQLにおいても、ユーザごとに1つのスキーマを作成すると、このようになります。
また標準SQLには、publicスキーマという概念もありません。 標準に最大限従うためには、publicスキーマは使用すべきではありません(おそらく削除した方がいいかもしれません。)。
もちろん、スキーマをまったく実装していなかったり、または、データベース間アクセスを(場合によっては制限付きで)許可することによって名前空間の使用をサポートしているSQLデータベースもあります。 このようなシステムで作業する必要がある場合は、スキーマをまったく使わないようにすることで最大限の移植性を実現できます。