PostgreSQLの権限を完全マスター！一覧確認から管理まで全て分かる完全ガイド

「誰がどのテーブルにアクセスできるか分からない…」
「権限エラーが出るけど、何の権限が足りないの？」
「新しいユーザーにどんな権限を付与すればいい？」
「権限の一覧ってどうやって確認するの？」

こんな悩み、今日で解決します！

PostgreSQLの権限システムは強力で柔軟ですが、その分複雑に見えがちです。この記事を読めば、権限の確認から設定まで、すべてが手に取るように分かるようになります！

---

PostgreSQLの権限体系を理解する

権限の3つのレベル

データベースクラスター
    ↓
データベース（DATABASE）
    ↓
スキーマ（SCHEMA）
    ↓
オブジェクト（TABLE, VIEW, FUNCTION等）

各レベルで異なる権限が存在します！

主要な権限一覧

権限名	記号	説明	対象オブジェクト
SELECT	r	データ読み取り	TABLE, VIEW
INSERT	a	データ追加	TABLE, VIEW
UPDATE	w	データ更新	TABLE, VIEW
DELETE	d	データ削除	TABLE, VIEW
TRUNCATE	D	全データ削除	TABLE
REFERENCES	x	外部キー作成	TABLE
TRIGGER	t	トリガー作成	TABLE
CREATE	C	オブジェクト作成	DATABASE, SCHEMA
CONNECT	c	接続許可	DATABASE
TEMPORARY	T	一時テーブル作成	DATABASE
EXECUTE	X	実行	FUNCTION
USAGE	U	使用	SCHEMA, SEQUENCE

---

権限を確認する7つの方法

方法1：\dpコマンド（テーブル権限の確認）

-- psqlで実行
\dp  -- 全テーブルの権限表示
\dp employees  -- 特定テーブルの権限

-- 出力例：
                         Access privileges
 Schema |   Name    | Type  |   Access privileges   | Column privileges
--------|-----------|-------|----------------------|------------------
 public | employees | table | postgres=arwdDxt/postgres+ |
        |           |       | app_user=arwd/postgres    |
        |           |       | readonly=r/postgres       |

権限記号の読み方：

• postgres=arwdDxt : postgresユーザーが全権限保持
• app_user=arwd : app_userがSELECT/INSERT/UPDATE/DELETE可能
• readonly=r : readonlyはSELECTのみ
• /postgres : postgresが権限を付与した

方法2：\duコマンド（ユーザー/ロール一覧）

-- ユーザーとロールの一覧
\du

-- 出力例：
                List of roles
 Role name |  Attributes  |  Member of
-----------|--------------|------------
 postgres  | Superuser, Create role, Create DB | {}
 app_user  | | {}
 readonly  | Cannot login | {}
 admin     | Create DB | {app_user}

方法3：pg_rolesビュー（詳細情報）

-- ロールの詳細情報
SELECT 
    rolname AS ロール名,
    rolsuper AS スーパーユーザー,
    rolinherit AS 継承,
    rolcreaterole AS ロール作成可,
    rolcreatedb AS DB作成可,
    rolcanlogin AS ログイン可,
    rolreplication AS レプリケーション,
    rolconnlimit AS 接続数制限,
    rolvaliduntil AS 有効期限
FROM pg_roles
ORDER BY rolname;

方法4：information_schemaで権限確認

-- テーブル権限の詳細確認
SELECT 
    grantee AS 権限保持者,
    table_schema AS スキーマ,
    table_name AS テーブル,
    privilege_type AS 権限タイプ,
    is_grantable AS 付与可能
FROM information_schema.table_privileges
WHERE table_schema NOT IN ('pg_catalog', 'information_schema')
ORDER BY grantee, table_schema, table_name;

-- カラムレベルの権限
SELECT 
    grantee,
    table_schema,
    table_name,
    column_name,
    privilege_type
FROM information_schema.column_privileges
WHERE table_schema = 'public';

方法5：has_*_privilege関数で確認

-- 現在のユーザーの権限確認
SELECT has_table_privilege('employees', 'SELECT');  -- true/false
SELECT has_table_privilege('employees', 'INSERT');
SELECT has_table_privilege('employees', 'UPDATE');
SELECT has_table_privilege('employees', 'DELETE');

-- 特定ユーザーの権限確認
SELECT has_table_privilege('app_user', 'employees', 'SELECT');

-- データベース権限
SELECT has_database_privilege('mydb', 'CREATE');

-- スキーマ権限
SELECT has_schema_privilege('public', 'USAGE');

方法6：一括権限確認スクリプト

