PostgreSQL の基本: データベース権限を管理するためのテンプレート - シンプル トーク (2023)

PostgreSQL 権限に関するこのシリーズの最初の 2 つの記事では、次のことを確認しました。役割の作成方法、データベース オブジェクトに対する権限を付与する方法、およびその方法オブジェクトの所有権これは、データベース内のアクセスと制御を管理する上で重要な側面です。

どのロールが既存のオブジェクトにアクセスまたは変更できるかを管理する場合、所有権は究極の権限です。 PostgreSQL の権限は最小特権の原則に基づいて機能するため、オブジェクト (テーブル、トリガー、関数、プロシージャなど) の所有者は次のことを行う必要があります。許す他の役割に対する特権。

これを手動で行う方法について説明しました。許すただし、オブジェクトが作成されるたびにコマンドを実行すると、管理に時間がかかり、詳細を見落としやすくなります。

代わりに、PostgreSQL は、データベース オブジェクトの作成時にオブジェクト所有者に代わって付与されるデフォルトの権限を設定するメソッドを提供します。デフォルトの権限を使用すると、ロールは事前にデータベースを準備して、一貫したアクセス権限が適用されるようにしながら、時間の経過とともに管理の負担を軽減できます。

しかし、適切なレベルの制御とアクセスを提供する一連のロールとデフォルト権限を作成するにはどうすればよいでしょうか?もう少し深く掘り下げてみましょう。

デフォルトの権限を使用した移行の管理

デフォルトの権限はロール (ユーザーまたはユーザーのグループを表すことができる) ごとに設定されることを思い出してください。つまり、各ロールは、特定の種類のオブジェクトが作成されるたびに、他のロールにどの権限を付与するかを指定する必要があります。以下のイメージ図では、内部に多数のオブジェクトがあり、それぞれが異なるロールによって所有されているデータベースを考えてみましょう。

多くのロールがオブジェクトの作成と所有を許可するデータベースでは、少なくとも 2 つの問題が発生します。

まず、多くの役割には作成各スキーマの権限。以前の記事で説明したように、オブジェクト作成者はデフォルトで所有者でもあり、オブジェクトに対してスーパーユーザーのような権限を持ちます。ただし、それ以上に、スキーマ内にオブジェクトを作成する権限があると、潜在的なセキュリティ問題が発生します。作成PostgreSQL 15 では、権限が public ロールから削除されました。

第 2 に、多くの異なるロールがスキーマ内にオブジェクトを作成する正当な理由がある場合でも、他のロールが必要なアクセス権を確実に持つことができるように、それぞれのロールがデフォルトの権限のセットを作成して維持する必要があります。現在、PostgreSQL でデフォルトの権限を簡単に視覚化するのは難しいため、管理するのは大変です。

代わりに、すべてのデータベース オブジェクトを所有する 1 つのグループ ロールを示す、この 2 番目の図を検討してください。このシナリオでは、グループ ロールはログインできないため、ある程度のセキュリティが提供されます。デフォルトの権限セットは 1 つだけデータベース オブジェクトとデータへのアクセスを効果的かつ効率的に管理するには、時間をかけて維持する必要があります。

PostgreSQL データベース内でこの種のセキュリティを設定する方法を見てみましょう。

高レベルのグループ役割の作成

PostgreSQL では、最初の記事で説明したように、ロールに他のロールのメンバーシップを付与し、その権限を継承することができます。したがって、データベース内のさまざまなロールにどのような種類の権限を付与する必要があるか、またそれらをより上位のロールのセットに抽象化する方法を考えることが課題となります。

