MySQLに慣れている人がPostgreSQLを触り始めたとき、最初につまずきやすい概念が schema と role です。
MySQLでは、だいたい次のように考えることが多いです。
MySQL
└── database
└── table
一方、PostgreSQLでは次のような階層で考えます。
PostgreSQL
└── database
└── schema
├── table
├── view
├── function
├── sequence
└── type
PostgreSQLのschemaは、単なるテーブル置き場ではありません。
そしてroleは、単なるユーザーではありません。
この記事では、MySQL経験者にもわかりやすいように、PostgreSQLの schema と role を実務目線で整理します。
PostgreSQLのschemaとは
PostgreSQLのschemaは、ひとことで言うと データベース内の名前空間 です。
かなりざっくり言えば、DBの中にある「フォルダ」のようなものです。
app_db
├── public
│ └── users
├── auth
│ └── users
└── billing
└── invoices
このように、同じ users という名前のテーブルでも、schemaが違えば共存できます。
SELECT * FROM public.users;
SELECT * FROM auth.users;
つまり、PostgreSQLではテーブルを正確に指定するときに、
schema_name.table_name
という形で指定します。
MySQLのdatabaseとPostgreSQLのschemaは同じ？
似ていますが、完全には同じではありません。
MySQLでは、CREATE DATABASE と CREATE SCHEMA はほぼ同じ意味で使われます。
一方、PostgreSQLでは database と schema は明確に別物です。
PostgreSQL
└── database
├── schema
│ └── table
└── schema
└── table
MySQL感覚で言うと、MySQLで複数databaseに分けていた一部の用途を、PostgreSQLでは1つのdatabase内のschemaで分けることがあります。
例：
MySQLの場合
app_db
app_log_db
app_analytics_db
PostgreSQLでは、次のようにすることがあります。
PostgreSQLの場合
app_db
├── app
├── audit
└── analytics
schemaの典型的な使い方
1. 機能ごとに分ける
Webアプリケーションでは、機能やドメインごとにschemaを分けることがあります。
app_db
├── app
│ ├── projects
│ └── tasks
├── auth
│ ├── users
│ ├── sessions
│ └── password_resets
├── billing
│ ├── customers
│ ├── invoices
│ └── payments
└── audit
└── operation_logs
このようにすると、テーブル数が増えても責務が見えやすくなります。
SELECT * FROM auth.users;
SELECT * FROM billing.invoices;
SELECT * FROM audit.operation_logs;
2. 権限管理の単位として使う
schemaは整理のためだけではありません。
権限管理の単位としてもよく使われます。
例えば、
- アプリケーションは
appschema を読み書きできる - BIツールは
analyticsschema を参照だけできる - 一般ユーザーには
auditschema を見せない
というような設計ができます。
GRANT USAGE ON SCHEMA app TO app_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA app TO app_user;
GRANT USAGE ON SCHEMA analytics TO bi_user;
GRANT SELECT ON ALL TABLES IN SCHEMA analytics TO bi_user;
3. マルチテナント構成で使う
SaaSなどでは、顧客ごとにschemaを分ける設計もあります。
app_db
├── tenant_a
│ ├── users
│ └── orders
├── tenant_b
│ ├── users
│ └── orders
└── tenant_c
├── users
└── orders
この構成では、テーブル構造は同じまま、データをschema単位で分離できます。
ただし、テナント数が非常に多くなる場合は管理が複雑になります。
数千、数万テナント規模を想定する場合は、schema分離ではなく、tenant_idによる行レベル分離や、DB分離も検討したほうがよいです。
4. 一時取り込み・分析・監査ログを分ける
ETLやCSVインポートなどで、一時的な取り込み先としてschemaを分けることもあります。
app_db
├── app
├── staging
├── analytics
└── audit
例：
CREATE SCHEMA staging;
CREATE TABLE staging.import_users (
email text,
name text,
imported_at timestamptz DEFAULT now()
);
本番テーブルと一時テーブルを分けられるので、運用上も安全です。
public schemaとは
PostgreSQLでdatabaseを作成すると、通常 public というschemaが最初から存在します。
特にschemaを指定せずにテーブルを作ると、多くの場合 public schema に作成されます。
CREATE TABLE users (
id bigserial PRIMARY KEY,
name text NOT NULL
);
これは実質的に次と同じような意味になります。
CREATE TABLE public.users (
id bigserial PRIMARY KEY,
name text NOT NULL
);
小規模なアプリケーションでは、すべて public に置いても問題ありません。
ただし、業務アプリケーションでテーブルが増えてくる場合は、最初から次のように分けると管理しやすくなります。
app_db
├── app
├── auth
├── audit
└── analytics
search_pathとは
PostgreSQLでは、schema名を省略してテーブルを参照できます。
SELECT * FROM users;
このとき、PostgreSQLがどのschemaから探すかを決めるのが search_path です。
確認するには次のコマンドを使います。
SHOW search_path;
例えば、次のように設定されているとします。
SET search_path TO app, public;
この状態で、
SELECT * FROM users;
を実行すると、PostgreSQLはまず app.users を探します。
見つからなければ、次に public.users を探します。
つまり、次の順番です。
1. app.users
2. public.users
便利ですが、実務では注意が必要です。
同じテーブル名が複数schemaに存在する場合、意図しないテーブルを参照してしまう可能性があります。
重要なSQLでは、schema名を明示するほうが安全です。
SELECT * FROM app.users;
SELECT * FROM auth.users;
PostgreSQLのroleとは
PostgreSQLのroleは、ひとことで言うと ユーザーとグループを統合した権限管理の単位 です。
MySQLでは、一般的にユーザーを次のように作ります。
CREATE USER 'app_user'@'%' IDENTIFIED BY 'password';
PostgreSQLでは、ユーザーもroleとして作ります。
CREATE ROLE app_user LOGIN PASSWORD 'password';
ポイントは LOGIN です。
LOGIN があるroleは、ログインできるroleです。
つまり、実質的に「ユーザー」として使えます。
CREATE ROLE app_user LOGIN PASSWORD 'password';
一方、LOGIN がないroleは、ログインできません。
これは「権限グループ」のように使います。
CREATE ROLE readonly;
CREATE ROLE readwrite;
roleはユーザーにもグループにもなる
PostgreSQLでは、roleを次の2種類に分けて考えるとわかりやすいです。
role
├── ログインできるrole
│ └── ユーザーとして使う
└── ログインできないrole
└── 権限グループとして使う
例：
CREATE ROLE app_readonly;
CREATE ROLE app_readwrite;
CREATE ROLE app_user LOGIN PASSWORD 'password';
CREATE ROLE bi_user LOGIN PASSWORD 'password';
権限グループ用roleを、ログイン用roleに付与します。
GRANT app_readwrite TO app_user;
GRANT app_readonly TO bi_user;
この設計にすると、個別ユーザーに直接権限を付けるより管理しやすくなります。
roleと権限の実務設計
例えば、次のような構成を考えます。
schema
├── app
├── auth
└── audit
role
├── app_owner
├── app_readwrite
├── app_readonly
├── app_user
└── bi_user
役割は次のように分けます。
| role | 用途 |
|---|---|
| app_owner | テーブル所有者、マイグレーション用 |
| app_readwrite | アプリケーションの通常実行用 |
| app_readonly | BI・分析・参照用 |
| app_user | アプリケーション接続ユーザー |
| bi_user | BIツール接続ユーザー |
作成例です。
CREATE ROLE app_owner LOGIN PASSWORD 'owner_password';
CREATE ROLE app_readwrite;
CREATE ROLE app_readonly;
CREATE ROLE app_user LOGIN PASSWORD 'app_password';
CREATE ROLE bi_user LOGIN PASSWORD 'bi_password';
GRANT app_readwrite TO app_user;
GRANT app_readonly TO bi_user;
schemaを作成します。
CREATE SCHEMA app AUTHORIZATION app_owner;
CREATE SCHEMA auth AUTHORIZATION app_owner;
CREATE SCHEMA audit AUTHORIZATION app_owner;
権限を付与します。
GRANT USAGE ON SCHEMA app TO app_readwrite, app_readonly;
GRANT USAGE ON SCHEMA auth TO app_readwrite, app_readonly;
GRANT USAGE ON SCHEMA audit TO app_readwrite;
GRANT SELECT, INSERT, UPDATE, DELETE
ON ALL TABLES IN SCHEMA app
TO app_readwrite;
GRANT SELECT
ON ALL TABLES IN SCHEMA app
TO app_readonly;
今後作成されるテーブルにも自動で権限を付けたい場合は、default privilegesを設定します。
ALTER DEFAULT PRIVILEGES IN SCHEMA app
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_readwrite;
ALTER DEFAULT PRIVILEGES IN SCHEMA app
GRANT SELECT ON TABLES TO app_readonly;
schema権限とtable権限は別物
PostgreSQLでよくあるハマりどころがこれです。
GRANT SELECT ON ALL TABLES IN SCHEMA app TO app_readonly;
これだけでは不十分な場合があります。
schema内のテーブルを参照するには、通常schemaへの USAGE 権限も必要です。
GRANT USAGE ON SCHEMA app TO app_readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA app TO app_readonly;
つまり、schemaとtableにはそれぞれ権限があります。
schemaへの権限
└── そのschemaを使えるか
tableへの権限
└── SELECT / INSERT / UPDATE / DELETE できるか
この違いは重要です。
MySQLとの比較
MySQLとPostgreSQLの違いをざっくり比較すると、次のようになります。
| 観点 | MySQL | PostgreSQL |
|---|---|---|
| database | 主な分離単位 | 接続先のDB |
| schema | databaseとほぼ同義で扱われることが多い | database内の名前空間 |
| user |
'user'@'host' で管理 |
LOGIN可能なrole |
| role | MySQL 8.0以降で利用可能 | 権限管理の中心概念 |
| 権限設計 | userに直接付与することも多い | roleに権限を付け、user roleに継承させることが多い |
PostgreSQLでは、次のような考え方が基本です。
ログイン用role
↓ 所属
権限用role
↓ 権限付与
schema / table / sequence
よく使う確認コマンド
database一覧
\l
または、
psql -l
schema一覧
\dn
詳細付き：
\dn+
table一覧
\dt
schemaを指定する場合：
\dt app.*
role一覧
\du
現在のdatabase確認
SELECT current_database();
現在のschema確認
SELECT current_schema();
search_path確認
SHOW search_path;
実務でおすすめの最小構成
小さなアプリケーションなら、最初は public だけでも動きます。
ただし、業務アプリケーションとして長く運用するなら、最初から次の程度に分けると扱いやすいです。
app_db
├── app
├── auth
├── audit
└── analytics
roleは次のように分けます。
app_owner
app_readwrite
app_readonly
app_user
bi_user
基本方針は次の通りです。
- アプリ接続ユーザーに直接強い権限を付けない
- 権限用roleを作り、ログイン用roleに付与する
- schema単位で責務を分ける
- table権限だけでなくschemaのUSAGE権限も忘れない
- 重要なSQLではschema名を明示する
まとめ
PostgreSQLのschemaとroleは、最初は少しわかりにくいですが、実務では非常に強力です。
schemaは、DB内のオブジェクトを整理するための名前空間です。
database
└── schema
└── table
roleは、ユーザーとグループを統合した権限管理の単位です。
role
├── ログイン用role
└── 権限グループ用role
MySQL経験者向けにまとめると、次のように考えると理解しやすいです。
MySQL:
database中心
user中心
PostgreSQL:
databaseの中にschema
userもgroupもrole
PostgreSQLらしい設計では、schemaで責務を分け、roleで権限を分けます。
schemaで構造を整理する
roleでアクセス権限を整理する
この2つを理解すると、PostgreSQLの設計・運用・セキュリティ管理がかなり見通しやすくなります。