このブログ記事では、ビューとロールについて紹介します。
これまで、私たちはデータベース管理者として論理スキーマの設定と使用に焦点を当ててきました。 しかし、セキュリティのために適切な権限を持つユーザーやアプリケーションに対して、 データベースの必要な部分のみを公開する仮想スキーマを設定する必要があります。 この記事では、安全に仮想スキーマを提供するために関連するビュー、およびロールについて説明します。
ビュー
このデータベースシリーズの最初の記事で、アプリケーション用に作成できる仮想スキーマについて簡単に触れましたが、これはビューを使用して実現できます。
ビューは、データベース内の1つ以上の実際のテーブルを含むクエリから生成される仮想テーブルであり、CREATE VIEW <view_name> AS <query>;を使用して簡単に作成できます。
ビューは、特定の列をデータベースユーザーから隠したり、結合された仮想テーブルを準備してクエリを簡素化したりする場合に便利なツールです。
例えば、注文に関する全ての関連情報と合計金額を含むレシートを作成したい場合、ビューがなければ
SELECT name, price, quantity, price * quantity as total FROM OrderItems JOIN Product ON OrderItems.product_id=Product.id WHERE OrderItems.id=1 UNION SELECT "Total", NULL, NULL, SUM(price * quantity) FROM OrderItems JOIN Product ON OrderItems.product_id=Product.id WHERE Order.id=1;
のような非常に長いSQLクエリを書く必要があります。しかし、
CREATE VIEW receipt AS SELECT OrderItems.id, name, price, quantity, price * quantity as total FROM OrderItems JOIN Product ON OrderItems.product_id=Product.id;
のようにreceiptというビューを設定することで、ユーザークエリを
SELECT name, price, quantity, total FROM receipt WHERE id=1 UNION SELECT "Total", NULL, NULL, SUM(total) FROM receipt WHERE id=1;
と大幅に短縮することができます。
ユーザーロール
PostgreSQLなどのDBMSはロールベースのアクセス制御（RBAC）の下で動作し、データベースへのアクセスはユーザーのロール(役割)によって制限されます。
デフォルトユーザーのpostgresはすべての特権を持つスーパーユーザーであり、セキュリティ上の理由からアプリケーションでは使用すべきではありません。
例えば、postgresとしてデータベースにアクセスするWebアプリケーションが攻撃者に侵害された場合、すべてのデータを盗まれたりデータベースを破壊されたりする可能性があり危険です。
同様に、分析チームのpostgresとしてのアクセスを許すと、分析チームが誤ってデータベースを変更したり削除したりする可能性があるため、これは避けるべきです。
代わりに、webappやanalyticsなどのユーザーロールを作成し、それぞれに適切な権限を割り当てることができます。
ユーザーロールはCREATE ROLE webapp LOGIN;やCREATE ROLE analytics LOGIN PASSWORD 'secret';などのコマンドで作成でき、
権限を持つユーザーはALTER USER webapp PASSWORD 'websecret';でパスワードを追加または変更できます。
その後、GRANT ALL ON Product TO webapp;やGRANT SELECT ON DATABASE prod_db TO analytics;などのコマンドでアクセス権を付与できます。
これにより、webappにProductテーブルのすべての権限を、analyticsにprod_dbデータベースのすべてのテーブルに対する選択権限をそれぞれ割り当てることができます。
また、ビューやストアドプロシージャへのアクセスも管理できます（GRANT EXECUTE ON <procedure_name> TO <role_name>;）。
これにより、必要なデータと機能のみを公開し、セキュリティリスクを軽減することができます。
ユーザーロールは小規模なプロジェクトでは便利ですが、ロールやテーブルの数が増えると管理が困難になります。
例えば、将来の権限変更に備えて、webappと同じ権限を持つapiユーザーロールを設定したい場合、
すべてのテーブルのすべての権限に対して手動でGRANTコマンドを実行する必要があります。
同様に、同じ権限を持つ複数の分析チームを管理する場合も、手動でGRANTコマンドを実行する必要があります。
さらに、新しいテーブルの新しい権限を分析チームに追加する場合、すべてのチームのすべてのユーザーロールに対して同じGRANTコマンドを手動で実行する必要があります。
グループロール
ユーザーロール管理を簡素化するために、ユーザーロールが所属できるグループロールを作成し、権限を手動で複製する必要を避けることができます。
例えば、webappとapiの両方が所属するclientロールを設定すると、clientロールの設定がwebappとapiに反映され、
同じ権限を持つ新しいユーザーロール（mobileなど）を追加する場合も、それをclientグループに追加するだけで簡単に実現できます。
同じ原則が分析チームにも適用され、analyticsグループロールを設定し、そこにanalytics_team1とanalytics_team2を追加することができます。
グループロールはCREATE ROLE client;（LOGINやPASSWORDなし）やCREATE ROLE analytics;などのコマンドで作成でき、
その権限はユーザーロールと同じ方法で割り当てられます。ユーザーロールはGRANT client TO webapp, api;やGRANT analytics TO analytics_team1, analytics_team2;などのコマンドを使用してグループロールのメンバーになることができます。
これにより、個別のグループ権限を簡単に管理できます。ユーザーロールはREVOKE client FROM api;を使用してグループロールから削除できます。
また、pg_read_all_dataなどの便利な事前定義されたグループロールもあり、GRANT pg_read_all_data TO analytics;を使用してanalyticsを設定できます。
ロールの削除は、特にそのロールがテーブルの所有権を持っていたり、グループロールのメンバーシップを持っていたりする場合は複雑になることがあるため、
DROP ROLE <role_name>;を使用してロールを削除する前に、所有権とメンバーシップの問題を解決する必要があることに注意が必要です。
結論
この記事では、仮想スキーマとRBACの概念とそれをビューとロールで実装する方法について説明しました。これらは安全で使いやすいデータベースを設定するために不可欠です。 ビューとロールの設定を設定、クライアント（Pythonのpsycopg2、JavaScriptとNode.JSのpostgres.js、node-postgresなど）はユーザー名とパスワードでログインし、 権限に基づいて関連情報のみを含む仮想スキーマにアクセスできます。これらの概念と実装の詳細については、 対応するDBMSの公式ドキュメントを確認することをお勧めします（PostgreSQLについては下に引用されています）。
リソース
- PostgreSQL. n.d. PostgreSQL 17.4 Documentation. PostgreSQL.