PostgreSQL兼具审计与反背锅功能之pgAudit插件介绍

前言

pgAudit插件几乎在各大云厂商里都成为了标配，意味着它是真的很重要。过去，网上时不时的爆出消息，一些DBA（也有可能不是DBA)可能执行了不可饶恕的误操作，结果被迫”提桶跑路“。这一方面，反映了所在单位对数据库开发与维护的安全意识非常薄弱，另一方面，数据备份是DBA安身立命之本，这也往往被一些初入门的DBA所忽略。

pgAudit可以记录针对目标数据库的一些重要的SQL操作，即算有此误操作，无论是有意的还是恶意的，都能够留下相关证据。另一方面，有些DDL操作，如果在它之前没有完整的基础备份，如果是真的误操作，想恢复起来也是碰运气的一件事。如果有相关的Audit日志，至少你能找到准确的时间段，做进一步分析，然后结合第三方工具，有更大的希望去恢复。

像这种大型云厂商都拿它做标配的插件，要全面掌握PostgreSQL，强烈建议熟悉并熟练掌握它的使用。他们拿来做标配，证明这个东西做的很成熟，也很实用。至于“反背锅”，是啊，提供的就是证据，谁也做不了假 。

PostgreSQL审计扩展(pgAudit)通过标准的PostgreSQL日志工具提供详细的会话和/或对象审计日志。

pgAudit的目标是为PostgreSQL用户提供生成审计日志的能力，这些审计日志通常需要符合政府、金融或ISO认证。

审计是对个人或组织账户的正式检查，通常由独立机构进行。pgAudit收集的信息被恰当地称为审计跟踪或审计日志。本文档中使用术语审计日志。

详细介绍

为何选择pgAudit?

使用log_statement = all的标准日志工具可以提供基本的语句日志记录。这对于监视和其他用途是可以接受的，但不能提供审计通常所需的更详细的级别。只是拥有对数据库执行的所有操作的列表是不够的。还必须能够找到审计人员感兴趣的特定报告。标准的日志记录工具显示用户请求的内容，而pgAudit侧重于数据库满足请求时发生的事情的细节。

例如，审计人员可能想要验证一个特定的表是否在一个记录的维护窗口中创建。对于grep来说，这似乎是一项简单的工作，但如果您看到这样的示例(故意混淆)，该怎么办?

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

标准的日志会提供如下结果给你：

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)

不仅DO块被记录，而且子语句2包含CREATE TABLE的全文以及语句类型、对象类型和全限定名，以便于搜索。

在记录SELECT和DML语句时，pgAudit可以配置为为语句中引用的每个关系记录单独的条目。查找涉及特定表的所有语句不需要解析。实际上，这里的目标是语句文本主要用于深度取证，而不应该用于审计。

用法方面的考虑

根据不同的设置，pgAudit有可能生成大量的日志记录。请仔细确定您的环境中需要审计记录的内容，以避免记录过多内容。

例如，在OLAP环境中工作时，审计大型事实表中的日志插入可能并不明智。日志文件的大小可能是插入的实际数据大小的许多倍，因为日志文件以文本形式表示。由于日志通常与操作系统一起存储，这可能会导致磁盘空间很快耗尽。如果不可能将审计日志记录限制在某些表中，请确保在测试时评估性能影响，并在日志卷上分配足够的空间。对于OLTP环境也是如此。即使插入量没有那么大，审计日志记录的性能影响仍然可能显著影响延迟。

要限制为SELECT和DML语句记录的关系审计日志的数量，请考虑使用对象审计日志(请参阅对象审计)。对象审计日志记录允许选择要记录的关系，从而减少总体日志量。但是，在添加新关系时，必须显式地将它们添加到对象审计日志中。在这种情况下，将指定的表排除在日志记录之外而包括所有其他表的编程解决方案可能是一个不错的选择。

版本兼容性

我们从源码官网上，可以很清晰的看到，pgAudit与PostgreSQL的版本有如下的对应关系：

源码下载

pgAudit插件的源码位于： https://github.com/pgaudit/pgaudit。下载的时候，可以直接下载它的Release版本。可以从网址：https://github.com/pgaudit/pgaudit/releases 里头寻找合适的版本。比如14.x的PostgreSQL可以下载：1.6.2:  https://github.com/pgaudit/pgaudit/archive/refs/tags/1.6.2.tar.gz

源码安装

如果是pg15, 可以下载1.7.x的源码，或者直接从源码取branch:

git clone https://github.com/pgaudit/pgaudit.git
cd pgaudit
git checkout REL_15_STABLE
make install USE_PGXS=1 PG_CONFIG=/usr/pgsql-15/bin/pg_config

这里以PostgreSQL 14为例：

