PostgreSQLのスキーマとは？CREATE SCHEMAコマンドを徹底解説

By /

PostgreSQLのスキーマとは？CREATE SCHEMAコマンドを徹底解説

はじめに

データベース管理システム（DBMS）において、データを効率的、安全かつ論理的に整理することは極めて重要です。特に、複数のアプリケーションやユーザーが同じデータベースを共有する場合、オブジェクト（テーブル、ビュー、関数など）の名前の衝突を避けたり、関連するオブジェクトをまとめて管理したり、きめ細やかな権限設定を行ったりする必要があります。

PostgreSQLは、このような課題を解決するための強力なメカニズムとして「スキーマ」を提供しています。スキーマは、データベース内のオブジェクトに対する「名前空間」として機能し、論理的なグループ化と権限管理の基本単位となります。

この記事では、PostgreSQLにおけるスキーマの概念について深く掘り下げ、なぜそれが重要なのか、そしてスキーマを作成するための最も基本的なコマンドである CREATE SCHEMA を徹底的に解説します。さらに、スキーマの操作方法、関連する権限管理、検索パスといった重要な概念についても詳細に説明し、PostgreSQLをより効果的に活用するための知識を提供します。

データベースの構造設計に携わる方、PostgreSQLの権限管理について学びたい方、あるいは単にPostgreSQLのスキーマについて理解を深めたい方にとって、この記事が包括的なガイドとなることを目指します。

データベースとスキーマの関係

PostgreSQLにおいて、データベースとスキーマは密接に関連していますが、異なる役割を持っています。この関係を理解することは、スキーマの概念を正確に把握する上で不可欠です。

  • データベース: データベースは、PostgreSQLサーバー上で最も高レベルのコンテナです。これは、論理的に関連するデータの集まりと、それを管理するためのオブジェクト（テーブル、インデックス、ビュー、関数、トリガーなど）全体を保持します。物理的には、通常、ファイルシステム上の特定のディレクトリに格納されます。PostgreSQLサーバーインスタンスは、複数のデータベースを持つことができますが、セッションごとに接続できるデータベースは一つだけです。

    ファイルシステムの例で言うと、データベースは独立した「ドライブ」や「ルートディレクトリ」のようなものと考えることができます。

  • スキーマ: スキーマは、特定のデータベースの内部に存在する名前空間です。スキーマは、そのデータベース内に作成されるさまざまなオブジェクト（テーブル、ビュー、シーケンス、関数など）を論理的にグループ化するために使用されます。一つのデータベースは、複数のスキーマを持つことができます。

    ファイルシステムの例で言うと、データベースがルートディレクトリだとすれば、スキーマはそのルートディレクトリ内のサブディレクトリのようなものです。各サブディレクトリ（スキーマ）は、ファイル（オブジェクト）を格納し、同じファイル名であっても異なるサブディレクトリにあれば共存できます。

例えば、mydatabase というデータベース内に、app1_schema と app2_schema という二つのスキーマを作成することができます。この場合、app1_schema 内に users というテーブルを作成し、同時に app2_schema 内にも users という名前の別のテーブルを作成することが可能です。これらは異なるスキーマに属しているため、名前の衝突は発生しません。

PostgreSQLの各データベースには、デフォルトで public という名前のスキーマが存在します。特にスキーマを指定せずにオブジェクトを作成した場合、多くの場合、この public スキーマ内に作成されます（これは後述する検索パスの設定に依存します）。

この階層構造（サーバー > データベース > スキーマ > オブジェクト）を理解することで、スキーマがどのようにデータベース内のオブジェクトを整理し、管理するための論理的な区切りを提供しているのかが明確になります。

スキーマとは何か

改めて、PostgreSQLにおけるスキーマの定義とその役割を詳しく見ていきましょう。

スキーマは、データベースオブジェクト（テーブル、ビュー、シーケンス、関数、演算子、データ型など）を組織化するための名前空間です。これにより、以下のことが可能になります。

  1. 名前の衝突の回避: 複数のユーザーやアプリケーションが同じデータベースを共有する場合でも、同じ名前のオブジェクトを異なるスキーマ内に作成できます。例えば、users というテーブルが必要なアプリケーションが複数あっても、それぞれのアプリケーション専用のスキーマ（例: app1.users, app2.users）を用意すれば、名前の衝突を心配する必要はありません。
  2. オブジェクトの論理的なグループ化: 関連するオブジェクトをまとめて管理できます。これにより、データベースの構造がより明確になり、管理が容易になります。例えば、人事管理モジュールに関連するテーブルや関数を hr スキーマに、顧客管理モジュールに関連するオブジェクトを crm スキーマにまとめることができます。
  3. 権限管理の単位: スキーマは、権限管理の便利な単位となります。特定のユーザーやロールに対して、スキーマ全体に対する操作（オブジェクト作成権限など）や、スキーマ内のオブジェクトに対する操作（データの読み書きなど）の権限を付与または剥奪できます。

オブジェクトを参照する際には、通常 スキーマ名.オブジェクト名 という形式のスキーマ修飾名を使用します。例えば、myschema というスキーマ内の mytable というテーブルを参照する場合は、myschema.mytable と記述します。

しかし、毎回スキーマ修飾名を書くのは煩雑です。そこでPostgreSQLは検索パス (search_path) という仕組みを提供しています。検索パスは、オブジェクト名がスキーマ修飾されていない場合に、PostgreSQLがどのスキーマを順番に検索するかを指定するリストです。例えば、検索パスが "$user", public と設定されている場合、mytable という名前のオブジェクトを参照すると、まず現在のユーザー名と同じ名前のスキーマを探し、次に public スキーマを探します。検索パス内で最初に見つかったオブジェクトが使用されます。検索パスの設定は、セッションごと、ユーザーごと、またはデータベース全体で設定できます。

