非アクティブな Amazon Aurora PostgreSQL ユーザーの管理

データはあらゆる組織にとって最も貴重な資産の 1 つであり、データを安全に保つことは常に最優先事項です。データベースの一般的なセキュリティ要件の 1 つは、ユーザーアクセスを制限することです。データベースユーザーが危険にさらされた場合、データに重大な損害を与える可能性があるからです。データベースユーザーの最小権限モデルに沿って実行する必要があります。つまり、作業に必要な一連の最小権限のみをユーザーに付与します。また、データベースユーザーが長期間非アクティブである場合は、無効にすることをお勧めします。これにより、ミスやセキュリティ違反による影響を制限できます。詳細については、PostgreSQL ユーザーとロールの管理を参照してください。

この記事では、アクティブでない Amazon Aurora PostgreSQL ユーザーを特定し、自動的にロックまたは削除するソリューションを提供します。記事では、サンプルコードと AWS CloudFormation テンプレートも共有しているため、そのまま開始できます。

次の使用例では、特定の日数 (この記事では 90 日) の間、非アクティブであったユーザーを特定する必要があります。これらのユーザーを特定したら、ロックアウトします。DBA は、いつでもこれらのユーザーをロック解除できます。ただし、ユーザーが 180 日間非アクティブである場合は、自動的に削除する必要があります。

ただし、PostgreSQL はこの機能をネイティブでサポートしていません。一部の商用データベースでは、この機能にユーザープロファイル機能を含めています。これにより、指定した日数の間データベースにログインしていないユーザーアカウントをロックできます。

PostgreSQL の場合、PostgreSQL はユーザーログインのタイムスタンプをどのカタログテーブルにも保存しないため、作業がより困難になります。したがって、ユーザーが非アクティブである期間を判別するのは簡単ではありません。さらに、PostgreSQL にはログイントリガーがないため、トリガーベースのソリューションを適用するのも不可能です。

ソリューション

パラメータグループでパラメータ log_connections を 1 に設定することにより、成功した各ログインの詳細 PostgreSQL へのロギングを有効にできます。このパラメータを設定すると、PostgreSQL エンジンはログインが成功するたびに次のようなメッセージを記録します。

2020-01-07 03:51:56 UTC:10.0.0.123(52820):postgres@logtestdb:[18161]:LOG: connection authorized: user=postgres database=logtestdb SSL enabled (protocol=TLSv1.2, cipher=ECDHE-RSA-AES256-GCM-SHA384, compression=off)

Aurora PostgreSQL では、これらのエンジンログを Amazon CloudWatch Logs に発行できます。ログの発行を有効にすると、スケジュールされた AWS Lambda 関数を開発することができます。この関数は、ログからこのログイン情報を解析してユーザーの最終ログインのタイムスタンプを取得して作成できます。

Lambda 関数は、以下の高レベルのステップを実行します。

ログメッセージからログイン情報を抽出する
ユーザーの最終ログインタイムスタンプをステータステーブルに保存する
各ユーザーのタイムスタンプを使用して、ロックまたは削除ポリシーを適用します。

前提条件

始める前に、以下の前提条件を必ず満たす必要があります。

パラメータグループでパラメータ log_connection を 1 に設定します。インスタンスのカスタムパラメータグループの作成については、DB パラメータグループの操作を参照してください。
PostgreSQL ログを Amazon CloudWatch Logs に発行するオプションを選択します。手順については、Aurora PostgreSQL Logs の Amazon CloudWatch Logs への発行を参照してください。
データベースのユーザー認証情報を AWS Secrets Manager に保存します。このユーザーには、rds_superuser ロールが割り当てられているか、他のユーザーをロックおよび削除するアクセス許可が必要です。
Lambda 関数コードを、Lambda 関数と同じリージョンにある Amazon S3 バケットに保存します (関数は、CloudFormation スタックを作成するのと同じリージョンに作成されます)。

ソリューションのアーキテクチャ

このソリューションで使われる AWS サービスは、以下のとおりです。

