PostgreSQLのグループ確認を完全マスター！ロール管理からGROUP BY集計まで実例で学ぶ完全ガイド

「このユーザーはどのグループに所属してるの？」 「データベースの権限設定がよく分からない…」 「GROUP BYの集計結果を確認したい」

こんな疑問を抱えていませんか？

PostgreSQLでは、ロール（役割）という仕組みでユーザーとグループを管理しています。 また、データの集計ではGROUP BY句を使ってグループ化を行います。

この記事では、PostgreSQLの「グループ」に関するすべてを解説します。 権限管理の確認方法から、効率的なデータ集計まで、実例たっぷりでマスターしましょう！

---

PostgreSQLのロール（グループ）とは？

ユーザーとグループの統一概念

PostgreSQLでは、ユーザーもグループも「ロール」として扱います。 これが他のデータベースとの大きな違いです。

ロールの2つの使い方：

• ログインロール（ユーザー）：データベースにログインできる
• グループロール（グループ）：権限をまとめて管理する

-- ユーザーロール（ログイン可能）
CREATE ROLE tanaka LOGIN PASSWORD 'secret123';

-- グループロール（ログイン不可）
CREATE ROLE developers;

-- ユーザーをグループに追加
GRANT developers TO tanaka;

なぜグループ管理が重要なの？

グループを使うメリット：

1. 権限管理が簡単：グループに権限を付与すれば、メンバー全員に適用
2. メンテナンスが楽：ユーザーの追加・削除が簡単
3. セキュリティ向上：権限の一元管理でミスを防げる

---

ロール（グループ）の確認方法

すべてのロールを確認

基本的な確認コマンド：

-- すべてのロールを表示
SELECT rolname FROM pg_roles;

-- より詳細な情報を表示
SELECT 
    rolname AS ロール名,
    rolsuper AS スーパーユーザー,
    rolinherit AS 継承,
    rolcreaterole AS ロール作成可,
    rolcreatedb AS DB作成可,
    rolcanlogin AS ログイン可,
    rolconnlimit AS 接続数制限,
    rolvaliduntil AS 有効期限
FROM pg_roles
ORDER BY rolname;

psqlコマンドでの確認：

-- psqlのメタコマンド
\du     -- ユーザー/ロール一覧
\du+    -- 詳細情報付き

特定ユーザーの所属グループを確認

メンバーシップの確認：

-- 特定ユーザーが所属するグループを確認
SELECT 
    r1.rolname AS ユーザー名,
    r2.rolname AS 所属グループ
FROM pg_auth_members m
JOIN pg_roles r1 ON m.member = r1.oid
JOIN pg_roles r2 ON m.roleid = r2.oid
WHERE r1.rolname = 'tanaka'
ORDER BY r2.rolname;

-- より見やすい形式で表示
WITH RECURSIVE cte AS (
    -- 直接のメンバーシップ
    SELECT 
        oid,
        rolname AS role_name,
        0 AS level
    FROM pg_roles
    WHERE rolname = 'tanaka'
    
    UNION ALL
    
    -- 継承されたロール
    SELECT 
        r.oid,
        r.rolname,
        cte.level + 1
    FROM cte
    JOIN pg_auth_members m ON cte.oid = m.member
    JOIN pg_roles r ON m.roleid = r.oid
)
SELECT 
    level AS 階層,
    role_name AS ロール名
FROM cte
WHERE level > 0
ORDER BY level, role_name;

グループのメンバー一覧を確認

特定グループに所属するユーザー一覧：

-- developersグループのメンバーを確認
SELECT 
    r2.rolname AS グループ名,
    r1.rolname AS メンバー名,
    m.admin_option AS 管理権限
FROM pg_auth_members m
JOIN pg_roles r1 ON m.member = r1.oid
JOIN pg_roles r2 ON m.roleid = r2.oid
WHERE r2.rolname = 'developers'
ORDER BY r1.rolname;

-- 全グループとメンバーの一覧
SELECT 
    r2.rolname AS グループ,
    STRING_AGG(r1.rolname, ', ' ORDER BY r1.rolname) AS メンバー
FROM pg_auth_members m
JOIN pg_roles r1 ON m.member = r1.oid
JOIN pg_roles r2 ON m.roleid = r2.oid
GROUP BY r2.rolname
ORDER BY r2.rolname;

