PostgreSQL ユーザーとロールの管理

PostgreSQL は最も人気のあるオープンソースのリレーショナルデータベースシステムの 1 つです。30 年以上の開発作業を経て、PostgreSQL は、多数の複雑なデータワークロードを処理できる、信頼性が高く堅牢なデータベースであることが証明されています。Oracle などの商用データベースから移行する場合、PostgreSQL はオープンソースデータベースの主要な選択肢と見なされています。アマゾンウェブサービス（AWS）は、管理された 2 つの　PostgreSQL オプションを提供します：PostgreSQL および Amazon Aurora PostgreSQL 用の Amazon Relational Database Service（Amazon RDS）。この記事では、PostgreSQL でユーザーとロールを管理するためのベストプラクティスについて説明しています。

PostgreSQL を使用すると、きめ細かいアクセス権限を持つユーザーとロールを作成できます。新しいユーザーまたはロールには、各データベースオブジェクトに必要な権限を選択的に付与する必要があります。これはエンドユーザーに多くの力を与えますが、それと同時に、正しい許可を持つユーザーとロールを作成するプロセスを潜在的に複雑にしています。

PostgreSQL では、データベースユーザーに直接権限を付与することができます。ただし、グッドプラクティスとして、アプリケーションとアクセスの要件に基づいて、特定の権限のセットを持つ複数のロールを作成することをおすすめします。次に、各ユーザーに適切な役割を割り当てます。ロールは、データベースオブジェクトにアクセスするための最小権限モデルを強制するために使用するべきです。Amazon RDS および Aurora PostgreSQL インスタンスの作成中に作られたマスターユーザーは、他のユーザー、ロール、およびデータベースの作成などのデータベース管理タスクにのみ使用する必要があります。マスターユーザーはアプリケーションによって使用されるべきではありません。

PostgreSQL できめ細かいアクセスコントロールを設定するための推奨アプローチは次のとおりです：

マスターユーザーを使用して、readonly や readwrite などのアプリケーションまたはユースケースごとにロールを作成します。
これらのロールがさまざまなデータベースオブジェクトにアクセスできるように権限を追加します。例えば、readonly ロールは SELECT クエリのみを実行できます。
機能にとって最低限必要な権限をロールに付与します。
app_user や reporting_user のように、アプリケーションごとまたは個別の機能ごとに新しいユーザーを作成します。
適切なロールをこれらのユーザーに割り当てて、ロールと同じ権限をすばやく付与します。例えば、readwrite ロールを app_user に付与し、readonly ロールを reporting_user に付与します。
いつでも、権限を取り消すためにユーザーからロールを削除できます。

次の図は、これらの推奨事項をまとめたものです：

次のセクションでは、これらの手順について詳しく説明します。psql などのクライアントを使用して PostgreSQL データベースの RDS エンドポイントに接続し、SQL ステートメントを実行することができます。

ユーザー、グループ、およびロール

ユーザー、グループ、ロールは PostgreSQL でも同じですが、唯一の違いはユーザーがデフォルトでログインする権限を持っているということです。CREATE USER ステートメントと CREATE GROUP ステートメントは、実際には CREATE ROLE ステートメントの別名です。

Oracle のような他のリレーショナルデータベース管理システム（RDBMS）では、ユーザーとロールは 2 つの異なるエンティティです。Oracle では、ロールを使用してデータベースにログインすることはできません。ロールは、補助金およびその他のロールをグループ化するためにのみ使用されます。その後、このロールを 1 人以上のユーザーに割り当て、それらのユーザーにすべての許可を付与することができます。ユーザー、ロール、および付与を Oracle から PostgreSQL へ移行する方法の詳細については、AWS ブログの記事SQL を使用して Oracle から PostgreSQL へユーザー、ロール、および付与をマッピングするをご参照ください。

PostgreSQL ユーザーを作成するには、次の SQL ステートメントを使用します：

CREATE USER myuser WITH PASSWORD 'secret_passwd';

次の SQL ステートメントを使用してユーザーを作成することもできます：

CREATE ROLE myuser WITH LOGIN PASSWORD 'secret_passwd';

