如何使用 pgaudit 扩展审计正在运行 PostgreSQL 的 Amazon RDS 数据库实例？

上次更新时间：2019 年 10 月 3 日

我想要审计所有的数据库、角色、关系或列，并且希望向不同的角色提供不同级别的审计。如何为运行 PostgreSQL 的 Amazon Relational Database Service (Amazon RDS) 数据库实例上的不同角色级别配置 pgaudit 扩展？

简短描述

您可以设置不同的参数记录您的 PostgreSQL 数据库实例上的活动。要审计不同的数据库、角色、表或列，您可以使用 pgaudit 扩展。启用 pgaudit 扩展后，您可以配置 pgaudit.log 参数来审计特定的数据库、角色、表和列。

解决方法

在运行 PostgreSQL 的 Amazon RDS 实例上启用 pgaudit 扩展

1.通过运行以下命令创建称为 rds_pgaudit 的特定数据库角色：

CREATE ROLE rds_pgaudit;
CREATE ROLE

2.修改与您的数据库实例关联的数据库参数组，以使用包含 pgaudit 的共享库并设置参数 pgaudit.role。然后，将 pgaudit.role 设置为您创建的角色 rds_pgaudit。

3.重启实例，以便对参数组的更改可以应用到该实例。

4.通过运行以下命令确认 pgaudit 被初始化：

show shared_preload_libraries;
shared_preload_libraries 
--------------------------
rdsutils,pgaudit
(1 row)

5.通过运行以下命令创建 pgaudit 扩展：

CREATE EXTENSION pgaudit;
CREATE EXTENSION

6.通过运行以下命令确认 pgaudit.role 已被设置为 rds_pgaudit：

show pgaudit.role;
pgaudit.role 
------------------
rds_pgaudit

7.配置 pgaudit.log 参数以审计以下任何内容：

ALL 用于审计以下命令。 MISC_SET 用于审计其他 SET 命令，如 SET ROLE。 MISC 用于审计其他命令，如 DISCAR、FETCH、CHECKPOINT、VACUUM、SET。 DDL 用于审计不包含在 ROLE 类中的所有数据描述语言 (DDL)。 ROLE 用于审计与角色和权限相关的语句，如 GRANT、REVOKE、CREATE/ALTER/DROP ROLE。 FUNCTION 用于审计函数调用和 DO 数据块。 WRITE 在目标为关系时审计 INSERT、UPDATE、DELETE、TRUNCATE 和 COPY。 READ 在源为关系或查询时审计 SELECT 和 COPY。

根据您要审计的内容，设置数据库、角色或表的 pgaudit.log 参数值。

使用 pgaudit 扩展来审计数据库

1.要设置数据库、角色或表的 pgaudit.log 参数值，请在参数组级别将参数 pgaudit.log 设置为无：

> show pgaudit.log
+---------------+
| pgaudit.log   |
|---------------|
| none          |
+---------------+
SHOW

2.运行以下命令，以仅在此数据库中覆盖此参数的系统设置：

ALTER DATABASE test_database set pgaudit.log='All';

此操作会将参数 pgaudit.log 的值更改为 All，因此，test_database 为 RDS 数据库实例中审计的唯一数据库。

3.连接到 test_database 并运行以下查询：

select * from test_table;

错误日志的输出类似于以下内容：

2019-06-25 19:21:35 UTC:192.0.2.7(39330):testpar@test_database::LOG: AUDIT: SESSION,2,1,READ,SELECT,,,select * from test_table;,<not logged>

使用 pgaudit 扩展审计角色

与在数据库级别配置 pgaudit.log 参数类似的是，将角色修改为具有不同的 pgaudit.log 参数值。在以下示例命令中，角色 test1 和 test2 被更改为具有不同的 pgaudit.log 配置。

1.通过运行以下命令为 test1 和 test2 设置不同的 pgaudit.log 参数值：

ALTER ROLE test1 set pgaudit.log='All';

ALTER ROLE test2 set pgaudit.log='DDL';

2.通过运行以下查询检查并确认已在角色级别进行修改：

> select rolname,rolconfig from pg_roles where rolname in ('test1','
                  test2');
+-----------+----------------------+
| rolname   | rolconfig            |
|-----------+----------------------|
| test1     |  |
| test2     |  |
+-----------+----------------------+
SELECT 2
Time: 0.010s 

3.同时为 test1 和 test2 运行以下查询：

CREATE TABLE test_table (id int);
CREATE TABLE

select * from test_table;
id 
----
(0 rows)

test1 的日志输出类似于以下内容：

...
2019-06-26 14:51:12 UTC:192.0.2.7(44754):test1@postgres::LOG:  
AUDIT: SESSION,1,1,DDL,CREATE TABLE,,,CREATE TABLE test_table (id 
int);,<not logged>

2019-06-26 14:51:18 UTC:192.0.2.7(44754):test1@postgres::LOG:  
AUDIT: SESSION,2,1,READ,SELECT,,,select * from test_table;,<not 
logged>
...

运行相同的查询后，test2 的日志输出类似于以下内容：

...
2019-06-26 14:53:54 UTC:192.0.2.7(44772):test2@postgres::LOG:  
AUDIT: SESSION,1,1,DDL,CREATE TABLE,,,CREATE TABLE test_table (id 
int);,<not logged>
... 

注意：SELECT 查询没有审计条目，这是因为 test2 的 pgaudit.log 参数仅被配置为 DDL。

使用 pgaudit 扩展审计表

配置 pgaudit.log 参数审计并记录影响特定关系的语句。pgaudit 扩展只会记录 SELECT、INSERT、UPDATE 和 DELETE 命令。TRUNCATE 不包含在对象审计日志记录中。如果您授予 rds_pgaudit 角色访问您要审计的表中某个操作（如 SELECT、DELETE、INSERT 或 UPDATE）的权限，任何授权审计都会记录相应的语句。以下示例授予 rds_pgaudit 角色访问 SELECT 和 DELETE 的权限，因此，test_table 上的所有 SELECT 和 DELETE 语句都将被审计。

1.通过运行以下命令向 rds_pgaudit 角色授予访问 SELECT 和 DELETE 的权限：

grant select, delete on test_table to rds_pgaudit;

2.通过在 test_table 上运行 DELETE 语句测试审计日志记录已被正确配置：

Time: 0.008s 
DELETE 1
>delete from test_table where pid=5050;

DELETE 语句的输出类似于以下内容：

2019-06-25 17:13:02 
UTC:192.0.2.7(41810):postgresql104saz@postgresql104saz::LOG: 
AUDIT: OBJECT,3,1,WRITE,DELETE,TABLE,public.t1,delete from test_table where 
pid=5050,<not logged>

使用 pgaudit 扩展审计列

您还可以为特定表设置列级别的审计，例如，当只有一列包含敏感数据时。在以下示例命令中，创建了一个工资单表，且该表有一个必须审计的包含工资数据的敏感列：

create table payroll
(    
    name text,
    salary text
);

1.授予 rds_pgaudit 角色访问工资列中 SELECT 的权限，以便此列中的任何 SELECT 都将被审计：

grant select (salary)  on payroll to rds_pgaudit;

2.选择表中的全部列，包括工资列：

select * from payroll;

在以下示例输出中，包含工资列的任何 SELECT 均已被审计。但不包含工资列的 SELECT 未被审计。

2019-06-25
 18:25:02 
UTC:192.0.2.7(42056):postgresql104saz@postgresql104saz::LOG: 
AUDIT: OBJECT,2,1,READ,SELECT,TABLE,public.payroll,select * from 
payroll,<not logged>