バージョンごとのドキュメント一覧

5.9. スキーマ #

<title>Schemas</title>

A <productname>PostgreSQL</productname> database cluster contains one or more named databases. Roles and a few other object types are shared across the entire cluster. A client connection to the server can only access data in a single database, the one specified in the connection request. PostgreSQLデータベースクラスタには、1つ以上の名前付きデータベースが含まれます。 ロールおよびいくつかの他のタイプのオブジェクトはクラスタ全体で共有されます。 サーバに接続しているクライアント接続は、単一のデータベース、つまり接続要求で指定したデータベース内のデータにしかアクセスできません。

注記

Users of a cluster do not necessarily have the privilege to access every database in the cluster. Sharing of role names means that there cannot be different roles named, say, <literal>joe</literal> in two databases in the same cluster; but the system can be configured to allow <literal>joe</literal> access to only some of the databases. クラスタのユーザは、クラスタ内の全てのデータベースへのアクセス権限を持っているとは限りません。 ロール名を共有するということは、例えばjoeという同じロール名を持つ異なるユーザが同じクラスタ内の2つのデータベースに存在することはできないということです。 しかし、joeが一部のデータベースにのみアクセスできるようにシステムを構成することはできます。

A database contains one or more named <firstterm>schemas</firstterm>, which in turn contain tables. Schemas also contain other kinds of named objects, including data types, functions, and operators. The same object name can be used in different schemas without conflict; for example, both <literal>schema1</literal> and <literal>myschema</literal> can contain tables named <literal>mytable</literal>. Unlike databases, schemas are not rigidly separated: a user can access objects in any of the schemas in the database they are connected to, if they have privileges to do so. データベースには、1つ以上の名前付きスキーマが含まれ、スキーマにはテーブルが含まれます。 スキーマには、データ型、関数および演算子などの他の名前付きオブジェクトも含まれます。 同じオブジェクト名を異なるスキーマで使用しても競合は起こりません。 例えば、schema1myschemaの両方のスキーマにmytableというテーブルが含まれていても構いません。 スキーマはデータベースとは異なり厳格に分離されていないので、ユーザは、権限さえ持っていれば接続しているデータベース内のどのスキーマのオブジェクトにでもアクセスすることができます。

There are several reasons why one might want to use schemas: スキーマの使用が好まれる理由はいくつかあります。

Schemas are analogous to directories at the operating system level, except that schemas cannot be nested. スキーマは、入れ子にできないという点を除き、オペレーティングシステムのディレクトリと似ています。

5.9.1. スキーマの作成 #

<title>Creating a Schema</title>

To create a schema, use the <xref linkend="sql-createschema"/> command. Give the schema a name of your choice. For example: スキーマを作成するには、CREATE SCHEMAコマンドを使用します。 スキーマに自由に名前を付けます。 例を示します。

CREATE SCHEMA myschema;

To create or access objects in a schema, write a <firstterm>qualified name</firstterm> consisting of the schema name and table name separated by a dot: スキーマ内にオブジェクトを作成したりこれにアクセスするには、スキーマ名とテーブル名をドットで区切った修飾名を書きます。

schema.table

This works anywhere a table name is expected, including the table modification commands and the data access commands discussed in the following chapters. (For brevity we will speak of tables only, but the same ideas apply to other kinds of named objects, such as types and functions.) この方法は、後の章で説明するテーブル変更コマンドやデータアクセスコマンドなど、テーブル名を必要とする場合すべてに使用できます。 (話を簡単にするため、テーブルについてのみ述べます。 しかし型や関数といった名前付きのオブジェクトの他の種類について同様の考え方が適用できます。)

Actually, the even more general syntax 実際には、より一般的な以下の構文

database.schema.table

can be used too, but at present this is just for pro forma compliance with the SQL standard. If you write a database name, it must be the same as the database you are connected to. を使用することもできますが、今の所この構文は標準SQLに形式的に準拠するためにのみ存在しています。 記述されるデータベース名は、接続しているデータベースと同じ名前でなければなりません。

So to create a table in the new schema, use: ですから、新しいスキーマにテーブルを作成するには次のようにします。

CREATE TABLE myschema.mytable (
 ...
);