-- 全ユーザーの全テーブル権限を一覧表示
WITH permissions AS (
    SELECT 
        n.nspname AS schema_name,
        c.relname AS table_name,
        a.rolname AS grantee,
        string_agg(p.privilege_type, ', ' ORDER BY p.privilege_type) AS privileges
    FROM pg_class c
    CROSS JOIN pg_roles a
    JOIN pg_namespace n ON c.relnamespace = n.oid
    CROSS JOIN LATERAL (
        SELECT 'SELECT' AS privilege_type WHERE has_table_privilege(a.rolname, c.oid, 'SELECT')
        UNION ALL
        SELECT 'INSERT' WHERE has_table_privilege(a.rolname, c.oid, 'INSERT')
        UNION ALL
        SELECT 'UPDATE' WHERE has_table_privilege(a.rolname, c.oid, 'UPDATE')
        UNION ALL
        SELECT 'DELETE' WHERE has_table_privilege(a.rolname, c.oid, 'DELETE')
    ) p
    WHERE c.relkind IN ('r', 'v')  -- テーブルとビューのみ
      AND n.nspname NOT IN ('pg_catalog', 'information_schema')
      AND a.rolname NOT LIKE 'pg_%'
    GROUP BY n.nspname, c.relname, a.rolname
)
SELECT * FROM permissions
ORDER BY schema_name, table_name, grantee;

方法7：権限マトリックス表示

-- ユーザー×テーブルの権限マトリックス
SELECT 
    t.schemaname || '.' || t.tablename AS テーブル,
    MAX(CASE WHEN grantee = 'app_user' THEN privilege_type END) AS app_user,
    MAX(CASE WHEN grantee = 'readonly' THEN privilege_type END) AS readonly,
    MAX(CASE WHEN grantee = 'admin' THEN privilege_type END) AS admin
FROM pg_tables t
LEFT JOIN information_schema.table_privileges p
    ON t.schemaname = p.table_schema 
    AND t.tablename = p.table_name
WHERE t.schemaname = 'public'
GROUP BY t.schemaname, t.tablename
ORDER BY t.tablename;

---

権限の付与と取り消し

GRANT：権限を付与する

-- 基本構文
GRANT 権限 ON オブジェクト TO ユーザー;

-- テーブル権限の付与
GRANT SELECT ON employees TO readonly;
GRANT SELECT, INSERT, UPDATE ON employees TO app_user;
GRANT ALL PRIVILEGES ON employees TO admin;

-- 複数テーブルに一括付与
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;

-- 将来作成されるテーブルにも自動付与
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT ON TABLES TO readonly;

-- WITH GRANT OPTION（他ユーザーへの権限付与を許可）
GRANT SELECT ON employees TO team_lead WITH GRANT OPTION;

REVOKE：権限を取り消す

-- 基本構文
REVOKE 権限 ON オブジェクト FROM ユーザー;

-- 権限の取り消し
REVOKE INSERT ON employees FROM app_user;
REVOKE ALL PRIVILEGES ON employees FROM temp_user;

-- CASCADE（依存する権限も取り消す）
REVOKE SELECT ON employees FROM team_lead CASCADE;

---

実践的な権限管理パターン

パターン1：読み取り専用ユーザー

-- 読み取り専用ロールの作成
CREATE ROLE readonly_role;

-- 権限付与
GRANT CONNECT ON DATABASE mydb TO readonly_role;
GRANT USAGE ON SCHEMA public TO readonly_role;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_role;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO readonly_role;

-- デフォルト権限設定
ALTER DEFAULT PRIVILEGES IN SCHEMA public 
GRANT SELECT ON TABLES TO readonly_role;

-- ユーザー作成してロール付与
CREATE USER report_user WITH PASSWORD 'secret123';
GRANT readonly_role TO report_user;

パターン2：アプリケーション用ユーザー

-- アプリケーション用ロール
CREATE ROLE app_role;

-- CRUD権限を付与
GRANT CONNECT ON DATABASE mydb TO app_role;
GRANT USAGE ON SCHEMA public TO app_role;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_role;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO app_role;

-- 関数実行権限
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO app_role;

-- ユーザー作成
CREATE USER app_user WITH PASSWORD 'app_pass123';
GRANT app_role TO app_user;

パターン3：開発者用ユーザー

-- 開発者ロール（テーブル作成可能）
CREATE ROLE developer_role;

GRANT CONNECT ON DATABASE mydb TO developer_role;
GRANT CREATE ON SCHEMA public TO developer_role;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO developer_role;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO developer_role;

-- 自分が作成したオブジェクトの権限を自動設定
ALTER DEFAULT PRIVILEGES FOR ROLE developer_role IN SCHEMA public
GRANT ALL ON TABLES TO developer_role;

