PostgreSQL 18 のロールと権限管理：最小権限で安全に運用する設計



前回の記事で外部ネットワークからの接続許可とアクセス制御（postgresql.confおよびpg_hba.conf）を完了しました。しかし、ネットワークの疎通が確立された次に多くの管理者が直面するのが、「データベース内部のロールと権限管理」の壁です。初期運用の段階で手間を惜しみ、すべてのアプリケーションからスーパーユーザー（postgres）で直接接続させてしまう事例が後を絶ちません。この状態を放置すると、SQLインジェクションやアプリケーションの不具合によるデータ全損、意図しないテーブル破棄など、致命的なセキュリティ事故を招きます。

安全な本番運用を実現するためには、「最小権限の原則（PoLP: Principle of Least Privilege）」に基づき、実行すべき業務に必要な権限のみを切り出して各ロールへ割り当てる必要があります。本稿では、PostgreSQL特有の「3階層権限モデル（Database → Schema → Table）」を詳細に解読し、実務で直面する代表的なエラーの対処法と権限設計のベストプラクティスを整理します。

1. PostgreSQLにおける「ロール」と「ユーザー」の本質#

1.1 ロールとユーザーは同一の内部オブジェクト#

多くの商用RDBMS（OracleやMySQLなど）では、「ユーザー（接続アカウント）」と「グループ / ロール（権限の束）」が明確に別個のオブジェクトとして分離されています。しかし、PostgreSQLではバージョン8.1以降、ユーザーとグループの概念が「ロール（Role）」という単一の仕組みに完全統合されました。

PostgreSQLの内部カタログ（pg_rolesおよびpg_authid）において、ユーザーとロールにデータ構造上の差異は一切存在しません。SQL標準に配慮してCREATE USERやCREATE GROUPというコマンドが用意されていますが、これらは内部的には単なる構文糖（エイリアス）に過ぎません。

-- 以下の2つのコマンドは内部的に完全に等価
CREATE USER app_user WITH PASSWORD 'SecurePassword123';
CREATE ROLE app_user WITH LOGIN PASSWORD 'SecurePassword123';

違いは「LOGIN属性（ログイン権限）がデフォルトで有効になるかどうか」という点だけです。実務のインフラ構築・コード管理においては、混乱を防ぐためにすべてCREATE ROLE構文で統一し、明示的に属性を付与する運用を推奨します。

1.2 主なロール属性の整理#

ロールを作成する際、WITH句に続けて各種のグローバル権限属性を指定できます。代表的な属性の意味とセキュリティ上の注意点は下表の通りです。

1.3 \du メタコマンドで既存ロールと属性一覧を読み解く#

データベースクラスタ内に登録されているロールとその属性は、psqlのメタコマンド\du（または\du+）で一覧確認できます。

$ sudo -u postgres /usr/pgsql-18/bin/psql -c "\du"
                                       ロール一覧
 ロール名 |                                     属性                                     
----------+------------------------------------------------------------------------------
 postgres | スーパーユーザー, ロール作成可, DB作成可, レプリケーション可, RLS のバイパス

初期化直後の状態では、最高特権を持つpostgresロールのみが存在します。属性欄に「スーパーユーザー」と明記されていることが確認できます。

2. 権限管理の3階層モデル：Database → Schema → Table#

2.1 なぜテーブルだけにGRANTしてもエラーになるのか#

PostgreSQLの権限管理で初心者が最も頻繁に遭遇するトラブルが、「テーブルに対してGRANT SELECTを実行したのに、アクセスすると拒否される」という事象です。PostgreSQLのオブジェクト階層は、「データベース（Database） ＞ スキーマ（Schema） ＞ テーブル等のオブジェクト（Table / View / Sequence）」という入れ子構造を持っています。

クライアントが特定のテーブル内のデータを読み書きするためには、外側の関門から内側に向かって、3つの階層すべての認可を順に通過しなければなりません。

2.2 第1関門：データベース接続権限（CONNECT）#

最初の関門は、対象のデータベース自体にセッションを確立できるかどうかです。この制御を担うのがCONNECT権限です。

-- データベースへの接続権限を付与
GRANT CONNECT ON DATABASE production_db TO app_user;

-- 接続権限を剥奪
REVOKE CONNECT ON DATABASE production_db FROM PUBLIC;