Amazon Aurora PostgreSQL – 最もリクエストの厳しいビジネスアプリケーションのリレーショナルデータベースとして機能します。
AWS CloudFormation – クラウド環境のすべてのインフラストラクチャリソースについて説明し、プロビジョニングします。
Amazon CloudWatch Events – レート式を使用して、特定の時間に Lambda 関数のトリガーアクションをスケジュールします。
Amazon CloudWatch Logs – さまざまなシステムやサービスからのログを、非常にスケーラブルな単一のサービスに一元化できます。
AWS Identity and Access Management (IAM) – ユーザー、グループ、アクセス許可を介して AWS サービスとリソースへのアクセスを安全に管理するのに役立ちます。
AWS Lambda – サーバーのプロビジョニングや管理を行うことなく、コードを実行できます。
AWS Secrets Manager – 認証情報の簡単なローテーション、管理、および取得を提供します。

次の図は、ソリューションがこれらのさまざまなサービスをどのように連携するかを示しています。

このソリューションには次の利点があります。

非アクティブなデータベースユーザーを監査し、必要に応じてロックおよび削除できます。
目的のリージョン、VPC、およびサブネットでソリューションを実行できます。
Aurora PostgreSQL に対してのみ実行できますが、Amazon RDS for PostgreSQL で動作するようにコードを変更できます。
ソリューションは、Secrets Manager を使用してデータベース認証情報を保存します。
非アクティブ状態に関係なくロックまたは削除してはならないユーザーのリストを除外できます。
このソリューションは、構成可能な非アクティブ時間制限を提供し、その後、非アクティブユーザーはロックおよび削除されます。
Lambda 関数の実行スケジュールを構成できます。

以下のセクションでは、このソリューションを実装するために各コンポーネントがどのように連携するかについて詳しく説明します。

Lambda 関数のアクション

Lambda 関数は、構成パラメータ通りにポリシーを実装します。処理には次の手順が含まれます。

AWS Secrets Manager からデータベースユーザー認証情報を取得します。
AWS CLI を使用して、Aurora クラスターの詳細を取得します。

次のテーブルが存在しない場合は作成します。

CREATE TABLE user_login_status (
user_name TEXT PRIMARY KEY,
last_login_time TIMESTAMPTZ DEFAULT now(),
status TEXT
);

CREATE TABLE user_login_job_status (
	instance_name TEXT,
	last_processed_time TIMESTAMPTZ
);

次のように、pg_users テーブルから user_login_status テーブルにユーザー名を同期します。
- pg_users から現在のユーザーリストを取得します。ただし、rdsadmin、マスターユーザー、および指定された除外ユーザーリスト (入力パラメータで指定されている場合) は除きます。
- 最終ログイン時刻を現在の時刻に設定して、新しいユーザーを user_login_status にコピーします。
- リストに存在しないユーザーを user_login_status テーブルから削除します。
- すべての新規ユーザーのテーブル user_login_status のステータス列をアクティブに設定します。
テーブル user_login_job_status から last_processed_timestamp を取得します。
- 行が存在しない場合は、最終時刻を 24 時間前に設定して行を作成します。
CloudWatch Logs で、last_processed_timestamp から始め、PostgreSQL ログからデータベースログインメッセージを読み取ります。
ログにログインメッセージが含まれている場合は、user_login_status テーブルの最終ログインタイムスタンプを更新します。
最終ログのタイムスタンプをテーブル user_login_job_status に保存します。
user_login_status テーブルから、非アクティブ時間があるユーザーのリストを取得します。
90 日間 (または希望する日数) 非アクティブであったすべてのユーザーをロックし、テーブル user_login_status のステータス列を [ロック済み] に設定します。
オブジェクトを所有していない場合は、180 日間 (または希望する日数) 非アクティブであるすべてのユーザーを削除します。オブジェクトを所有している場合は、user_login_status テーブルで [削除準備完了] をマークします。

次のセクションでは、Lambda 関数 LockNDeleteUser を使用してこのロジックを実装する詳細方法を示します。

Lambda 関数のデプロイパッケージの作成

このユーティリティは、Python Lambda 関数コードで構成されています。GitHub リポジトリから LockNDeleteUser.py のコードをダウンロードできます。このコードは、ダウンロードが必要な psycopg2 ライブラリを使用します。Lambda 関数デプロイメントパッケージを作成するには、コードとすべての依存ライブラリをバンドルする必要があります。