---

権限の確認方法

データベース権限の確認

-- データベースレベルの権限確認
SELECT 
    datname AS データベース名,
    datacl AS アクセス権限
FROM pg_database
WHERE datname NOT IN ('template0', 'template1')
ORDER BY datname;

-- 見やすく整形した権限表示
SELECT 
    d.datname AS データベース,
    r.rolname AS ロール,
    CASE 
        WHEN has_database_privilege(r.rolname, d.datname, 'CREATE') THEN '○' 
        ELSE '×' 
    END AS CREATE権限,
    CASE 
        WHEN has_database_privilege(r.rolname, d.datname, 'CONNECT') THEN '○' 
        ELSE '×' 
    END AS CONNECT権限,
    CASE 
        WHEN has_database_privilege(r.rolname, d.datname, 'TEMP') THEN '○' 
        ELSE '×' 
    END AS TEMP権限
FROM pg_database d
CROSS JOIN pg_roles r
WHERE d.datname = current_database()
  AND r.rolname NOT LIKE 'pg_%'
ORDER BY r.rolname;

スキーマ権限の確認

-- スキーマレベルの権限確認
SELECT 
    nspname AS スキーマ名,
    nspacl AS アクセス権限
FROM pg_namespace
WHERE nspname NOT LIKE 'pg_%'
  AND nspname != 'information_schema'
ORDER BY nspname;

-- 特定ユーザーのスキーマ権限を確認
SELECT 
    n.nspname AS スキーマ,
    r.rolname AS ロール,
    CASE 
        WHEN has_schema_privilege(r.rolname, n.nspname, 'USAGE') THEN '○' 
        ELSE '×' 
    END AS USAGE権限,
    CASE 
        WHEN has_schema_privilege(r.rolname, n.nspname, 'CREATE') THEN '○' 
        ELSE '×' 
    END AS CREATE権限
FROM pg_namespace n
CROSS JOIN pg_roles r
WHERE n.nspname = 'public'
  AND r.rolname = 'tanaka';

テーブル権限の確認

-- テーブルレベルの権限確認
SELECT 
    schemaname AS スキーマ,
    tablename AS テーブル,
    tableowner AS 所有者,
    privileges AS 権限詳細
FROM information_schema.table_privileges
WHERE grantee = 'tanaka'
ORDER BY schemaname, tablename;

-- より詳細な権限マトリックス
SELECT 
    t.schemaname AS スキーマ,
    t.tablename AS テーブル,
    r.rolname AS ロール,
    CASE WHEN has_table_privilege(r.rolname, t.schemaname||'.'||t.tablename, 'SELECT') THEN '○' ELSE '×' END AS SELECT,
    CASE WHEN has_table_privilege(r.rolname, t.schemaname||'.'||t.tablename, 'INSERT') THEN '○' ELSE '×' END AS INSERT,
    CASE WHEN has_table_privilege(r.rolname, t.schemaname||'.'||t.tablename, 'UPDATE') THEN '○' ELSE '×' END AS UPDATE,
    CASE WHEN has_table_privilege(r.rolname, t.schemaname||'.'||t.tablename, 'DELETE') THEN '○' ELSE '×' END AS DELETE
FROM pg_tables t
CROSS JOIN pg_roles r
WHERE t.schemaname = 'public'
  AND r.rolname IN ('tanaka', 'developers')
ORDER BY t.tablename, r.rolname;

---

グループの作成と管理

グループロールの作成

-- 基本的なグループ作成
CREATE ROLE developers;
CREATE ROLE managers;
CREATE ROLE readonly_users;

-- 属性付きグループ作成
CREATE ROLE senior_developers
    INHERIT         -- メンバーに権限を継承
    NOCREATEDB      -- DB作成不可
    NOCREATEROLE    -- ロール作成不可
    NOLOGIN;        -- ログイン不可（グループなので）

ユーザーをグループに追加・削除

-- グループにユーザーを追加
GRANT developers TO tanaka;
GRANT managers TO suzuki;

-- 管理権限付きで追加（グループの管理ができる）
GRANT developers TO yamada WITH ADMIN OPTION;

-- グループからユーザーを削除
REVOKE developers FROM tanaka;

-- 複数ユーザーを一度に追加
GRANT readonly_users TO tanaka, suzuki, yamada;