PostgreSQLではデフォルトで、すべての接続アカウントを含む疑似ロールPUBLICに対してCONNECT権限が暗黙付与されています。そのため、一般的な環境では意識せずにログインできますが、マルチテナント環境や機密データベースでは、まずPUBLICからCONNECT権限を剥奪した上で、特定の業務ロールのみに明示付与する設計が推奨されます。

2.3 第2関門：スキーマ使用権限（USAGE と CREATE）#

第2の関門が、データベース内部の論理的な名前空間である「スキーマ（Schema）」に対する権限です。ここには2種類の重要な権限が存在します。

• USAGE（使用権限）：スキーマの内部を覗き見ること、およびスキーマ内に含まれるテーブルやビュー、関数を参照・実行することを許可します。テーブル自体の権限をいくら持っていても、親スキーマのUSAGE権限がなければ、テーブルの存在を確認することすら拒否されます。
• CREATE（作成権限）：スキーマの内部に新しいテーブル、ビュー、インデックスなどのオブジェクトを作成することを許可します。

2.4 第3関門：オブジェクト操作権限とシーケンス権限#

第3の関門が、個々のテーブルやシーケンスに対する操作権限です。

• テーブル権限：SELECT（参照）、INSERT（追加）、UPDATE（更新）、DELETE（削除）、TRUNCATE（全件切り捨て）、REFERENCES（外部キー制約設定）など。
• シーケンス権限（重要）：自動採番カラム（serial型やGENERATED AS IDENTITY）を持つテーブルにデータを追加（INSERT）する場合、テーブルのINSERT権限に加えて、裏で採番を実行しているシーケンスオブジェクトに対するUSAGEおよびSELECT権限が必須となります。シーケンス権限が抜けていると、INSERT実行時に実行時エラーが発生します。

3. 実務で必須の権限設定レシピ#

実務現場で標準的に求められる「Webアプリケーション運用ロール」と「分析・調査用リードオンリーロール」の2大テンプレートを整理します。

3.1 レシピ1：Webアプリ用運用ユーザー（CRUD操作）#

Webアプリケーションの接続用には、日常業務に必要なデータの登録・参照・変更・削除（CRUD）を許可しつつ、テーブルの削除（DROP TABLE）や一括消去（TRUNCATE）といった破滅的操作を禁止する最小権限ロールを設計します。

-- 1. ロールの作成（スーパーユーザーやDB作成権限は与えない）
CREATE ROLE app_user WITH LOGIN PASSWORD 'SecureAppPass2026';

-- 2. データベース接続権限とスキーマ使用権限の付与
GRANT CONNECT ON DATABASE production_db TO app_user;
GRANT USAGE ON SCHEMA app_schema TO app_user;

-- 3. 既存テーブルへのCRUD権限付与
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA app_schema TO app_user;

-- 4. 既存シーケンスへの採番権限付与
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA app_schema TO app_user;

3.2 レシピ2：分析・調査用リードオンリーユーザー（SELECT限定）#

RedashやMetabaseなどのBIツール、あるいは障害調査・監査で利用するアカウントには、誤操作によるデータ書き換えを物理的に防止するため、SELECT権限のみを付与した参照専用ロールを用意します。

-- 1. 参照専用ロールの作成
CREATE ROLE readonly_user WITH LOGIN PASSWORD 'ReadOnlyPass2026';

-- 2. データベース接続権限とスキーマ使用権限の付与
GRANT CONNECT ON DATABASE production_db TO readonly_user;
GRANT USAGE ON SCHEMA app_schema TO readonly_user;

-- 3. 既存テーブルに対するSELECT権限のみを付与
GRANT SELECT ON ALL TABLES IN SCHEMA app_schema TO readonly_user;

このロールにはシーケンスの権限やINSERT/UPDATE/DELETEの権限を与えないため、万が一SQLインジェクションや不正アクセスを受けても、データの改ざんや破壊を防ぐことができます。

3.3 将来追加されるテーブルへの権限自動付与：ALTER DEFAULT PRIVILEGES#

上記で実行したGRANT ... ON ALL TABLESは、「現在すでにデータベース内に存在しているテーブル」に対してのみ有効です。運用開始後にマイグレーションツール（Flyway、Liquibase、Prismaなど）や管理者によって新しいテーブルが作成された場合、その新テーブルには上記ロールの権限が付与されません。

この問題を恒久的に解決するのが、ALTER DEFAULT PRIVILEGES（デフォルト権限の変更）です。これによって、「特定の作成者が今後作成するオブジェクト」に対して、自動的に付与される権限テンプレートを定義できます。

