PostgreSQLで読み取り専用ロールを作る：GRANTによる最小権限アクセスの基礎

はじめに

複数のアプリケーションが同じデータベースサーバーを共有する構成で、「あるアプリからは別のアプリのデータを閲覧だけしたい(書き込みはさせたくない)」という要件が出てくることがあります。このとき、既存の管理用ロール(読み書きどちらもできるロール)をそのまま使い回すのは、バグや設定ミスによる誤書き込みのリスクを抱えることになります。ここで使うのが、PostgreSQLの読み取り専用ロールという考え方です。

PostgreSQLにおける「ロール」とは

PostgreSQLでは、バージョン8.1以降、「ユーザー」と「グループ」の概念がロール(Role) という1つの仕組みに統合されています。ロールには、ログイン可能な属性を持たせて「ユーザー」のように使うことも、ログイン不可の属性のまま「権限のグループ」として使うこともできます。

権限は、テーブル単位・カラム単位で細かく制御でき、SELECT(閲覧)・INSERT(追加)・UPDATE(更新)・DELETE(削除)といった操作ごとに個別に付与・剥奪できます。

読み取り専用ロールの作り方

典型的な構成は、以下の4ステップです。

-- 1. ログイン可能な新しいロールを作成する
CREATE ROLE readonly_app LOGIN PASSWORD 'strong_password_here';

-- 2. 対象データベースへの接続権限を付与する
GRANT CONNECT ON DATABASE target_db TO readonly_app;

-- 3. 対象スキーマの使用権限を付与する
GRANT USAGE ON SCHEMA public TO readonly_app;

-- 4. スキーマ内のテーブルに対する閲覧(SELECT)権限のみを付与する
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_app;

これでreadonly_appロールは、対象データベースに接続してSELECTによるデータ閲覧はできますが、INSERT・UPDATE・DELETEといった書き込み系の操作は一切できません。誤って書き込みを行うコードを実行してしまっても、DB側の権限によって拒否されるため、事故を未然に防げます。

特定のテーブルだけに絞りたい場合

データベース内のすべてのテーブルではなく、特定のテーブルだけを閲覧させたい場合は、ALL TABLES IN SCHEMAの代わりにテーブル名を個別に指定します。

GRANT SELECT ON transactions TO readonly_app;

利用させたいテーブルが最小限であれば、こちらの方が安全です。

USAGE権限の付与漏れに注意

GRANT SELECTだけを付与してGRANT USAGE ON SCHEMAを忘れると、テーブルへのSELECT自体が拒否されてしまいます。USAGE権限は「そのスキーマの中を見てよい」という前提の権限であり、SELECT(個々のテーブルを読んでよい)とは別物です。

特にPostgreSQL 15以降では、新規作成されたデータベースのpublicスキーマに対して、デフォルトでPUBLIC(全ロール)へのCREATE権限が付与されなくなるなど、スキーマ関連の既定権限の扱いが変更されています。バージョンによる挙動の違いがあるため、意図通りの権限になっているかは\dp(psqlのメタコマンド)等で確認するとよいでしょう。

新規作成されるテーブルには自動で権限が付かない

GRANT SELECT ON ALL TABLES IN SCHEMA publicは、実行した時点で存在するテーブルにのみ適用されます。この後で新しいテーブルが追加されても、自動的にreadonly_appが閲覧できるようにはなりません。

将来追加されるテーブルにも自動的に同じ権限を適用したい場合は、ALTER DEFAULT PRIVILEGESを使います。

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

これにより、以降そのスキーマ内に新しく作られるテーブルにも、自動的にreadonly_appへのSELECT権限が付与されるようになります。

まとめ

参考

• PostgreSQL: Documentation: GRANT
• PostgreSQL: Documentation: ALTER DEFAULT PRIVILEGES
• Creating a Read-Only Postgres User - Crunchy Data Blog