PostgreSQL 全テーブル権限管理の完全ガイド：効率的な一括付与とセキュリティ管理

「新しいユーザーに全テーブルの参照権限を付与したい」 「開発環境で全テーブルへのアクセス権限を一括設定したい」 「既存の権限設定を確認して、セキュリティを強化したい」

PostgreSQLで複数のテーブルを管理していると、こんな権限管理のニーズが頻繁に発生しませんか？

全テーブルへの権限を効率的に管理することは、セキュリティを保ちながら開発効率を向上させる鍵となります。一つずつテーブルに権限を付与するのは非効率的で、ミスも起こりやすくなります。

この記事では、PostgreSQLで全テーブルに対する権限を一括管理する方法から、セキュリティベストプラクティス、トラブルシューティングまで、実例を交えながら徹底的に解説していきます。

---

🔐 権限管理の基本概念

PostgreSQLの権限体系

権限の種類と意味

主要な権限一覧：

-- テーブルに対する権限の種類
SELECT：データの参照
INSERT：データの挿入
UPDATE：データの更新
DELETE：データの削除
TRUNCATE：テーブルの全データ削除
REFERENCES：外部キー制約の作成
TRIGGER：トリガーの作成
ALL PRIVILEGES：すべての権限

-- 実際の付与例
GRANT SELECT ON table_name TO user_name;              -- 参照のみ
GRANT SELECT, INSERT ON table_name TO user_name;      -- 参照と挿入
GRANT ALL PRIVILEGES ON table_name TO user_name;      -- すべての権限

スキーマとテーブルの関係

階層構造の理解

-- PostgreSQLの階層構造
データベース
  └── スキーマ (public, schema1, schema2...)
        └── テーブル (users, orders, products...)
              └── カラム (id, name, email...)

-- スキーマレベルの権限
GRANT USAGE ON SCHEMA public TO user_name;  -- スキーマへのアクセス権
GRANT CREATE ON SCHEMA public TO user_name; -- オブジェクト作成権限

---

🚀 全テーブルへの権限一括付与

既存テーブルへの一括付与

基本的な一括付与コマンド

-- publicスキーマの全テーブルに対してSELECT権限を付与
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_user;

-- 複数の権限を一括付与
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_user;

-- 全権限を付与（注意：強力すぎる）
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO admin_user;

-- 複数スキーマに対して実行
DO $$
DECLARE
    schema_name TEXT;
BEGIN
    FOR schema_name IN 
        SELECT nspname FROM pg_namespace 
        WHERE nspname NOT IN ('pg_catalog', 'information_schema', 'pg_toast')
    LOOP
        EXECUTE format('GRANT SELECT ON ALL TABLES IN SCHEMA %I TO readonly_user', schema_name);
    END LOOP;
END $$;

将来作成されるテーブルへの自動付与

DEFAULT PRIVILEGES の設定

-- 今後作成されるテーブルに自動的に権限を付与
ALTER DEFAULT PRIVILEGES IN SCHEMA public 
GRANT SELECT ON TABLES TO readonly_user;

-- 特定ユーザーが作成したテーブルのみ対象
ALTER DEFAULT PRIVILEGES FOR USER table_creator IN SCHEMA public 
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_user;

-- 複数の権限を組み合わせ
ALTER DEFAULT PRIVILEGES IN SCHEMA public 
GRANT SELECT ON TABLES TO readonly_user;

ALTER DEFAULT PRIVILEGES IN SCHEMA public 
GRANT ALL PRIVILEGES ON TABLES TO app_user;

-- 設定を確認
SELECT 
    defaclrole::regrole AS grantor,
    defaclnamespace::regnamespace AS schema,
    defaclobjtype AS object_type,
    defaclacl AS privileges
FROM pg_default_acl
WHERE defaclnamespace = 'public'::regnamespace;

ロール（グループ）を使った権限管理

効率的な権限管理戦略

