【译】postgresql 扩展工具审计扩展 pgaudit

原创贾勇智 2022-03-24

2202

审计扩展 pgaudit

开源 PostgreSQL 审计日志

3.1 介绍

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

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

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

3.2 为什么选择 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 配置为为语句中引用的每个关系记录一个单独的条目。无需解析即可找到涉及特定表的所有语句。事实上，我们的目标是声明文本主要是为深度取证提供的，而不应该是审计所必需的。

3.3 使用注意事项

根据设置，pgAudit 可能会生成大量的日志记录。小心确定在您的环境中需要审核记录的确切内容，以避免记录过多。

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

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

3.4 PostgreSQL 版本兼容性

pgAudit 是为支持 PostgreSQL 9.5 或更高版本而开发的。

为了支持每个 PostgreSQL 版本中引入的新功能，pgAudit 为每个 PostgreSQL 主要版本（当前为 PostgreSQL 9.5 - 11）维护一个单独的分支，该分支将以类似于 PostgreSQL 项目的方式维护。

除了错误修复之外，没有计划对稳定分支进行进一步的开发。新的开发，如果有的话，将严格针对下一个未发布的 PostgreSQL 主要版本。

pgAudit 版本与 PostgreSQL 主要版本的关系如下：

pgAudit v1.6.X is intended to support PostgreSQL 14.
pgAudit v1.5.X is intended to support PostgreSQL 13.
pgAudit v1.4.X is intended to support PostgreSQL 12.
pgAudit v1.3.X is intended to support PostgreSQL 11.
pgAudit v1.2.X is intended to support PostgreSQL 10.
pgAudit v1.1.X is intended to support PostgreSQL 9.6.
pgAudit v1.0.X is intended to support PostgreSQL 9.5.

3.5 编译安装

pgAudit 可以通过使用 PGXS 的开发包针对已安装的 PostgreSQL 副本进行编译。

以下说明适用于 RHEL 7。

克隆 pgAudit 扩展： 当前下载的是1.6版本的，不需要git checkout REL_13_STABLE

git clone https://github.com/pgaudit/pgaudit.git
复制

切换到 pgAudit 目录：

cd pgaudit
复制

签出 REL_13_STABLE 分支（请注意，未发布版本的 PostgreSQL 可能不存在稳定分支）：对于pg14 pgaudit 1.6 不用执行此步

git checkout REL_13_STABLE
复制

构建并安装 pgAudit：

[root@pg14 pgaudit]# make install USE_PGXS=1 PG_CONFIG=/opt/pgsql/bin/pg_config
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fPIC -I. -I./ -I/opt/pg14/include/postgresql/server -I/opt/pg14/include/postgresql/internal  -D_GNU_SOURCE   -c -o pgaudit.o pgaudit.c
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fPIC -shared -o pgaudit.so pgaudit.o  -L/opt/pg14/lib    -Wl,--as-needed -Wl,-rpath,'/opt/pg14/lib',--enable-new-dtags  
/usr/bin/mkdir -p '/opt/pg14/lib/postgresql'
/usr/bin/mkdir -p '/opt/pg14/share/postgresql/extension'
/usr/bin/mkdir -p '/opt/pg14/share/postgresql/extension'
/usr/bin/install -c -m 755  pgaudit.so '/opt/pg14/lib/postgresql/pgaudit.so'
/usr/bin/install -c -m 644 .//pgaudit.control '/opt/pg14/share/postgresql/extension/'
/usr/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  '/opt/pg14/share/postgresql/extension/'
复制

postgres=# create extension pgaudit;
CREATE EXTENSION
复制

测试和开发说明可以在测试中找到。

3.6 设置

设置只能由超级用户修改。允许普通用户更改他们的设置会破坏审计日志的意义。

可以在全局（在 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 扩展被删除并需要重新创建，那么 pgaudit.log 必须首先取消设置，否则将引发错误。

3.6.1 pgaudit.log

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

READ: SELECT and COPY when the source is a relation or a query.
WRITE: INSERT, UPDATE, DELETE, TRUNCATE, and COPY when the destination is a relation.
FUNCTION: Function calls and DO blocks.
ROLE: Statements related to roles and privileges: GRANT, REVOKE, CREATE/ALTER/DROP ROLE.
DDL: All DDL that is not included in the ROLE class.
MISC: Miscellaneous commands, e.g. DISCARD, FETCH, CHECKPOINT, VACUUM, SET.
MISC_SET: Miscellaneous SET commands, e.g. SET ROLE.
ALL: Include all of the above.

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

默认值为 none。

3.6.2 pgaudit.log_catalog

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

默认为 on。

3.6.3 pgaudit.log_client

指定日志消息是否对客户端进程（如 psql）可见。此设置通常应禁用，但可能对调试或其他目的有用。

注意 pgaudit.log_level 仅在 pgaudit.log_client 开启时启用。

默认为 off.

3.6.4 pgaudit.log_level

指定将用于日志条目的日志级别（有关有效级别，请参阅消息严重级别），但请注意不允许使用 ERROR、FATAL 和 PANIC）。此设置用于回归测试，也可能对最终用户用于测试或其他目的有用。

注意 pgaudit.log_level 仅在 pgaudit.log_client 开启时启用；否则将使用默认值。