次の手順では、LockNDeleteUser Lambda 関数のデプロイメントパッケージを作成する手順を説明します。デプロイメントパッケージの詳細については、Python の AWS Lambdaデプロイメントパッケージを参照してください。

次の手順では、Amazon Linux を使用して Amazon EC2 インスタンスからデプロイメントパッケージを作成する方法を詳しく説明します。

Linux ホストで、デプロイメントパッケージのプロジェクトディレクトリを作成します。
```
mkdir ~/lockndeleteuser 
cd ~/lockndeleteuser 
```
GitHub リポジトリから Python コードファイル LockNDeleteUser.py を取得し、このディレクトリにコピーします。
dos2unix をインストールして、ファイルが Unix 形式であることを確認します。
```
sudo yum install -y dos2unix
dos2unix LockNDeleteUser.py
```
Python 3.6 環境がアクティブになっていることを確認します。

pip を使用してコードにインポートされる psycopg2 ライブラリをインストールします。
```
pip3 install psycopg2-binary --user
```

必要な権限を設定します。

chmod a+r ~/lockndeleteuser
chmod 744 LockNDeleteUser.py

pip を使用してインストールしたパッケージを見つけ、そこにグローバルリードアクセス権限があることを確認します。Python 3.6 の site-packages ディレクトリにあります。
```
ls /home/ec2-user/.local/lib/python3.6/site-packages
sudo chmod a+r -R /home/ec2-user/.local/lib/python3.6/site-packages
```
Python パッケージがインストールされた packages ディレクトリに移動し、ディレクトリの内容を圧縮します (ディレクトリそのものではありません)。.zip ファイルをプロジェクトフォルダに配置します。すべての隠しファイルを含めるには、オプション zip -r9 を使用します。
```
cd /home/ec2-user/.local/lib/python3.6/site-packages
zip -r9 ~/lockndeleteuser/LockNDeleteUser.zip *
```
プロジェクトディレクトリから、LockNDeleteUser.py スクリプトを zip ファイルに追加します。
```
cd ~/lockndeleteuser
zip -g LockNDeleteUser.zip LockNDeleteUser.py
```

このユーティリティの使用

このユーティリティは非常に使いやすいです。これを機能させるには、次の手順を実行します。

デプロイメントパッケージファイル LockNDeleteUser.zip を S3 バケットにアップロードします。
次のパラメータの値を用意します。
- シークレット名 – データベースの認証情報を保存する Secrets Manager のシークレット。
- S3 バケット名 – デプロイメントパッケージがアップロードされる S3 バケットの場所。
- サブネット ID – Lambda 関数が使用するサブネット。
- セキュリティグループ ID – Lambda 関数が使用するセキュリティグループ。
- LambdaFunctionName – Lambda 関数の名前。
- LambdaIAMRole – Lambda IAM ロールの ARN。Lambda 関数の実行、Secrets Manager からの読み取り、CloudWatch への読み取りと書き込みへのアクセス権があります。
- HttpsProxy (オプション) – インターネットにバインドされたリクエストに使用する必要がある https_proxy の値 (必要な場合)。
- ロック間隔 (日数) (オプション) – ロック間隔を指定すると、ユーティリティは指定日数の間非アクティブであったデータベースユーザーをロックします。この値を指定しない場合、非アクティブなユーザーはロックされません。
- 削除間隔 (日数) (オプション) – 削除間隔を指定すると、ユーティリティは指定日数の間非アクティブであったデータベースユーザーを削除します。この値を指定しない場合、非アクティブなユーザーは削除されません。
- ログレベル (オプション) – この値はデフォルトで info に設定されています。詳細なロギングについては、ロギングを debug に設定できます。
- ユーザーリストを無視する (オプション) デフォルトでは、rdsadmin とマスターユーザーはロックアクションや削除アクションからスキップされます。他のユーザーをスキップする必要がある場合は、このパラメータにリスト化できます。
- 実行率 (オプション) – デフォルトでは、このユーティリティは 24 時間ごとに実行されます。スケジュールを調整する必要がある場合は、このパラメータを使用して変更できます。
CloudFormation テンプレート JSON を GitHub リポジトリからダウンロードします。
スタック名とパラメータの値を指定して、CloudFormation スタックを作成します。CloudFormation スタックの作成については、AWS CloudFormation の仕組みを参照してください。