-- ロールの作成と権限設定
-- 読み取り専用ロール
CREATE ROLE readonly_role;
GRANT USAGE ON SCHEMA public TO readonly_role;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_role;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readonly_role;

-- アプリケーションロール
CREATE ROLE 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;
ALTER DEFAULT PRIVILEGES IN SCHEMA public 
    GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_role;
ALTER DEFAULT PRIVILEGES IN SCHEMA public 
    GRANT USAGE, SELECT ON SEQUENCES TO app_role;

-- 管理者ロール
CREATE ROLE admin_role;
GRANT ALL PRIVILEGES ON SCHEMA public TO admin_role;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO admin_role;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO admin_role;
GRANT ALL PRIVILEGES ON ALL FUNCTIONS IN SCHEMA public TO admin_role;

-- ユーザーをロールに追加
GRANT readonly_role TO john_doe;
GRANT app_role TO app_service;
GRANT admin_role TO database_admin;

-- ログイン権限付きユーザーの作成
CREATE USER john_doe WITH PASSWORD 'secure_password';
CREATE USER app_service WITH PASSWORD 'app_password';
CREATE USER database_admin WITH PASSWORD 'admin_password';

---

🔍 権限の確認と監査

現在の権限設定を確認

テーブル権限の一覧表示

-- 特定テーブルの権限を確認
SELECT 
    grantee,
    table_schema,
    table_name,
    privilege_type
FROM information_schema.table_privileges
WHERE table_schema = 'public'
  AND table_name = 'users'
ORDER BY grantee, privilege_type;

-- 全テーブルの権限を確認
SELECT 
    grantee,
    table_schema,
    table_name,
    STRING_AGG(privilege_type, ', ' ORDER BY privilege_type) AS privileges
FROM information_schema.table_privileges
WHERE table_schema NOT IN ('pg_catalog', 'information_schema')
GROUP BY grantee, table_schema, table_name
ORDER BY table_schema, table_name, grantee;

-- 特定ユーザーの権限を確認
SELECT 
    table_schema,
    table_name,
    STRING_AGG(privilege_type, ', ' ORDER BY privilege_type) AS privileges
FROM information_schema.table_privileges
WHERE grantee = 'app_user'
  AND table_schema = 'public'
GROUP BY table_schema, table_name
ORDER BY table_name;

権限マトリックスの生成

包括的な権限レポート

-- 権限マトリックスビュー
CREATE OR REPLACE VIEW v_permission_matrix AS
WITH user_privileges AS (
    SELECT 
        grantee,
        table_schema,
        table_name,
        privilege_type
    FROM information_schema.table_privileges
    WHERE table_schema NOT IN ('pg_catalog', 'information_schema')
)
SELECT 
    table_schema,
    table_name,
    grantee,
    MAX(CASE WHEN privilege_type = 'SELECT' THEN '✓' ELSE '' END) AS "SELECT",
    MAX(CASE WHEN privilege_type = 'INSERT' THEN '✓' ELSE '' END) AS "INSERT",
    MAX(CASE WHEN privilege_type = 'UPDATE' THEN '✓' ELSE '' END) AS "UPDATE",
    MAX(CASE WHEN privilege_type = 'DELETE' THEN '✓' ELSE '' END) AS "DELETE",
    MAX(CASE WHEN privilege_type = 'TRUNCATE' THEN '✓' ELSE '' END) AS "TRUNCATE",
    MAX(CASE WHEN privilege_type = 'REFERENCES' THEN '✓' ELSE '' END) AS "REFERENCES",
    MAX(CASE WHEN privilege_type = 'TRIGGER' THEN '✓' ELSE '' END) AS "TRIGGER"
FROM user_privileges
GROUP BY table_schema, table_name, grantee
ORDER BY table_schema, table_name, grantee;

-- 使用例
SELECT * FROM v_permission_matrix 
WHERE table_schema = 'public' 
  AND grantee != 'postgres';

psqlメタコマンドでの確認

-- テーブルの権限を確認
\dp users
-- または
\z users