-- postgres ユーザーが app_schema 内に将来作成する全テーブルに対し、app_user にCRUD権限を自動付与
ALTER DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA app_schema
    GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_user;

-- postgres ユーザーが app_schema 内に将来作成する全テーブルに対し、readonly_user にSELECT権限を自動付与
ALTER DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA app_schema
    GRANT SELECT ON TABLES TO readonly_user;

-- postgres ユーザーが将来作成する全シーケンスに対し、app_user に採番権限を自動付与
ALTER DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA app_schema
    GRANT USAGE, SELECT ON SEQUENCES TO app_user;

3.4 権限剥奪（REVOKE）とロール削除（DROP ROLE）時の依存関係エラー対処#

ロールを削除しようとしてDROP ROLE app_user;を実行した際、以下のようなエラーに直面することがあります。

ERROR: role "app_user" cannot be dropped because some objects depend on it
DETAIL: privileges for table orders

PostgreSQLでは、何らかのオブジェクトの所有者（Owner）になっていたり、アクセス権限が付与されているロールを直接削除することはできません。ロールを安全に破棄するには、以下の2ステップを踏む必要があります。

-- 1. 対象ロールが所有するオブジェクトの所有権を別ロール（postgresなど）に移転
REASSIGN OWNED BY app_user TO postgres;

-- 2. 対象ロールに付与されているすべての権限・従属性を剥奪
DROP OWNED BY app_user;

-- 3. 安全にロールを削除
DROP ROLE app_user;

4. 【実機検証】AlmaLinux 10 / PostgreSQL 18でのロール・権限構築テスト#

実機環境（DBノード: alma-db01、クライアントノード: alma-proxy）を用いて、設計した権限モデルの動作検証を行います。

4.1 データベースとカスタムスキーマの作成#

まず、DBノード（alma-db01）に接続し、検証用のデータベースとスキーマ、およびロールを作成します。

$ sudo -u postgres /usr/pgsql-18/bin/psql
-- データベースとロールの作成
CREATE DATABASE production_db;
CREATE ROLE app_user WITH LOGIN PASSWORD 'AppUserSecurePass2026';
CREATE ROLE readonly_user WITH LOGIN PASSWORD 'ReadOnlyPass2026';

-- production_db へ接続切り替え
\c production_db

-- 専用スキーマの作成
CREATE SCHEMA app_schema AUTHORIZATION postgres;

-- スキーマのアクセス権限設定
GRANT USAGE ON SCHEMA app_schema TO app_user, readonly_user;
GRANT CREATE ON SCHEMA app_schema TO app_user;

-- テストテーブルの作成
CREATE TABLE app_schema.orders (
    order_id serial PRIMARY KEY,
    customer_name text NOT NULL,
    total_amount integer NOT NULL,
    order_date timestamp with time zone DEFAULT current_timestamp
);

-- オブジェクト権限の付与
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE app_schema.orders TO app_user;
GRANT USAGE, SELECT ON SEQUENCE app_schema.orders_order_id_seq TO app_user;
GRANT SELECT ON TABLE app_schema.orders TO readonly_user;

-- デフォルト権限の自動付与設定
ALTER DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA app_schema
    GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_user;
ALTER DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA app_schema
    GRANT SELECT ON TABLES TO readonly_user;
ALTER DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA app_schema
    GRANT USAGE, SELECT ON SEQUENCES TO app_user;

4.2 権限確認コマンド \dp のアクセス権表記（arwdDxt）完全読み解き#

作成したテーブルの権限状態を、psqlのメタコマンド\dp（または\z）で確認します。

production_db=# \dp app_schema.orders
                                   アクセス権限
  スキーマ  |  名前  |  タイプ  |        アクセス権限        | 列の権限 | ポリシー 
------------+--------+----------+----------------------------+----------+----------
 app_schema | orders | テーブル | postgres=arwdDxtm/postgres+|          | 
            |        |          | app_user=arwd/postgres    +|          | 
            |        |          | readonly_user=r/postgres   |          | 
(1 行)

アクセス権限欄に表示されるapp_user=arwd/postgresという文字列は、PostgreSQL特有の圧縮記法です。この構造を理解することが運用トラブルを防止する上で極めて重要です。

この記法は[権限を付与されたロール] = [権限フラグ] / [権限を付与したロール（Grantor）]を表しています。