これらのステートメントは両方ともまったく同じユーザーを作成します。この新しいユーザーには、public ロールで使用できるデフォルトの権限以外の権限はありません。すべての新しいユーザーとロールは、public ロールから権限を引継ぎます。次のセクションでは、public ロールについて詳しく説明します。

public スキーマと public ロール

新しいデータベースが作成されると、PostgreSQL はデフォルトで public という名前のスキーマを作成し、このスキーマへのアクセスを public という名前のバックエンドロールに付与します。すべての新しいユーザーおよびロールには、デフォルトでこの public ロールが付与されているため、public スキーマにオブジェクトを作成できます。

PostgreSQL は検索パスの概念を使用しています。検索パスは、データベースオブジェクトの修飾名を使用しない場合、 PostgreSQL がチェックするスキーマ名のリストです。例えば、「mytable」という名前のテーブルから選択すると、PostgreSQL は検索パスにリストされているスキーマ内でこのテーブルを探します。最初に見つかった一致を選択します。デフォルトでは、検索パスには次のスキーマが含まれています：

postgres=# show search_path;
   search_path   
-----------------
 "$user", public
(1 row)

ファーストネーム「$ user」は、現在ログインしているユーザーの名前として結論付けられます。デフォルトでは、ユーザー名と同じ名前のスキーマは存在しません。従って、修飾されていないオブジェクト名が使用されている場合は常に、public スキーマがデフォルトのスキーマになります。このため、ユーザーがスキーマ名を指定せずに新しいテーブルを作成しようとすると、そのテーブルは public スキーマに作成されます。前述のように、デフォルトでは、すべてのユーザーが public スキーマにオブジェクトを作成するためのアクセス権限を持っているため、テーブルは正常に作成されます。

読み取り専用ユーザーを作成しようとしている場合、これが問題になります。すべての権限を制限しても、public ロールを介して引継がれた許可によって、ユーザーは public スキーマ内にオブジェクトを作成できます。

これを修正するには、次のSQLステートメントを使用して、public ロールから public スキーマに対するデフォルトの作成権限を取り消す必要があります：

REVOKE CREATE ON SCHEMA public FROM PUBLIC;

自分が public スキーマの所有者であること、またはこの SQL ステートメントを実行できるロールの一部であることを確認してください。

次のステートメントは、public ロールがデータベースに接続する機能を無効にします：

REVOKE ALL ON DATABASE mydatabase FROM PUBLIC;

これにより、この権限が明示的に付与されていない限り、ユーザーはデフォルトでデータベースに接続できなくなります。

public ロールから権限を取り消すと、既存のすべてのユーザーとロールに影響します。データベースに接続したり、パブリックスキーマにオブジェクトを作成できるユーザーおよびロールには、プロダクション環境で public ロールから権限を取り消す前に、明示的に権限を付与する必要があります。

データベースロールの作成

次のセクションでは、新しいロールを作成し、さまざまなデータベースオブジェクトにアクセスするための権限を付与するプロセスについて説明します。権限は、データベース、スキーマ、およびスキーマオブジェクトの各レベルで付与する必要があります。例えば、テーブルへのアクセス権限を付与する必要がある場合は、そのテーブルが存在するデータベースおよびスキーマへのアクセス権限を、そのロールが持っていることも確認する必要があります。いずれかの権限が抜けていると、ロールはテーブルにアクセスできません。

読み取り専用ロール

最初のステップは、次のSQLステートメントを使用して readonly という名前の新しいロールを作成することです：

CREATE ROLE readonly;

これは、権限もパスワードもない基本的なロールです。データベースへのログインには使用できません。

このロールに、「mydatabase」という名前のターゲットデータベースに接続するためのアクセス権限を付与します：

GRANT CONNECT ON DATABASE mydatabase TO readonly;

次のステップは、このロールの使用法に自分のスキーマへのアクセス権限を付与することです。スキーマが myschema という名前だと仮定しましょう：

GRANT USAGE ON SCHEMA myschema TO readonly;

このステップでは、スキーマ内で何らかのアクティビティを実行するための readonly ロール権限を付与します。このステップがないと、readonly ロールは、これらのオブジェクトに対する権限が付与されていても、このスキーマ内のオブジェクトに対してアクションを実行できません。

次のステップは、必要なテーブルで「選択」を実行するための readonly ロールのアクセス権限を付与することです。