-- 実行結果例
                                Access privileges
 Schema |  Name  | Type  |     Access privileges     | Column privileges | Policies 
--------+--------+-------+---------------------------+-------------------+----------
 public | users  | table | postgres=arwdDxt/postgres+|                   | 
        |        |       | readonly_user=r/postgres  +|                   |
        |        |       | app_user=arwd/postgres    |                   |

---

🔄 権限の取り消し（REVOKE）

全テーブルからの権限取り消し

一括取り消しの実行

-- 特定ユーザーから全権限を取り消し
REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA public FROM unwanted_user;

-- 特定の権限のみ取り消し
REVOKE INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public FROM readonly_user;

-- デフォルト権限も取り消し
ALTER DEFAULT PRIVILEGES IN SCHEMA public 
REVOKE ALL ON TABLES FROM unwanted_user;

-- CASCADEオプションで依存する権限も取り消し
REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA public FROM unwanted_user CASCADE;

段階的な権限削減

-- 権限を段階的に削減する関数
CREATE OR REPLACE FUNCTION reduce_user_privileges(p_username TEXT)
RETURNS VOID AS $$
DECLARE
    v_table RECORD;
BEGIN
    -- まず危険な権限から取り消し
    FOR v_table IN 
        SELECT schemaname, tablename 
        FROM pg_tables 
        WHERE schemaname = 'public'
    LOOP
        -- DELETE権限を取り消し
        EXECUTE format('REVOKE DELETE ON %I.%I FROM %I', 
                      v_table.schemaname, v_table.tablename, p_username);
        -- TRUNCATE権限を取り消し
        EXECUTE format('REVOKE TRUNCATE ON %I.%I FROM %I', 
                      v_table.schemaname, v_table.tablename, p_username);
    END LOOP;
    
    RAISE NOTICE 'Dangerous privileges revoked from %', p_username;
END;
$$ LANGUAGE plpgsql;

-- 使用例
SELECT reduce_user_privileges('suspicious_user');

---

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

最小権限の原則

役割別の推奨権限設定

-- 1. 読み取り専用ユーザー（レポート、分析用）
CREATE USER report_user WITH PASSWORD 'report_pass';
GRANT USAGE ON SCHEMA public TO report_user;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO report_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO report_user;

-- 2. アプリケーションユーザー（CRUD操作）
CREATE USER app_user WITH PASSWORD 'app_pass';
GRANT USAGE ON SCHEMA public TO app_user;
GRANT SELECT, INSERT, UPDATE ON ALL TABLES IN SCHEMA public TO app_user;
-- DELETEは論理削除を使用するため付与しない
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO app_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA public 
    GRANT SELECT, INSERT, UPDATE ON TABLES TO app_user;

-- 3. バッチ処理ユーザー（特定時間のみ有効）
CREATE USER batch_user WITH PASSWORD 'batch_pass' VALID UNTIL '2024-12-31';
GRANT USAGE ON SCHEMA public TO batch_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO batch_user;

-- 4. 開発者用ユーザー（開発環境のみ）
CREATE USER dev_user WITH PASSWORD 'dev_pass';
-- 開発環境のみで使用
GRANT CREATE ON SCHEMA public TO dev_user;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO dev_user;

権限の定期監査

