Postgres扩展之PGAudit：审计

简介

PGAudit是一个专为PostgreSQL数据库设计的审计扩展模块，它为用户提供了详细的查询和命令审计功能，有助于满足监管要求并保护敏感信息。这可以帮助您满足法规的审计要求，检测可疑的数据库活动，识别并修复数据库问题。

功能特点

全面审计能力：

PGAudit能够记录对数据库的所有读写操作，包括SELECT、INSERT、UPDATE、DELETE等SQL命令，以及DDL（如CREATE TABLE）和系统权限修改等。
它支持行模式和语句模式两种日志输出方式：行模式记录每个受影响的行，语句模式则记录整个SQL语句。

灵活配置：

用户可以根据自己的需求配置审计规则，选择监视特定用户、特定表或特定操作类型。
支持全局审计和角色审计，允许针对特定用户或所有用户的审计配置。

集成与定制：

PGAudit集成了PostgreSQL的标准日志机制，可以利用现有的日志处理工具（如logrotate或syslog）来管理和存储审计日志。
日志格式可以定制，以适应不同的日志分析工具。

合规性支持：

对于金融、医疗等受严格法规约束的行业，PGAudit可以帮助满足PCI DSS、HIPAA等合规性要求。
它能够生成符合合规性标准的审计报告。

安全性增强：

通过审计功能，数据库管理员可以检测潜在的安全风险，如未经授权的访问或数据修改。
可以记录和报告异常或可疑的数据库活动。

性能优化：

分析查询历史，找出低效的SQL，有助于提升数据库性能。
尽管PGAudit会生成大量的日志记录，但它使用高效的日志记录机制，可以在不影响数据库性能的情况下记录和监视数据库活动。

工作原理

PGAudit在PostgreSQL内核级别插入审计钩子。当执行查询或命令时，这些钩子捕获并记录相关信息，如事件类型、用户名、时间戳、SQL语句等，并将这些信息写入审计日志文件中。

以下是一个简单的教程，指导你如何配置 PGAudit 并在 PostgreSQL 中使用它。

准备工作

登录MemFire Cloud平台，创建一个新应用，如下图所示：

应用创建成功后，即可获得一个云端的Postgres数据库。

启用PGAudit扩展

转到仪表板中的数据库页面。
点击侧边栏中的扩展。
搜索pgaudit并启用该扩展。

以下是如何启用和禁用 pgaudit扩展的 SQL 代码：

-- Enable the "pgaudit" extension
create extension pgaudit;

-- Disable the "pgaudit" extension
drop extension if exists pgaudit;

即使 SQL 代码是 create extension，这等同于启用扩展。要禁用扩展，你可以调用 drop extension。

配置扩展

PGAudit可以用不同的精度级别进行配置。

PGAudit日志记录精度：

会话：记录连接内的活动，比如psql连接。
用户：记录特定数据库用户（例如，匿名用户或postgres）的活动。
全局：记录整个数据库的活动。
对象：记录与特定数据库对象（例如，auth.users表）相关的事件。

尽管会话、用户和全局模式在精度上有所不同，但它们都被认为属于会话模式，并且使用相同的输入类别进行配置。

会话模式类别

这些模式可以监控数据库操作的预定义类别：

类别	它记录的内容	描述
read	数据检索（SELECT, COPY）	跟踪正在访问的数据。
write	数据修改（INSERT, DELETE, UPDATE, TRUNCATE, COPY）	跟踪对数据库所做的更改。
function	函数、过程和DO/END块的执行	跟踪例程/函数的执行
role	用户管理操作（用户和权限上的CREATE, DROP, ALTER）	跟踪用户权限和访问的更改。
ddl	架构更改（CREATE, DROP, ALTER语句）	监控对数据库结构（表、索引等）的修改。
misc	不太常见的命令（FETCH, CHECKPOINT）	如果需要，捕获不太常见的操作以进行深入分析。
all	以上所有	综合日志记录，用于完整的审计跟踪。

会话日志记录

当您在会话环境中连接，例如psql连接时，您可以配置PGAudit以记录会话内启动的事件。

在会话中，默认情况下，PGAudit不会记录任何内容：

-- 返回'none'
SHOW pgaudit.log;

查看结果如下：

在会话中，您可以设置pgaudit.log变量以记录事件：

-- 记录CREATE, ALTER和DROP事件
SET pgaudit.log = 'ddl';
-- 记录所有CREATE, ALTER, DROP和SELECT事件
SET pgaudit.log = 'read, ddl';
-- 不记录任何内容
SET pgaudit.log = 'none';

用户日志记录

在某些情况下，您可能想要监控数据库用户的活动。例如，假设您将数据库连接到Zapier并为其创建了一个自定义角色：

CREATE USER "test" WITH PASSWORD '<new password>';

您可能想要记录zapier发起的所有操作，这可以通过以下命令完成：

ALTER ROLE "test" SET pgaudit.log to 'all';

执行结果如下：

要删除设置，请执行以下代码：

-- 禁用角色的日志记录
ALTER ROLE "test" SET pgaudit.log to 'none';
-- 检查更改是否已最终确定：
SELECTrolname,rolconfig
FROM pg_roles
WHERE rolname = 'test';
-- 应该返回一个包含"pgaudit.log=none"的rolconfig路径