データベースオブジェクトは、常に何らかのスキーマに属しています。システムカタログテーブル（PostgreSQL自身のメタデータ情報を含むテーブル群）もまた、pg_catalog という専用のスキーマに格納されています。一時テーブルは、セッションごとに自動的に作成される一時スキーマに格納されます。

なぜスキーマを使うのか

スキーマを使うことの具体的な利点を掘り下げてみましょう。

  1. 名前の衝突を劇的に回避できる
    これはスキーマの最も基本的な、そして非常に重要な利点です。大規模なシステムや、複数の開発チームが同じデータベースを使用する場合、テーブル名や関数名の衝突は頻繁に起こり得ます。スキーマを使用すれば、「アプリケーションAの users テーブル」と「アプリケーションBの users テーブル」のように、明確に区別された名前空間にオブジェクトを配置できます。これにより、開発チームは他のチームが使用しているオブジェクト名を気にすることなく、自由に名前を付けることができます。
    “`sql
    — app_a スキーマを作成
    CREATE SCHEMA app_a;
    — app_b スキーマを作成
    CREATE SCHEMA app_b;

    — app_a スキーマ内に users テーブルを作成
    CREATE TABLE app_a.users (
    user_id SERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL
    );

    — app_b スキーマ内に users テーブルを作成 (別の構造でも良い)
    CREATE TABLE app_b.users (
    id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100)
    );

    — 参照時にはスキーマ名を指定
    SELECT username FROM app_a.users;
    SELECT name FROM app_b.users;
    ``
    このように、同じ    users` という名前のテーブルが、異なるスキーマで問題なく共存できます。

  2. オブジェクトの論理的なグループ化と整理
    スキーマは、関連するデータベースオブジェクトをまとめて管理するための強力なツールです。

    • アプリケーション別: データベースを共有する複数のアプリケーションがある場合、アプリケーションごとに専用のスキーマを用意します。
      例: billing_app, crm_app, reporting_app
    • 機能別: 単一のアプリケーション内でも、機能ごとにスキーマを分けることで、データベース構造の可読性と保守性が向上します。
      例: auth, products, orders, inventory
    • ユーザー別: PostgreSQLでは、ユーザー名と同じ名前のスキーマを作成し、そのユーザーのプライベートなオブジェクト（一時的な作業用テーブルなど）をそこに格納するという慣習があります。検索パスに "$user" を含めることで、ユーザーはそのスキーマ内のオブジェクトを修飾なしで参照できます。
    • 外部モジュール/エクステンション: PostgreSQLの多くのエクステンションは、自身のオブジェクトを public スキーマではなく、専用のスキーマ（例: postgis, pg_stat_statements）にインストールします。これは、コアデータベースオブジェクトや他のエクステンションとの名前の衝突を防ぐためであり、また関連するオブジェクトをまとめて管理するためです。

  3. 効率的な権限管理
    スキーマは、権限管理の単位として非常に優れています。

    • スキーマ全体の権限: 特定のユーザーやロールに対して、スキーマ内に新しいオブジェクトを作成する権限（CREATE）や、スキーマ内のオブジェクトにアクセスする権限（USAGE）をまとめて付与または剥奪できます。これにより、個々のテーブルやビューに対して権限を設定するよりも、はるかに効率的に権限管理を行えます。例えば、あるユーザーには reporting_app スキーマ内のオブジェクトに対する SELECT 権限のみを与え、billing_app スキーマ内のオブジェクトには一切アクセスさせない、といった制御が容易になります。
    • デフォルト権限: スキーマ内で将来作成されるオブジェクトに対して、デフォルトの権限を設定することも可能です。これにより、新しいテーブルを作成するたびに手動で権限を設定する手間を省くことができます。

  4. サードパーティ製エクステンションやモジュールの管理
    前述のように、多くのPostgreSQLエクステンションは専用スキーマにインストールされます。これはベストプラクティスとされており、データベースのクリーンさを保ち、管理を容易にします。ユーザー独自のカスタム関数やデータ型なども、専用のスキーマにまとめることで、管理がしやすくなります。

  5. データの分離 (高度な利用)
    あまり一般的ではありませんが、開発環境、テスト環境、本番環境のデータを同じPostgreSQLサーバー上の異なるデータベースに分けるのではなく、同じデータベース内の異なるスキーマに分離して管理するというアプローチも技術的には可能です。ただし、これにはいくつかの考慮事項（トランザクション分離、バックアップ/リストアの粒度など）が必要であり、通常は環境ごとにデータベースやサーバー自体を分ける方が推奨されます。しかし、マルチテナントシステムにおいて、テナントごとにスキーマを分けるという設計（テナント数が多い場合は非推奨）や、過去のデータバージョンを別のスキーマにアーカイブするといった利用シナリオは考えられます。

これらの理由から、PostgreSQLデータベースを効果的かつ安全に運用するためには、スキーマを積極的に活用することが強く推奨されます。特に、単一のデータベースで複数の用途を扱う場合や、多数のオブジェクトを管理する必要がある場合には、スキーマは不可欠な要素となります。

CREATE SCHEMA コマンド詳解

さて、スキーマの重要性が理解できたところで、実際にスキーマを作成するための CREATE SCHEMA コマンドの使い方を詳細に見ていきましょう。

CREATE SCHEMA コマンドは、新しいスキーマを現在のデータベース内に作成します。

基本的な構文は以下の通りです。

sql
CREATE SCHEMA schema_name;

ここで schema_name は、作成したいスキーマの名前です。スキーマ名は識別子の命名規則に従う必要があります（アルファベットで始まり、英数字とアンダースコア、ドル記号を使用できます。引用符で囲めばより自由な文字を使用できますが、通常は避けるのが良いでしょう）。スキーマ名はデータベース内で一意である必要があります。

例:

sql
-- 'sales' という名前の新しいスキーマを作成
CREATE SCHEMA sales;

