【PostgreSQL】スキーマを活用しよう！
PostgreSQLでは、スキーマを活用することでデータベースの管理や運用を柔軟に行うことができます。本ブログでは、スキーマの概念、基本的な操作方法、実用的な活用例について紹介します。
スキーマとは？
スキーマとは、データベース内でテーブルやビュー、関数、シーケンスなどのオブジェクトをグループ化するための名前空間です。スキーマを適切に活用することで、以下のようなメリットがあります。
- データの整理
テーブルをカテゴリ別に整理できます - アクセス制御
スキーマごとに権限の設定が可能となります - 名前衝突の回避
同じ名前のテーブルを異なるスキーマに作成可能となります - マルチテナント環境の実現
アプリケーションごとにスキーマを分けることで管理が容易になります
※マルチテナント環境については後述します。
下図はcustomer_db内に地域毎のスキーマを作成した結果です。
スキーマの基本操作
スキーマの作成
スキーマは CREATE SCHEMA コマンドで作成します。
CREATE SCHEMA sales;特定のユーザーが所有するスキーマを作成する場合は、以下のように指定します。
CREATE SCHEMA marketing AUTHORIZATION user_name;スキーマ内のオブジェクト作成
スキーマを作成した後、そのスキーマ内にテーブルを作成するには、スキーマ名を明示的に指定します。
CREATE TABLE sales.orders (
order_id SERIAL PRIMARY KEY,
customer_name TEXT,
total_amount NUMERIC(10,2)
);スキーマの一覧表示
作成したスキーマの一覧を確認するには、pg_namespace システムカタログを参照します。
SELECT nspname FROM pg_namespace;または、psqlの \dn コマンドでも一覧を表示できます。
\dn
スキーマの削除
スキーマを削除するには、DROP SCHEMA を使用します。
DROP SCHEMA sales;スキーマ内にオブジェクトがある場合は、CASCADE オプションを指定しないと削除できません。
DROP SCHEMA sales CASCADE;
スキーマの活用方法
アクセス制御
スキーマ単位で権限を設定することで、ユーザーのアクセスを制限できます。
例: sales スキーマへのアクセス権を sales_user に付与する
GRANT USAGE ON SCHEMA sales TO sales_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA sales TO sales_user;スキーマ検索パスの設定
スキーマの検索パスを設定することで、スキーマ名を省略してオブジェクトを操作できます。
SET search_path TO sales, public;これにより、sales スキーマ内のテーブルを sales.orders ではなく orders として参照できるようになります。
マルチテナント環境の実現
マルチテナントアプリケーションでは、各クライアントごとにスキーマを分けることでデータを分離できます。
CREATE SCHEMA tenant1;
CREATE SCHEMA tenant2;そして、クライアントごとに適切な検索パスを設定します。
SET search_path TO tenant1;
public スキーマ
public スキーマとは標準で作成されるスキーマです。デフォルトで全てのユーザーがpublicスキーマに対して以下の権限を持っています。
- アクセス権限
publicスキーマ内のオブジェクトにアクセスできる権限（USAGE）
GRANT USAGE ON SCHEMA public TO PUBLIC;- 作成権限
publicスキーマ内にテーブルやビューなどのオブジェクトを作成する権限
GRANT CREATE ON SCHEMA public TO PUBLIC;public スキーマのリスク管理
public スキーマは、デフォルトで すべてのユーザーがオブジェクトを作成できる 設定になっているため、適切なリスク管理をしないとセキュリティ上の問題が発生する可能性があります。
その為、リスクを最小限にする為には以下のような管理方法を取っても良いでしょう。
CREATE 権限を削除する
一般ユーザーが public スキーマにオブジェクトを作成できないようにする。
REVOKE CREATE ON SCHEMA public FROM PUBLIC;USAGE 権限は必要に応じて設定する
完全に public スキーマを使わない場合は、USAGE も削除する。ただし、これを行うと public スキーマ内のオブジェクトにアクセスできなくなるため、注意が必要です。
REVOKE USAGE ON SCHEMA public FROM PUBLIC;search_path を適切に設定する
デフォルトで public スキーマが検索パスの先頭にあるため、不要ならば外す。
ALTER ROLE my_user SET search_path TO my_schema;スキーマを分離して管理する
public スキーマを使用せず、テナントごとや用途ごとにスキーマを分ける。
CREATE SCHEMA my_schema AUTHORIZATION my_user;
まとめ
本ブログでは、PostgreSQLのスキーマの基本操作から応用的な活用方法までを紹介しました。
ポイントまとめ
- スキーマを使うとデータを整理しやすくなる
CREATE SCHEMAでスキーマを作成し、テーブルやビューを管理できる- アクセス制御や検索パスを活用すると、より柔軟な運用が可能になる
- マルチテナント環境ではスキーマを分けることでデータの分離が容易になる
スキーマを適切に活用し、より効率的なPostgreSQLの運用を目指しましょう！