执行结果如下：

全局日志记录

以下SQL配置PGAudit记录与"postgres"角色关联的所有事件。由于它具有广泛的权限，这实际上监控了所有数据库活动。

ALTER ROLE "postgres" SET pgaudit.log to 'all';

要检查postgres角色是否正在审计，请执行以下命令：

SELECTrolname,rolconfig
FROM pg_roles
WHERE rolname = 'postgres';
-- 应该返回一个包含"pgaudit.log=all"的rolconfig路径

执行结果如下：

要删除设置，请执行以下代码：

ALTER ROLE "postgres" SET pgaudit.log to 'none';

对象日志记录

要微调PGAudit将记录哪些对象事件，您必须创建一个具有有限权限的自定义数据库角色：

CREATE ROLE "some_audit_role" NOINHERIT;

没有其他Postgres用户可以通过此角色进行假设或登录。它仅存在以安全地定义PGAudit将记录的内容。

创建角色后，您可以通过将pgaudit.role变量分配给它来指导PGAudit进行日志记录：

ALTER ROLE "postgres" SET pgaudit.role to 'some_audit_role';

然后，您可以将角色分配给仅监控批准的对象事件，例如包括特定表的select语句：

GRANT SELECT ON random_table TO "some_audit_role";

有了这个权限，PGAudit将记录所有引用random_table的选择语句，无论谁或什么实际启动了事件。所有可分配的权限可以在Postgres文档中查看。

如果您不再希望使用对象日志记录，您将需要取消分配pgaudit.role变量：

-- 更改pgaudit.role，不再引用some_audit_role
ALTER ROLE "postgres" SET pgaudit.role to '';
-- 查看pgaudit.role是否已更改的以下命令：
SELECTrolname,rolconfig
FROM pg_roles
WHERE rolname = 'postgres';
-- 应该返回一个包含"pgaudit.role="的rolconfig路径

解析审计日志

PGAudit旨在将日志存储为CSV文件，具有以下标题：

标题	描述
AUDIT_TYPE	会话或对象
STATEMENT_ID	此会话的唯一语句ID。即使某些语句未记录，也是连续的。
SUBSTATEMENT_ID	主语句中每个子语句的顺序ID。即使有些未记录，也是连续的。
CLASS	..., 读取，角色（见pgaudit.log）。
COMMAND	..., 改变表，选择。
OBJECT_TYPE	表，索引，视图等。适用于SELECT，DML和大多数DDL语句。
OBJECT_NAME	完全限定的对象名称（例如，public.account）。适用于SELECT，DML和大多数DDL。
STATEMENT	在后端执行的语句。
PARAMETER	如果设置了pgaudit.log_parameter，则此字段包含语句参数作为引用的CSV，或<none>。否则，它是<not logged>。

由以下创建语句生成的日志：

CREATE TABLE account (id int primary key,name text,description text
);

生成以下日志：

AUDIT: SESSION,1,1,DDL,CREATE TABLE,TABLE,public.account,create table account(id int,name text,description text
); <not logged>

查找和过滤审计日志

由PGAudit生成的日志可以在Postgres日志中找到。要查找特定日志，您可以使用日志浏览器。以下是一个基本示例，用于提取引用CREATE TABLE事件的日志：

SELECTcast(t.timestamp as datetime) as timestamp,event_message
FROMpostgres_logs as tcross join unnest(metadata) as mcross join unnest(m.parsed) as p
WHERE event_message like 'AUDIT%CREATE TABLE%'
ORDER BY timestamp desc
LIMIT 100;

实践示例

监控API事件

要监控Postgrest API角色发起的所有写入：

ALTER ROLE "authenticator" SET pgaudit.log to 'write';
-- 上述等同于：
-- ALTER ROLE "anon" SET pgaudit.log TO 'write';
-- ALTER ROLE "authenticated" SET pgaudit.log TO 'write';
-- ALTER ROLE "service_role" SET pgaudit.log TO 'write';

监控`auth.users`表

在最坏的情况下，如果特权角色的密码被泄露，您可以使用PGAudit来监控是否针对了auth.users表。应该指出，API请求已经在API边缘网络中被监控，这更多的是关于提供对数据库层面正在发生的事情的更大清晰度。

以对象模式记录auth.user需要一个自定义角色：

-- 创建日志记录角色
CREATE ROLE "auth_auditor" NOINHERIT;
-- 给角色权限以观察相关表事件
GRANT SELECT ON auth.users TO "auth_auditor";
GRANT DELETE ON auth.users TO "auth_auditor";
-- 将auth_auditor分配给pgaudit.role
ALTER ROLE "postgres" SET pgaudit.role to 'auth_auditor';

有了上述代码，任何涉及从auth.users表读取或删除的查询都将被记录。

注意事项

在配置pgAudit时，应注意避免生成过多的日志，因为这可能会对数据库性能产生负面影响。
应根据实际需求和安全策略来配置会话日志记录参数，以确保只记录必要的日志信息。
在使用pgAudit时，应定期检查和分析日志，以便及时发现和处理潜在的安全问题或异常行为。