TECH BLOG
技術ブログ

ARTICLE

  • 2024-10-30 PostgreSQL

    PostgreSQLでロールに権限およびロールを付与するGRANTについて

今回も引き続きロールに関する記事となります。
今回は、ロールに権限を付与する方法についての説明となります。以前の記事と内容が重複している部分もありますが、実際に SQL を実行して挙動を確認していきます。

環境

検証を行った環境は下記となります。

  • RockeyLinux 9.4
  • PostgreSQL 16.4

GRANT コマンド

PostgreSQL でロールに権限を付与するには GRANT コマンドを利用します。GRANT コマンドには、下記 2 種類の利用法が存在します。

  1. ロールに対してアクセス権限を付与する
  2. ロールに対してロールを付与する

上記 2 点について、PostgreSQL 文書の GRANT の説明と比較しながら確認していきます。

【 PostgreSQL 16.4文書 - GRANT - 】
https://www.postgresql.jp/document/16/html/sql-grant.html

1. ロールに対してアクセス権限を付与する

PostgreSQL 文書では「データベースオブジェクトに対するGRANT」という節題で説明されています。
ロールに対して SELECT などの権限を付与することを目的として GRANT コマンドを実行します。例えば、test_role ロールに test_table テーブルに対する SELECT 権限を付与したい場合は、下記のような SQL となります。

-- test_role ロールに test_table テーブルに対する SELECT 権限を付与
postgres=# GRANT SELECT ON test_table TO test_role;
GRANT

すべての権限を付与したい場合は ALL PRIVILEGES を指定します。

-- test_role ロールに test_table テーブルに対する全ての権限を付与
postgres=# GRANT ALL PRIVILEGES ON test_table TO test_role;
GRANT

PUBLIC ロールに対して付与すると、すべてのロールに対して権限を付与したことになります。

-- PUBLIC ロールに test_table テーブルに対する全ての権限を付与
postgres=# GRANT ALL PRIVILEGES ON test_table TO PUBLIC;
GRANT

とはいえ PUBLIC ロールに権限を付与すると影響が大きくなりすぎるため、安易な権限付与は非推奨となります。

2. ロールに対してロールを付与する

PostgreSQL 文書では「ロールに対するGRANT」という節題で説明されています。
ロールに対して別のロールを付与することを目的として GRANT コマンドを実行します。
PostgreSQL 文書内の説明で「ロール内のメンバ資格を1つ以上の他のロールに付与し、」と説明があります。この「メンバ資格」というのは、英文(翻訳前)の PostgreSQL 文書では「membership 」という部分が該当しています。ロールが付与されると、そのロールのメンバーになる(付与元のロールに所属する)ことから、「メンバ資格」という表現になっていると考えられます。
例えば、role_oya と role_ko があり、role_oya に role_ko を所属させたい(role_ko を role_oya のメンバ資格にしたい)場合は、下記のような GRANT 文となります。

-- role_oya を role_ko に付与(role_oya に role_ko を所属)
GRANT role_oya TO role_ko;

role_oya を role_ko に付与したことで、role_ko は role_oya のメンバ資格となりました。その結果、role_ko は role_oya の権限を利用できるようになります。

GRANT のオプション

ロールをロールに付与する場合、SET、INHERIT、ADMIN のオプションを指定できます。INHERIT、ADMIN については CREATE ROLE する際にも指定できるのですが、GRANT で上書きすることができます。この上書きは、ロールが付与されている状況でのみ有効となる設定です(ロール自体の属性を上書きするものではありません)。
また、SET と ADMIN には未指定の場合のデフォルト値も用意されています(SET のデフォルト値は true、ADMIN のデフォルト値は false)。INHERIT が未指定の場合には、付与されるロールの INHERIT 属性が適用されます。

ロールにロールを付与した際の挙動確認

以下で、ロールをロールに付与した場合の挙動を検証用の SQL を実行して確認します。
具体的には、test_role ロールと、test_user ロール(ユーザ)を作成し、それぞれのユーザが statsrepo.snapshot テーブルを参照できるかどうかを確認します。
まずはロールの作成と権限の付与を行います。

-- test_role と test_user を作成
postgres=# CREATE ROLE test_role;
CREATE ROLE
postgres=# CREATE ROLE test_user WITH LOGIN;
CREATE ROLE

-- test_role に statsrepo.snapshot を参照できる権限を付与
postgres=# GRANT USAGE ON SCHEMA statsrepo TO test_role;
GRANT
postgres=# GRANT SELECT ON statsrepo.snapshot TO test_role;
GRANT

これで test_role が statsrepo.snapshot テーブルを参照できるようになりました。

-- test_user で statsrepo.snapshot を SELECT できないことを確認
$ psql -U test_user -d postgres -c "SELECT * FROM statsrepo.snapshot"
ERROR:  permission denied for schema statsrepo
LINE 1: SELECT * FROM statsrepo.snapshot

ただ、test_user に対しては権限付与は行なっていないため、statsrepo.snapshot テーブルの参照はできません。この状態で test_role を test_user に付与します。

-- test_role を test_user に付与
postgres=# GRANT test_role TO test_user;
GRANT ROLE

-- メンバシップ関係(ロールの付与状況)を確認
postgres=# SELECT am.oid, am.roleid, r1.rolname AS rol_name, am.member, r2.rolname AS member_name, am.grantor, r3.rolname AS grantor_name FROM pg_auth_members am INNER JOIN pg_roles r1 ON am.roleid = r1.oid INNER JOIN pg_roles r2 ON am.member = r2.oid INNER JOIN pg_roles r3 ON am.grantor = r3.oid WHERE am.oid = 17140;
  oid  | roleid | rol_name  | member | member_name | grantor | grantor_name 
-------+--------+-----------+--------+-------------+---------+--------------
 17140 |  17133 | test_role |  17134 | test_user   |      10 | postgres
(1 row)

※ 検証していて気がついたのですが、「ロールに対してアクセス権限を付与」した場合は、GRANT コマンドの結果は「GRANT」ですが、「ロールをロールに付与」した場合は、「GRANT ROLE」となっています。
ロールの付与が完了したので、test_user で statsrepo.snapshot を参照できるかを確認します。

-- test_user が statsrepo.snapshot を SELECT できるようになったことを確認
$ psql -U test_user -d postgres -c "SELECT * FROM statsrepo.snapshot"
 snapid | instid |             time              | comment |    exec_time    | snapshot_increase_size | xid_current 
--------+--------+-------------------------------+---------+-----------------+------------------------+-------------
      1 |      1 | 2024-09-03 06:30:00.082054+00 |         | 00:00:00.172181 |                 221184 |         755
(1 row)

想定通り test_user で statsrepo.snapshot を参照できました。test_user 自体には権限を付与していませんが、test_user が test_role の権限を利用して、テーブル参照が可能となりました。

まとめ

本記事では PostgreSQL で権限を付与するための GRANT コマンドについて説明しました。
基本的には権限管理はユーザ単位で行うのではなく、上記のようにロール単位で行うことで効率よく管理できるようになります。権限の変更しやすさや、権限の管理しやすさ(分かりやすさ)というメリットもありますので、できるだけロール単位で権限を管理するのが推奨となります。

CATEGORY

ARCHIVE

PostgreSQLの
ハイパフォーマンスチューニングのご相談は
株式会社インサイトまで
お気軽にお問い合わせください。

CONTACT