1、下载：https://github.com/pgaudit/pgaudit/archive/refs/tags/1.6.2.tar.gz

2、编译并安装：

基本指令：

make USE_PGXS=1 PG_CONFIG=/usr/pgsql-14/bin/pg_config
sudo make install  USE_PGXS=1 PG_CONFIG=/usr/pgsql-14/bin/pg_config

示例：

[14:53:37-postgres@sean-rh1:/iihero/source/pgaudit]$ make USE_PGXS=1 PG_CONFIG=/usr/pgsql-14/bin/pg_config
......
[14:54:51-postgres@sean-rh1:/iihero/source/pgaudit]$ sudo make install  USE_PGXS=1 PG_CONFIG=/usr/pgsql-14/bin/pg_config

/bin/mkdir -p '/usr/pgsql-14/lib'
/bin/mkdir -p '/usr/pgsql-14/share/extension'
/bin/mkdir -p '/usr/pgsql-14/share/extension'
/bin/install -c -m 755  pgaudit.so '/usr/pgsql-14/lib/pgaudit.so'
/bin/install -c -m 644 .//pgaudit.control '/usr/pgsql-14/share/extension/'
/bin/install -c -m 644 .//pgaudit--1.6.2.sql .//pgaudit--1.6--1.6.1.sql .//pgaudit--1.6.1--1.6.2.sql  '/usr/pgsql-14/share/extension/'
/bin/mkdir -p '/usr/pgsql-14/lib/bitcode/pgaudit'
/bin/mkdir -p '/usr/pgsql-14/lib/bitcode'/pgaudit/
/bin/install -c -m 644 pgaudit.bc '/usr/pgsql-14/lib/bitcode'/pgaudit/./
cd '/usr/pgsql-14/lib/bitcode' && /usr/lib64/llvm5.0/bin/llvm-lto -thinlto -thinlto-action=thinlink -o pgaudit.index.bc pgaudit/pgaudit.bc

配置使用

只有超级用户才能修改设置。允许普通用户更改其设置将破坏审计日志的意义。

设置可以全局指定(在postgresql.conf中或使用ALTER SYSTEM… SET)，在数据库级别(使用ALTER database…SET)，或者在角色级别(使用ALTER role…SET)。注意，设置不是通过普通的角色继承来继承的，SET role不会改变用户的pgAudit设置。这是角色系统的限制，而不是pgAudit所固有的。

pgAudit扩展必须加载在shared_preload_libraries中。否则，将在加载时引发错误，并且不会发生审计日志记录。

此外，必须在设置pgaudit.log之前调用CREATE EXTENSION pgaudit，以确保正确的pgaudit功能。扩展安装事件触发器，为DDL添加额外的审计。pgAudit在没有安装扩展的情况下也可以工作，但是DDL语句没有关于对象类型和名称的信息。

如果pgaudit扩展被删除并且需要重新创建，那么pgaudit.log必须首先取消设置，否则将引发错误。

pgaudit.log

指定会话审计日志记录将记录哪些类的语句。可能的值有:

• READ: 当源是关系或查询时，SELECT和COPY。

• WRITE:  当目标是关系时，INSERT, UPDATE, DELETE, TRUNCATE, COPY。

• FUNCTION:函数调用和DO块。

• ROLE:与角色和权限相关的语句:GRANT, REVOKE, CREATE/ALTER/DROP ROLE。

• DDL:不包括在ROLE类中的所有DDL。

• MISC:杂项命令，例如丢弃，获取，检查点，真空，设置。

• MISC_SET: 杂项设置命令，如设置角色。

• 全部:包括上述所有内容。

可以使用逗号分隔的列表提供多个类，并且可以通过在类的前面加上-符号来减去类(请参阅会话审计日志)。

默认值是none。

pgaudit.log_catalog

指定如果语句中的所有关系都在pg_catalog中，则应该启用会话日志记录。禁用此设置将减少来自psql和PgAdmin等大量查询目录的工具的日志噪音。

默认为on。

pgaudit.log_client

指定日志消息是否对客户端进程(如psql)可见。此设置通常应保持禁用状态，但对于调试或其他目的可能很有用。

注意，pgaudit.log_level只有在pgaudit.log_client打开时才会被启用。

默认为off (关闭)。

pgaudit.log_level

指定将用于日志条目的日志级别(有关有效级别，请参阅Message Severity Levels)，但注意不允许ERROR, FATAL和PANIC)。此设置用于回归测试，并且对于用于测试或其他目的的最终用户也可能很有用。

注意，pgaudit.log_level只有在pgaudit.log_client为on时才会被启用;否则将使用默认值。

默认为log。

pgaudit.log_parameter

