データストアにPostgresqlを採用し、アプリケーションから当該DBに対してDB接続する。そういうユースケースは枚挙に暇がありません。
データベース・スキーマの作成
前提として、Postgresqlには、データベース-スキーマという階層関係があります。
特定のアプリケーションを構築していく場合、それ専用のデータベースを構築するのよ良いでしょう。というわけで、まずはそれらを作成していきます。
-- アプリケーションで利用する専用データベースを作成 CREATE DATABASE yourapp; -- データベースを切り替えた上で、スキーマを作成 \c yourapp CREATE SCHEMA yourschema;
アプリケーション用ユーザーの作成
アプリケーションがデータベースに接続するためのユーザーを作成します。
-- 本ユーザーはアプリ単独で利用するため、INHERIT は無効化。 -- RLS はデフォルトで有効化されているが、意図を明確にするために NOBYPASSRLS を明示的に指定 CREATE USER app NOINHERIT NOBYPASSRLS PASSWORD 'P@ssw0rd';
Postgresqlにおいては、USERとROLEの扱いは等しいのですが、CREATE USERとCREATE ROLEはわずかな機能的な違いがあります。それは、CREATE USERではデフォルトでLOGINが可能になることです。
ここではアプリケーションが利用するユーザーであることを明確にする目的で、CREATE USERを利用しました。
唯一の違いは、CREATE USERという名前でコマンドが呼び出されると、デフォルトでLOGINになり、CREATE ROLEという名前でコマンドが呼び出されると、デフォルトでNOLOGINとなる点です。
ユーザーのデフォルトスキーマ切り替え
当該ユーザーのデフォルトスキーマをアプリ用スキーマに変更したいため、ここではALTER USERを発行しています。
-- デフォルトスキーマをアプリケーション用スキーマへ変更 ALTER USER app SET search_path TO yourschema;
ユーザーへの権限付与
アプリ用ユーザーには以下のようにして権限を付与しました。
基本的には、yourschemaスキーマ配下のテーブル及びシーケンスに対する全権限を許可します。
もちろん利用を想定するならば、各種PROCEDURE等に対する権限を付与する場合もあるでしょう。
こうすることにより、特定データベース・特定スキーマの特定データベースオブジェクト以外は操作できない状況を確立し、セキュリティを強固にします。
-- スキーマを利用する権限を付与 GRANT USAGE ON SCHEMA yourschena TO app; -- スキーマ配下の全テーブルに対する権限を付与 GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA yourschema TO app; ALTER DEFAULT PRIVILEGES IN SCHEMA yourschema GRANT ALL PRIVILEGES ON TABLES TO app; -- スキーマ配下の全シーケンスに対する権限を付与 GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA yourschema TO app; ALTER DEFAULT PRIVILEGES IN SCHEMA yourschema GRANT ALL PRIVILEGES ON SEQUENCES TO app;
ポイントとなるのはGRANT文で権限を付与するだけでなく、ALTER DEFAULT PRIVILEGESでユーザーのデフォルトアクセス権限を変更しに行っていることです。
GRANT文は発行時点で存在するデータベースオブジェクトに対しては権限を付与できるものの、その後に作成されるオブジェクトに対する権限までは関与しません。
例えば、以下のようにスキーマ配下の全テーブルに対する権限を付与したところで、その後に作成されるテーブルに対し、appユーザは権限を持ちません。
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA yourschema TO app;
こうなると、テーブルを作成都度GRANT文を発行する必要が生じ、運用負荷が上がります。
「このスキーマ配下のオブジェクト」というような粒度で権限管理をするのであれば、「未来の作成するオブジェクト」まで影響を及ぼせるALTER DEFAULT PRIVILEGESも用いる方が楽でしょう。
ALTER DEFAULT PRIVILEGESにより今後作成されるオブジェクトに適用される権限を設定することができます。 （既存のオブジェクトに割り当てられている権限には影響しません。）
Publicスキーマへの対処
厳密には「ユーザー」単位の設定ではない内容ですが。
Postgresqlには、デフォルトのスキーマとしてpublicが用意されています。
このpublicスキーマに対しては、デフォルトではどのロールからもCREATE権限が可能になっています。
言い換えれば、作成したユーザー全てがpublicスキーマにはデータベースオブジェクトの作成が可能になっている状態です。
これはセキュリティ的になんとかしたいので、CREATE権限を剥奪します。
-- 全ユーザから public スキーマの CREATE 権限を剥奪し、 -- 当該スキーマに勝手にオブジェクトを作れないようにする REVOKE CREATE ON SCHEMA public FROM PUBLIC;
PUBLICキーワードは、今後作成されるロールを含む、全てのロールへの許可を示します。 PUBLICは、全てのロールを常に含む、暗黙的に定義されたグループと考えることができます。 個々のロールは全て、ロールに直接許可された権限、ロールが現在属しているロールに許可された権限、そして、PUBLICに許可された権限を合わせた権限を持っています。