GRANT SELECT ON TABLE mytable1, mytable2 TO readonly;

スキーマ内のすべての表およびビューに対するアクセス権限を付与することが要件である場合は、次の SQL を使用できます：

GRANT SELECT ON ALL TABLES IN SCHEMA myschema TO readonly;

前述の SQL ステートメントは、スキーマ myschema 内のすべての既存のテーブルおよびビューに対する readonly ロールへの選択アクセス権限を付与します。将来追加される新しいテーブルには、 readonly ユーザーはアクセスできなくなることをご了承ください。新しいテーブルやビューにも確実にアクセスできるようにするには、次のステートメントを実行して権限を自動的に付与します：

ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT SELECT ON TABLES TO readonly;

読み取り/書き込みロール

読み取り/書き込みロールを追加するプロセスは、前述の読み取り専用ロールプロセスと非常によく似ています。最初のステップはロールを作成することです：

CREATE ROLE readwrite;

このロールに、ターゲットデータベースに接続するためのアクセス権限を付与します：

GRANT CONNECT ON DATABASE mydatabase TO readwrite;

スキーマ使用権限を付与します：

GRANT USAGE ON SCHEMA myschema TO readwrite;

このロールがこのスキーマの表のような新しいオブジェクトを作成できるようにする場合は、前述の SQL ではなく次の SQL を使用します：

GRANT USAGE, CREATE ON SCHEMA myschema TO readwrite;

次のステップはテーブルへのアクセスを許可することです。前のセクションで説明したように、付与はスキーマ内の個々のテーブルまたはすべてのテーブルに対して行うことができます。個々のテーブルには、次の SQL を使用してください：

GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE mytable1, mytable2 TO readwrite;

スキーマ内のすべてのテーブルとビューに対して、次の SQL を使用します：

GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA myschema TO readwrite;

将来追加されるテーブルとビューに対するアクセス権限を自動的に付与するには：

ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO readwrite;

読み取り/書き込みロールの場合、通常はシーケンスも使用する必要があります。次のように選択的なアクセス権限を与えることができます：

GRANT USAGE ON SEQUENCE myseq1, myseq2 TO readwrite;

次の SQL ステートメントを使用して、すべてのシーケンスにアクセス権限を付与することもできます：

GRANT USAGE ON ALL SEQUENCES IN SCHEMA myschema TO readwrite;

将来追加されるシーケンスに対するアクセス権限を自動的に付与するには：

ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT USAGE ON SEQUENCES TO readwrite;

要件に基づいて、より多くの、またはより少ない権限を付与できます。PostgreSQL の「許可」コマンドのドキュメンテーションには、権限を付与できるオブジェクトと必要な SQL ステートメントに関する詳細が記載されています。

データベースユーザーの作成

ロールが整っていれば、ユーザーを作成するプロセスは単純化されます。ユーザーを作成して既存のロールの 1 つを付与するだけです。このプロセスの SQL ステートメントは次のとおりです：CREATE USER

CREATE USER myuser1 WITH PASSWORD 'secret_passwd';
GRANT readonly TO myuser1;

これにより、myuser1 に readonly ロールと同じ権限が付与されます。

同様に、readwrite ロールを付与することで、ユーザーに読み取りおよび書き込みアクセス権限を付与できます。PostgreSQL の「ユーザーを作成」ドキュメンテーションには、ユーザー作成中に設定できるパラメータに関する詳細が含まれています。例えば、ユーザーに有効期限を指定したり、ユーザーにデータベースの作成を許可できます。

ユーザーパスワードの管理

ユーザーを作成した後、データベースにアクセスできるように、これらの資格情報をアプリケーションに提供する必要があります。これらの資格情報がソースコードにハードコードされていないこと、または一部の共有設定ファイルにクリアテキストとして配置されていないことを確認することが不可欠です。AWSは AWS Secrets Managerを使用してこれを解決します。Secrets Managerを使用すると、認証情報を保存してから、AWS Identity and Access Management（IAM）を使用して特定のIAMユーザーとロールのみに認証情報の読み取りを許可することができます。これに関する手順については、AWS Secrets Managerユーザーガイドの「AWS Secrets Managerを使用したシークレットの作成と管理」を参照してください。