To drop a schema if it's empty (all objects in it have been dropped), use: 空のスキーマ(全てのオブジェクトが削除されたスキーマ)を削除するには次のようにします。

DROP SCHEMA myschema;

To drop a schema including all contained objects, use: スキーマ内の全オブジェクトも含めてスキーマを削除する場合には次のようにします。

DROP SCHEMA myschema CASCADE;

See <xref linkend="ddl-depend"/> for a description of the general mechanism behind this. この背後にある一般的な機構についての詳細は5.14を参照してください。

Often you will want to create a schema owned by someone else (since this is one of the ways to restrict the activities of your users to well-defined namespaces). The syntax for that is: 他のユーザが所有するスキーマを作成したい場合があります(これは他のユーザの活動を明確に定義された名前空間内に制限する方法の1つです)。 そのための構文は次の通りです。

CREATE SCHEMA schema_name AUTHORIZATION user_name;

You can even omit the schema name, in which case the schema name will be the same as the user name. See <xref linkend="ddl-schemas-patterns"/> for how this can be useful. スキーマ名は省略することもでき、その場合スキーマ名はユーザ名と同じになります。 この構文の便利な使用方法は5.9.6に記載されています。

Schema names beginning with <literal>pg_</literal> are reserved for system purposes and cannot be created by users. pg_で始まるスキーマ名は、システム上の使用のため予約されており、ユーザが作成することはできません。

5.9.2. publicスキーマ #

<title>The Public Schema</title>

In the previous sections we created tables without specifying any schema names. By default such tables (and other objects) are automatically put into a schema named <quote>public</quote>. Every new database contains such a schema. Thus, the following are equivalent: これまでの節ではスキーマ名を指定せずにテーブルを作成してきました。 デフォルトでは、このようなテーブル(および他のオブジェクト)は自動的にpublicという名前のスキーマに入れられます。 新しいデータベースには全てこのようなスキーマが含まれています。 そのため、以下の2つの構文は同等です。

CREATE TABLE products ( ... );

and: および

CREATE TABLE public.products ( ... );

5.9.3. スキーマ検索パス #

<title>The Schema Search Path</title>

Qualified names are tedious to write, and it's often best not to wire a particular schema name into applications anyway. Therefore tables are often referred to by <firstterm>unqualified names</firstterm>, which consist of just the table name. The system determines which table is meant by following a <firstterm>search path</firstterm>, which is a list of schemas to look in. The first matching table in the search path is taken to be the one wanted. If there is no match in the search path, an error is reported, even if matching table names exist in other schemas in the database. 修飾名を書くのは手間がかかりますし、どちらにしても、アプリケーションに特定のスキーマ名を書き込まない方が良いことも多いのです。 そのため、テーブルは多くの場合、テーブル名しか持たない非修飾名として参照されます。 システムは、検索するスキーマのリストである検索パスに従って、どのテーブルを指しているのかを判別します。 検索パスで最初に一致したテーブルが、該当テーブルだと解釈されます。 検索パス内に一致するテーブルがないと、データベースの他のスキーマ内に一致するテーブルがある場合でもエラーが報告されます。

The ability to create like-named objects in different schemas complicates writing a query that references precisely the same objects every time. It also opens up the potential for users to change the behavior of other users' queries, maliciously or accidentally. Due to the prevalence of unqualified names in queries and their use in <productname>PostgreSQL</productname> internals, adding a schema to <varname>search_path</varname> effectively trusts all users having <literal>CREATE</literal> privilege on that schema. When you run an ordinary query, a malicious user able to create objects in a schema of your search path can take control and execute arbitrary SQL functions as though you executed them. 同じ名前のオブジェクトを異なるスキーマに作成できる結果、正確に同じオブジェクトを参照する問合せを書く作業が、いつも複雑になります。 また、ユーザが悪意を持って、あるいは偶然に他のユーザの問合せの挙動を変える可能性をもたらします。 PostgreSQL内部では非修飾名を問合せ中で使うことが一般的なので、search_pathにスキーマを追加することは、CREATEの書き込み権限を持っているすべてのユーザを、実質的に信頼することになります。 あなたが通常の問合せを実行する際、あなたの検索パス内のスキーマにオブジェクトを作成できる悪意のあるユーザは、支配権を奪い、あたかもあなたが実行したように任意のSQL関数を実行できます。