指定审计日志记录应包括随语句传递的参数。当参数存在时，它们将以CSV格式包含在语句文本之后。

默认为off。

pgaudit.log_parameter_max_size

指定超过此设置(以字节为单位)的参数值不应被记录，而是用替换。这是以字节而不是字符为单位设置的，因此不考虑文本参数编码中的多字节字符。如果log_parameter为off，则此设置不起作用。如果此设置为0(默认值)，则无论长度如何，都将记录所有参数

默认值为0。

pgaudit.log_relation

指定会话审计日志是否应该为SELECT或DML语句中引用的每个关系(TABLE、VIEW等)创建单独的日志条目。这是一个有用的快捷方式，可以在不使用对象审计日志的情况下进行详尽的日志记录。

默认为off。

pgaudit.log_rows

指定会话审计日志记录是否应该为语句中引用的每个关系(TABLE, VIEW等)创建单独的日志条目。指定审计日志记录应该包括语句检索或影响的行。启用后，rows字段将包含在parameter字段之后。

默认为off。

pgaudit.log_statement

指定日志记录是否包括语句文本和参数(如果启用)。根据需求，审计日志可能不需要这样做，并且它使日志不那么冗长。

默认为on。

pgaudit.log_statement_once

指定日志记录是将语句文本和参数包含在语句/子语句组合的第一个日志条目中，还是包含在每个条目中。禁用此设置将减少详细的日志记录，但可能会使确定生成日志条目的语句变得更加困难，尽管语句/子语句对以及进程id应该足以识别与前一个条目一起记录的语句文本。

默认为off。

pgaudit.role

指定要用于对象审计日志记录的主角色。可以通过将多个审计角色授予主角色来定义它们。这允许多个组负责审计日志记录的不同方面。

没有默认值。

Session Audit Logging

会话审计日志记录提供了用户在后端执行的所有语句的详细日志。

Configuration

会话日志记录是通过pgaudit.log设置启用的。

启用所有DML和DDL的会话日志记录，记录DML语句中的所有关系。

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

启用除MISC以外的所有命令的会话日志记录，并引发审计日志消息NOTICE:

set pgaudit.log = 'all, -misc';
set pgaudit.log_level = notice;

示例：

在本例中，会话审计日志记录用于记录DDL和SELECT语句。注意，由于没有启用WRITE类，因此不会记录插入语句SQL:

set pgaudit.log = 'read, ddl';

create table account
(
    id int,
    name text,
    password text,
    description text
);

insert into account (id, name, password, description)
             values (1, 'user1', 'HASH1', 'blah, blah');

select * from account;

Log的输出为：

AUDIT: SESSION,1,1,DDL,CREATE TABLE,TABLE,public.account,create table account
(
    id int,
    name text,
    password text,
    description text
);,<not logged>
AUDIT: SESSION,2,1,READ,SELECT,,,select *
    from account,,<not logged>

Object Audit Logging

对象审计日志记录影响特定关系的语句。只支持SELECT、INSERT、UPDATE和DELETE命令。TRUNCATE不包括在对象审计日志中。

对象审计日志记录旨在成为ppgaudit .log = 'read, write'的细粒度替代品。因此，将它们结合使用可能没有意义，但一种可能的情况是使用会话日志记录来捕获每个语句，然后用对象日志记录补充，以获得有关特定关系的更多细节。

Configuration

对象级审计日志记录是通过角色系统实现的。pgaudit。角色设置定义将用于审计日志记录的角色。当审计角色对执行的命令具有权限或从另一个角色继承权限时，将对关系(TABLE、VIEW等)进行审计记录。这允许您有效地拥有多个审计角色，即使在任何上下文中只有一个主角色。

设置pgaudit。角色为审计员，并授予帐户表上的SELECT和DELETE权限。现在，账户表上的任何SELECT或DELETE语句都将被记录:

set pgaudit.role = 'auditor';

grant select, delete
   on public.account
   to auditor;

示例：

在本例中，对象审计日志用于说明如何采用粒度方法记录SELECT和DML语句。注意，帐户表上的日志记录是由列级权限控制的，而account_role_map表上的日志记录是表级的。

SQL:

set pgaudit.role = 'auditor';

create table account
(
    id int,
    name text,
    password text,
    description text
);

grant select (password)
   on public.account
   to auditor;

select id, name
  from account;

select password
  from account;

grant update (name, password)
   on public.account
   to auditor;

update account
   set description = 'yada, yada';

update account
   set password = 'HASH2';

create table account_role_map
(
    account_id int,
    role_id int
);

grant select
   on public.account_role_map
   to auditor;

select account.password,
       account_role_map.role_id
  from account
       inner join account_role_map
            on account.id = account_role_map.account_id