CREATE USER dev_user WITH PASSWORD 'dev123';
GRANT developer_role TO dev_user;

---

カラムレベルの権限管理

特定カラムのみアクセス許可

-- 給与情報を含むテーブル
CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(255),
    salary INTEGER,      -- 機密情報
    department VARCHAR(50)
);

-- 給与以外のカラムへのアクセスを許可
GRANT SELECT (id, name, email, department) ON employees TO regular_user;
GRANT UPDATE (email, department) ON employees TO regular_user;

-- 給与カラムへのアクセスは管理者のみ
GRANT SELECT (salary) ON employees TO payroll_admin;

---

RLS（Row Level Security）で行レベル制御

部門ごとにデータを制限

-- RLSを有効化
ALTER TABLE employees ENABLE ROW LEVEL SECURITY;

-- ポリシー作成（自部門のみ表示）
CREATE POLICY dept_isolation ON employees
FOR ALL
TO app_user
USING (department = current_setting('app.current_department'));

-- セッション変数で部門を設定
SET app.current_department = '営業部';

-- これで営業部のデータのみ見える
SELECT * FROM employees;  -- 営業部のみ表示

---

権限のトラブルシューティング

よくあるエラーと解決方法

-- エラー1: permission denied for table
-- 解決: SELECT権限を付与
GRANT SELECT ON table_name TO user_name;

-- エラー2: permission denied for schema
-- 解決: スキーマのUSAGE権限を付与
GRANT USAGE ON SCHEMA schema_name TO user_name;

-- エラー3: permission denied for sequence
-- 解決: シーケンスの権限を付与
GRANT USAGE, SELECT ON SEQUENCE sequence_name TO user_name;

-- エラー4: must be owner of table
-- 解決: オーナー変更または適切な権限付与
ALTER TABLE table_name OWNER TO new_owner;

権限チェックスクリプト

-- ユーザーが特定操作可能か診断
CREATE OR REPLACE FUNCTION check_user_permissions(
    p_user TEXT,
    p_table TEXT
) RETURNS TABLE (
    operation TEXT,
    has_permission BOOLEAN
) AS $$
BEGIN
    RETURN QUERY
    SELECT 'SELECT'::TEXT, has_table_privilege(p_user, p_table, 'SELECT')
    UNION ALL
    SELECT 'INSERT'::TEXT, has_table_privilege(p_user, p_table, 'INSERT')
    UNION ALL
    SELECT 'UPDATE'::TEXT, has_table_privilege(p_user, p_table, 'UPDATE')
    UNION ALL
    SELECT 'DELETE'::TEXT, has_table_privilege(p_user, p_table, 'DELETE');
END;
$$ LANGUAGE plpgsql;

-- 使用例
SELECT * FROM check_user_permissions('app_user', 'employees');

---

セキュリティベストプラクティス

最小権限の原則

-- ❌ 悪い例：全権限を付与
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO app_user;

-- ⭕ 良い例：必要最小限の権限のみ
GRANT SELECT, INSERT ON orders TO app_user;
GRANT SELECT ON products TO app_user;

ロールベースの管理

-- 基本ロールを作成
CREATE ROLE base_read;
CREATE ROLE base_write;
CREATE ROLE base_admin;

-- 階層的に権限付与
GRANT SELECT ON ALL TABLES IN SCHEMA public TO base_read;
GRANT base_read TO base_write;  -- 継承
GRANT INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO base_write;
GRANT base_write TO base_admin;  -- 継承
GRANT CREATE ON SCHEMA public TO base_admin;

-- ユーザーにロールを付与
GRANT base_read TO viewer;
GRANT base_write TO operator;
GRANT base_admin TO manager;

---

まとめ：権限管理のプロになろう！

今日マスターした重要ポイント：

✅ \dpでテーブル権限を素早く確認
✅ GRANT/REVOKEで権限を自在に操作
✅ ロールベースで効率的に管理
✅ カラムレベルの細かい制御も可能
✅ RLSで行レベルのセキュリティ実現
✅ 最小権限の原則を常に意識
✅ has_*_privilege関数で詳細チェック

権限管理は最初は複雑に見えますが、基本パターンを理解すれば怖くありません。

「誰が何をできるか」を明確にすることで、セキュアで管理しやすいデータベースが実現できます。

今日から、あなたも権限管理のエキスパート！

---

次のステップにおすすめ：

• PostgreSQLのRLS（Row Level Security）詳細ガイド
• ロールの継承と権限の委譲
• 監査ログで権限使用を追跡する方法