【PostgreSQL】 「スキーマ」って何？

2024.08.31

スキーマ（SCHEMA)とは？

  • 同一データベースの中に複数作成できる「名前空間」の事
  • テーブル等を含めたオブジェクトをそれぞれのスキーマに作成する事ができる

※名前空間：同じオブジェクト名（テーブル名、変数名、関数名など）でも、エラーとならないように区切られた「領域」


●使用方法

例えば、同一データベースに「スキーマ_A」と「スキーマ_B」を作成し、「ユーザー_A」には「スキーマ_A」を、「ユーザーB」には「スキーマ_B」を割り当てたとします。

この設定によって、使用するデータベースが同じでも、業務が異なる「ユーザー_A」と「ユーザーB」は、お互いを意識することなくリソースを使用する事ができるようになります。


スキーマとスキーマ内オブジェクトの操作

スキーマは以下のSQL文で作成します。この場合、スキーマを作成したユーザーが所有者となり、デフォルトでオブジェクトを操作できます。

※オブジェクトを操作するには、後述する「search_path」の設定が必要です。

CREATE SCHEMA schema_a;



特定のユーザーにオブジェクト操作権限を与えたい場合、下記のSQL文でスキーマを作成します。user_bがschema_bの所有者となり、オブジェクトを操作できる様になります。

CREATE SCHEMA schema_b AUTHORIZATION user_b;



下図はスキーマとその所有者を表しています。



スキーマ内オブジェクトの操作権限は、他のユーザーに対して付与する事も可能です。例えば、schema_a内のtable_aに対して、user_cだけにSELECT権限を付与したい場合は、次の様にします。

GRANT SELECT ON schema_a.table_a TO user_c;



又、全ユーザーに対してSELECT権限を付与する事もできます。PUBLICはすべてのデータベースユーザーを指します。

GRANT SELECT ON schema_a.table_a TO PUBLIC;



スキーマ所有者とスキーマの「使用割り当て」について

スキーマに対する所有者や権限は、スキーマの「使用割り当て」とは関係ありません。スキーマの「使用割り当て」を実施するまでは、デフォルトの「使用割り当て」に従います。

デフォルトの「使用割り当て」は次のようにして確認できます。

purchase_db=# SHOW search_path;
   search_path
-----------------
 "$user", public
(1 行)

“$user”は、現在のデータベースユーザー名と同じ名前のスキーマを指します。もしそのスキーマが存在しない場合はスキップされます。publicスキーマは、全ユーザーがアクセス可能なデフォルトスキーマです。



「SET search_path」 によるスキーマの「使用割り当て」

下記のSQL文では、デフォルトのままだと、「スキーマ_A」にある「table_a」を参照する事はできません。

SELECT * FROM table_a;


スキーマを指定して参照する事もできますが、毎回スキーマ名を指定するのは面倒です。以下は「search_path」がデフォルトの場合、スキーマを指定しない場合と指定した場合の実行結果です。

purchase_db=> SELECT * FROM table_a;　    ------> スキーマを指定しないで実行　　　　
ERROR:  relation "table_a" does not exist　　  -> エラー
行 1: SELECT * FROM table_a;
                    
purchase_db=> SELECT * FROM schema_a.table_a; --> スキーマを指定して実行
 id  |      name
-----+----------------
 100 | table_a_name_1　　　　　　　　 ----------> 参照可能　　



この為、各ユーザー毎に、スキーマ名を省略した場合、優先的に使用するスキーマを「search_path」で設定しておきます。「user_a」を例にして、「schema_a」→「public」の順で設定するとしたら、以下の様にします。

※SETでの設定はセション内での一時的な設定となります。

purchase_db=> SET search_path TO schema_a, public;
SET
purchase_db=> SHOW search_path;
   search_path
------------------
 schema_a, public　　　　　--------------> サーチパスの順番



この状態で、「SELECT * FROM table_a;」を実行すると、「schema_a」の「table_a」を参照できる様になります。

purchase_db=> select * from table_a;
 id  |      name
-----+----------------
 100 | table_a_name_1
(1 行)



「search_path」の永続的な設定

永続的にsearch_pathを設定するには、ALTER DATABASEまたはALTER ROLEコマンドを使用します。この設定は、次回以降のセッションにも引き継がれます。


データベース全体での設定

データベース全体に対してsearch_pathを設定するには、ALTER DATABASEを使用します。

ALTER DATABASE database_name SET search_path TO schema1, schema2, ...;


特定のユーザーに対する設定

特定のユーザーに対してsearch_pathを設定するには、ALTER ROLEを使用します。

ALTER ROLE user_name SET search_path TO schema1, schema2, ...;



デフォルト設定に戻す方法

デフォルトのsearch_path設定に戻したい場合は、RESETコマンドを使用します。

RESET search_path;



「search_path」の永続的な設定を削除

永続的な設定を削除したい場合は、ALTER DATABASEまたはALTER ROLEコマンドを使ってデフォルトの設定に戻すことができます。

ALTER DATABASE database_name RESET search_path;
ALTER ROLE user_name RESET search_path;

これにより、search_pathの設定はデフォルトに戻ります。

タイトルとURLをコピーしました