-- 権限監査レポートの生成
CREATE OR REPLACE FUNCTION audit_permissions()
RETURNS TABLE(
    finding_type TEXT,
    severity TEXT,
    username TEXT,
    details TEXT
) AS $$
BEGIN
    -- 過剰な権限を持つユーザーを検出
    RETURN QUERY
    SELECT 
        'Excessive Privileges'::TEXT,
        'HIGH'::TEXT,
        grantee::TEXT,
        'User has ALL PRIVILEGES on ' || COUNT(*)::TEXT || ' tables'
    FROM information_schema.table_privileges
    WHERE privilege_type = 'DELETE'
      AND grantee NOT IN ('postgres', 'owner_user')
    GROUP BY grantee
    HAVING COUNT(*) > 10;
    
    -- publicロールへの権限付与をチェック
    RETURN QUERY
    SELECT 
        'Public Access'::TEXT,
        'CRITICAL'::TEXT,
        'PUBLIC'::TEXT,
        'PUBLIC role has access to ' || table_name
    FROM information_schema.table_privileges
    WHERE grantee = 'PUBLIC'
      AND table_schema = 'public';
    
    -- 長期間未使用のユーザー
    RETURN QUERY
    SELECT 
        'Inactive User'::TEXT,
        'MEDIUM'::TEXT,
        usename::TEXT,
        'Last login: ' || COALESCE(last_login::TEXT, 'Never')
    FROM pg_user
    LEFT JOIN (
        SELECT usename AS username, MAX(backend_start) AS last_login
        FROM pg_stat_activity
        GROUP BY usename
    ) activity ON pg_user.usename = activity.username
    WHERE usename NOT IN ('postgres')
      AND (last_login IS NULL OR last_login < NOW() - INTERVAL '90 days');
END;
$$ LANGUAGE plpgsql;

-- 監査レポートの実行
SELECT * FROM audit_permissions() ORDER BY severity;

---

🔧 高度な権限管理テクニック

行レベルセキュリティ（RLS）との組み合わせ

-- 行レベルセキュリティの有効化
ALTER TABLE sensitive_data ENABLE ROW LEVEL SECURITY;

-- ポリシーの作成
CREATE POLICY user_own_data ON sensitive_data
    FOR ALL
    TO app_user
    USING (owner_id = current_user);

-- 管理者は全行アクセス可能
CREATE POLICY admin_all_access ON sensitive_data
    FOR ALL
    TO admin_role
    USING (true);

-- テーブル権限と組み合わせ
GRANT SELECT, INSERT, UPDATE ON sensitive_data TO app_user;
GRANT ALL PRIVILEGES ON sensitive_data TO admin_role;

動的な権限管理

-- 条件に基づいて権限を付与する関数
CREATE OR REPLACE FUNCTION grant_conditional_access(
    p_username TEXT,
    p_schema TEXT DEFAULT 'public',
    p_table_pattern TEXT DEFAULT '%',
    p_permissions TEXT[] DEFAULT ARRAY['SELECT']
)
RETURNS VOID AS $$
DECLARE
    v_table RECORD;
    v_grant_sql TEXT;
    v_permission TEXT;
BEGIN
    FOR v_table IN 
        SELECT tablename 
        FROM pg_tables 
        WHERE schemaname = p_schema 
          AND tablename LIKE p_table_pattern
    LOOP
        FOREACH v_permission IN ARRAY p_permissions
        LOOP
            v_grant_sql := format('GRANT %s ON %I.%I TO %I',
                                 v_permission, p_schema, v_table.tablename, p_username);
            EXECUTE v_grant_sql;
            RAISE NOTICE 'Executed: %', v_grant_sql;
        END LOOP;
    END LOOP;
END;
$$ LANGUAGE plpgsql;

-- 使用例：'user_'で始まるテーブルにのみアクセス権を付与
SELECT grant_conditional_access('limited_user', 'public', 'user_%', ARRAY['SELECT', 'INSERT']);

一時的な権限付与

-- 期限付き権限管理システム
CREATE TABLE temp_permissions (
    id SERIAL PRIMARY KEY,
    username TEXT NOT NULL,
    table_name TEXT NOT NULL,
    permissions TEXT[],
    granted_at TIMESTAMP DEFAULT NOW(),
    expires_at TIMESTAMP NOT NULL,
    revoked BOOLEAN DEFAULT FALSE
);

-- 一時権限を付与する関数
CREATE OR REPLACE FUNCTION grant_temporary_access(
    p_username TEXT,
    p_table TEXT,
    p_permissions TEXT[],
    p_duration INTERVAL
)
RETURNS VOID AS $$
DECLARE
    v_permission TEXT;