資格情報の保管に加えて、Secrets Managerが提供する非常に便利な機能は、データベースユーザーのパスワードローテーションです。この機能を使用して、特定の頻度で自動的にパスワードを変更するようにポリシーを設定できます。アプリケーションのダウンタイムが発生しないための詳細設定方法は、「AWS Secrets Manager Secrets ローテーション」をご参照ください。

Amazon RDS と Amazon Aurora PostgreSQL は、PostgreSQL 10.6 以降でサポートされる、新しい制限付きパスワード管理機能を提供します。新しいパラメータと特別なロールを使用して、データベースユーザーのパスワード変更を。この特別なロールメンバーに制限できます。これを行うことで、クライアント側でパスワード管理をより細かくコントロールできるようになります（有効期限の要件や複雑さの要件など）。

IAM データベース認証

Amazon RDS と Aurora PostgreSQL は IAM と統合されているため、IAM データベース認証を使用して DB インスタンスに対して認証ができます。この機能は、Amazon RDS PostgreSQL バージョン 9.5.14、9.6.9 以上、およびバージョン 10.4 以上で利用できます。Aurora PostgreSQL の場合、この機能はバージョン 9.6.9 以上およびバージョン 10.4 以上で利用できます。この機能の主な利点は、各 DB インスタンスで個別にアクセスを管理するのではなく、IAM を利用してデータベースリソースへのアクセスを集中管理できることです。この方法を利用すると、管理者は IAM ポリシーを介してデータベースアクセスを簡単に許可または取り消すことができます。

IAM 付与が追加された後、ユーザーは AWS CLI を使用して一時パスワードをリクエストし、次からはこの一時パスワードを使用して DB に接続できます。以下の図は、このワークフローを示しています。

この機能の詳細については、MySQL および PostgreSQL 用の IAM データベース認証をご参照ください。ドキュメンテーションには、IAM DB 認証を構成するための詳細な手順も含まれています。

このメソッドは認証部分のみを扱います。この記事で説明しているように、さまざまなデータベースオブジェクトに対する権限の付与は、データベース内で行われます。例えば、このユーザーに readwrite アクセス権限を付与するには、次の SQL ステートメントを実行します：

GRANT readwrite TO db_user;

ユーザー権限の取り消しまたは変更

前述の方法を使用すると、ユーザーから権限を取り消すことがとても簡単になります。例えば、次の SQL ステートメントを使用して、myuser1 から readwrite 権限を削除できます：

REVOKE readwrite FROM myuser1;

同様に、次のようにして新しいロールを付与することができます：

GRANT readonly TO myuser1;

モニタリング使用方法

RDS パラメータグループで PostgreSQL のログパラメータを使用可能に設定することで、ユーザーアクティビティを監視できます。例えば、log_connections および log_disconnections パラメータを設定して、すべての新しい接続と切断をキャプチャできます。これらのパラメータをパラメータグループに設定すると、ログファイルに次のメッセージが表示されます：

2018-11-09 21:08:39 UTC:XX-XX-XX-XX.amazon.com(27585):myuser@mydb:[18014]:LOG: connection authorized: user=myuser database=mydb SSL enabled (protocol=TLSv1.2, cipher=ECDHE-RSA-AES256-GCM-SHA384, compression=off)

2018-11-09 21:09:19 UTC:XX-XX-XX-XX.amazon.com(27585):myuser@mydb:[18014]:LOG: disconnection: session time: 0:00:39.649 user=myuser database=mydb host=XX-XX-XX-XX.amazon.com port=27585

より詳細なセッションまたはオブジェクトレベルのカスタム監査情報が必要な場合は、pgAudit 拡張機能を使用できます。Amazon RDSおよびAurora PostgreSQL を使用して pgAudit を設定する手順は、Amazon RDS ユーザーガイドの pgaudit 拡張機能で利用できます。

データベースロギングを増やすと、ストレージサイズ、I/O 使用量、および CPU 使用量に影響します。このため、本番環境に展開する前にこれらの変更テストを実施することが重要です。

付与されたロールを確認する

次のクエリを使用して、すべてのデータベースユーザーとロールのリストと、それらに付与されているロールのリストを取得できます：

