PostgreSQLデータベースクラスタには、1つ以上の名前付きデータベースが含まれます。 ロールおよびいくつかの他のタイプのオブジェクトはクラスタ全体で共有されます。 サーバに接続しているクライアント接続は、単一のデータベース、つまり接続要求で指定したデータベース内のデータにしかアクセスできません。
クラスタのユーザは、クラスタ内の全てのデータベースへのアクセス権限を持っているとは限りません。
ロール名を共有するということは、例えば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;
この背後にある一般的な機構についての詳細は5.14を参照してください。
他のユーザが所有するスキーマを作成したい場合があります(これは他のユーザの活動を明確に定義された名前空間内に制限する方法の1つです)。 そのための構文は次の通りです。
CREATE SCHEMAschema_name
AUTHORIZATIONuser_name
;
スキーマ名は省略することもでき、その場合スキーマ名はユーザ名と同じになります。 この構文の便利な使用方法は5.9.6に記載されています。
pg_
で始まるスキーマ名は、システム上の使用のため予約されており、ユーザが作成することはできません。
これまでの節ではスキーマ名を指定せずにテーブルを作成してきました。 デフォルトでは、このようなテーブル(および他のオブジェクト)は自動的に「public」という名前のスキーマに入れられます。 新しいデータベースには全てこのようなスキーマが含まれています。 そのため、以下の2つの構文は同等です。
CREATE TABLE products ( ... );
および
CREATE TABLE public.products ( ... );
修飾名を書くのは手間がかかりますし、どちらにしても、アプリケーションに特定のスキーマ名を書き込まない方が良いことも多いのです。 そのため、テーブルは多くの場合、テーブル名しか持たない非修飾名として参照されます。 システムは、検索するスキーマのリストである検索パスに従って、どのテーブルを指しているのかを判別します。 検索パスで最初に一致したテーブルが、該当テーブルだと解釈されます。 検索パス内に一致するテーブルがないと、データベースの他のスキーマ内に一致するテーブルがある場合でもエラーが報告されます。
同じ名前のオブジェクトを異なるスキーマに作成できる結果、正確に同じオブジェクトを参照する問合せを書く作業が、いつも複雑になります。
また、ユーザが悪意を持って、あるいは偶然に他のユーザの問合せの挙動を変える可能性をもたらします。
PostgreSQL内部では非修飾名を問合せ中で使うことが一般的なので、search_path
にスキーマを追加することは、CREATE
の書き込み権限を持っているすべてのユーザを、実質的に信頼することになります。
あなたが通常の問合せを実行する際、あなたの検索パス内のスキーマにオブジェクトを作成できる悪意のあるユーザは、支配権を奪い、あたかもあなたが実行したように任意のSQL関数を実行できます。
検索パスの最初に列挙されているスキーマは、「現在のスキーマ」と呼ばれます。
現在のスキーマは、検索される最初のスキーマであると同時に、スキーマ名を指定せずに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.26を参照してください。
検索パスはデータ型名、関数名、演算子名についても、テーブル名の場合と同じように機能します。 データ型および関数の名前は、テーブル名とまったく同じように修飾できます。 式で修飾演算子名を書く場合には、特別な決まりがあります。 それは以下のとおりです。
OPERATOR(
schema
.
operator
)
この規則は構文が曖昧になることを防ぐためのものです。 以下に例を示します。
SELECT 3 OPERATOR(pg_catalog.+) 4;
実際の場面ではこのような見づらい構文を書かなくて済むように、演算子についても検索パスが使用されています。
ユーザは、デフォルトでは所有していないスキーマのオブジェクトをアクセスすることはできません。
アクセスを許可するには、そのスキーマの所有者がスキーマのUSAGE
権限を付与しなければなりません。
デフォルトでは、誰でもpublic
にその権限を所有しています。
ユーザにそのスキーマ内のオブジェクトの利用を許可するには、そのオブジェクトに応じて、さらに追加の権限が必要となる場合があります。
ユーザが、他のユーザのスキーマ内でオブジェクトを作成することを許可することもできます。
これを許可するには、スキーマに対するCREATE
権限を付与する必要があります。
PostgreSQL 14以前からアップグレードされたデータベースでは、誰もがpublic
スキーマに対するその権限を持っています。
一部の使用パターンでは、その権限を取り消す必要があります:
REVOKE CREATE ON SCHEMA public FROM PUBLIC;
(最初の「public」はスキーマです。2番目の「public」は「全てのユーザ」を意味します。 最初のpublicは識別子で、2番目のpublicはキーワードなので、それぞれ小文字、大文字を使用しています。4.1.1のガイドラインを思い出してください。)
各データベースには、public
およびユーザ作成のスキーマの他にpg_catalog
スキーマが含まれています。
このスキーマにはシステムテーブルと全ての組み込みデータ型、関数および演算子が含まれています。
pg_catalog
は常に検索パスに含まれています。
パスに明示的にリストされていない場合は、パスのスキーマを検索する前に暗黙的に検索されます。
これにより組み込みの名前が常に検索されることを保証されます。
しかし、ユーザ定義の名前で組み込みの名前を上書きする場合は、pg_catalog
を明示的にパスの最後に置くことができます。
システムカタログの名前はpg_
で始まりますので、このような名前は使用しないのが得策と言えます。
今後のバージョンでユーザのテーブルと同じ名前のシステムカタログが定義され、競合する事態を避けるためです。
(その結果、デフォルトの検索パスでは、ユーザのテーブル名への非修飾の参照はシステムカタログとして解決されることになります。)
システムカタログは今後もpg_
で始まる規則に従うので、ユーザがpg_
という接頭辞を使わない限り、非修飾のユーザ定義テーブル名がシステムカタログと競合することはありません。
スキーマは様々な方法でデータの編成に使用できます。
セキュアなスキーマの使用パターンは信頼できないユーザが他のユーザの問い合わせの振る舞いを変えるのを防ぎます。
データベースがセキュアなスキーマの使用パターンを使わない場合、セキュアにデータベースを問い合わせたいユーザはセッションの開始毎に防御的なアクションを取るようにします。
具体的には、search_path
に空文字をセットするか、スーパーユーザ以外が書き込み可能なスキーマをsearch_path
から削除することによって、各セッションを開始します。
デフォルト構成で簡単にサポートできるお勧めの使用パターンがいくつかあります。
通常のユーザをユーザの非公開スキーマに制約します。
このパターンを実装するには、最初にどのスキーマもpublic CREATE
権限を持っていないことを確認します。
次に、一時的でないオブジェクトを作成する必要があるすべてのユーザに対して、そのユーザと同じ名前のスキーマを作成します。
たとえば、CREATE SCHEMA alice AUTHORIZATION alice
のようになります。
(デフォルトの検索パスは$user
で始まり、これがユーザ名に解決されることを思い出してください。
したがって、各ユーザが個別のスキーマを持っている場合、デフォルトでは独自のスキーマにアクセスします。)
このパターンは、信頼されていないユーザがデータベース所有者であるか、CREATEROLE
権限を保持していないかぎり、セキュアなスキーマ使用パターンですが、該当する場合はセキュアなスキーマ使用パターンは存在しません。
PostgreSQL 15以降では、デフォルト設定がこの使用方法のパターンをサポートしています。
以前のバージョン、または以前のバージョンからアップグレードされたデータベースを使用する場合、public
スキーマからpublic CREATE
権限を削除する必要があります(REVOKE CREATE ON SCHEMA public FROM PUBLIC
を実行します)。
その後、public
スキーマについて、pg_catalog
スキーマのオブジェクトと同じ名前のオブジェクトがないかどうかを調査することを検討してください。
postgresql.conf
を変更、あるいはALTER ROLE ALL SET search_path = "$user"
を実行することにより、デフォルト検索パスからpublicスキーマを削除します。
それから、publicスキーマ内での作成権限を与えます。
オブジェクトの選択はpublicスキーマ修飾によってのみ行われます。
修飾されたテーブル名による参照は問題ありませんが、publicスキーマ内の関数呼び出しは安全ではないか、あるいは信頼性がありません。
publicスキーマ内に関数や拡張を作る場合は、最初のパターンを代わりに使ってください。
それ以外では、最初のパターン同様、信頼できないユーザがデータベース所有者である場合や、CREATEROLE
権限を持っている場合を除き、これはセキュアです。
デフォルト検索パスを維持し、publicスキーマ内の作成権限を与えます。 すべてのユーザがpublicスキーマに暗黙的にアクセスします。 これはスキーマを考慮しない世界からのスムースな移行を可能にしながら、スキーマがまったく利用できない状況をシミュレートします。 しかし、これは決してセキュアなパターンではありません。 このパターンは、データベースに一人、あるいは少数のお互いに信頼できるユーザだけが存在する場合にのみ受け入れ可能です。 PostgreSQL 14以前のデータベースをアップグレードした場合はこれがデフォルトです。
どのパターンでも、共有のアプリケーション(全員が使うテーブル、サードパーティが提供する追加の関数など)をインストールするには、別のスキーマにアプリケーションを入れてください。 他のユーザがアプリケーションにアクセスするために、適切な権限を与えることを忘れないようにしてください。 ユーザはスキーマ名で名前を修飾するか、あるいは追加スキーマを検索パスに入れるかを選択し、これらの追加オブジェクトを参照できます。
標準SQLでは、1つのスキーマ内のオブジェクトを異なるユーザが所有するという概念は存在しません。
それどころか、実装によっては所有者と異なる名前のスキーマを作成することが許可されていない場合もあります。
実際、標準で規定されている基本スキーマサポートのみを実装しているデータベースシステムでは、スキーマという概念とユーザという概念はほとんど同じなのです。
そのため、修飾名とは
のことであると思っているユーザはたくさんいます。
PostgreSQLにおいても、ユーザごとに1つのスキーマを作成すると、このようになります。
user_name
.table_name
また、標準SQLには、public
スキーマという概念もありません。
標準に最大限従うためには、public
スキーマは使用すべきではありません。
もちろん、スキーマをまったく実装していなかったり、または、データベース間アクセスを(場合によっては制限付きで)許可することによって名前空間の使用をサポートしているSQLデータベースもあります。 このようなシステムで作業する必要がある場合は、スキーマをまったく使わないようにすることで最大限の移植性を実現できます。