このコマンドを実行すると、現在のデータベース内に sales という名前の新しいスキーマが作成されます。このスキーマの所有者は、コマンドを実行したユーザー（またはロール）になります。

CREATE SCHEMA コマンドには、スキーマの所有者を指定したり、スキーマの作成と同時にオブジェクトを作成したり、権限を設定したりするためのオプション句があります。これらのオプションを組み合わせることで、スキーマの初期設定を効率的に行うことができます。

以下に、CREATE SCHEMA コマンドの完全な構文を示します（主要な句のみを抜粋）。

sql
CREATE SCHEMA schema_name [ AUTHORIZATION user_name ] [ IF NOT EXISTS ]
[ schema_element [ ... ] ];

または

sql
CREATE SCHEMA AUTHORIZATION user_name [ IF NOT EXISTS ]
[ schema_element [ ... ] ];

  • schema_name: 作成するスキーマの名前。
  • AUTHORIZATION user_name: 作成されるスキーマの所有者を指定します。この句を省略した場合、コマンドを実行したユーザーが所有者になります。指定された user_name は既存のロール（ユーザー）である必要があります。
  • IF NOT EXISTS: 同じ名前のスキーマが既に存在する場合でもエラーにならず、何も実行しないようにします。スクリプトなどで冪等性を確保したい場合に便利です。
  • schema_element: スキーマの作成と同時に、そのスキーマ内に作成されるオブジェクトの定義です。これには CREATE TABLE, CREATE VIEW, CREATE SEQUENCE, CREATE INDEX, CREATE TYPE, CREATE DOMAIN, CREATE FUNCTION, CREATE PROCEDURE などが含まれます。また、スキーマ内のオブジェクトに対する GRANT コマンドも schema_element として含めることができます。複数の schema_element を指定できます。

例: 所有者を指定してスキーマを作成

特定のユーザー（例: app_user）に所有させたいスキーマを作成する場合に使用します。

sql
-- 'app_user' ロールに所有される 'app_data' スキーマを作成
CREATE SCHEMA app_data AUTHORIZATION app_user;

このコマンドを実行すると、app_data という名前のスキーマが作成され、その所有者は app_user になります。スキーマの所有者は、そのスキーマ内でオブジェクトを作成するデフォルトの権限を持ちます。

例: スキーマの作成と同時にオブジェクトを作成

CREATE SCHEMA コマンドの強力な機能の一つに、スキーマを作成するトランザクション内で、そのスキーマ内に含めるオブジェクトも同時に作成できる点があります。これは、関連するスキーマとオブジェクト群を単一の操作で定義できるため、スクリプトの実行などが容易になります。

sql
-- 'myapp' スキーマを作成し、所有者を 'myapp_owner' に設定し、
-- そのスキーマ内に 'users' テーブルと 'products' テーブルを同時に作成
CREATE SCHEMA myapp AUTHORIZATION myapp_owner
CREATE TABLE users (
user_id SERIAL PRIMARY KEY,
username VARCHAR(50) NOT NULL
)
CREATE TABLE products (
product_id SERIAL PRIMARY KEY,
product_name VARCHAR(100) NOT NULL,
price DECIMAL(10, 2)
);

この例では、myapp スキーマが作成され、myapp_owner がその所有者となります。さらに、myapp.users テーブルと myapp.products テーブルも同時に作成されます。これらのテーブル定義は、CREATE SCHEMA コマンドの一部として実行されます。

schema_element として複数の CREATE コマンドを含めることができます。これらの CREATE コマンドは、スキーマ名なしでオブジェクト名だけを記述します。これは、それらのオブジェクトが現在作成中のスキーマ内に作成されることを意味します。

例: スキーマの作成と同時に権限を設定

CREATE SCHEMA コマンドは、スキーマ内のオブジェクトに対する GRANT コマンドを schema_element として含めることもできます。これにより、スキーマの作成と同時に、そのスキーマ内のオブジェクトへのアクセス権を設定できます。

sql
-- 'reporting' スキーマを作成し、所有者を 'reporter_admin' に設定
-- そのスキーマ内に 'sales_data' テーブルを作成し
-- 'sales_analysts' ロールに 'sales_data' テーブルへの SELECT 権限を付与
CREATE SCHEMA reporting AUTHORIZATION reporter_admin
CREATE TABLE sales_data (
sale_id SERIAL PRIMARY KEY,
sale_date DATE,
amount DECIMAL(10, 2)
)
GRANT SELECT ON TABLE sales_data TO sales_analysts;

この例では、reporting スキーマと reporting.sales_data テーブルが作成され、同時に sales_analysts ロールに対して sales_data テーブルへの SELECT 権限が付与されます。

CREATE SCHEMA とトランザクション性

CREATE SCHEMA コマンド、そしてそれに含まれる schema_element 群は、単一のトランザクションとして実行されます。これは非常に重要な特性です。もし CREATE SCHEMA コマンド内のいずれかの schema_element（例えば、テーブル作成や権限設定）が失敗した場合、コマンド全体がロールバックされ、スキーマ自体も作成されず、データベースはコマンド実行前の状態に戻ります。これにより、部分的にだけ作成された不完全な状態を防ぐことができます。

例: IF NOT EXISTS 句の使用

同じスキーマ名を複数回 CREATE SCHEMA コマンドで作成しようとすると、通常はエラーになります。しかし、IF NOT EXISTS 句を使用すると、既に存在する場合はエラーにならず、何も実行されません。

“`sql
— スキーマが既に存在しない場合のみ作成
CREATE SCHEMA IF NOT EXISTS config;

— もう一度実行してもエラーにならない
CREATE SCHEMA IF NOT EXISTS config; — 何も実行されない
“`

これは、デプロイスクリプトなどで、スキーマが既に存在するかどうかにかかわらずコマンドを実行したい場合に非常に便利です。