BEGIN
    -- 権限を付与
    FOREACH v_permission IN ARRAY p_permissions
    LOOP
        EXECUTE format('GRANT %s ON %I TO %I', v_permission, p_table, p_username);
    END LOOP;
    
    -- 記録を保存
    INSERT INTO temp_permissions (username, table_name, permissions, expires_at)
    VALUES (p_username, p_table, p_permissions, NOW() + p_duration);
    
    RAISE NOTICE 'Temporary access granted to % until %', p_username, NOW() + p_duration;
END;
$$ LANGUAGE plpgsql;

-- 期限切れ権限を取り消すジョブ
CREATE OR REPLACE FUNCTION revoke_expired_permissions()
RETURNS VOID AS $$
DECLARE
    v_record RECORD;
    v_permission TEXT;
BEGIN
    FOR v_record IN 
        SELECT * FROM temp_permissions 
        WHERE expires_at < NOW() 
          AND NOT revoked
    LOOP
        FOREACH v_permission IN ARRAY v_record.permissions
        LOOP
            EXECUTE format('REVOKE %s ON %I FROM %I', 
                          v_permission, v_record.table_name, v_record.username);
        END LOOP;
        
        UPDATE temp_permissions SET revoked = TRUE WHERE id = v_record.id;
        RAISE NOTICE 'Revoked expired permissions for %', v_record.username;
    END LOOP;
END;
$$ LANGUAGE plpgsql;

---

📊 権限管理の自動化

バックアップとリストア

-- 権限設定のバックアップ
CREATE OR REPLACE FUNCTION backup_permissions()
RETURNS TABLE(grant_statement TEXT) AS $$
BEGIN
    RETURN QUERY
    SELECT 
        format('GRANT %s ON %s.%s TO %s;',
               STRING_AGG(privilege_type, ', ' ORDER BY privilege_type),
               table_schema,
               table_name,
               grantee)
    FROM information_schema.table_privileges
    WHERE table_schema NOT IN ('pg_catalog', 'information_schema')
    GROUP BY grantee, table_schema, table_name
    ORDER BY table_schema, table_name, grantee;
END;
$$ LANGUAGE plpgsql;

-- バックアップをファイルに保存
COPY (SELECT * FROM backup_permissions()) TO '/tmp/permissions_backup.sql';

-- リストア用スクリプトの生成
SELECT string_agg(grant_statement, E'\n')
FROM backup_permissions();

権限テンプレート

-- 権限テンプレートテーブル
CREATE TABLE permission_templates (
    template_name TEXT PRIMARY KEY,
    description TEXT,
    schema_name TEXT DEFAULT 'public',
    table_pattern TEXT DEFAULT '%',
    permissions TEXT[],
    created_at TIMESTAMP DEFAULT NOW()
);

-- テンプレートの登録
INSERT INTO permission_templates (template_name, description, permissions) VALUES
    ('readonly', '読み取り専用アクセス', ARRAY['SELECT']),
    ('dataentry', 'データ入力用', ARRAY['SELECT', 'INSERT']),
    ('editor', '編集者用', ARRAY['SELECT', 'INSERT', 'UPDATE']),
    ('admin', '管理者用', ARRAY['SELECT', 'INSERT', 'UPDATE', 'DELETE', 'TRUNCATE']);

-- テンプレートを適用する関数
CREATE OR REPLACE FUNCTION apply_permission_template(
    p_username TEXT,
    p_template_name TEXT
)
RETURNS VOID AS $$
DECLARE
    v_template RECORD;
    v_table RECORD;
    v_permission TEXT;