The first schema named in the search path is called the current schema. Aside from being the first schema searched, it is also the schema in which new tables will be created if the <command>CREATE TABLE</command> command does not specify a schema name. 検索パスの最初に列挙されているスキーマは、「現在のスキーマ」と呼ばれます。 現在のスキーマは、検索される最初のスキーマであると同時に、スキーマ名を指定せずにCREATE TABLEコマンドでテーブルを作成した場合に新しいテーブルが作成されるスキーマでもあります。

To show the current search path, use the following command: 現行の検索パスを示すには次のコマンドを使用します。

SHOW search_path;

In the default setup this returns: デフォルトの設定では次のように返されます。

 search_path
--------------
 "$user", public

The first element specifies that a schema with the same name as the current user is to be searched. If no such schema exists, the entry is ignored. The second element refers to the public schema that we have seen already. 最初の要素は、現行ユーザと同じ名前のスキーマを検索することを指定しています。 そのようなスキーマが存在していない場合、この項目は無視されます。 2番目の要素は、先ほど説明したpublicスキーマを参照しています。

The first schema in the search path that exists is the default location for creating new objects. That is the reason that by default objects are created in the public schema. When objects are referenced in any other context without schema qualification (table modification, data modification, or query commands) the search path is traversed until a matching object is found. Therefore, in the default configuration, any unqualified access again can only refer to the public schema. 実存するスキーマのうち、検索パス内で最初に現れるスキーマが、新規オブジェクトが作成されるデフォルトの場所になります。 これが、デフォルトでオブジェクトがpublicスキーマに作成される理由です。 オブジェクトがスキーマ修飾なしで別の文脈で参照される場合(テーブル変更、データ変更、あるいは問い合わせコマンドなど)、一致するオブジェクトが見つかるまで検索パス内で探索されます。 そのためデフォルト構成では、非修飾のアクセスはpublicスキーマしか参照できません。

To put our new schema in the path, we use: 新しいスキーマをパスに追加するには次のようにします。

SET search_path TO myschema,public;

(We omit the <literal>$user</literal> here because we have no immediate need for it.) And then we can access the table without schema qualification: $userはまだ必要ないので、ここでは省略しています。) そして、次のようにしてスキーマ修飾なしでテーブルにアクセスします。

DROP TABLE mytable;

Also, since <literal>myschema</literal> is the first element in the path, new objects would by default be created in it. また、myschemaはパス内の最初の要素なので、新しいオブジェクトはデフォルトでここに作成されます。

We could also have written: 以下のように書くこともできます。

SET search_path TO myschema;

Then we no longer have access to the public schema without explicit qualification. There is nothing special about the public schema except that it exists by default. It can be dropped, too. このようにすると、今後は修飾名なしでpublicスキーマにアクセスすることができなくなります。 publicスキーマはデフォルトで存在するということ以外に特別な意味はありません。 他のスキーマと同様に削除することもできます。

See also <xref linkend="functions-info"/> for other ways to manipulate the schema search path. スキーマ検索パスを操作する他の方法については9.26を参照してください。

The search path works in the same way for data type names, function names, and operator names as it does for table names. Data type and function names can be qualified in exactly the same way as table names. If you need to write a qualified operator name in an expression, there is a special provision: you must write 検索パスはデータ型名、関数名、演算子名についても、テーブル名の場合と同じように機能します。 データ型および関数の名前は、テーブル名とまったく同じように修飾できます。 式で修飾演算子名を書く場合には、特別な決まりがあります。 それは以下のとおりです。

OPERATOR(schema.operator)

This is needed to avoid syntactic ambiguity. An example is: この規則は構文が曖昧になることを防ぐためのものです。 以下に例を示します。

SELECT 3 OPERATOR(pg_catalog.+) 4;

In practice one usually relies on the search path for operators, so as not to have to write anything so ugly as that. 実際の場面ではこのような見づらい構文を書かなくて済むように、演算子についても検索パスが使用されています。

5.9.4. スキーマおよび権限 #

<title>Schemas and Privileges</title>