SELECT 
      r.rolname,
      ARRAY(SELECT b.rolname
            FROM pg_catalog.pg_auth_members m
            JOIN pg_catalog.pg_roles b ON (m.roleid = b.oid)
            WHERE m.member = r.oid) as memberof
FROM pg_catalog.pg_roles r
WHERE r.rolname NOT IN ('pg_signal_backend','rds_iam',
                        'rds_replication','rds_superuser',
                        'rdsadmin','rdsrepladmin')
ORDER BY 1;

テスト RDS インスタンスからの出力例は次のとおりです：

    rolname     |    memberof   
-----------------+--------------
 app_user       | {readwrite}
 postgres       | {rds_superuser}
 readonly       | {}
 readwrite      | {}
 reporting_user | {readonly}

ユーザーは、異なる権限または重複する権限を持つ複数のロールのメンバーになることができます。この場合、ユーザーはすべての権限の合計を取得します。

カタログテーブル pg_roles を使用して、パスワードの有効期限や許可されている並列接続の数などの属性を確認することもできます。

まとめ

この記事では、PostgreSQL でユーザーとロールを管理するためのベストプラクティスをいくつか共有しました。この記事では、アプリケーション要件と最小権限の原則に基づいて変更できるベーシックフレームワークを提供します。

概念を要約するために、シナリオ例を使用してユーザーとロールを実装するための、以下の参照用 SQL ステートメントを提供しました：

PostgreSQL データベースは、mydatabase という名前のプライマリデータベースで作成されています。
複数のテーブルを持つ myschema という名前の新しいスキーマが作成されました。
スキーマ myschema 内のすべてのテーブルを読み取る権限を持つ、2 人のレポートユーザーを作成する必要があります。
スキーマ myschema 内のすべてのテーブルを読み書きする権限と、新しいテーブルを作成する権限を持つ、2 人のアプリユーザーを作成する必要があります。
ユーザーは、将来追加される新しいテーブルに対するアクセス権限の許可を自動的に取得する必要があります。

このシナリオを実装するには、マスターユーザーを使用してデータベース mydatabase に接続した後、psql や pgAdmin などのPostgreSQL クライアントを使用して、次の SQL ステートメントを実行する必要があります：

-- Revoke privileges from 'public' role
REVOKE CREATE ON SCHEMA public FROM PUBLIC;
REVOKE ALL ON DATABASE mydatabase FROM PUBLIC;

-- Read-only role
CREATE ROLE readonly;
GRANT CONNECT ON DATABASE mydatabase TO readonly;
GRANT USAGE ON SCHEMA myschema TO readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA myschema TO readonly;
ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT SELECT ON TABLES TO readonly;

-- Read/write role
CREATE ROLE readwrite;
GRANT CONNECT ON DATABASE mydatabase TO readwrite;
GRANT USAGE, CREATE ON SCHEMA myschema TO readwrite;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA myschema TO readwrite;
ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO readwrite;
GRANT USAGE ON ALL SEQUENCES IN SCHEMA myschema TO readwrite;
ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT USAGE ON SEQUENCES TO readwrite;

-- Users creation
CREATE USER reporting_user1 WITH PASSWORD 'some_secret_passwd';
CREATE USER reporting_user2 WITH PASSWORD 'some_secret_passwd';
CREATE USER app_user1 WITH PASSWORD 'some_secret_passwd';
CREATE USER app_user2 WITH PASSWORD 'some_secret_passwd';

-- Grant privileges to users
GRANT readonly TO reporting_user1;
GRANT readonly TO reporting_user2;
GRANT readwrite TO app_user1;
GRANT readwrite TO app_user2;

PostgreSQL のユーザーとロールについての詳細は、PostgreSQL ドキュメンテーション Web サイトをご参照ください。

このブログ記事に質問やご意見がある場合は、コメント欄に自由に記入してください。

著者について

Yaser Raja は、アマゾンウェブサービスでプロフェッショナルサービスチームのシニアコンサルタントを務めています。顧客と協力し、AWS クラウドでスケーラブルかつ可用性と安全性の高いソリューションの構築に取り組んでいます。彼の専門分野は、オンプレミスのデータベースの AWS RDS や Aurora PostgreSQL への同種間または異種間の移行です。

Amazon Web Services ブログ