この記事では、関連するパターンの内容を紹介します。
PostgreSQLデータベースクラスターには、1つ以上の名前付きデータベースが含まれます。 ロールと他のいくつかのオブジェクトタイプは、クラスター全体で共有されます。 サーバーへのクライアント接続は、接続要求で指定された単一のデータベース内のデータにのみアクセスできます。
お知らせ
クラスターのユーザーには、必ずしもクラスター内のすべてのデータベースにアクセスする権限があるとは限りません。 役割名を共有すると、同じクラスター内の2つのデータベースにjoeという名前の異なる役割を含めることはできませんが、一部のデータベースにのみjoeアクセスを許可するようにシステムを構成できます。
データベースには、1つまたは複数の名前付きスキーマが含まれています。 スキーマには、データ型、関数、演算子など、他の種類の名前付きオブジェクトも含まれます。 同じオブジェクト名を異なるスキーマで使用できます。たとえば、schema1とmyschemaの両方にmytableという名前のテーブルを含めることができます。 データベースとは異なり、スキーマは厳密に分離されていません。ユーザーは、接続されているデータベース内のスキーマのいずれかのオブジェクトにアクセスできます。
スキーマを使用する理由はいくつかあります。
多くのユーザーが互いに干渉することなく1つのデータベースを使用できるようにする。
データベースオブジェクトを論理グループに整理して管理しやすくする。
サードパーティのアプリケーションは、他のオブジェクトの名前と衝突しないように、別々のスキーマに入れることができます。
スキーマは、オペレーティングシステムレベルのディレクトリに似ていますが、スキーマをネストできない点が異なります。
スキーマの作成
スキーマを作成するには、create schemaコマンドを使用します。 スキーマに選択した名前を付けます。 設定例：
スキーマを作成します。スキーマ内のオブジェクトを作成またはアクセスするには、ドットで区切られたスキーマ名とテーブル名で構成される修飾名を記述します。
schema.tableこれは、次の章で説明するテーブル変更コマンドやデータアクセスコマンドなど、テーブル名が必要な場所で機能します。 (簡潔にするために、テーブルについてのみ説明しますが、同じ考え方が、型や関数など、他の種類の名前付きオブジェクトにも当てはまります。)
実際には、さらに一般的な構文
database.schema.tableも使用できますが、現時点ではこれはSQL標準に準拠しているためです。 データベース名を記述する場合は、接続先のデータベースと同じである必要があります。
そのため、新しいスキーマにテーブルを作成するには、次を使用します。
CREATE TABLE myschema.mytable (
...
);スキーマが空の場合 (その中のすべてのオブジェクトが削除されている場合) に削除するには、次を使用します。
DROP SCHEMA myschema;含まれるすべてのオブジェクトを含むスキーマを削除するには、次を使用します。
DROP SCHEMA mysema CASCADE;多くの場合、他のユーザーが所有するスキーマを作成する必要があります (これは、ユーザーのアクティビティを明確に定義された名前空間に制限する方法の1つです) 。 その構文は次のとおりです。
スキーマスキーマ名認証user_nameを作成します。スキーマ名を省略することもできます。その場合、スキーマ名はユーザー名と同じになります。
pg_ で始まるスキーマ名はシステム用に予約されており、ユーザーは作成できません。
パブリックスキーマ
前のセクションでは、スキーマ名を指定せずにテーブルを作成しました。 デフォルトでは、このようなテーブル (および他のオブジェクト) は、「public」という名前のスキーマに自動的に配置されます。 すべての新しいデータベースは、そのようなスキーマを含む。 したがって、以下は同等です。
テーブル製品を作成する ( ... );と:
CREATE TABLE public.products ( ... );スキーマ検索パス
修飾された名前は書くのが面倒であり、特定のスキーマ名をアプリケーションに接続しないことが望ましい場合があります。 したがって、テーブルは、テーブル名だけで構成される非修飾名で参照されることがよくあります。 システムは、検索するスキーマのリストである検索パスをたどることによってどのテーブルが意味されるかを決定します。 探索経路における第1のマッチングテーブルは、所望のものであるとみなされる。 検索パスに一致がない場合、データベース内の他のスキーマに一致するテーブル名が存在する場合でも、エラーが報告されます。
異なるスキーマで同じ名前のオブジェクトを作成する機能は、毎回正確に同じオブジェクトを参照するクエリを作成するのを複雑にします。 また、ユーザーが他のユーザーのクエリの動作を悪意を持ってまたは誤って変更する可能性も開きます。 クエリでの非修飾名の普及とPostgreSQL内部での使用により、スキーマをsearch_pathに追加すると、そのスキーマに対してCREATE権限を持つすべてのユーザーが効果的に信頼されます。 通常のクエリを実行すると、検索パスのスキーマにオブジェクトを作成できる悪意のあるユーザーが、任意のSQL関数を実行したかのように制御して実行することができます。
検索パスで最初に名前が付けられたスキーマは、現在のスキーマと呼ばれます。 CREATE TABLEコマンドでスキーマ名が指定されていない場合、最初に検索されるスキーマとは別に、新しいテーブルが作成されるスキーマでもあります。
現在の検索パスを表示するには、次のコマンドを使用します。
SHOW search_path;デフォルトの設定では、これが返されます。
search_path
--------------
"$user", public 最初の要素は、現在のユーザーと同じ名前のスキーマを検索することを指定します。 このようなスキーマが存在しない場合、エントリは無視されます。 2番目の要素は、すでに見たパブリックスキーマを指します。
存在する検索パスの最初のスキーマは、新しいオブジェクトを作成するためのデフォルトの場所です。 これが、デフォルトでオブジェクトがパブリックスキーマに作成される理由です。 オブジェクトがスキーマ修飾 (テーブル変更、データ変更、またはクエリコマンド) なしに他のコンテキストで参照される場合、一致するオブジェクトが見つかるまで検索パスがトラバースされます。 したがって、既定の構成では、修飾されていないアクセスはパブリックスキーマのみを参照できます。
新しいスキーマをパスに配置するには、次を使用します。
SET search_pathにmyschema、public;($ユーザーはすぐに必要ないため、ここでは省略します。) そして、スキーマ修飾なしでテーブルにアクセスできます。
ドロップテーブルmytable;また、myschemaはパスの最初の要素なので、デフォルトで新しいオブジェクトが作成されます。
また、次のように書くこともできます。
SET search_pathにmyschema;その後、明示的な修飾なしにパブリックスキーマにアクセスできなくなりました。 パブリックスキーマには、デフォルトで存在することを除いて、特別なものはありません。 それも落とすことができます。
検索パスは、データ型名、関数名、および演算子名に対しても、テーブル名と同じように機能します。 データ型と関数名は、テーブル名とまったく同じ方法で修飾できます。 式に修飾演算子名を記述する必要がある場合は、特別な規定があります。
OPERATOR(schema.operator)これは、構文の曖昧さを避けるために必要です。 例は次のとおりです。
SELECT 3 OPERATOR(pg_catalog.+) 4;実際には、通常、演算子の検索パスに依存しているため、それほど醜いものを書く必要はありません。
スキーマと特権
デフォルトでは、ユーザーは所有していないスキーマ内のオブジェクトにアクセスできません。 これを許可するには、スキーマの所有者がスキーマに対するUSAGE権限を付与する必要があります。 ユーザーがスキーマ内のオブジェクトを使用できるようにするには、オブジェクトに応じて追加の特権を付与する必要があります。
ユーザーは、他の人のスキーマにオブジェクトを作成することもできます。 これを許可するには、スキーマに対するCREATE権限を付与する必要があります。 デフォルトでは、スキーマpublicに対するCREATEとUSAGEの権限が全員にあります。 これにより、特定のデータベースに接続できるすべてのユーザーが、パブリックスキーマでオブジェクトを作成できます。
公開からのSCHEMAパブリックでのREVOKE CREATE;(最初の「public」はスキーマであり、2番目の「public」は「すべてのユーザー」を意味します。 第1の意味ではそれは識別子であり、第2の意味ではそれはキーワードであり、したがって異なる大文字である。
システムカタログスキーマ
に加えてパブリックとユーザー作成スキーマの場合、各データベースにはpg_catalogスキーマには、システムテーブルとすべての組み込みデータ型、関数、および演算子が含まれます。. pg_catalogは常に有効に検索パスの一部です。 パス内で明示的に名前が付けられていない場合、パスのスキーマを検索する前に暗黙的に検索されます。 これにより、組み込み名が常に検索可能になります。 ただし、ユーザー定義の名前を組み込みの名前に上書きする場合は、検索パスの末尾にpg_catalogを明示的に配置できます。
システムテーブル名はpg_ で始まるため、将来のバージョンでテーブルと同じ名前のシステムテーブルが定義されている場合に競合が発生しないように、このような名前を避けることをお勧めします。 (デフォルトの検索パスを使用すると、テーブル名への修飾されていない参照がシステムテーブルとして解決されます。) システムテーブルは、pg_ で始まる名前を持つという規則に従い続けます。そのため、ユーザーがpg_ プレフィックスを避けている限り、修飾されていないユーザーテーブル名と競合しません。
使用パターン
スキーマを使用して、さまざまな方法でデータを整理できます。 安全なスキーマ使用パターンにより、信頼できないユーザーが他のユーザーのクエリの動作を変更できなくなります。 データベースが安全なスキーマ使用パターンを使用しない場合、そのデータベースを安全に照会したいユーザーは、各セッションの開始時に保護アクションを実行します。 具体的には、search_pathを空の文字列に設定するか、search_pathからスーパーユーザーが書き込みできないスキーマを削除することで、各セッションを開始します。 デフォルト設定では、いくつかの使用パターンが簡単にサポートされます。
通常のユーザーをユーザープライベートスキーマに拘束します。 これを実装するには、REVOKE CREATE ON SCHEMA public FROM PUBLICを発行し、各ユーザーのスキーマをそのユーザーと同じ名前で作成します。 デフォルトの検索パスは
$userで始まり、ユーザー名に解決されることを思い出してください。 したがって、各ユーザーが個別のスキーマを持っている場合、デフォルトで独自のスキーマにアクセスします。 信頼されていないユーザーがすでにログインしているデータベースでこのパターンを採用した後、スキーマpg_catalogのオブジェクトのような名前のオブジェクトのパブリックスキーマを監査することを検討してください。 このパターンは、信頼されていないユーザーがデータベース所有者であるか、CREATEROLE特権を保持している場合を除き、安全なスキーマ使用パターンです。postgresql.confを変更するか、ALTER ROLE ALL SET search_path = "$user"を発行して、既定の検索パスからパブリックスキーマを削除します。 誰もがパブリックスキーマでオブジェクトを作成する機能を保持しますが、それらのオブジェクトを選択するのは修飾名だけです。 修飾されたテーブル参照は問題ありませんが、パブリックスキーマの関数を呼び出します。安全でないか信頼できないでしょう。 パブリックスキーマで関数または拡張機能を作成する場合は、代わりに最初のパターンを使用します。 それ以外の場合、最初のパターンと同様に、信頼できないユーザーがデータベース所有者であるか、CREATEROLE特権を保持していない限り、これは安全です。デフォルトを維持します。 すべてのユーザーが暗黙的にパブリックスキーマにアクセスします。 これは、スキーマがまったく利用できない状況をシミュレートし、非スキーマ認識の世界からのスムーズな移行を提供します。 ただし、これは安全なパターンではありません。 データベースに単一のユーザーまたは少数の相互信頼ユーザーがいる場合にのみ受け入れられます。
どのパターンでも、共有アプリケーション (全員が使用するテーブル、サードパーティが提供する追加機能など) をインストールするには、それらを別々のスキーマに入れます。 他のユーザーがアクセスできるように、適切な権限を付与してください。 ユーザーは、スキーマ名で名前を修飾することによってこれらの追加オブジェクトを参照することも、選択したとおりに追加のスキーマを検索パスに配置することもできます。
移植性
SQL標準では、同じスキーマ内のオブジェクトが異なるユーザーによって所有されるという概念は存在しません。 さらに、一部の実装では、所有者とは異なる名前のスキーマを作成できません。 実際、スキーマとユーザーの概念は、標準で指定された基本的なスキーマサポートのみを実装するデータベースシステムではほぼ同等です。 したがって、多くのユーザーは、修飾名を実際にはuser_nameで構成すると考えています。table_nameを使用します。 これは、すべてのユーザーに対してユーザーごとのスキーマを作成する場合に、PostgreSQLが効果的に動作する方法です。
また、SQL標準にはパブリックスキーマの概念がない。 標準に最大限準拠するために、パブリックスキーマを使用しないでください。
もちろん、一部のSQLデータベースシステムは、スキーマをまったく実装しないか、または (場合によっては制限された) データベース間アクセスを許可することによって名前空間サポートを提供する場合があります。 これらのシステムで作業する必要がある場合は、スキーマをまったく使用しないことで最大の移植性が達成されます。