By default, users cannot access any objects in schemas they do not own. To allow that, the owner of the schema must grant the <literal>USAGE</literal> privilege on the schema. By default, everyone has that privilege on the schema <literal>public</literal>. To allow users to make use of the objects in a schema, additional privileges might need to be granted, as appropriate for the object. ユーザは、デフォルトでは所有していないスキーマのオブジェクトをアクセスすることはできません。 アクセスを許可するには、そのスキーマの所有者がスキーマのUSAGE権限を付与しなければなりません。 デフォルトでは、誰でもpublicにその権限を所有しています。 ユーザにそのスキーマ内のオブジェクトの利用を許可するには、そのオブジェクトに応じて、さらに追加の権限が必要となる場合があります。

A user can also be allowed to create objects in someone else's schema. To allow that, the <literal>CREATE</literal> privilege on the schema needs to be granted. In databases upgraded from <productname>PostgreSQL</productname> 14 or earlier, everyone has that privilege on the schema <literal>public</literal>. Some <link linkend="ddl-schemas-patterns">usage patterns</link> call for revoking that privilege: ユーザが、他のユーザのスキーマ内でオブジェクトを作成することを許可することもできます。 これを許可するには、スキーマに対するCREATE権限を付与する必要があります。 PostgreSQL 14以前からアップグレードされたデータベースでは、誰もがpublicスキーマに対するその権限を持っています。 一部の使用パターンでは、その権限を取り消す必要があります:

REVOKE CREATE ON SCHEMA public FROM PUBLIC;

(The first <quote>public</quote> is the schema, the second <quote>public</quote> means <quote>every user</quote>. In the first sense it is an identifier, in the second sense it is a key word, hence the different capitalization; recall the guidelines from <xref linkend="sql-syntax-identifiers"/>.) (最初のpublicはスキーマです。2番目のpublic全てのユーザを意味します。 最初のpublicは識別子で、2番目のpublicはキーワードなので、それぞれ小文字、大文字を使用しています。4.1.1のガイドラインを思い出してください。)

5.9.5. システムカタログスキーマ #

<title>The System Catalog Schema</title>

In addition to <literal>public</literal> and user-created schemas, each database contains a <literal>pg_catalog</literal> schema, which contains the system tables and all the built-in data types, functions, and operators. <literal>pg_catalog</literal> is always effectively part of the search path. If it is not named explicitly in the path then it is implicitly searched <emphasis>before</emphasis> searching the path's schemas. This ensures that built-in names will always be findable. However, you can explicitly place <literal>pg_catalog</literal> at the end of your search path if you prefer to have user-defined names override built-in names. 各データベースには、publicおよびユーザ作成のスキーマの他にpg_catalogスキーマが含まれています。 このスキーマにはシステムテーブルと全ての組み込みデータ型、関数および演算子が含まれています。 pg_catalogは常に検索パスに含まれています。 パスに明示的にリストされていない場合は、パスのスキーマを検索するに暗黙的に検索されます。 これにより組み込みの名前が常に検索されることを保証されます。 しかし、ユーザ定義の名前で組み込みの名前を上書きする場合は、pg_catalogを明示的にパスの最後に置くことができます。

Since system table names begin with <literal>pg_</literal>, it is best to avoid such names to ensure that you won't suffer a conflict if some future version defines a system table named the same as your table. (With the default search path, an unqualified reference to your table name would then be resolved as the system table instead.) System tables will continue to follow the convention of having names beginning with <literal>pg_</literal>, so that they will not conflict with unqualified user-table names so long as users avoid the <literal>pg_</literal> prefix. システムカタログの名前はpg_で始まりますので、このような名前は使用しないのが得策と言えます。 今後のバージョンでユーザのテーブルと同じ名前のシステムカタログが定義され、競合する事態を避けるためです。 (その結果、デフォルトの検索パスでは、ユーザのテーブル名への非修飾の参照はシステムカタログとして解決されることになります。) システムカタログは今後もpg_で始まる規則に従うので、ユーザがpg_という接頭辞を使わない限り、非修飾のユーザ定義テーブル名がシステムカタログと競合することはありません。

5.9.6. 使用パターン #

<title>Usage Patterns</title>