スキーマ名を指定しない CREATE SCHEMA

CREATE SCHEMA AUTHORIZATION user_name のように、スキーマ名を省略して CREATE SCHEMA コマンドを実行することも可能です。この場合、作成されるスキーマの名前は AUTHORIZATION 句で指定されたユーザー名と同じになります。

sql
-- ユーザー 'johndoe' と同じ名前のスキーマを作成し、所有者も 'johndoe' に設定
CREATE SCHEMA AUTHORIZATION johndoe;
-- これは CREATE SCHEMA johndoe AUTHORIZATION johndoe; と同じ

この形式は、ユーザーごとにプライベートなスキーマを作成したい場合に特に便利です。検索パスの $user 機能と組み合わせることで、ユーザーは自分自身のスキーマ内のオブジェクトに簡単にアクセスできるようになります。

CREATE SCHEMA コマンドは、このように新しいスキーマを作成し、その初期状態（所有者、含まれるオブジェクト、初期権限）を設定するための強力で柔軟なツールです。適切に使用することで、データベースの初期設定を効率的かつ安全に行うことができます。

スキーマの操作

スキーマを作成した後も、必要に応じてスキーマに対する様々な操作を行うことができます。主な操作には、削除、所有者の変更、名前の変更、そして最も重要な権限管理があります。

1. スキーマの削除 (DROP SCHEMA)

スキーマが不要になった場合は、DROP SCHEMA コマンドを使用して削除できます。

基本的な構文は以下の通りです。

sql
DROP SCHEMA schema_name;

注意: このコマンドは、スキーマ内にオブジェクト（テーブル、ビュー、関数など）が存在しない場合にのみ成功します。スキーマ内にオブジェクトが存在する場合、デフォルトではエラーが発生します。

スキーマ内にオブジェクトが存在する場合でも削除したい場合は、CASCADE オプションを使用します。

sql
DROP SCHEMA schema_name CASCADE;

CASCADE オプションを指定すると、削除しようとしているスキーマとその中に含まれる全てのオブジェクト（テーブル、ビュー、関数など）が再帰的に削除されます。さらに、それらのオブジェクトに依存する他のオブジェクト（例: 別のスキーマのビューが、削除されるスキーマのテーブルを参照している場合）も一緒に削除されます。CASCADE オプションの使用は非常に強力であるため、その影響範囲を十分に理解した上で慎重に行う必要があります。

デフォルトの挙動は RESTRICT と同じです。RESTRICT を明示的に指定することもできますが、オブジェクトが存在する場合はエラーとなるため、通常は省略されます。

sql
-- オブジェクトが存在する場合はエラーになる (デフォルトと同じ)
DROP SCHEMA schema_name RESTRICT;

例:

sql
-- sales スキーマとその中の全てのオブジェクトを削除
DROP SCHEMA sales CASCADE;

2. スキーマの所有者の変更 (ALTER SCHEMA ... OWNER TO)

スキーマの所有者を変更するには、ALTER SCHEMA コマンドを使用します。

構文は以下の通りです。

sql
ALTER SCHEMA schema_name OWNER TO new_owner;

new_owner は、新しい所有者となる既存のロール（ユーザー）名です。スキーマの所有者を変更するには、コマンドを実行するユーザー自身がそのスキーマの所有者であるか、データベースのスーパーユーザーである必要があります。また、新しい所有者となるユーザーは、そのデータベースに対する CREATE 権限を持っている必要があります。

例:

sql
-- sales スキーマの所有者を 'sales_admin' に変更
ALTER SCHEMA sales OWNER TO sales_admin;

3. スキーマ名の変更 (ALTER SCHEMA ... RENAME TO)

スキーマの名前を変更するには、ALTER SCHEMA コマンドを使用します。

構文は以下の通りです。

sql
ALTER SCHEMA old_name RENAME TO new_name;

コマンドを実行するユーザーは、そのスキーマの所有者である必要があります。

例:

sql
-- sales スキーマの名前を archived_sales に変更
ALTER SCHEMA sales RENAME TO archived_sales;

4. スキーマへの権限付与・剥奪 (GRANT / REVOKE)

スキーマに対する権限は、そのスキーマへのアクセスや、そのスキーマ内でのオブジェクト作成を制御します。スキーマに対する主要な権限は CREATE と USAGE です。

  • CREATE 権限: スキーマ内に新しいオブジェクト（テーブル、ビュー、関数など）を作成することを許可します。
  • USAGE 権限: スキーマ内のオブジェクト（テーブル、ビューなど）にアクセスすることを許可します。USAGE 権限がないと、スキーマ修飾名を使用してもスキーマ内のオブジェクトを参照できません。データを操作するには、個々のオブジェクトに対する適切な権限（SELECT, INSERT, UPDATE, DELETE など）も必要ですが、それらの権限を持っていても、スキーマに対する USAGE 権限がないとオブジェクトに到達できません。

スキーマに対する権限を付与するには GRANT コマンド、剥奪するには REVOKE コマンドを使用します。

構文:

“`sql
GRANT { { CREATE | USAGE } [, …] | ALL [ PRIVILEGES ] } ON SCHEMA schema_name [, …] TO role_name [, …] [ WITH GRANT OPTION ];

REVOKE { { CREATE | USAGE } [, …] | ALL [ PRIVILEGES ] } ON SCHEMA schema_name [, …] FROM role_name [, …] [ CASCADE | RESTRICT ];
“`

  • CREATE | USAGE: 付与または剥奪する権限を指定します。複数指定する場合はカンマで区切ります。ALL PRIVILEGES は、そのオブジェクトタイプに対して付与可能な全ての権限を意味します（スキーマの場合は CREATE と USAGE）。
  • ON SCHEMA schema_name: 対象となるスキーマ名を指定します。複数指定する場合はカンマで区切ります。
  • TO role_name: 権限を付与するロール（ユーザー）名を指定します。複数指定する場合はカンマで区切ります。PUBLIC を指定すると、全てのロール（ログイン権限を持つ全てのユーザー）に権限が付与されます。
  • FROM role_name: 権限を剥奪するロール（ユーザー）名を指定します。複数指定する場合はカンマで区切ります。PUBLIC を指定すると、全てのロールから権限が剥奪されます。
  • WITH GRANT OPTION: 付与された権限を他のロールにさらに付与することを許可します。
  • CASCADE | RESTRICT: REVOKE 時の動作を指定します。CASCADE は、この権限に依存する他の権限も再帰的に剥奪します。RESTRICT は、依存する権限がある場合はエラーになります（デフォルト）。