グループへの権限付与

-- データベース権限をグループに付与
GRANT CONNECT ON DATABASE mydb TO developers;

-- スキーマ権限をグループに付与
GRANT USAGE, CREATE ON SCHEMA public TO developers;

-- テーブル権限をグループに付与
GRANT SELECT, INSERT, UPDATE ON ALL TABLES IN SCHEMA public TO developers;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_users;

-- 今後作成されるテーブルへのデフォルト権限
ALTER DEFAULT PRIVILEGES IN SCHEMA public 
    GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO developers;

ALTER DEFAULT PRIVILEGES IN SCHEMA public 
    GRANT SELECT ON TABLES TO readonly_users;

---

GROUP BY句でのデータ集計確認

基本的なGROUP BY

-- 部署ごとの人数を確認
SELECT 
    department AS 部署,
    COUNT(*) AS 人数
FROM employees
GROUP BY department
ORDER BY COUNT(*) DESC;

-- 年月ごとの売上を確認
SELECT 
    TO_CHAR(sale_date, 'YYYY-MM') AS 年月,
    SUM(amount) AS 売上合計,
    COUNT(*) AS 取引数,
    AVG(amount) AS 平均売上
FROM sales
GROUP BY TO_CHAR(sale_date, 'YYYY-MM')
ORDER BY 年月;

GROUP BYの結果を確認する便利な方法

-- HAVING句で条件付き確認
SELECT 
    category AS カテゴリ,
    COUNT(*) AS 商品数,
    AVG(price) AS 平均価格
FROM products
GROUP BY category
HAVING COUNT(*) >= 5  -- 5個以上の商品があるカテゴリのみ
ORDER BY AVG(price) DESC;

-- ROLLUP で小計・合計も確認
SELECT 
    COALESCE(department, '【全社合計】') AS 部署,
    COALESCE(team, '小計') AS チーム,
    COUNT(*) AS 人数,
    AVG(salary) AS 平均給与
FROM employees
GROUP BY ROLLUP(department, team)
ORDER BY department NULLS LAST, team NULLS LAST;

-- GROUPING SETS で複数の集計を一度に確認
SELECT 
    department,
    position,
    gender,
    COUNT(*) AS 人数,
    AVG(salary) AS 平均給与
FROM employees
GROUP BY GROUPING SETS (
    (department),
    (position),
    (gender),
    (department, position),
    ()  -- 全体合計
)
ORDER BY department, position, gender;

ウィンドウ関数でグループ内順位を確認

-- 部署内での給与ランキングを確認
SELECT 
    department AS 部署,
    name AS 氏名,
    salary AS 給与,
    RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS 部署内順位,
    AVG(salary) OVER (PARTITION BY department) AS 部署平均
FROM employees
ORDER BY department, 部署内順位;

-- 累計や割合も確認
SELECT 
    category AS カテゴリ,
    product_name AS 商品名,
    sales AS 売上,
    SUM(sales) OVER (PARTITION BY category) AS カテゴリ合計,
    ROUND(sales * 100.0 / SUM(sales) OVER (PARTITION BY category), 2) AS 構成比,
    SUM(sales) OVER (PARTITION BY category ORDER BY sales DESC) AS 累計売上
FROM product_sales
ORDER BY category, sales DESC;

---

トラブルシューティング

問題1：グループ権限が反映されない

症状： グループに権限を付与したのに、メンバーが操作できない

解決法：

-- INHERITを確認
SELECT rolname, rolinherit FROM pg_roles WHERE rolname = 'tanaka';

-- INHERITを有効にする
ALTER ROLE tanaka INHERIT;

-- セッションを更新（再接続が必要な場合も）
SET ROLE developers;  -- 手動でロールを切り替え
RESET ROLE;          -- 元に戻す

問題2：権限の確認が複雑

便利なビューを作成：

-- 権限確認用のビューを作成
CREATE VIEW v_user_permissions AS
WITH RECURSIVE role_tree AS (
    -- 基本ロール
    SELECT 
        r.oid,
        r.rolname,
        r.rolname AS inherited_from,
        0 AS level
    FROM pg_roles r
    WHERE r.rolcanlogin = true
    
    UNION ALL
    
    -- 継承されたロール
    SELECT 
        rt.oid,
        rt.rolname,
        r.rolname,
        rt.level + 1
    FROM role_tree rt
    JOIN pg_auth_members m ON rt.oid = m.member
    JOIN pg_roles r ON m.roleid = r.oid
)
SELECT DISTINCT
    rolname AS ユーザー,
    inherited_from AS 権限元,
    level AS 継承レベル