ロールはインスタンス レベルで作成されることに注意してください。したがって、「」を作成すると、読み取り専用'ロールを使用すると、読み取り専用アクセスの管理に使用できます ('選択する') 各データベースにあります。 DML アクセスからのさまざまな種類の権限でも同じことができます (‘入れる'、「消去'、「アップデート') DDL 操作に至るまで (‘作成'）。

サンプル「テンプレート」は、すべてのデータベース インスタンスにこれら 3 つのグループ ロールを作成することから始めることができます。ニーズはさらに複雑になる可能性があるため、必要に応じて追加および調整してください。

• 次のスクリプトは、このスタックオーバーフローの回答テンプレートとして使用します。私は昨年この本を偶然見つけて、PostgreSQL データベース権限を設定するための簡単なプロセスの概要をうまく説明していると思いました。 (最高のデータベース DevOps ツールについても言及しているのは間違いありません! 😊)

PostgreSQL インスタンス グループのロール

次の 2 つのスクリプトを実行する必要があります一度各データベース インスタンスで適切なロールを作成し、各データベース内で権限を付与します。

これらの役割を作成しても、まだ実質的な成果は何もありません。代わりに、これらのロールは、データベースとスキーマ オブジェクトを作成するときに他の権限を管理するメカニズムになります。これらのロールではデータベースに直接接続できませんが、(「ログインなし」)、これらのロールは引き続きオブジェクトを所有し、デフォルトの権限を作成できます。

次に、必要に応じて、これらのグループ ロールのメンバーシップを他のロールに付与できます。以下の例では、3 種類のユーザー ロールがあります。

• オブジェクト管理者: これらの役割は、データベース内にオブジェクトを作成することを許可されます。オブジェクトの作成は DDL アクションであるため、ロールに名前を付けました「ddl_grp」
• データ管理者: これらのロールは、DML ステートメントを使用してデータと関連オブジェクトを変更することが許可されているため、ロールに「」という名前を付けました。dml_grp'
• 読み取り専用: これらのロールは、特定のスキーマまたはテーブルからデータを選択することができます。私たちはその役割に「読み取り専用_grp'

ユーザー役割の例 (例:「dev_admin1」、「フライウェイ」、「開発1'、「レポート_ユーザー'、など) は、グループ ロールのメンバーシップを付与する前に存在している必要があります。重要なのは、「メンバーシップが付与されたロールはすべて、ddl_grp'ロールは、データベース内のオブジェクトを作成または変更するスクリプトを「ddl_grp'。

すべてのロールが配置され、メンバーシップが更新されたので、特権が本当に重要な個々のデータベースに目を向けることができます。

個々のデータベース権限

データベース ロールを作成したので、個々のデータベース権限に注目することができます。この場合、選択肢は 2 つあります。

クラスター内に新しいデータベースが作成されるたびに、このスクリプトを実行できます。または、スーパーユーザーの役割を使用して、テンプレート データベース (通常は「テンプレート1') PostgreSQL インスタンス上で。テンプレート データベースに適用される設定は、そのテンプレートから作成される新しいデータベースごとにこれらの権限を自動的に設定します。

以下のスクリプトは 1 つのスクリプトとして適用できますが、各セクションの目的とデータベースへの適用方法を説明するために分割しました。

PUBLIC からすべての権限を取り消します

新しいデータベースに対して最初に行うべきことは、デフォルトでデータベースに付与されているすべての権限を取り消すことです。公共役割。最初の記事で、これが推奨される理由について説明しました。

ここで 1 つのニュアンスに注目してください。 「」を削除しています全て'からの特権'公共'データベースのスキーマ レベルではなく、データベース レベルで。スキーマからすべての権限も削除した場合、追加の権限をリセットしない限り、通常の PostgreSQL コマンドは多くのユーザーに対して機能しなくなります。

取り消し中'全て'以下に示すように、データベース レベルでは、他の場所で適用される特定の許可なしにユーザーがデータベースに接続できないようにします。

基本的な権限を付与する

すべてのロールには、データベースに接続し、(通常は) 一時テーブルにアクセスする機能が必要です。ただし、次のような追加の権限がなければ、使用法と選択する、これらの役割は実際にはできませんするデータベースにはまだ何もありません。

通常、すべてのロールはこれらの権限を公共ロールですが、各データベースとパブリック スキーマにアクセスするロールをより詳細に制御できるようにするために、上記の権限を取り消しただけです。

各グループの役割に使用法およびその他の権限を付与します。

ここからは良い点に移ります。データベースごとに、どのロールがデータベース オブジェクトを使用できるかを決定できます。（'使用法') とスキーマごとにできること。以下に示すように、この例では、パブリック スキーマに対する権限を付与する方法のみを示します。アプリケーションに他のスキーマがある場合 (おそらく存在します!)、各スキーマに適切なアクセス許可を付与する必要があります。

まず、私たちの「ddl_grp」(およびすべてのメンバー) は、スキーマおよび任意のシーケンス内のオブジェクトを使用および作成できます。などの既存のオブジェクトがある場合テーブル、順序、タイプ、ドメインなどを実行する必要があります。許すデフォルトの権限は新しく作成されたオブジェクトにのみ影響するため、毎回ステートメントを使用して既存の権限を適切に設定します。以下の例では、既存のシーケンスとテーブルに権限を付与する方法を示します。

次に、スキーマ内のオブジェクトの使用を許可されるグループ ロールに進み、データとシーケンス値を選択します。前と同様、データベースに複数のスキーマがある場合は、各スキーマのオブジェクト タイプごとにロールの使用を許可する必要があります。

最後に、スキーマ オブジェクトの作成に使用されるユーザーとして、データベース内の他のロールのデフォルト権限を設定します。次の例では 2 つの点に注目してください。

• テーブルとシーケンスに対してのみデフォルトの権限を設定します。オブジェクトの作成時にユーザーが必要とする他の権限を考慮し、スクリプトに追加する必要があります。
• の「ddl_grp」ロールにはデフォルトの権限が割り当てられておらず、そのメンバーであるロールにもデフォルトの権限が割り当てられていません。これは、グループがオブジェクトの所有者となり、他のメンバーの役割が自動的に所有権を継承するためです。それ以上は何も必要ありません。

通常、デフォルトの権限はステートメントを実行するロールに適用されます。したがって、スクリプトでは、将来オブジェクトを作成する所有者にロールを一時的に設定する必要があります。これには、このスクリプトを実行するロールが他のロールのメンバーである必要があります。

あるいは、長期的にはもう少し明確になりますが、デフォルトの権限を別のロールに設定することもできます。デフォルトの権限を変更する現在のロールがそのメンバーである限り、ステートメントは使用されません。

以下の両方の例で、SQL を実行しているロールは、他のグループ ロールのメンバーである必要があります。 2 番目の形式はもう少し冗長で、どの権限が誰に適用されているかをステートメント レベルで追跡しやすくなります。

オプション 1: 権限を割り当てる前に SET ROLE を実行する

オプション 2: 別のロールに権限を割り当てる

権限が正しく設定されていることを確認してください

最後のステップは、デフォルトの権限が設定されていて、そのジョブを実行する準備ができていることを確認することです。作業の結果を確認する最も簡単な方法は、「psql」の「\ddp」コマンドを使用することです。

これは、「ddl_grp」role は、パブリック スキーマ内のすべてのテーブルとシーケンスに対するデフォルトの権限を設定します。

明らかに、実際のアプリケーションとロールの設定は、このサンプル テンプレートよりも複雑になる可能性があります。ただし、この原則は、管理する必要があるロールとオブジェクトの種類の数に関係なく適用されます。これを出発点として使用してください。

移行時に統合する

すべての準備が整ったので、運用データベース (たとえば) でこれを実際にどのように使用するかを見てみましょう。

移行パイプラインでは、上記の「フライウェイ」として示されているパイプライン ロールを使用するように移行スクリプトの接続文字列を設定します。理想的には、このロールのパスワードはボールトに保存され、パイプライン スクリプトの一部として取得され、通常のユーザーがこのロールとして認証できないようにする必要があります。

移行スクリプトの先頭では、まずロールを「ddl_grp」ロールにより、作成 DDL によってオブジェクトがそのグループに所有されるようになります。とデフォルトの権限が起動し、正しく適用されます。簡単な例は次のようになります。

移行が適用されたら、権限が正しく適用されたことをすぐに確認できます。'\dp'コマンドイン'\psql'。

成功！この時点から、「ddl_grp」はテーブルとシーケンス (またはデフォルトの権限を設定したその他のオブジェクト) の作成に使用され、他のロールには定義したアクセス権が与えられ、全体的な管理の負担が軽減されます。

結論

PostgreSQL の権限は、考えたり管理したりするのが複雑になる場合があります。ロールとロール間のメンバーシップに特権を付与する方法を理解すると、この負担をすぐに軽減できます。ただし、デフォルトの権限を効果的に設定する方法を学ぶことから、データベース管理の真の力が始まります。