例: スキーマへの権限付与

“`sql
— ‘app_user’ ロールに ‘app_data’ スキーマへの USAGE 権限を付与
GRANT USAGE ON SCHEMA app_data TO app_user;

— ‘admin_role’ ロールに ‘app_data’ スキーマへの CREATE 権限と USAGE 権限を付与
GRANT CREATE, USAGE ON SCHEMA app_data TO admin_role;

— PUBLIC (全てのユーザー) から public スキーマへの CREATE 権限を剥奪 (セキュリティ強化のためによく行われる)
REVOKE CREATE ON SCHEMA public FROM PUBLIC;
“`

デフォルトでは、新しいスキーマを作成すると、その所有者には CREATE 権限と USAGE 権限が付与されます。また、public スキーマについては、デフォルトで PUBLIC ロール（全てのユーザー）に対して CREATE 権限と USAGE 権限が付与されています。この public スキーマに対する CREATE 権限は、誰でも public スキーマ内にオブジェクトを作成できてしまうため、特に本番環境などでは REVOKE CREATE ON SCHEMA public FROM PUBLIC; を実行して無効化することがセキュリティ上のベストプラクティスとされることが多いです。

将来作成されるオブジェクトに対するデフォルト権限 (ALTER DEFAULT PRIVILEGES)

スキーマに対する権限設定だけでなく、スキーマ内で将来作成されるオブジェクト（テーブル、シーケンス、関数など）に対するデフォルトの権限を設定することも重要です。これは ALTER DEFAULT PRIVILEGES コマンドで行います。

例えば、「app_data スキーマ内で app_user によって作成される全てのテーブルに対して、report_user ロールに SELECT 権限を自動的に付与したい」といった場合にこれを使用します。

sql
-- 'app_user' ロールが 'app_data' スキーマ内で作成する全てのテーブルに対して
-- 'report_user' ロールに SELECT 権限をデフォルトで付与
ALTER DEFAULT PRIVILEGES FOR ROLE app_user IN SCHEMA app_data
GRANT SELECT ON TABLES TO report_user;

この設定は、コマンドを実行した時点ではなく、将来 app_user が app_data スキーマ内で新しいテーブルを作成した際に適用されます。既存のオブジェクトには影響しません。

スキーマの操作コマンドを理解し、特に権限管理を適切に行うことで、データベースのセキュリティと管理性を大幅に向上させることができます。

検索パス (search_path) の詳細

スキーマ修飾名（例: schema_name.object_name）はオブジェクトを一意に特定できますが、毎回フルネームを書くのは手間がかかります。そこで、PostgreSQLは検索パス (search_path) という仕組みを提供しています。

検索パスは、スキーマ修飾されていないオブジェクト名（例: users）が参照された場合に、PostgreSQLがどのスキーマを順番に探すべきかを定義したリストです。

検索パスは、スキーマ名のカンマ区切りリストとして指定されます。PostgreSQLはリストの先頭から順番にスキーマを検索し、最初に見つかった名前一致オブジェクトを使用します。

検索パスの確認

現在のセッションの検索パスを確認するには、以下のコマンドを使用します。

sql
SHOW search_path;

デフォルトの検索パスは、通常 $user, public です。

  • $user: 現在のログインユーザー名と同じ名前のスキーマを意味します。ユーザーが自分の名前と同じスキーマを持っている場合、まずそこが検索されます。
  • public: データベース作成時にデフォルトで存在するスキーマです。

検索パスの設定

検索パスは、以下のレベルで設定できます。

  1. セッションレベル: 現在のクライアントセッションに対してのみ有効な設定です。セッションが終了すると失われます。
    sql
    SET search_path TO schema1, schema2, ..., public;
    または、より簡潔に SET search_path = ...; も使用できます。

    例:

    “`sql
    — 現在のセッションの検索パスを myapp_data, public に設定
    SET search_path TO myapp_data, public;

    — これ以降、myapp_data スキーマ内のオブジェクトは修飾なしで参照可能になる
    SELECT * FROM users; — myapp_data.users が参照される (myapp_data が public より先にある場合)
    “`

  2. ユーザー（ロール）レベル: 特定のユーザー（ロール）が接続するたびに適用される設定です。
    sql
    ALTER ROLE role_name SET search_path TO schema1, schema2, ..., public;

    例:

    sql
    -- 'app_user' ロールが接続した際のデフォルト検索パスを myapp_data, public に設定
    ALTER ROLE app_user SET search_path TO myapp_data, public;

    これにより、app_user はログイン後すぐに myapp_data スキーマ内のオブジェクトを修飾なしで参照できるようになります。

  3. データベースレベル: 特定のデータベースに接続する全てのユーザーに適用されるデフォルト設定です。ユーザーレベルやセッションレベルの設定がない場合に適用されます。
    sql
    ALTER DATABASE dbname SET search_path TO schema1, schema2, ..., public;

    例:

    sql
    -- 'mydatabase' データベース全体のデフォルト検索パスを '$user', common, public に設定
    ALTER DATABASE mydatabase SET search_path TO '$user', common, public;

    この設定により、mydatabase に接続したユーザーは、まず自分のスキーマ、次に common スキーマ、最後に public スキーマの順でオブジェクトが検索されるようになります。