CloudFormation テンプレートは VPC 内に Lambda 関数を作成するため、Aurora PostgreSQL クラスターに安全にアクセスできます。Lambda 関数は、Amazon RDS API エンドポイントに接続してクラスターメタデータ情報を取得するためにインターネットアクセスを必要とします。このため、Lambda 関数に使用されるサブネットには、デフォルトルートが NAT ゲートウェイに設定されている必要があります。サブネット設定の詳細については、VPC で Lambda 関数へのインターネットアクセスを許可するにはどうすればよいですか? を参照してください。

関数を構成すると、ソリューションが配置されます。Lambda 関数は、実行率パラメータが指定する間隔ごとに呼び出されます。関数が実行されるたびに、前回の実行以降に生成されたすべての新しいログメッセージがチェックされ、ログイン情報が更新されます。

ユーザーの削除

PostgreSQL ユーザーを削除するには、ユーザーがオブジェクトを所有していない状態で、アクセス許可も持っていない場合、DROP USER コマンドを入力します。rds_superuser ロールを持つユーザーは、別のユーザーを削除できます。次のコードを参照してください。

postgres=> DROP USER testuser;
DROP ROLE
postgres=>

削除する PostgreSQL ユーザーが権限を持っているか、オブジェクトを所有している場合、このプロセスはより複雑になります。このようなユーザーを削除しようとすると、次のエラーが表示されます。

postgres=> DROP USER testuser;
ERROR: role "testuser" cannot be dropped because some objects depend on it
DETAIL: privileges for table mytable1
owner of table perm_test_table
postgres=>

この記事の Lambda 関数は、テーブルや関数などのオブジェクトを所有していない場合にのみユーザーを削除します。ターゲットユーザーがオブジェクトを所有している場合、コードはテーブル user_login_status のユーザーステータス列を [ReadyToDelete] に設定します。このテーブルでユーザーステータスを確認するプロセスを設定し、所有するオブジェクトの処理方法を決定した後、ユーザーを手動で削除できます。

このユーザーを削除するには、ユーザーに付与されているすべてのアクセス許可を取り消し、オブジェクトの所有権を変更する必要があります。これを行うには、最初にユーザーの所有権とアクセス許可を変更するアクセス許可が必要です。必要なアクセス許可がない場合、所有権を変更する次のコードが失敗します。

postgres=> REASSIGN OWNED BY testuser TO postgres;
ERROR: permission denied to reassign objects
postgres=>

最初のステップは、testuser が持つすべてのアクセス許可を、このユースケースで使用しているユーザー (この記事では、ユーザー postgres) に付与することです。次のコードを参照してください。

postgres=> GRANT testuser TO postgres;
GRANT ROLE
postgres=>

postgres が testuser にすでに付与されている場合、上記のコードは失敗します。その場合は、次のコードを入力してアクセス許可を取り消す必要があります。

REVOKE postgres FROM testuser;

このアクセス許可を付与すると、postgres ユーザーは testuser が所有するオブジェクトを変更および削除できます。これで、すべてのオブジェクトの所有権を再度割り当てできます。

postgres=> REASSIGN OWNED BY testuser TO postgres;
REASSIGN OWNED
postgres=>

上記のコマンドを使用すると、testuser が所有するすべてのオブジェクトが postgres ユーザーに転送されます。ただし、許可はまだ保留中であるため、削除は引き続き失敗します。

postgres=> DROP USER testuser;
ERROR: role "testuser" cannot be dropped because some objects depend on it
DETAIL: privileges for table mytable1
postgres=>

testuser が所有するすべてのものを削除します。すべてのオブジェクトが postgres ユーザーにすでに再割り当てされているため、安全になりました。次のコードは、現在 testuser に割り当てられている権限を削除します。

postgres=> DROP OWNED BY testuser;
DROP OWNED
postgres=>