BEGIN
    SELECT * INTO v_template 
    FROM permission_templates 
    WHERE template_name = p_template_name;
    
    IF NOT FOUND THEN
        RAISE EXCEPTION 'Template % not found', p_template_name;
    END IF;
    
    -- スキーマ権限を付与
    EXECUTE format('GRANT USAGE ON SCHEMA %I TO %I', v_template.schema_name, p_username);
    
    -- テーブル権限を付与
    FOR v_table IN 
        SELECT tablename 
        FROM pg_tables 
        WHERE schemaname = v_template.schema_name
          AND tablename LIKE v_template.table_pattern
    LOOP
        FOREACH v_permission IN ARRAY v_template.permissions
        LOOP
            EXECUTE format('GRANT %s ON %I.%I TO %I', 
                          v_permission, v_template.schema_name, v_table.tablename, p_username);
        END LOOP;
    END LOOP;
    
    RAISE NOTICE 'Template % applied to user %', p_template_name, p_username;
END;
$$ LANGUAGE plpgsql;

-- 使用例
SELECT apply_permission_template('new_developer', 'editor');

---

⚠️ トラブルシューティング

よくある問題と解決策

問題1：権限を付与したのにアクセスできない

-- チェックリスト
-- 1. スキーマへのUSAGE権限を確認
SELECT has_schema_privilege('user_name', 'public', 'USAGE');

-- 2. 継承関係を確認
SELECT r.rolname, r.rolinherit, r.rolcanlogin,
       ARRAY_AGG(m.rolname) AS member_of
FROM pg_roles r
LEFT JOIN pg_auth_members am ON r.oid = am.member
LEFT JOIN pg_roles m ON am.roleid = m.oid
WHERE r.rolname = 'user_name'
GROUP BY r.rolname, r.rolinherit, r.rolcanlogin;

-- 3. 実効権限を確認
SELECT has_table_privilege('user_name', 'schema.table', 'SELECT');

-- 解決策：必要な権限を再付与
GRANT USAGE ON SCHEMA public TO user_name;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO user_name;

問題2：DEFAULT PRIVILEGESが効かない

-- 原因：既に存在するテーブルには適用されない
-- 解決策：既存テーブルにも明示的に権限付与
DO $$
DECLARE
    v_schema TEXT := 'public';
    v_user TEXT := 'app_user';
BEGIN
    -- 既存テーブルに権限付与
    EXECUTE format('GRANT SELECT ON ALL TABLES IN SCHEMA %I TO %I', v_schema, v_user);
    
    -- 将来のテーブルにも権限付与
    EXECUTE format('ALTER DEFAULT PRIVILEGES IN SCHEMA %I GRANT SELECT ON TABLES TO %I', 
                  v_schema, v_user);
END $$;

問題3：権限の取り消しが効かない

-- 権限の継承元を確認
WITH RECURSIVE role_tree AS (
    SELECT 
        r.rolname,
        r.oid,
        '{}'::TEXT[] AS path
    FROM pg_roles r
    WHERE r.rolname = 'target_user'
    
    UNION ALL
    
    SELECT 
        r.rolname,
        r.oid,
        rt.path || rt.rolname
    FROM pg_roles r
    JOIN pg_auth_members m ON r.oid = m.roleid
    JOIN role_tree rt ON rt.oid = m.member
)
SELECT * FROM role_tree;

-- 完全な権限取り消し
REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA public FROM target_user CASCADE;
REVOKE ALL PRIVILEGES ON SCHEMA public FROM target_user CASCADE;
REVOKE USAGE ON SCHEMA public FROM target_user CASCADE;

---

📚 まとめ：PostgreSQL権限管理のマスターになる

PostgreSQL全テーブル権限管理の重要ポイント：

✅ GRANT ALL TABLES IN SCHEMAで一括権限付与
✅ ALTER DEFAULT PRIVILEGESで将来のテーブルも自動設定
✅ ロールベースで効率的に権限管理
✅ 定期的な権限監査でセキュリティ維持
✅ 最小権限の原則を常に意識

適切な権限管理により、セキュリティと利便性のバランスを保てます。

今すぐ実践すべき3つのアクション：

1. 現在の権限設定を監査して過剰な権限を削除
2. ロールベースの権限管理体系を構築
3. DEFAULT PRIVILEGESを設定して自動化

これらのテクニックで、安全で効率的なデータベース環境を実現しましょう！

---