これらの設定レベルは、セッションレベル > ユーザーレベル > データベースレベル の順で優先されます。

特別なスキーマと検索パス

  • pg_catalog: システムカタログテーブルが格納されているスキーマです。このスキーマは常に検索パスに含まれており、通常はリストの最初に暗黙的に追加されます。これにより、pg_class や pg_proc といったシステムテーブルを常に修飾なしで参照できます。SHOW search_path; の出力には明示的に表示されないことが多いですが、内部的には常に検索されます。
  • 一時テーブル用スキーマ: 一時テーブルを作成すると、現在のセッション専用の一時スキーマに格納されます。この一時スキーマは、検索パスの最初に暗黙的に追加されます。これにより、一時テーブルは修飾なしで参照できます。

検索パスとパフォーマンス・セキュリティ

  • パフォーマンス: 検索パスが長い場合や、先頭に近いスキーマに存在しないオブジェクトを参照しようとする場合、PostgreSQLはリストの最後までオブジェクトを探しに行くため、わずかにパフォーマンスに影響を与える可能性があります。しかし、通常これは無視できるレベルの影響です。重要なのは、意図しないオブジェクトにアクセスしないように、検索パスを適切に管理することです。
  • セキュリティ: 検索パスを不適切に設定すると、意図しないオブジェクト（特に悪意を持って作成された関数など）が実行されてしまう「スキーマ検索パスのハイジャック」といった脆弱性につながる可能性があります。例えば、検索パスの先頭に信頼できないユーザーが書き込み権限を持つスキーマがあり、そこに標準関数（例: substr）と同じ名前の悪意のある関数が作成された場合、修飾なしで substr を呼び出すと、そちらの関数が実行されてしまう可能性があります。これを防ぐためには、信頼できるスキーマ（pg_catalog や信頼できるアプリケーションスキーマ）を検索パスの先頭に置き、信頼できないユーザーに public スキーマなどへの CREATE 権限を与えない（あるいは、pg_catalog を除く全てのスキーマを修飾名で参照させることを強制する）といった対策が必要です。public スキーマから CREATE 権限を剥奪することは、セキュリティ上のベストプラクティスとして広く推奨されています。

検索パスは、PostgreSQLの使いやすさを向上させる便利な機能ですが、その動作原理とセキュリティ上の考慮事項を理解しておくことが重要です。特に、複数のユーザーやアプリケーションがデータベースを共有する環境では、検索パスの設定がオブジェクトの参照とセキュリティに直接影響するため、慎重に管理する必要があります。

スキーマの活用例とベストプラクティス

スキーマの概念と操作方法を理解した上で、どのような状況でスキーマを活用するのが有効なのか、またスキーマを管理する上でのベストプラクティスを見ていきましょう。

1. アプリケーションの分離

最も一般的な活用例です。複数の異なるアプリケーションが同じPostgreSQLサーバーやデータベースを使用する場合、アプリケーションごとに専用のスキーマを作成します。

  • application_a_schema
  • application_b_schema
  • application_c_schema

これにより、各アプリケーションは自身のスキーマ内で自由にオブジェクトを作成・管理でき、他のアプリケーションのオブジェクトと名前が衝突する心配がありません。また、アプリケーション専用ユーザー（ロール）を作成し、そのユーザーに自分のスキーマに対する CREATE 権限と USAGE 権限、そして他の必要なスキーマ（例: 共通データスキーマ）への USAGE 権限のみを与えることで、セキュリティを高めることができます。

2. 機能モジュールごとの分離

単一のアプリケーションが複数の大規模な機能モジュールで構成されている場合、モジュールごとにスキーマを分けることで、データベース構造の保守性を向上させることができます。

  • auth_schema (ユーザー認証、認可関連)
  • billing_schema (請求関連)
  • inventory_schema (在庫管理関連)
  • reporting_schema (レポート関連)

これにより、各モジュールは自身のスキーマ内で関連するオブジェクトを管理し、他のモジュールの内部実装に依存しにくくなります。コードの変更やデプロイメントが特定のモジュールに限定される場合、影響範囲をデータベーススキーマレベルで把握しやすくなります。

3. バージョン管理

アプリケーションの異なるバージョンを同じデータベース上で一時的に共存させる必要がある場合、バージョンごとにスキーマを分けるアプローチが有効な場合があります。

  • app_v1_schema
  • app_v2_schema

新しいバージョンのスキーマに新しいテーブルや変更されたテーブルを作成し、アプリケーションの切り替え時に検索パスを変更する、といった方法が考えられます。ただし、データ移行の複雑さなどを考慮すると、この方法は限定的なシナリオ（例えば、ローリングアップデートの際の一時的な共存）に適しているかもしれません。

4. 共通オブジェクトの管理

複数のアプリケーションやスキーマで共有される関数、データ型、参照テーブルなどは、専用の「共通」スキーマにまとめるのが良いでしょう。

  • common_schema

そして、共通オブジェクトを利用する各スキーマやユーザーの検索パスに common_schema を含めることで、修飾なしでそれらのオブジェクトを参照できるようにします。

“`sql
— common スキーマに共通関数を作成
CREATE SCHEMA common;
CREATE FUNCTION common.calculate_tax(…) AS …;

— アプリケーションスキーマやユーザーの検索パスに common を追加
SET search_path TO app_a, common, public;
SET search_path TO app_b, common, public;
“`

5. テナント分離 (スケーラビリティに注意)

マルチテナントアプリケーションにおいて、テナントごとにスキーマを完全に分離するという設計も考えられます。

  • tenant_a_schema
  • tenant_b_schema
  • …

