惊爆！数据库安全的 “守护者”，竟有如此神奇力量！

青年数据库学习互助会 2025-03-03

点击上方蓝字，关注我们

想学会更多实用技巧，欢迎加入青学会MOP技术社区（实名社区）。

加入方法：公众号后台回复关键字“加入”获取小助手微信，添加后登记入会。

同时欢迎大家在评论区留言互动交流！社区会不定期举行相关的抽奖、公开分享活动。

如果你有想了解的知识点希望我们发文可以后台私信。

最近联合几个 Oracle ACE技术专家开通了一个付费微信群，都是具有10多年金融、医疗、制造业10年以上的一线专家，坑位费399/人，无限期，目前群内近150人。加群后会有一些福利（分享各类技术文档，干货资源，问题解答等等），更有特邀嘉宾会定期在群内直播，解读AWR，快问快答等！有问题我们尽量都解答，毕竟399不能都解决所有问题。有兴趣联系微：ywu0613

内部知识库正在筹建中,不止有oracle!

正文开始

在数据库管理的世界里，安全性一直是大家非常关注的话题。今天，我想给大家介绍一个超级实用的工具——PostgreSQL 审计扩展（pgAudit）。它就像是数据库的“守护者”，能帮我们记录下数据库里发生的各种操作，方便我们随时查看和管理。下面，我就来带大家了解一下这个强大的工具。

什么是 pgAudit？

pgAudit 是一个开源的 PostgreSQL 审计扩展，它可以通过标准的 PostgreSQL 日志记录工具，为我们提供详细的会话和对象审计日志。简单来说，它就像是一个“数据库监控器”，能帮我们记录下数据库里的各种操作，比如谁在什么时候执行了什么操作、修改了哪些数据等等。这些记录对于数据库的安全审计非常重要，能帮助我们追踪问题、排查故障，还能满足一些政府、金融或 ISO 认证的要求。

为什么要用 pgAudit？

在没有 pgAudit 的情况下，PostgreSQL 的标准日志记录功能只能提供一些基本的语句日志，比如用户执行了什么操作。但是，这些日志往往不够详细，无法满足审计的需求。比如，如果一个审计人员想要确认某个表是否在特定的时间段内被创建，标准日志可能就显得力不从心了。

举个例子，如果有人用动态 SQL 创建了一个表，标准日志可能只会显示一个模糊的记录，像这样：

LOG:  statement: DO $$
BEGIN
    EXECUTE 'CREATE TABLE import' || 'ant_table (id INT)';
END $$;

复制

从这条日志里，我们根本看不出具体创建了什么表。而 pgAudit 就能解决这个问题，它会把详细的语句和相关信息都记录下来，比如：

AUDIT: SESSION,33,1,FUNCTION,DO,,,"DO $$
BEGIN
    EXECUTE 'CREATE TABLE import' || 'ant_table (id INT)';
END $$;"
AUDIT: SESSION,33,2,DDL,CREATE TABLE,TABLE,public.important_table,CREATE TABLE important_table (id INT)

复制

这样一来，我们就能清楚地看到创建的表名、语句类型等详细信息，大大方便了审计工作。

如何安装 pgAudit？

安装 pgAudit 其实并不复杂，下面我就来简单说一下步骤：

