如何管理不活跃 Amazon Aurora PostgreSQL 用户

Original URL：https://aws.amazon.com/cn/blogs/database/managing-inactive-amazon-aurora-postgresql-users/

对于各类组织机构而言，数据可以说是当下最具价值的资产之一，而保障数据安全也成为组织内的头等大事。作为数据库中的一项常规安全要求，我们需要严格限制用户的访问权限——否则一旦数据库用户账户遭到窃取，可能会对现有数据造成重大破坏。我们应在数据库用户层面遵循最低权限原则，意味着仅向用户授予其执行必要工作所需的最低权限集合。此外，如果数据库用户长时间不活动，则最好将其禁用以降低可能引发的错误或安全影响。关于更多详细信息，请参阅管理PostgreSQL用户与角色。

本文将介绍一套解决方案，用于识别不活跃Amazon Aurora PostgreSQL用户，自动锁定对应账户或将其删除。本文还将分享示例代码与一套AWS CloudFormation模板，帮助您快速建立起这样一套用户管理机制。

在以下用例中，我们要确定在特定天数内（本文设定为90天）处于非活跃状态的用户。在标记这些用户后，我们将执行锁定操作。作为数据库管理员，您可以随时解锁这些用户；但如果用户在180天内始终处于非活跃状态，则自动将其删除。

但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。关于为实例创建定制化参数组的更多详细信息，请参阅数据库参数组使用说明。
选择对应选项，将PostgreSQL日志发布至Amazon CloudWatch Logs。关于具体操作，请参阅将Aurora PostgreSQL日志发布至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身份与访问管理（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 Secret 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表中提取当前用户（不包括主用户redsadmin，以及被输入参数排除在外的用户）。
- 将新用户复制于user_login_status，并将最后登录时间设置为当前时间。
- 从user_login_status表中删除列表中不存在的用户。
- 在user_login_status表中，将所有新用户的状态列设置为Active。
从 user_login_job_status表中提取last_processed_timestamp。
- 如果不存在任何行，请创建一行并将时间设置为24小时之前。
在CloudWatch Logs的PostgreSQL日志中，以last_processed_timestamp为起点读取数据库登录消息。
如果日志中包含有登录消息，则将最后登录时间戳更新至user_login_status表。
将最后登录时间戳保存在 user_login_job_status表中。
从user_login_status表中获取不活跃用户以及对应时间。
锁定所有不活跃周期达到90天的用户（也可指定其他天数），并在user_login_status表中将这些用户的状态列设定为Locked。
删除所有不活跃周期达到180天且不包含任何对象的用户（也可指定其他天数）。如果其中包含对象，则在user_login_status表中将其标记为Ready to delete。

在以下章节中，我们将探讨如何使用Lambda函数LockNDeleteUser实现上述逻辑。

创建Lambda函数部署包

此实用程序由Python Lambda函数代码组成。您可以从GitHub repo中直接下载LockNDeleteUser.py代码。代码中使用到psycopg2库，也需要提前下载完成。要创建一套Lambda函数部署包，我们需要将代码与所有依赖库捆绑在一起。

以下步骤将引导您为LockNDeleteUser Lambda函数创建部署包。关于部署包的更多详细信息，请参阅Python中的AWS Lambda部署包。

通过以下步骤，我们可以使用Amazon Linux在Amazon EC2实例上创建部署包：

在您的Linux主机上，为部署包创建项目目录：
```
mkdir ~/lockndeleteuser 
cd ~/lockndeleteuser 
```
从GitHub repo处获取Python代码文件LockNDeleteUser.py，并将其复制到项目目录内：

保证该文件为Unix格式，安装dos2unix：

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软件包的目录，压缩目录中的所有内容（注意，不是压缩目录本身）。将.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 name – 即Secrets Manager保存的数据库凭证。
- S3 bucket name – 作为部署包上传目标的S3存储桶位置。
- Subnet ID(s) – 供Lambda函数使用的子网。
- Security Group ID(s) – 供Lambda函数使用的安全组。
- LambdaFunctionName – Lambda函数的名称。
- LambdaIAMRole – Lambda IAM角色的ARN，其应有权执行Lambda函数、从Secrets Manager中读取凭证并对CloudWatch进行读取与写入。
- HttpsProxy (可选) – https_proxy的值，用于在必要时做为互联网请求代理。
- Lock Time Interval in Days (optional) – 如果您设定了锁定间隔，而数据库用户确实在指定天数之内处于非活跃状态，则实用程序将锁定该数据库用户。如果不设置任何值，则不会锁定任何不活跃用户。
- Delete Time Interval in Days (optional) – 如果您设定了删除间隔，而数据库用户确实在指定天数之内处于非活跃状态，则实用程序将删除该数据库用户。如果不设置任何值，则不会删除任何不活跃用户。
- Log Level (可选) – 此值被默认设置为info。要获取更多细节记录，您可以将其设置为debug。
- Ignore Users List (可选) – 在默认情况下，rdsadmin与主用户将不会受到锁定与删除操作的影响。如果还需要排除其他用户，请将其在此参数中列出。
- Execution Rate (可选) – 在默认情况下，此实用程序每24小时运行一次。如果您需要调整计划，则可通过此参数进行变更。
从GitHub repo处下载CloudFormation模板的JSON文件。
提供栈名称与参数值以创建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。作为数据库管理员，您需要检查此表中的用户状态并手动删除对应用户。

如果您的数据库拥有大量传入连接，则需要频繁运行Lambda函数以处理连接日志消息。为此，我们应该通过测试确定Lambda函数处理各连接消息所耗费的时间。在高连接负载情况下，我们还应考虑提高Lambda函数的运行频率，确保其每次运行只需要处理一小部分日志消息，而不必以每天运行一次的形式将过去24小时内的消息积攒起来。

总结

本文向您解释如何根据当前安全策略识别不活跃的Aurora PostgreSQL用户，并对其执行锁定或删除。在尝试使用此解决方案时，请测试并不断修改代码以满足您的实际需求。您还应考虑添加警报机制以保证当前作业的正常运行，并在用户被锁定或删除时发出通知。

AWS欢迎您提出各类反馈意见，请在评论区中分享您的体验与疑问。

亚马逊AWS官方博客