これにより、テナント間のデータ分離を強力に保証できます。しかし、テナント数が増えるにつれてスキーマ数も爆発的に増加し、データベースの管理（バックアップ/リカバリ、スキーマ変更の適用、統計情報の収集など）が非常に困難になります。PostgreSQLのバージョンによっては、大量のスキーマがパフォーマンスに影響を与える可能性もあります。そのため、数万、数百万といった多数のテナントを扱うシステムでは、テナントごとにスキーマを分ける方法は一般的に推奨されません。代わりに、共有スキーマ内でテナントIDカラムを設けてデータを分離する、あるいは行レベルセキュリティ（RLS）を活用するといった方法がよりスケーラブルです。テナント数が限定的で、厳密なデータ分離が最優先される場合は、スキーマ分離が有効な選択肢となることもあります。

6. セキュリティの強化

権限管理のセクションでも触れましたが、スキーマはセキュリティ強化に不可欠です。
* public スキーマのロックダウン: デフォルトで public スキーマに対する PUBLIC の CREATE 権限は剥奪することが強く推奨されます。
sql
REVOKE CREATE ON SCHEMA public FROM PUBLIC;
* 必要最小限の権限付与: 各ユーザーやロールに対して、必要なスキーマに対する USAGE 権限と、操作対象のオブジェクトに対する適切な権限のみを付与します。不必要なスキーマへの CREATE 権限は絶対に与えないようにします。
* 検索パスの管理: 信頼できるスキーマのみを検索パスに含めます。特に $user スキーマを検索パスに含める場合は、そのユーザーが自分自身のスキーマ内で信頼できるオブジェクトしか作成しないことを確認する必要があります。

ベストプラクティス

  • 命名規則の確立: スキーマ名に一貫性のある命名規則（例: アプリケーション名、機能名、モジュール名など）を適用することで、データベース構造の理解と管理が容易になります。
  • スキーマ所有者の設定: CREATE SCHEMA AUTHORIZATION 句を使用して、適切なロールをスキーマの所有者に設定します。スキーマの所有者は、そのスキーマに対するフルコントロール権限（ただしスーパーユーザーを除く）を持ちます。
  • CREATE SCHEMA のトランザクション活用: スキーマとそれに属する初期オブジェクトの作成は、CREATE SCHEMA ... CREATE TABLE ... CREATE VIEW ... の形式で単一のトランザクションとして実行することで、データベース設定の原子性を保証します。
  • 検索パスの明示的な設定: 特にアプリケーション接続においては、デフォルトの検索パスに依存するのではなく、接続文字列やセッションの最初に SET search_path コマンドを使用して、使用するスキーマを明確に設定することが推奨されます。
  • ドキュメント化: どのスキーマが何のために存在し、どのようなオブジェクトを含んでいるのか、検索パスはどのように設定されているのかをしっかりとドキュメント化します。

これらの活用例とベストプラクティスを参考に、ご自身のPostgreSQLデータベース設計においてスキーマを最大限に活用してください。適切に設計されたスキーマ構造は、データベースの管理、保守、セキュリティ、そしてスケーラビリティに大きく貢献します。

スキーマと他のPostgreSQL機能との関連

スキーマはPostgreSQLの他の多くの機能と連携して動作します。いくつかの主要な関連機能との関係を見ていきましょう。

  • データベース: 前述の通り、スキーマはデータベースの内部構造です。すべてのスキーマは特定のデータベースに属し、データベースを跨いでオブジェクトを参照することは通常できません（postgres_fdw のような外部データラッパーを使用すれば可能ですが、これはスキーマの基本的な利用方法とは異なります）。
  • テーブルスペース: スキーマはオブジェクトの論理的なグループ化と名前空間を提供しますが、テーブルスペースはオブジェクト（特にテーブルやインデックス）がファイルシステム上の物理的などこに格納されるかを定義します。特定のスキーマ内のオブジェクトを特定のテーブルスペースに格納するように設定することは可能ですが、スキーマ自体が格納場所を決めるわけではありません。格納場所はテーブルやインデックスの CREATE TABLESPACE 句で指定されます。スキーマとテーブルスペースは独立した概念ですが、組み合わせて使用することができます（例: 特定のスキーマ内のアーカイブ用テーブルを低速で大容量のディスクを割り当てたテーブルスペースに格納する）。
  • ロール（ユーザー）: PostgreSQLの権限管理はロール（ユーザーもロールの一種）を主体として行われます。スキーマに対する CREATE や USAGE 権限、そしてスキーマ内のオブジェクトに対する権限は全てロールに対して付与されます。スキーマの所有者もロールです。スキーマとロールは密接に関連しており、効果的な権限管理には両方の概念の理解が不可欠です。
  • エクステンション: 多くのPostgreSQLエクステンションは、自身の関数、データ型、テーブルなどを専用のスキーマ（例: postgis, pg_stat_statements, uuid-ossp など）にインストールします。これは、コアシステムカタログや他のエクステンションとの名前の衝突を避けるためであり、また関連するオブジェクトをまとめて管理しやすくするためです。CREATE EXTENSION コマンドは、通常、エクステンションのオブジェクトを特定のスキーマにインストールします（SCHEMA 句で指定可能）。
  • 外部データラッパー (FDW): 外部データラッパーを使用して外部のデータベースやデータソースにアクセスする場合、外部テーブルは通常ローカルのデータベース内のスキーマに作成されます。これにより、外部データソースのオブジェクトをローカルデータベースの他のオブジェクトと同様にスキーマ内で管理できます。

これらの機能との連携を理解することで、PostgreSQLの全体像の中でスキーマがどのような位置づけにあるのか、そして他の機能とどのように組み合わせて使うことができるのかがより明確になります。

注意点とトラブルシューティング

