PostgreSQL / 認証と認可
`GRANT`と`REVOKE`を使用したPostgreSQLでの権限管理
はじめに
各ユーザーがデータベース内で何を行うことを許可されているかを制御することは、データベースクラスターを管理する上で重要な部分です。PostgreSQLは、認証と認可を制御するための一連のツールを提供しています。
認証面では、pg_hba.confファイルが人々がデータベースに接続する方法を制御します。これには、サーバーへのアクセスが許可される正確なユーザー、データベース、接続方法、および認証方法の組み合わせが含まれます。認可コンポーネントは、システム内のユーザーエンティティを定義し、そのグローバル権限を制御するPostgreSQLのロールとロール属性の概念から始まります。
ロール属性によって提供されるものを超えて、さらに認可のレベルが存在します。特定のデータベースオブジェクトに対する所有権と権限の管理は、どのロールがデータベース、テーブル、シーケンスなどを管理、変更、表示できるかを制御する主要な方法です。このガイドでは、PostgreSQLの付与および取り消しメカニズムを使用して、各データベースオブジェクトにどのロールがアクセスできるかを正確に設定する方法について説明します。
PostgreSQLオブジェクト権限とは？
ロールとロール属性に関する記事では、ロールに対するシステム全体の権限を定義する概念が導入されました。ロール属性を使用することで、管理者は、ロールがデータベースを作成または変更できるか、ロールを管理できるか、さらにはシステム自体にログインできるかを定義できます。これらの種類の権限はデータベースクラスター全体で有効であるため、データベース内の個々のオブジェクトへのアクセスを制御する際の粒度は提供されません。
その代わりに、PostgreSQLは特定のデータベースエンティティに対する個別の権限付与または権利の補完的なシステムを使用します。これにより、データベースオブジェクトの所有者は、どのロールがどの種類のアクションを許可されているかを決定できます。この追加の柔軟性と粒度により、マルチユーザーおよびマルチテナントのデプロイメントが可能かつ実用的になります。
データベースの権限付与（grants）または特権（privileges）は、さまざまな認証されたロールが利用できる特定の操作セットまたはアクセスレベルを定義します。PostgreSQLの権限付与システムは「許可リスト」モデルに従っており、これは明示的に付与されたもの以外のデータベースオブジェクトへのアクセスはロールに与えられないことを意味します。
オブジェクトの所有権とロールのメンバーシップはオブジェクト権限にどのように影響するか？
このシステムの基本は、オブジェクトの所有権とロールメンバーシップの概念です。PostgreSQLでは、すべてのデータベースオブジェクトには厳密に1つの所有者がおり、その所有者だけが、superuserロールとともに、オブジェクト自体を変更、削除、管理する独自の能力を持っています。オブジェクトの所有者は、権限を付与することで、他のロールに対するオブジェクトの権限を管理します。データベースオブジェクトに付与されたすべての権限は、最終的にオブジェクトの所有者によって制御できます。
ロールメンバーシップは、ロールがメンバーであるロールの権限をロールに付与するシステムです。INHERIT属性を持つロールは、他のロールのメンバーである場合、現在のロールを変更するためにSET ROLEを使用することなく、自動的にその権限にアクセスできます。
例えば、salesデータベース内のデータを変更できるsalesadminロールを想像してみてください。sallyロールにINHERITロール属性が設定されている場合、salesadminロールにsallyを追加すると、sallyは自動的にsalesデータベース内のデータを変更できるようになります。特性を継承する機能により、「機能」ロールによって属性とアクセスをグループ化し、必要に応じて実際の「ユーザー」ロールをそれらの機能ロールに追加することで、柔軟なアクセス管理スタイルが可能になります。
システム上のすべてのロールは、デフォルトでPUBLICロールのメンバーです。これにより、権限を定義する際には「全員」と同義になります。
利用可能なオブジェクト権限の概要
PostgreSQLには、特定の機能を有効にするためにロールに付与できる多くの権限があります。
権限は、それが意味をなすデータベースオブジェクトのサブセットにのみ適用されます。例えば、データベースを「実行」することは意味がありません。どの権限がどのデータベースオブジェクトに対して有効かについては、PostgreSQLドキュメントのACL権限略語表とアクセス権限の概要表を参照してください。
以下は、各権限名とその機能のリストです。各権限の完全な説明はPostgreSQLドキュメントで確認できます。
SELECT:SELECT権限は、ロールにデータベースオブジェクトから選択（読み取り）する機能を与えます。この権限は、既存の列値を参照するすべてのUPDATEおよびDELETE操作にも必要です。INSERT: テーブル、ビュー、または列に新しいデータ行を追加する機能を提供します。UPDATE: データベースオブジェクト内の列に格納されている値を更新する機能を与えます。ほとんどのUPDATE操作では、SELECT権限も必要になります。DELETE: ロールがテーブルまたはビューから行を削除できるようにします。UPDATEと同様に、ほとんどのDELETE操作では、正しい行を対象とするためにSELECT権限も必要です。TRUNCATE:TRUNCATE権限は、ロールがテーブルまたはビューのすべてのデータを空にすることを許可します。REFERENCES: ロールに、テーブルまたはテーブル列を参照する外部キーを作成する機能を提供します。TRIGGER: ロールがテーブルまたはビューにトリガーを定義できるようにします。CREATE: ロールがデータベース、スキーマ、またはテーブルスペースの子エンティティを作成することを許可します。例えば、データベースではCREATE権限によりロールは新しいスキーマを作成でき、スキーマでは新しいデータベースを作成できます。CONNECT: ロールがデータベースに接続できるようにします。これは接続時にチェックされます。TEMPORARY: ロールがデータベース内に一時テーブルを作成することを許可します。EXECUTE: ロールに、関数またはプロシージャを呼び出す権限を与えます。USAGE: ロールにオブジェクトの基本的な機能性を許可します。例えば、スキーマに対するUSAGEは、ロールがその中のオブジェクトを検索することを許可し、シーケンスに対するUSAGEは、ロールがcurrvalおよびnextval関数を呼び出すことを許可します。ALL PRIVILEGES: 対象のロールに、指定されたオブジェクトに対するすべての権限を与える省略形です。
GRANTコマンドの使用
GRANTコマンドは、ロール管理において2つの異なるが関連する目的で使用されます。
- データベースオブジェクト上の特定の権限をロールに付与する
- 他のロールのメンバーとしてロールを追加する
これら2つの機能は、コマンド構文に見られる2つの構造に反映されています。
特定のデータベースオブジェクトに対する権限を指定されたロールに付与するには、以下の形式を使用します。
オプションのWITH GRANT OPTION句は、受信側のロールにこの機能を他のロールに渡す能力をさらに与えます。例えば、adamがWITH GRANT OPTIONを使用してcustomersからデータをDELETEする能力を付与された場合、彼は、オプションでその能力をdeloresに付与することができます。実質的に、これにより、特定のオブジェクトに対する特定の機能をロールに管理させる能力が得られます。
他のロールにロールを追加するために使用されるもう1つの構文は次のようになります。この文脈では、<role_member>に<provider_role>の権限が付与されます。
<role_member>にINHERIT属性が設定されている場合、そのロールは<provider_role>の権限に直ちにアクセスできます。この属性がない場合、<role_member>はSET ROLEを使用して現在のロールを変更することで、<provider_role>の権限にアクセスできます。
他の構文のWITH GRANT OPTION句と同様に、ロールへのメンバーシップを付与する際に、オプションでWITH ADMIN OPTION句を追加できます。この句は、ロールメンバーに新しいメンバーを追加する能力をさらに与えます。
例
samにbooksデータベースに対するCRUD権限を付与する
samがbooksデータベースに対するCRUD操作を委任できるようにする
jasmineにcustomersテーブルに対する完全な権限を与える
keishaがorder_count関数を実行できるようにする
calinをadminロールに追加する
sofieをsalespersonロールに追加し、メンバーシップを管理できるようにする
REVOKEコマンドの使用
REVOKEコマンドは、データベースオブジェクトに対するロールの権限を取り消します。ほとんどの場合、GRANTコマンドの構文を反映しており、2つのユースケースに対応する2つの形式があります。
特定のデータベースオブジェクトに対する特定の権限を所定のロールから取り消すには、以下の形式を使用します。
この場合、オプションのGRANT OPTION FOR句を追加することで、指定されたロールが与えられた権限を他のロールに渡す能力を削除できます。すでに他のロールに権限を渡しているロールからGRANT OPTIONを取り消そうとすると、競合が発生する可能性があります。その場合、最初のロールからGRANT OPTIONを削除すると、セカンダリロールに権限を与える権限付与の連鎖が切断されてしまいます。
例えば、adaがWITH GRANT OPTIONを使用してrecordsデータベースのコンテンツを更新する能力をpeteに付与した場合、peteはさらにコンテンツを更新する能力をsimoneに与えることができます。adaがpeteからGRANT OPTIONを取り消した場合、simoneに渡された更新権限がどうなるべきかは不明確です。
オプションのCASCADEまたはRESTRICT句は、このシナリオでREVOKEが何をするべきかを明示的に指定することで、この競合を解決します。デフォルトの動作はRESTRICTであり、これは権限が他のロールに渡されている場合にREVOKE GRANT OPTION FORコマンドを失敗させます。CASCADEオプションは、この動作を変更して、指定されたロールに加えて「下流」のロールからも権限を取り消し、壊れた連鎖を削除することで競合を解決します。
ロールメンバーシップを取り消すために使用されるもう1つの構文は次のようになります。
この場合、<member_role>は、他の手段で付与されない限り、<provider_role>に与えられた権限を持たなくなります。
例
eddyがlogsテーブルから行を削除する能力を取り消す
jerryからfinancesへのすべてのアクセスを削除する
aliceがsnacksテーブルで他のロールにDELETE権限を付与する能力を削除します。aliceはこのコマンドの実行後もDELETE権限を引き続き持ちますが、その権限を他のロールに渡すことはできなくなることに注意してください。
natashaをmoderatorsロールから削除する
tonyからhrロールのメンバーシップを管理する能力を取り消します。tonyは引き続きhrのメンバーであることに注意してください。
まとめ
PostgreSQLの付与および権限システムにより、特定のデータベースオブジェクトに対して個々のロールにきめ細かな権限を定義できます。この権限付与システムは、PostgreSQLの認可制御を、所有者によって管理できる個々のオブジェクトまで拡張します。
この取り決めにより、個々のユーザーは自身のデータベースオブジェクトを制御できます。アクセス権を付与したり取り消したり、特定の管理機能を他のロールに委任したりできます。さらに、同じ取り決めが、権限管理を簡素化するためにロールメンバーシップを実装するために使用されます。
