【PostgreSQL】ロール・権限一覧を確認する

\duコマンドでロール一覧を確認する

PostgreSQLでロール一覧を確認するには、psqlコマンドラインツールの\duコマンドを使う。

\du
                               List of roles
   Role name   |                         Attributes
---------------+------------------------------------------------------------
 app_admin     | Create role, Create DB
 app_user      |
 postgres      | Superuser, Create role, Create DB, Replication, Bypass RLS
 readonly_user |

Attributes列にはロールに付与された属性が表示される。
Superuser（スーパーユーザー）、Create role（ロール作成権限）、Create DB（データベース作成権限）などがあり、属性を持たないロールは空欄になる。

pg_rolesビューでロール一覧を確認する

pg_rolesシステムカタログをSELECTしても、ロール一覧を確認できる。

SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication
FROM pg_roles
ORDER BY rolname;

pg_rolesには以下のカラムが含まれる。

rolname: ロール名
rolsuper: スーパーユーザーか
rolinherit: 所属するロールの権限を自動的に継承するか
rolcreaterole: ロールを作成できるか
rolcreatedb: データベースを作成できるか
rolcanlogin: ログインできるか（falseの場合はグループロール）
rolreplication: レプリケーション用の接続を開始できるか
rolconnlimit: 同時接続数の上限（-1は無制限）
rolvaliduntil: パスワードの有効期限

pg_rolesにはPostgreSQLがあらかじめ用意しているpg_monitorやpg_read_all_dataなどの定義済みロールも含まれる。
自分で作成したロールだけに絞る場合は、rolnameがpg_から始まるロールを除外する。

SELECT rolname FROM pg_roles WHERE rolname NOT LIKE 'pg\_%' ORDER BY rolname;
    rolname
---------------
 app_admin
 app_user
 postgres
 readonly_user

\dpコマンドでテーブルの権限一覧を確認する

テーブルに対して付与されている権限を確認するには、\dpコマンドを使う。

\dp 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    |                    |

Access privileges列はロール名=権限記号/権限を付与したロール名の形式で表示される。
権限記号の意味は以下のとおり。

r: SELECT
w: UPDATE
a: INSERT
d: DELETE
D: TRUNCATE
x: REFERENCES
t: TRIGGER

例えばreadonly_user=r/postgresは、postgresロールがreadonly_userロールに対してSELECT権限（r）を付与したことを表す。

information_schema.table_privilegesビューで権限一覧を確認する

information_schema.table_privilegesビューを使うと、権限をロールと権限の種類ごとに1行ずつ確認できる。

SELECT grantee, privilege_type
FROM information_schema.table_privileges
WHERE table_name = 'users'
ORDER BY grantee, privilege_type;

    grantee    | privilege_type
---------------+----------------
 app_user      | DELETE
 app_user      | INSERT
 app_user      | SELECT
 app_user      | UPDATE
 postgres      | DELETE
 postgres      | INSERT
 postgres      | REFERENCES
 postgres      | SELECT
 postgres      | TRIGGER
 postgres      | TRUNCATE
 postgres      | UPDATE
 readonly_user | SELECT

\dpの結果と異なり権限記号を読み解く必要がなく、WHERE句で特定のロールや権限に絞り込みやすい点がメリットである。