Schemas can be used to organize your data in many ways. A <firstterm>secure schema usage pattern</firstterm> prevents untrusted users from changing the behavior of other users' queries. When a database does not use a secure schema usage pattern, users wishing to securely query that database would take protective action at the beginning of each session. Specifically, they would begin each session by setting <varname>search_path</varname> to the empty string or otherwise removing schemas that are writable by non-superusers from <varname>search_path</varname>. There are a few usage patterns easily supported by the default configuration: スキーマは様々な方法でデータの編成に使用できます。 セキュアなスキーマの使用パターンは信頼できないユーザが他のユーザの問い合わせの振る舞いを変えるのを防ぎます。 データベースがセキュアなスキーマの使用パターンを使わない場合、セキュアにデータベースを問い合わせたいユーザはセッションの開始毎に防御的なアクションを取るようにします。 具体的には、search_pathに空文字をセットするか、スーパーユーザ以外が書き込み可能なスキーマをsearch_pathから削除することによって、各セッションを開始します。 デフォルト構成で簡単にサポートできるお勧めの使用パターンがいくつかあります。

  • Constrain ordinary users to user-private schemas. To implement this pattern, first ensure that no schemas have public <literal>CREATE</literal> privileges. Then, for every user needing to create non-temporary objects, create a schema with the same name as that user, for example <literal>CREATE SCHEMA alice AUTHORIZATION alice</literal>. (Recall that the default search path starts with <literal>$user</literal>, which resolves to the user name. Therefore, if each user has a separate schema, they access their own schemas by default.) This pattern is a secure schema usage pattern unless an untrusted user is the database owner or has been granted <literal>ADMIN OPTION</literal> on a relevant role, in which case no secure schema usage pattern exists. 通常のユーザをユーザの非公開スキーマに制約します。 このパターンを実装するには、最初にどのスキーマもpublic CREATE権限を持っていないことを確認します。 次に、一時的でないオブジェクトを作成する必要があるすべてのユーザに対して、そのユーザと同じ名前のスキーマを作成します。 たとえば、CREATE SCHEMA alice AUTHORIZATION aliceのようになります。 (デフォルトの検索パスは$userで始まり、これがユーザ名に解決されることを思い出してください。 したがって、各ユーザが個別のスキーマを持っている場合、デフォルトでは独自のスキーマにアクセスします。) このパターンは、信頼されていないユーザがデータベース所有者であるか、または関連するロールにADMIN OPTIONが付与されていない場合に限りセキュアですが、該当する場合はセキュアなスキーマ使用パターンは存在しません。

    A database owner can attack the database's users via "CREATE SCHEMA trojan; ALTER DATABASE $mydb SET search_path = trojan, public;".

    In <productname>PostgreSQL</productname> 15 and later, the default configuration supports this usage pattern. In prior versions, or when using a database that has been upgraded from a prior version, you will need to remove the public <literal>CREATE</literal> privilege from the <literal>public</literal> schema (issue <literal>REVOKE CREATE ON SCHEMA public FROM PUBLIC</literal>). Then consider auditing the <literal>public</literal> schema for objects named like objects in schema <literal>pg_catalog</literal>. PostgreSQL 15以降では、デフォルト設定がこの使用方法のパターンをサポートしています。 以前のバージョン、または以前のバージョンからアップグレードされたデータベースを使用する場合、publicスキーマからpublic CREATE権限を削除する必要があります(REVOKE CREATE ON SCHEMA public FROM PUBLICを実行します)。 その後、publicスキーマについて、pg_catalogスキーマのオブジェクトと同じ名前のオブジェクトがないかどうかを調査することを検討してください。

    "DROP SCHEMA public" is inferior to this REVOKE, because pg_dump doesn't preserve that DROP.
  • Remove the public schema from the default search path, by modifying <link linkend="config-setting-configuration-file"><filename>postgresql.conf</filename></link> or by issuing <literal>ALTER ROLE ALL SET search_path = "$user"</literal>. Then, grant privileges to create in the public schema. Only qualified names will choose public schema objects. While qualified table references are fine, calls to functions in the public schema <link linkend="typeconv-func">will be unsafe or unreliable</link>. If you create functions or extensions in the public schema, use the first pattern instead. Otherwise, like the first pattern, this is secure unless an untrusted user is the database owner or has been granted <literal>ADMIN OPTION</literal> on a relevant role. postgresql.confを変更、あるいはALTER ROLE ALL SET search_path = ""を実行することにより、デフォルト検索パスからpublicスキーマを削除します。 それから、publicスキーマ内での作成権限を与えます。 オブジェクトの選択はpublicスキーマ修飾によってのみ行われます。 修飾されたテーブル名による参照は問題ありませんが、publicスキーマ内の関数呼び出しは安全ではないか、あるいは信頼性がありません。 publicスキーマ内に関数や拡張を作る場合は、最初のパターンを代わりに使ってください。 それ以外では、最初のパターン同様、信頼できないユーザがデータベース所有者である場合や、関連するロールにADMIN OPTIONが付与されていない限り、これはセキュアです。

  • Keep the default search path, and grant privileges to create in the public schema. All users access the public schema implicitly. This simulates the situation where schemas are not available at all, giving a smooth transition from the non-schema-aware world. However, this is never a secure pattern. It is acceptable only when the database has a single user or a few mutually-trusting users. In databases upgraded from <productname>PostgreSQL</productname> 14 or earlier, this is the default. デフォルト検索パスを維持し、publicスキーマ内の作成権限を与えます。 すべてのユーザがpublicスキーマに暗黙的にアクセスします。 これはスキーマを考慮しない世界からのスムースな移行を可能にしながら、スキーマがまったく利用できない状況をシミュレートします。 しかし、これは決してセキュアなパターンではありません。 このパターンは、データベースに一人、あるいは少数のお互いに信頼できるユーザだけが存在する場合にのみ受け入れ可能です。 PostgreSQL 14以前のデータベースをアップグレードした場合はこれがデフォルトです。