FROM role_tree
ORDER BY rolname, level, inherited_from;

-- 使用例
SELECT * FROM v_user_permissions WHERE ユーザー = 'tanaka';

問題3：GROUP BYエラー

「column must appear in the GROUP BY clause」エラー：

-- エラーになる例
SELECT department, name, COUNT(*)
FROM employees
GROUP BY department;  -- nameがGROUP BYにない

-- 解決法1：GROUP BYに追加
SELECT department, name, COUNT(*)
FROM employees
GROUP BY department, name;

-- 解決法2：集計関数を使用
SELECT 
    department, 
    STRING_AGG(name, ', ') AS メンバー,
    COUNT(*) AS 人数
FROM employees
GROUP BY department;

-- 解決法3：サブクエリを使用
SELECT 
    e.*,
    d.dept_count
FROM employees e
JOIN (
    SELECT department, COUNT(*) AS dept_count
    FROM employees
    GROUP BY department
) d ON e.department = d.department;

---

ベストプラクティス

ロール設計の推奨パターン

-- 1. 機能別グループを作成
CREATE ROLE app_readers;      -- 読み取り専用
CREATE ROLE app_writers;      -- 読み書き可能
CREATE ROLE app_admins;       -- 管理者

-- 2. 階層的な権限付与
GRANT app_readers TO app_writers;  -- writersはreadersの権限も持つ
GRANT app_writers TO app_admins;   -- adminsはすべての権限を持つ

-- 3. 権限を付与
GRANT SELECT ON ALL TABLES IN SCHEMA public TO app_readers;
GRANT INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_writers;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO app_admins;

-- 4. ユーザーを適切なグループに配置
GRANT app_readers TO intern_user;
GRANT app_writers TO developer_user;
GRANT app_admins TO lead_developer;

定期的な権限監査

-- 権限監査用のクエリ
CREATE OR REPLACE FUNCTION audit_permissions()
RETURNS TABLE (
    check_type TEXT,
    detail TEXT,
    status TEXT
) AS $$
BEGIN
    -- スーパーユーザーの確認
    RETURN QUERY
    SELECT 
        'スーパーユーザー' AS check_type,
        rolname AS detail,
        'WARNING' AS status
    FROM pg_roles
    WHERE rolsuper = true
      AND rolname NOT IN ('postgres');
    
    -- 期限切れユーザーの確認
    RETURN QUERY
    SELECT 
        '期限切れユーザー',
        rolname,
        'ERROR'
    FROM pg_roles
    WHERE rolvaliduntil < CURRENT_TIMESTAMP;
    
    -- 未使用グループの確認
    RETURN QUERY
    SELECT 
        '空のグループ',
        r.rolname,
        'INFO'
    FROM pg_roles r
    LEFT JOIN pg_auth_members m ON r.oid = m.roleid
    WHERE r.rolcanlogin = false
      AND m.member IS NULL;
END;
$$ LANGUAGE plpgsql;

-- 実行
SELECT * FROM audit_permissions();

---

まとめ：グループ管理で安全なデータベース運用を

PostgreSQLのグループ（ロール）管理について、重要なポイントをまとめます：

ロール確認の基本：

1. 👥 \du コマンド – 簡単にロール一覧を表示
2. 🔍 pg_roles – 詳細な情報を取得
3. 🔗 pg_auth_members – メンバーシップを確認

権限確認のポイント：

• has_*_privilege関数で権限チェック
• 階層的な権限継承を理解
• 定期的な権限監査を実施

GROUP BY集計の活用：

• 基本的な集計から高度な分析まで
• ROLLUP、GROUPING SETSで多次元分析
• ウィンドウ関数で詳細な順位付け

ベストプラクティス：

• 機能別のグループ設計
• 最小権限の原則
• 定期的な監査と見直し

適切なグループ管理は、セキュリティと運用効率の両立につながります。 この記事で紹介した確認方法を活用して、安全で効率的なデータベース運用を実現しましょう！