Log输出：

AUDIT: OBJECT,1,1,READ,SELECT,TABLE,public.account,select password
  from account,<not logged>
AUDIT: OBJECT,2,1,WRITE,UPDATE,TABLE,public.account,update account
   set password = 'HASH2',<not logged>
AUDIT: OBJECT,3,1,READ,SELECT,TABLE,public.account,select account.password,
       account_role_map.role_id
  from account
       inner join account_role_map
            on account.id = account_role_map.account_id,<not logged>
AUDIT: OBJECT,3,1,READ,SELECT,TABLE,public.account_role_map,select account.password,
       account_role_map.role_id
  from account
       inner join account_role_map
            on account.id = account_role_map.account_id,<not logged>

格式

审计条目被写入标准日志工具，并以逗号分隔的格式包含以下列。只有当删除每个日志条目的日志行前缀部分时，输出才符合CSV格式。

• AUDIT_TYPE -会话或对象。

• STATEMENT_ID -该会话的唯一语句ID。每个语句ID表示一个后端调用。语句id是连续的，即使有些语句没有被记录。当记录多个关系时，一个语句ID可能有多个条目。

• SUBSTATEMENT_ID—主语句中每个子语句的顺序ID。例如，从查询中调用函数。子语句id是连续的，即使有些子语句没有被记录。当记录多个关系时，子语句ID可能有多个条目。

• CLASS-例如读，角色(见pgaudit .log)。

• COMMAND-例如:ALTER TABLE, SELECT。

• OBJECT_TYPE-表，索引，视图等。可用于SELECT、DML和大多数DDL语句。

• OBJECT_NAME -完全限定的对象名称(例如public.account)。可用于SELECT、DML和大多数DDL语句。

• STATEMENT -在后端执行的语句。

• PARAMETER—如果设置了pgaudit.log_parameter，则该字段将包含语句参数，如引号CSV或(如果没有参数)。否则，该字段为。

使用log_line_prefix添加满足审计日志需求所需的任何其他字段。典型的日志行前缀可能是'%m %u %d [%p]: '
，它将为每个审计日志提供日期/时间、用户名、数据库名和进程id。

重要提示(Caveats)

对象重命名记录在它们重命名到的名称下。例如，重命名表将产生以下结果:

ALTER TABLE test RENAME TO test2;

AUDIT: SESSION,36,1,DDL,ALTER TABLE,TABLE,public.test2,ALTER TABLE test RENAME TO test2,<not logged>

可以将一个命令记录多次。例如，当使用在创建时指定的主键创建表时，将独立记录主键的索引，并为create条目下的索引制作另一个审计日志。然而，多个条目将包含在一个语句ID中。

不记录自动真空和自动分析。

在事务进入中止状态后执行的语句将不会被审计记录。但是，导致错误的语句以及在终止的事务中执行的任何后续语句将被标准日志记录工具记录为错误。

使用pgAudit不可能可靠地审计超级用户。一种解决方案是限制对超级用户帐户的访问，并在需要时使用set_user扩展来升级权限。

作者信息

PostgreSQL审计扩展是基于由Simon Riggs, Abhijit Menon-Sen和Ian Barwick撰写的2ndQuadrant(http://www.2ndquadrant.com/) pgaudit项目(https://github.com/2ndQuadrant/pgaudit)，并作为扩展提交到PostgreSQL核心。Crunchy Data(http://www.crunchydata.com/)的David Steele进行了进一步的开发。

FAQ彩蛋附送

借本文之际，附着一个FAQ彩蛋，有同学问，pg_timetable之类的插件程序，在启动命令行时，能不能去掉明文的 --password之类的参数，有明文的话，人家一列进程，就能看到密码了?

问题合情合理。

首先,  查看pg_timetable的帮助文档，它确实没怎么详细介绍如何不用指定密码。但是它有另外一个参数项说明：

  -u, --user=                                      PostgreSQL user (default: scheduler) [$PGTT_PGUSER]
      --password=                                  PostgreSQL user password [$PGTT_PGPASSWORD]

这里，可以在shell的profile文件如.bash_profile或.bash_rc里头定义好这个环境变量: PGTT_PGPASSWORD。这样就可以不用显式指定： --password的值了。

另外，因为pg_timetable基本上也可以算作是pgsql的客户端应用程序。那么它必然也可以复用libpsql的那套默认环境变量了。也就是说，可以在postgres用户的$HOME/.pgpass里头定义相关的连接信息了。然后在相同的用户下边运行pg_timetable命令就可以了。这种用法应该适用于绝大多数c或者go开发的pgsql的客户端应用程序。

参考

1、https://github.com/pgaudit/pgaudit:
https://github.com/pgaudit/pgaudit