• postgres=arwdDxtm/postgres：テーブル所有者であるpostgresが自身に対してすべての権限（r: SELECT, a: INSERT, w: UPDATE, d: DELETE, D: TRUNCATE, x: REFERENCES, t: TRIGGER, m: MAINTAIN）を保持。
• app_user=arwd/postgres：postgresによって、app_userにCRUD操作（SELECT, INSERT, UPDATE, DELETE）が付与されている。
• readonly_user=r/postgres：postgresによって、readonly_userに参照権限（r: SELECT）のみが付与されている。

4.3 検証1：各ユーザーによる参照と更新の分離確認#

クライアントノード（alma-proxy: 192.168.2.121）からリモート接続し、意図通りの権限制御が機能しているかテストします。

# app_user でのデータ挿入（成功）
$ PGPASSWORD='AppUserSecurePass2026' psql -h 192.168.2.132 -U app_user -d production_db -c "INSERT INTO app_schema.orders (customer_name, total_amount) VALUES ('Alice', 1500), ('Bob', 3200) RETURNING *;"
 order_id | customer_name | total_amount |          order_date           
----------+---------------+--------------+-------------------------------
        1 | Alice         |         1500 | 2026-09-09 20:22:21.029687+09
        2 | Bob           |         3200 | 2026-09-09 20:22:21.029687+09
(2 行)

INSERT 0 2

# readonly_user でのデータ参照（成功）
$ PGPASSWORD='ReadOnlyPass2026' psql -h 192.168.2.132 -U readonly_user -d production_db -c "SELECT order_id, customer_name, total_amount FROM app_schema.orders;"
 order_id | customer_name | total_amount 
----------+---------------+--------------
        1 | Alice         |         1500
        2 | Bob           |         3200
(2 行)

# readonly_user によるデータ挿入試行（テーブル権限不足エラー）
$ PGPASSWORD='ReadOnlyPass2026' psql -h 192.168.2.132 -U readonly_user -d production_db -c "INSERT INTO app_schema.orders (customer_name, total_amount) VALUES ('Charlie', 4800);"
ERROR:  テーブル orders へのアクセスが拒否されました

readonly_userによるSELECTは正常に結果を返しますが、INSERTを実行しようとすると即座にERROR: テーブル orders へのアクセスが拒否されました（英語表記: ERROR: permission denied for table orders）となり、データ書き込みが物理的に拒否されます。

4.4 検証2：スキーマ USAGE 剥奪による関門遮断テスト#

次に、テーブルに対するSELECT権限が残った状態で、親スキーマであるapp_schemaのUSAGE権限のみを剥奪した場合の挙動を確認します。

-- alma-db01 上で USAGE 権限を剥奪
$ sudo -u postgres /usr/pgsql-18/bin/psql -d production_db -c "REVOKE USAGE ON SCHEMA app_schema FROM readonly_user;"
REVOKE

この状態でクライアントから再度SELECTを実行します。

$ PGPASSWORD='ReadOnlyPass2026' psql -h 192.168.2.132 -U readonly_user -d production_db -c "SELECT order_id, customer_name, total_amount FROM app_schema.orders;"
ERROR:  スキーマ app_schema へのアクセスが拒否されました
行 1: SELECT order_id, customer_name, total_amount FROM app_schema...
                                                        ^

エラーメッセージがテーブルではなくERROR: スキーマ app_schema へのアクセスが拒否されました（英語表記: ERROR: permission denied for schema app_schema）に変化しました。テーブル自体の権限をいくら調整しても、親スキーマのUSAGE権限が欠落していれば第2関門で確実に遮断されることが実証されました。

-- 検証後、USAGE 権限を復旧
$ sudo -u postgres /usr/pgsql-18/bin/psql -d production_db -c "GRANT USAGE ON SCHEMA app_schema TO readonly_user;"
GRANT

4.5 検証3：ALTER DEFAULT PRIVILEGES による新規テーブル自動権限確認#

最後に、運用開始後に新しいテーブルが追加されたシナリオをテストします。管理者（postgres）で新規テーブルproductsを作成します。

$ sudo -u postgres /usr/pgsql-18/bin/psql -d production_db << "EOF"
CREATE TABLE app_schema.products (
    product_id serial PRIMARY KEY,
    product_name text NOT NULL,
    price integer NOT NULL
);
INSERT INTO app_schema.products (product_name, price) VALUES ('PostgreSQL入門書', 3000);
EOF

テーブル作成後、個別のGRANT SELECTを一切実行しない状態で、クライアントからreadonly_userで検索を行います。

$ PGPASSWORD='ReadOnlyPass2026' psql -h 192.168.2.132 -U readonly_user -d production_db -c "SELECT * FROM app_schema.products;"
 product_id |   product_name   | price 