上記コードのスコープが及ぶのは、接続しているデータベースのみです。複数のデータベースがある場合は、各データベースに接続して、これらの手順を繰り返す必要があります。

すべてのアクセス許可を取り消して再度割り当てたら、ユーザーを削除できます。

postgres=> DROP USER testuser;
DROP ROLE
postgres=>

ユーザーのロック解除

ユーティリティが非アクティブなユーザーをロックアウトした後、ユーザーがデータベースにアクセスする場合は、ロックを解除できます。ユーザーのロックを解除するには、次の SQL ステートメントを入力します。

ALTER USER some_user CONNECTION LIMIT -1;

UPDATE user_login_status 
   SET last_login_time=now(),
       status= 'Active' 
 WHERE user_name='some_user';

ユーザーを削除した場合は、ユーザーを再度作成し、必要なアクセス許可を付与する必要があります。

制限

前述のように、PostgreSQL はデータベースユーザーのログインタイムスタンプを保存しません。したがって、ユーティリティが初めて実行されるときは、キャプチャして CloudWatch Logs に公開されたログに依存します。ログにユーザーのログイン情報が含まれていない場合、ユーティリティは現在のタイムスタンプを最終ログインタイムスタンプと見なします。

このソリューションで、Lambda 関数は指定された時間間隔で実行されるようにスケジュールされています。したがって、ロックアクションと削除アクションは、Lambda 関数が実行されているときに発生し、90 日または 180 日の境界で実行されているときに発生するものではありません。たとえば、Lambda 関数が 24 時間ごとに実行されるようにスケジュールされているとします。実行したアクションのいずれかによって、ユーザーが 89 日と 23 時間非アクティブであると判断します。これは 90 日といった基準を下回っているので、ユーザーはこの実行によってロックされることはありません。ユーザーは 1 時間後にロックされる必要がありますが、次の Lambda 関数の実行は 24 時間後にトリガーされるため、このユーザーは 90 日のマークを超えてロック解除されたままになります。

前述のように、Lambda 関数は設計上、テーブルや関数などのデータベースオブジェクトを所有するユーザーを削除しません。PostgreSQL では、オブジェクトを所有するユーザーを削除することはできません。このようなユーザーを削除するには、オブジェクトを削除するか、その所有権を変更する必要があります。オブジェクトを削除したり、オブジェクトの所有権を自動的に変更したりすることは危険であるため、Lambda 関数は、user_login_status テーブルでこのユーザーのステータスを [ReadyToDelete] に変更するだけにしています。DBA は、このテーブルでユーザーのステータスを確認し、ユーザーを手動で削除する必要があります。

データベースに非常に多数の接続を取り込んでいる場合、実行される各 Lambda 関数が処理する必要のある多数の接続ログメッセージが発生します。Lambda 関数が接続メッセージを処理するのにかかる時間を決定するためにテストを行う必要があります。接続の負荷が高い場合は、Lambda 関数をより頻繁に実行することも検討してください。これにより、1 回の実行で 1 日 1 回実行される単一の Lambda 関数ではなく、ログメッセージの小さなチャンクが各実行で確実に処理され、その日に収集されたすべての接続ログメッセージが処理されます。

まとめ

この記事では、非アクティブな Aurora PostgreSQL ユーザーを特定し、セキュリティポリシーに従ってロックまたは削除するメカニズムを実装する方法について説明しました。このソリューションを試すときは、特定の要件を満たすようにコードをテストおよび変更する必要があります。アラートを追加してジョブが実行されていることを確認し、ユーザーがロックまたは削除されたときに通知を送信することについても検討する必要があります。

いつものように、AWS では皆さんのフィードバックをお待ちしています。ご意見やご質問があれば、コメント欄にご記入ください。

著者について

Amishi Shah は DevOps コンサルタントで、アマゾンウェブサービスのプロフェッショナルサービスチームに所属しています。お客様とともに、AWS クラウドでスケーラブルで可用性の高い、安全なソリューションを構築しています。技術、組織、SDLC の大規模な変革を通じてエンタープライズのお客様を手引きすることに取り組んでいます。

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

David Rader は、アマゾンウェブサービスのデータベースエンジニアリングマネージャーです。

Amazon Web Services ブログ