默认为 log 。

3.6.5 pgaudit.log_parameter

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

默认为 off.

3.6.6 pgaudit.log_relation

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

默认为 off 。

3.6.7 pgaudit.log_rows

指定审计日志记录应包括检索到或受语句影响的行。启用后，行字段将包含在参数字段之后。

默认为off.

3.6.8 pgaudit.log_statement

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

默认为 on 。

3.6.9 pgaudit.log_statement_once

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

默认为 off.

3.6.10 pgaudit.role

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

没有默认值。

3.7 会话审计日志

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

3.7.1 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;
复制

3.7.2 Example

在此示例中，会话审计日志记录用于记录 DDL 和 SELECT 语句。请注意，由于未启用 WRITE 类，因此不会记录插入语句

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 Output:

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>
复制

3.8 对象审计日志

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

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

3.8.1 配置

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

将 pgaudit.role 设置为 audit 并授予对 account 表的 SELECT 和 DELETE 权限。现在将记录帐户表上的任何 SELECT 或 DELETE 语句：

create role auditor;

set pgaudit.role = 'auditor';

grant select, delete
   on public.account
   to auditor;
复制

3.8.2 范例

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

SQL:

create role auditor;

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 Output:

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>
复制

3.9 审计日志输出格式

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

AUDIT_TYPE - SESSIONorOBJECT。
STATEMENT_ID - 此会话的唯一语句 ID。每个语句 ID 代表一个后端调用。即使未记录某些语句，语句 ID 也是连续的。当记录多个关系时，一个语句 ID 可能有多个条目。
SUBSTATEMENT_ID - 主语句中每个子语句的顺序 ID。例如，从查询中调用函数。即使未记录某些子语句，子语句 ID 也是连续的。当记录多个关系时，子语句 ID 可能有多个条目。
CLASS - 例如READ,ROLE（参见 pgaudit.log）。
COMMAND - 例如ALTER TABLE,SELECT。
OBJECT_TYPE - TABLE、INDEX、VIEW 等。可用于 SELECT、DML 和大多数 DDL 语句。
OBJECT_NAME - 完全限定的对象名称（例如 public.account）。可用于 SELECT、DML 和大多数 DDL 语句。
STATEMENT - 在后端执行的语句。
PARAMETER - 如果设置了 pgaudit.log_parameter，那么这个字段将包含语句参数作为引用的 CSV 或者如果没有参数则没有。否则，该字段不会被记录。

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

log_line_prefix:

https://www.postgresql.org/docs/13/runtime-config-logging.html#GUC-LOG-LINE-PREFIX

3.10 警告

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

ALTER TABLE test RENAME TO test2;

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

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

不记录 Autovacuum 和 Autoanalyze。

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

3.11 作者

PostgreSQL 审计扩展基于 Simon Riggs、Abhijit Menon-Sen 和 Ian Barwick 编写的 2ndQuadrant pgaudit 项目，并作为 PostgreSQL 核心的扩展提交。 Crunchy Data 的 David Steele 进行了额外的开发。

墨力计划 postgresql

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

文章被以下合辑收录

postgresql扩展工具介绍（共17篇）

postgresql扩展工具介绍

审计扩展 pgaudit
- 3.1 介绍
- 3.2 为什么选择 pgAudit？
- 3.3 使用注意事项
- 3.4 PostgreSQL 版本兼容性
- 3.5 编译安装
- 3.6 设置
  - 3.6.1 pgaudit.log
  - 3.6.2 pgaudit.log_catalog
  - 3.6.3 pgaudit.log_client
  - 3.6.4 pgaudit.log_level
  - 3.6.5 pgaudit.log_parameter
  - 3.6.6 pgaudit.log_relation
  - 3.6.7 pgaudit.log_rows
  - 3.6.8 pgaudit.log_statement
  - 3.6.9 pgaudit.log_statement_once
  - 3.6.10 pgaudit.role
- 3.7 会话审计日志
  - 3.7.1 Configuration
  - 3.7.2 Example
- 3.8 对象审计日志
  - 3.8.1 配置
  - 3.8.2 范例
- 3.9 审计日志输出格式
- 3.10 警告
- 3.11 作者

【译】postgresql 扩展工具 审计扩展 pgaudit

审计扩展 pgaudit

3.1 介绍

3.2 为什么选择 pgAudit？

3.3 使用注意事项

3.4 PostgreSQL 版本兼容性

3.5 编译安装

3.6 设置

3.6.1 pgaudit.log

3.6.2 pgaudit.log_catalog

3.6.3 pgaudit.log_client

3.6.4 pgaudit.log_level

3.6.5 pgaudit.log_parameter

3.6.6 pgaudit.log_relation

3.6.7 pgaudit.log_rows

3.6.8 pgaudit.log_statement

3.6.9 pgaudit.log_statement_once

3.6.10 pgaudit.role

3.7 会话审计日志

3.7.1 Configuration

3.7.2 Example

3.8 对象审计日志

3.8.1 配置

3.8.2 范例

3.9 审计日志输出格式

3.10 警告

3.11 作者

文章被以下合辑收录

评论

【译】postgresql 扩展工具审计扩展 pgaudit