For any pattern, to install shared applications (tables to be used by everyone, additional functions provided by third parties, etc.), put them into separate schemas. Remember to grant appropriate privileges to allow the other users to access them. Users can then refer to these additional objects by qualifying the names with a schema name, or they can put the additional schemas into their search path, as they choose. どのパターンでも、共有のアプリケーション(全員が使うテーブル、サードパーティが提供する追加の関数など)をインストールするには、別のスキーマにアプリケーションを入れてください。 他のユーザがアプリケーションにアクセスするために、適切な権限を与えることを忘れないようにしてください。 ユーザはスキーマ名で名前を修飾するか、あるいは追加スキーマを検索パスに入れるかを選択し、これらの追加オブジェクトを参照できます。

5.9.7. 移植性 #

<title>Portability</title>

In the SQL standard, the notion of objects in the same schema being owned by different users does not exist. Moreover, some implementations do not allow you to create schemas that have a different name than their owner. In fact, the concepts of schema and user are nearly equivalent in a database system that implements only the basic schema support specified in the standard. Therefore, many users consider qualified names to really consist of <literal><replaceable>user_name</replaceable>.<replaceable>table_name</replaceable></literal>. This is how <productname>PostgreSQL</productname> will effectively behave if you create a per-user schema for every user. 標準SQLでは、1つのスキーマ内のオブジェクトを異なるユーザが所有するという概念は存在しません。 それどころか、実装によっては所有者と異なる名前のスキーマを作成することが許可されていない場合もあります。 実際、標準で規定されている基本スキーマサポートのみを実装しているデータベースシステムでは、スキーマという概念とユーザという概念はほとんど同じなのです。 そのため、修飾名とはuser_name.table_nameのことであると思っているユーザはたくさんいます。 PostgreSQLにおいても、ユーザごとに1つのスキーマを作成すると、このようになります。

Also, there is no concept of a <literal>public</literal> schema in the SQL standard. For maximum conformance to the standard, you should not use the <literal>public</literal> schema. また、標準SQLには、publicスキーマという概念もありません。 標準に最大限従うためには、publicスキーマは使用すべきではありません。

Of course, some SQL database systems might not implement schemas at all, or provide namespace support by allowing (possibly limited) cross-database access. If you need to work with those systems, then maximum portability would be achieved by not using schemas at all. もちろん、スキーマをまったく実装していなかったり、または、データベース間アクセスを(場合によっては制限付きで)許可することによって名前空間の使用をサポートしているSQLデータベースもあります。 このようなシステムで作業する必要がある場合は、スキーマをまったく使わないようにすることで最大限の移植性を実現できます。