スキーマは非常に便利な機能ですが、利用する上で注意すべき点や、遭遇しうるトラブルシューティングのシナリオがあります。

  • 検索パスの設定ミスによるオブジェクト未検出エラー: 最も一般的なトラブルの一つです。オブジェクトを修飾なしで参照した際に、「ERROR: relation "table_name" does not exist」のようなエラーが発生する場合、原因の多くは現在のセッションの検索パスに、そのオブジェクトが含まれるスキーマが含まれていないか、あるいは含まれているもののリストの後の方にあり、その前に同じ名前の別のオブジェクトが見つかってしまった、というケースです。
    • 対応: SHOW search_path; で現在の検索パスを確認し、参照したいオブジェクトが含まれるスキーマが適切に含まれているか、順序は正しいかを確認します。必要に応じて SET search_path = ...; で修正します。恒久的に設定したい場合は、ユーザーレベル (ALTER ROLE) またはデータベースレベル (ALTER DATABASE) で設定します。確実なのは、常にスキーマ修飾名（schema_name.object_name）を使用することです。
  • 権限不足によるオブジェクト作成/アクセスエラー: スキーマ内にオブジェクトを作成しようとしたり、スキーマ内のオブジェクトにアクセスしようとしたりした際に権限エラーが発生する場合、原因は対象のスキーマに対する CREATE 権限（作成時）または USAGE 権限（アクセス時）、あるいは対象のオブジェクトに対する適切な権限（SELECT, INSERT など）がないことです。
    • 対応: データベースのスーパーユーザーまたはスキーマの所有者として接続し、GRANT コマンドを使用して対象のユーザー（ロール）に必要な権限を付与します。どの権限が不足しているかは、エラーメッセージや \dp schema_name.* (psql コマンド) などで確認できます。
  • DROP SCHEMA CASCADE の誤用: CASCADE オプション付きで DROP SCHEMA を実行すると、スキーマ内の全てのオブジェクトだけでなく、それらに依存する他のオブジェクトまで無警告で削除される可能性があります。これにより、意図しないデータの損失やアプリケーションの機能停止を招くことがあります。
    • 対応: DROP SCHEMA を実行する前に、対象のスキーマ内にどのようなオブジェクトが存在するか（\dn+ schema_name やシステムカタログクエリで確認）、そしてそれらのオブジェクトに依存するものが他にないか（pg_depend システムカタログなどを調査）を十分に確認します。本番環境では特に慎重に行い、事前に十分なテストを行うべきです。デフォルトの RESTRICT は安全ですが、依存関係がある場合にエラーで止まってしまうため、手動で依存関係を解消してから再度削除を試みる必要があります。
  • 大量のスキーマの管理: 前述のマルチテナントの例のように、スキーマの数が非常に多くなると、管理作業（スキーマの作成、変更、削除、権限設定、バックアップ、リストアなど）が煩雑になり、パフォーマンスに影響を与える可能性もあります。
    • 対応: スキーマ設計時には、将来的なスケーラビリティを考慮する必要があります。テナント分離のようなシナリオでは、スキーマ分離が適切かどうかを慎重に検討し、他のアプローチ（RLSなど）と比較検討します。大量のスキーマを管理するための自動化スクリプトやツールを開発することも有効です。

これらの注意点やトラブルシューティングの知識は、PostgreSQLを運用する上でスキーマを効果的かつ安全に利用するために役立ちます。

まとめ

PostgreSQLのスキーマは、データベース内のオブジェクトを論理的にグループ化し、名前空間を提供するための強力な機能です。これにより、名前の衝突回避、オブジェクトの整理、効率的な権限管理、そしてセキュリティの向上が実現されます。

この記事では、データベースとスキーマの関係から始まり、スキーマとは何か、なぜそれが重要なのかを詳細に解説しました。そして、新しいスキーマを作成するための CREATE SCHEMA コマンドについて、その基本的な構文から、所有者の指定 (AUTHORIZATION)、スキーマ内オブジェクトの同時作成 (schema_element)、既存確認 (IF NOT EXISTS) といった様々なオプション句までを徹底的に掘り下げました。CREATE SCHEMA コマンドのトランザクション性についても触れ、その利便性を説明しました。

さらに、スキーマの削除 (DROP SCHEMA)、名前変更 (ALTER SCHEMA RENAME)、所有者変更 (ALTER SCHEMA OWNER) といった基本的な操作方法を説明し、特にスキーマに対する権限 (CREATE, USAGE) の重要性と、GRANT/REVOKE コマンドによる権限管理について詳しく解説しました。将来作成されるオブジェクトに対するデフォルト権限の設定 (ALTER DEFAULT PRIVILEGES) も、効率的な権限管理に不可欠な機能として紹介しました。

オブジェクト名を修飾なしで参照する際にPostgreSQLがどのようにスキーマを検索するかを定義する「検索パス (search_path)」についても、その設定方法（セッション、ユーザー、データベースレベル）や、$user, public, pg_catalog といった特別なスキーマ、そして検索パスがパフォーマンスやセキュリティに与える影響を含めて詳細に解説しました。

最後に、アプリケーション分離、機能モジュール分離、バージョン管理、共通オブジェクト管理、テナント分離といった具体的なスキーマの活用例と、public スキーマのロックダウン、命名規則、検索パスの明示的な設定といったベストプラクティスを紹介しました。スキーマとテーブルスペース、ロール、エクステンションなどの他のPostgreSQL機能との関連性や、利用上の注意点、遭遇しうるトラブルシューティングのシナリオについても言及しました。

PostgreSQLを効果的に活用するためには、スキーマの概念を正しく理解し、CREATE SCHEMA コマンドを始めとする関連コマンドを使いこなすことが非常に重要です。この記事が、PostgreSQLにおけるスキーマに関する包括的な知識を提供し、皆様のデータベース設計、開発、運用の一助となれば幸いです。

スキーマを適切に活用することで、より保守性が高く、安全で、スケーラブルなPostgreSQLデータベースを構築することができるでしょう。

コメントする

メールアドレスが公開されることはありません。 ※ が付いている欄は必須項目です

上部へスクロール