下载 pgAudit：我们可以从 pgAudit 的 GitHub 页面 (https://github.com/pgaudit/pgaudit)下载最新的版本。
解压并进入目录：将下载的文件解压后，进入解压后的目录。

编译并安装：使用以下命令进行编译和安装：

make install USE_PGXS=1 PG_CONFIG=/postgresql/pg16/bin/pg_config

复制

配置 PostgreSQL：在 postgresql.conf
文件中，添加或修改 shared_preload_libraries
，增加 pgaudit
的支持。
重启数据库：执行 pg_ctl restart
命令，重启数据库服务。
创建扩展：在数据库中执行 create extension pgaudit;
，创建 pgAudit 扩展。

安装完成后，我们就可以开始使用 pgAudit 了。

pgAudit 的配置参数

pgAudit 提供了许多配置参数，我们可以根据自己的需求进行设置。下面是一些常用的参数：

**pgaudit.log
**：这个参数用来指定需要记录的语句类型，比如 READ
（读操作）、WRITE
（写操作）、DDL
（数据定义语言）等。我们可以用逗号分隔多个类型，还可以用减号来排除某些类型。
**pgaudit.log_catalog
**：这个参数决定了是否记录 pg_catalog
中的对象操作。如果禁用它，可以减少一些不必要的日志记录。
**pgaudit.log_client
**：这个参数控制日志消息是否对客户端可见，比如 psql。通常我们会禁用它，但在调试时可能会用到。
**pgaudit.log_level
**：这个参数用来设置日志的级别，比如 LOG
、ERROR
等。注意，ERROR
、FATAL
和 PANIC
是不允许的。
**pgaudit.log_parameter
**：这个参数决定是否记录语句的参数。如果启用，参数会以 CSV 格式记录在日志中。
**pgaudit.log_relation
**：这个参数控制是否为每个引用的关系（比如表、视图）创建单独的日志条目。启用它可以让我们更方便地追踪特定表的操作。

这些参数的默认值大多是 off
或 none
，我们需要根据自己的需求进行调整。

使用案例

为了更好地理解 pgAudit 的用法，我们来看一个简单的案例。

配置审计角色

首先，我们创建一个审计角色 wyaudit
：

create role wyaudit with password 'wy123456' login;

复制

然后，我们把这个角色设置为 pgAudit 的主角色：

alter system set pgaudit.role ='wyaudit';

复制

接下来，我们重新加载配置文件，让设置生效：

select pg_reload_conf();

复制

创建测试表并插入数据

我们创建一个测试表 audit_wy
，并插入一些数据：

create table audit_wy(idint, namevarchar);
grantselect, insert, deleteon public.audit_wy to wyaudit;
insertinto audit_wy values(1,'老徐');
insertinto audit_wy values(2,'老李');
update audit_wy setname='总监'whereid=1;
select * from audit_wy;

复制

启用会话日志记录

我们可以通过设置 pgaudit.log
来启用会话日志记录：

set pgaudit.log = 'write,ddl';
set pgaudit.log_relation = on;

复制

查看审计日志

最后，我们查看生成的审计日志。日志文件通常会存储在数据库的日志目录下，比如 postgresql-2024-01-19_014904.csv
。打开文件，我们可以看到类似这样的记录：

AUDIT: OBJECT,1,1,WRITE,INSERT,TABLE,public.audit_wy,"insert into audit_wy values(1,'老徐');",<not logged>
AUDIT: OBJECT,2,1,WRITE,INSERT,TABLE,public.audit_wy,"insert into audit_wy values(2,'老李');",<not logged>
AUDIT: OBJECT,3,1,WRITE,UPDATE,TABLE,public.audit_wy,update audit_wy set name='总监' where id=1;,<not logged>

复制

从这些日志中，我们可以清楚地看到每个操作的详细信息，比如操作类型、表名、执行的语句等。

卸载 pgAudit

如果我们不再需要 pgAudit，可以按照以下步骤卸载：

修改配置文件：打开 postgresql.conf
文件，注释掉 pgaudit
相关的配置。
重置审计角色：执行 alter system reset pgaudit.role;
。
删除扩展：执行 drop extension pgaudit;
。
重启数据库：执行 pg_ctl restart
。

注意事项

在使用 pgAudit 时，有一些注意事项需要大家留意：

日志量可能很大：根据我们的设置，pgAudit 可能会生成大量的日志记录。因此，我们需要仔细确定需要审计的内容，避免记录过多不必要的信息。
OLAP 和 OLTP 环境：在 OLAP 环境中，如果对大型事实表进行审计日志记录，可能会导致日志文件迅速膨胀，占用大量磁盘空间。在 OLTP 环境中，即使插入量不高，审计日志也可能对性能产生明显影响。因此，我们需要在测试时评估性能影响，并合理分配日志存储空间。

总的来说，pgAudit 是一个非常强大的工具，能帮助我们更好地管理和审计数据库操作。希望今天的介绍能帮助大家更好地理解和使用它！

END