------------+------------------+-------
          1 | PostgreSQL入門書 |  3000
(1 行)

追加の権限付与作業をすることなく、即座に検索が成功しました。ALTER DEFAULT PRIVILEGESが正常に作用し、運用の自動化・権限漏れ防止が機能していることが確認できます。

5. 実務チートシート：psql権限確認コマンド一覧#

日々のデータベース保守やトラブルシューティングで即座に役立つpsqlメタコマンドの対照表をまとめます。

5.1 ロール継承（INHERIT）とSET ROLEによる安全な権限昇格#

複数の運用者が関わる本番システムでは、個々の担当者に直接強い権限を付与するのではなく、「グループロール」を作成して各個人ロールをそのメンバーとして所属させる運用が定石です。この際に鍵となるのが INHERIT（継承）属性です。

デフォルトでは作成されたロールは INHERIT 属性を持つため、所属する親グループの権限を暗黙的に行使できます。しかし、本番環境の更新権限など事故リスクの高い権限については、暗黙的な継承を禁止し、必要な作業を行う際のみ SET ROLE コマンドを発行して一時的に権限を昇格させる運用設計が推奨されます。

ここで実務上極めて重要な注意点があります。PostgreSQLにおける INHERIT / NOINHERIT は「親ロール側の属性」ではなく、「所属するメンバー側の属性」です。親ロールに NOINHERIT を付与しても、メンバー側が INHERIT（デフォルト）であれば親の権限を自動継承してしまいます。暗黙継承を阻止するには、PostgreSQL 16以降で導入された GRANT ... WITH INHERIT FALSE を使用するか、メンバーロール側で NOINHERIT を明示設定します。

-- 1. 個人ロールおよび昇格運用用グループロールの作成
CREATE ROLE tanaka_user WITH LOGIN PASSWORD 'TanakaPass2026';
CREATE ROLE prod_operator WITH NOLOGIN;
GRANT USAGE, CREATE ON SCHEMA app_schema TO prod_operator;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA app_schema TO prod_operator;

-- 2. 個人ロールに「継承無効（INHERIT FALSE）」でグループ権限を付与（PostgreSQL 16以降）
GRANT prod_operator TO tanaka_user WITH INHERIT FALSE;
-- （※PG15以前またはロール単位で制御する場合は ALTER ROLE tanaka_user NOINHERIT; を併用）

-- 3. 検証：通常時は個人権限（アクセス拒否）、SET ROLE 発行時のみ一時昇格
psql -U tanaka_user -d production_db
=> SELECT * FROM app_schema.orders;
ERROR:  permission denied for schema app_schema

=> SET ROLE prod_operator;  -- 明示的な権限昇格
SET
=> SELECT count(*) FROM app_schema.orders;  -- 実行成功（4.3節で投入した2件を確認）
 count 
-------
     2
(1 行)

=> RESET ROLE;             -- 元の権限に戻す
RESET

このアプローチを採用することで、誤ったクエリ実行による人為的ミスを抑止し、操作ログ（pgaudit等）で「誰がどのロールを行使したか」を明確に追跡できます。

5.2 組み込み事前定義ロール（pg_read_all_data等）の活用と留意点#

PostgreSQL 14以降、システム全体にわたる特定業務をスーパーユーザー（postgres）なしで安全に委譲するための「組み込み事前定義ロール（Predefined Roles）」が強化されました。

特に pg_read_all_data は、全社データ分析用ETL基盤やバックアップ専用ロールの作成において極めて有効に機能します。ただし、将来追加される顧客情報や機密データテーブルを含め、システム内のあらゆる情報が無条件で閲覧可能となるため、本番運用においては「最小権限の原則」とのバランスを十分に評価し、利用者を厳格に制限することが不可欠です。

6. 理解度確認演習問題（全3問・解答解説付き）#

ALTER DEFAULT PRIVILEGES FOR ROLE admin_user IN SCHEMA app_schema
    GRANT SELECT ON TABLES TO readonly_user;

7. まとめと次回予告#

次回は第5回「【論理バックアップ実務】pg_dumpとpg_restoreによるバックアップ・リストア完全手順」を解説します。テキスト形式とカスタム形式（-Fc）の違い、テーブル単位のピンポイント抽出復旧、--cleanオプションによる安全な再作成など、障害復旧の現場で直ちに役立つリストア技術を実機検証で整理します。

リンク