PG审计插件之pgaudit
👉 本文共约4349个字，系统预计阅读时间或需17分钟。
简介
https://github.com/pgaudit/pgaudit
PostgreSQL可以通过log_statement=all 提供日志审计，但是没有提供审计要求的详细程度。PostgreSQL Audit Extension (pgAudit)能够提供详细的会话和对象审计日志，是PG的一个扩展插件。pgAudit通过标准PostgreSQL日志记录工具提供详细的会话和/或对象审核日志记录。
注意：pgAudit可能会生成大量日志。请谨慎确定要在您的环境中记录哪些审核内容，以避免过多记录,可以根据需要开启审计，关闭审计设置pgaudit.log=’none’，并重新加载即可。
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.
注意版本和数据库的匹配，最新的v.1.6.X版本并不支持PG13版本，编译会报错：pgaudit.c:1556:38: error: incompatible type for argument 4 of ‘next_ProcessUtility_hook’。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 | [pg13@lhrpgcituscn80 pgaudit-1.6.1]$ make install USE_PGXS=1 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/pg13/pg13/include/postgresql/server -I/pg13/pg13/include/postgresql/internal -D_GNU_SOURCE -c -o pgaudit.o pgaudit.c pgaudit.c: In function ‘pgaudit_ProcessUtility_hook’: pgaudit.c:1556:38: error: incompatible type for argument 4 of ‘next_ProcessUtility_hook’ params, queryEnv, dest, qc); ^ pgaudit.c:1556:38: note: expected ‘ParamListInfo’ but argument is of type ‘ProcessUtilityContext’ pgaudit.c:1556:38: warning: passing argument 5 of ‘next_ProcessUtility_hook’ from incompatible pointer type [enabled by default] pgaudit.c:1556:38: note: expected ‘struct QueryEnvironment *’ but argument is of type ‘ParamListInfo’ pgaudit.c:1556:38: warning: passing argument 6 of ‘next_ProcessUtility_hook’ from incompatible pointer type [enabled by default] pgaudit.c:1556:38: note: expected ‘struct DestReceiver *’ but argument is of type ‘struct QueryEnvironment *’ pgaudit.c:1556:38: warning: passing argument 7 of ‘next_ProcessUtility_hook’ from incompatible pointer type [enabled by default] pgaudit.c:1556:38: note: expected ‘struct QueryCompletion *’ but argument is of type ‘struct DestReceiver *’ pgaudit.c:1556:38: error: too many arguments to function ‘next_ProcessUtility_hook’ pgaudit.c:1559:33: error: incompatible type for argument 4 of ‘standard_ProcessUtility’ params, queryEnv, dest, qc); ^ In file included from pgaudit.c:30:0: /pg13/pg13/include/postgresql/server/tcop/utility.h:82:13: note: expected ‘ParamListInfo’ but argument is of type ‘ProcessUtilityContext’ extern void standard_ProcessUtility(PlannedStmt *pstmt, const char *queryString, ^ pgaudit.c:1559:33: warning: passing argument 5 of ‘standard_ProcessUtility’ from incompatible pointer type [enabled by default] params, queryEnv, dest, qc); ^ In file included from pgaudit.c:30:0: /pg13/pg13/include/postgresql/server/tcop/utility.h:82:13: note: expected ‘struct QueryEnvironment *’ but argument is of type ‘ParamListInfo’ extern void standard_ProcessUtility(PlannedStmt *pstmt, const char *queryString, ^ pgaudit.c:1559:33: warning: passing argument 6 of ‘standard_ProcessUtility’ from incompatible pointer type [enabled by default] params, queryEnv, dest, qc); ^ In file included from pgaudit.c:30:0: /pg13/pg13/include/postgresql/server/tcop/utility.h:82:13: note: expected ‘struct DestReceiver *’ but argument is of type ‘struct QueryEnvironment *’ extern void standard_ProcessUtility(PlannedStmt *pstmt, const char *queryString, ^ pgaudit.c:1559:33: warning: passing argument 7 of ‘standard_ProcessUtility’ from incompatible pointer type [enabled by default] params, queryEnv, dest, qc); ^ In file included from pgaudit.c:30:0: /pg13/pg13/include/postgresql/server/tcop/utility.h:82:13: note: expected ‘struct QueryCompletion *’ but argument is of type ‘struct DestReceiver *’ extern void standard_ProcessUtility(PlannedStmt *pstmt, const char *queryString, ^ pgaudit.c:1559:33: error: too many arguments to function ‘standard_ProcessUtility’ params, queryEnv, dest, qc); ^ In file included from pgaudit.c:30:0: /pg13/pg13/include/postgresql/server/tcop/utility.h:82:13: note: declared here extern void standard_ProcessUtility(PlannedStmt *pstmt, const char *queryString, ^ pgaudit.c: In function ‘_PG_init’: pgaudit.c:2162:25: warning: assignment from incompatible pointer type [enabled by default] ProcessUtility_hook = pgaudit_ProcessUtility_hook; ^ make: *** [pgaudit.o] Error 1 |
pgaudit 安装
https://github.com/pgaudit/pgaudit
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | wget https://codeload.github.com/pgaudit/pgaudit/tar.gz/refs/tags/1.5.0 -O pgaudit-1.5.0.tar.gz tar -xzvf pgaudit-1.5.0.tar.gz cd pgaudit-1.5.0/ make install USE_PGXS=1 -- wget https://codeload.github.com/pgaudit/pgaudit/tar.gz/refs/tags/1.6.1 -O pgaudit-1.6.1.tar.gz -- tar -xzvf pgaudit-1.6.1.tar.gz -- cd pgaudit-1.6.1/ -- make install USE_PGXS=1 select * from pg_available_extensions where name like '%audit%'; show shared_preload_libraries; alter system set shared_preload_libraries='pgaudit'; pg_ctl restart create extension pgaudit; \dx \dx+ select name,setting from pg_settings where name like 'pgaudit%'; |
过程：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 | [pg13@lhrpgcituscn80 tmp]$ wget https://codeload.github.com/pgaudit/pgaudit/tar.gz/refs/tags/1.5.0 -O pgaudit-1.5.0.tar.gz --2022-02-21 09:34:00-- https://codeload.github.com/pgaudit/pgaudit/tar.gz/refs/tags/1.5.0 Resolving codeload.github.com (codeload.github.com)... 20.205.243.165 Connecting to codeload.github.com (codeload.github.com)|20.205.243.165|:443... connected. HTTP request sent, awaiting response... 200 OK Length: unspecified [application/x-gzip] Saving to: ‘pgaudit-1.5.0.tar.gz’ [ <=> ] 34,130 --.-K/s in 0.09s 2022-02-21 09:34:01 (392 KB/s) - ‘pgaudit-1.5.0.tar.gz’ saved [34130] [pg13@lhrpgcituscn80 tmp]$ ll total 84 -rwxrwxrwx 1 pg13 postgres 34130 Feb 21 09:34 pgaudit-1.5.0.tar.gz [pg13@lhrpgcituscn80 tmp]$ tar -zxvf pgaudit-1.5.0.tar.gz pgaudit-1.5.0/ pgaudit-1.5.0/.gitignore pgaudit-1.5.0/LICENSE pgaudit-1.5.0/Makefile pgaudit-1.5.0/README.md pgaudit-1.5.0/expected/ pgaudit-1.5.0/expected/pgaudit.out pgaudit-1.5.0/pgaudit--1.5.sql pgaudit-1.5.0/pgaudit.c pgaudit-1.5.0/pgaudit.conf pgaudit-1.5.0/pgaudit.control pgaudit-1.5.0/sql/ pgaudit-1.5.0/sql/pgaudit.sql pgaudit-1.5.0/test/ pgaudit-1.5.0/test/Vagrantfile [pg13@lhrpgcituscn80 tmp]$ cd pgaudit-1.5.0/ [pg13@lhrpgcituscn80 pgaudit-1.5.0]$ ll total 116 drwxr-xr-x 2 pg13 postgres 4096 Sep 9 2020 expected -rw-r--r-- 1 pg13 postgres 171 Sep 9 2020 LICENSE -rw-r--r-- 1 pg13 postgres 522 Sep 9 2020 Makefile -rw-r--r-- 1 pg13 postgres 581 Sep 9 2020 pgaudit--1.5.sql -rw-r--r-- 1 pg13 postgres 63955 Sep 9 2020 pgaudit.c -rw-r--r-- 1 pg13 postgres 35 Sep 9 2020 pgaudit.conf -rw-r--r-- 1 pg13 postgres 143 Sep 9 2020 pgaudit.control -rw-r--r-- 1 pg13 postgres 17474 Sep 9 2020 README.md drwxr-xr-x 2 pg13 postgres 4096 Sep 9 2020 sql drwxr-xr-x 2 pg13 postgres 4096 Sep 9 2020 test [pg13@lhrpgcituscn80 pgaudit-1.5.0]$ make install USE_PGXS=1 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/pg13/pg13/include/postgresql/server -I/pg13/pg13/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/pg13/pg13/lib -Wl,--as-needed -Wl,-rpath,'/pg13/pg13/lib',--enable-new-dtags /usr/bin/mkdir -p '/pg13/pg13/lib/postgresql' /usr/bin/mkdir -p '/pg13/pg13/share/postgresql/extension' /usr/bin/mkdir -p '/pg13/pg13/share/postgresql/extension' /usr/bin/install -c -m 755 pgaudit.so '/pg13/pg13/lib/postgresql/pgaudit.so' /usr/bin/install -c -m 644 .//pgaudit.control '/pg13/pg13/share/postgresql/extension/' /usr/bin/install -c -m 644 .//pgaudit--1.5.sql '/pg13/pg13/share/postgresql/extension/' [pg13@lhrpgcituscn80 pgaudit-1.5.0]$ psql psql (13.3) Type "help" for help. postgres=# postgres=# select * from pg_available_extensions where name like '%audit%'; name | default_version | installed_version | comment ---------+-----------------+-------------------+--------------------------------- pgaudit | 1.5 | | provides auditing functionality (1 row) postgres=# postgres=# create extension pgaudit; ERROR: pgaudit must be loaded via shared_preload_libraries postgres=# postgres=# show shared_preload_libraries; shared_preload_libraries -------------------------- (1 row) postgres=# postgres=# alter system set shared_preload_libraries='pgaudit'; ALTER SYSTEM postgres=# show shared_preload_libraries; shared_preload_libraries -------------------------- (1 row) postgres=# exit [pg13@lhrpgcituscn80 pgaudit-1.5.0]$ [pg13@lhrpgcituscn80 pgaudit-1.5.0]$ pg_ctl restart waiting for server to shut down.... done server stopped waiting for server to start....2022-02-21 09:41:11.879 CST [1801] LOG: pgaudit extension initialized 2022-02-21 09:41:11.918 CST [1801] LOG: redirecting log output to logging collector process 2022-02-21 09:41:11.918 CST [1801] HINT: Future log output will appear in directory "pg_log". done server started [pg13@lhrpgcituscn80 pgaudit-1.5.0]$ psql psql (13.3) Type "help" for help. postgres=# postgres=# create extension pgaudit; CREATE EXTENSION postgres=# postgres=# \dx List of installed extensions Name | Version | Schema | Description --------------------+---------+------------+------------------------------------------------------------------------ pageinspect | 1.8 | public | inspect the contents of database pages at a low level pg_stat_statements | 1.8 | public | track planning and execution statistics of all SQL statements executed pgaudit | 1.5 | public | provides auditing functionality plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language (4 rows) postgres=# \dx pgaudit List of installed extensions Name | Version | Schema | Description ---------+---------+--------+--------------------------------- pgaudit | 1.5 | public | provides auditing functionality (1 row) postgres=# \dx+ pgaudit Objects in extension "pgaudit" Object description --------------------------------------- event trigger pgaudit_ddl_command_end event trigger pgaudit_sql_drop function pgaudit_ddl_command_end() function pgaudit_sql_drop() (4 rows) postgres=# postgres=# select name,setting from pg_settings where name like 'pgaudit%'; name | setting ----------------------------+--------- pgaudit.log | none pgaudit.log_catalog | on pgaudit.log_client | off pgaudit.log_level | log pgaudit.log_parameter | off pgaudit.log_relation | off pgaudit.log_statement_once | off pgaudit.role | (8 rows) |
配置开启审计
分为会话和对象审计。
会话审计日志记录
会话审计日志提供用户在后端执行的所有语句的详细日志。使用pgaudit.log设置启用会话日志记录。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | set pgaudit.log = 'write, ddl'; set pgaudit.log_relation = on; set pgaudit.log_client=on; -- SELECT pg_reload_conf(); select name,setting,source from pg_settings where name like 'pgaudit%'; drop table account; 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; |
日志输出：
1 2 3 4 5 6 7 8 9 10 11 | 2022-02-21 10:55:04.007 CST [5300] LOG: AUDIT: SESSION,10,1,DDL,DROP TABLE,TABLE,public.account,drop table account;,<not logged> 2022-02-21 10:55:04.597 CST [5300] LOG: AUDIT: SESSION,11,1,DDL,CREATE TABLE,TABLE,public.account,"create table account ( id int, name text, password text, description text );",<not logged> 2022-02-21 10:55:17.032 CST [5300] LOG: AUDIT: SESSION,12,1,WRITE,INSERT,TABLE,public.account,"insert into account (id, name, password, description) values (1, 'user1', 'HASH1', 'blah, blah');",<not logged> |
对象审计日志记录
影响特定关系的对象审计日志记录语句。只支持 SELECT, INSERT, UPDATE 和DELETE 命令。对象审计日志中不包括 TRUNCATE 。
对象审计日志记录旨在成为pgaudit.log = 'read, write'的细粒度替代。因此，将它们结合使用可能没有任何意义，但是一种可能的场景是使用会话日志记录来捕获每个语句，然后用对象日志记录来补充这些语句，以获得关于特定关系的更多细节。
对象级审计日志是通过角色系统实现的。pgaudit.role 设置定义用于审计日志记录的角色。当审计角色对执行的命令具有权限或从另一个角色继承权限时，将记录一个关系(表、视图等)。这允许您有效地拥有多个审计角色，即使在任何上下文中只有一个主角色。
设置pgaudit.role为auditor，并授予account表的SELECT和DELETE权限。account表上的任何SELECT或DELETE语句都将被记录:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 | create role auditor with password 'lhr'; set pgaudit.log = ''; set pgaudit.role = 'auditor'; -- SELECT pg_reload_conf(); select name,setting from pg_settings where name like 'pgaudit%'; drop table account; create table account ( id int, name text, password text, description text ); grant select, delete on public.account to auditor; select * from information_schema.role_table_grants where grantee='auditor'; insert into account (id, name, password, description) values (1, 'user1', 'HASH1', 'blah, blah'); select * from account; |
日志输出：
1 2 | 2022-02-21 11:07:45.977 CST [5778] LOG: AUDIT: OBJECT,7,1,READ,SELECT,TABLE,public.account,select * from account;,<not logged> 2022-02-21 11:22:22.284 CST [5778] LOG: AUDIT: OBJECT,8,1,WRITE,DELETE,TABLE,public.account,delete from account;,<not logged> |
相关配置参数
Settings may be modified only by a superuser. Allowing normal users to change their settings would defeat the point of an audit log.
Settings can be specified globally (in postgresql.conf or using ALTER SYSTEM ... SET), at the database level (using ALTER DATABASE ... SET), or at the role level (using ALTER ROLE ... SET). Note that settings are not inherited through normal role inheritance and SET ROLE will not alter a user's pgAudit settings. This is a limitation of the roles system and not inherent to pgAudit.
The pgAudit extension must be loaded in shared_preload_libraries. Otherwise, an error will be raised at load time and no audit logging will occur. In addition, CREATE EXTENSION pgaudit must be called before pgaudit.log is set. If the pgaudit extension is dropped and needs to be recreated then pgaudit.log must be unset first otherwise an error will be raised.
pgaudit.log
Specifies which classes of statements will be logged by session audit logging. Possible values are:
- READ:
SELECTandCOPYwhen the source is a relation or a query. - WRITE:
INSERT,UPDATE,DELETE,TRUNCATE, andCOPYwhen the destination is a relation. - FUNCTION: Function calls and
DOblocks. - ROLE: Statements related to roles and privileges:
GRANT,REVOKE,CREATE/ALTER/DROP ROLE. - DDL: All
DDLthat is not included in theROLEclass. - MISC: Miscellaneous commands, e.g.
DISCARD,FETCH,CHECKPOINT,VACUUM,SET. - MISC_SET: Miscellaneous
SETcommands, e.g.SET ROLE. - ALL: Include all of the above.
Multiple classes can be provided using a comma-separated list and classes can be subtracted by prefacing the class with a - sign (see Session Audit Logging).
The default is none.
本人提供Oracle、MySQL、PG等数据库的培训和考证业务，私聊QQ646634621或微信db_bao，谢谢！
pgaudit.log_catalog
指定如果语句中的所有关系都在pg_catalog中，则应该启用会话日志记录。禁用此设置将减少psql和PgAdmin等工具在日志中大量查询catalog的噪音。
默认值为 on.
pgaudit.log_client
指定日志消息是否对客户端进程（如 psql）可见。此设置通常应保持禁用状态，但可能用于调试或其他目的。请注意，pgaudit.log_level 仅在 pgaudit.log_client 打开时启用。
默认值为关闭。
pgaudit.log_level
指定将用于日志条目的日志级别 (详见有效级别的消息严重级别)，但注意不允许出现 ERROR, FATAL, 和PANIC 。此设置用于回归测试，对于测试或其他目的的最终用户也可能有用。
默认值为 log.
pgaudit.log_level 取值：
| Severity | Usage | syslog | eventlog |
|---|---|---|---|
| DEBUG1..DEBUG5 | Provides successively-more-detailed information for use by developers. | DEBUG | INFORMATION |
| INFO | Provides information implicitly requested by the user, e.g., output from VACUUM VERBOSE. | INFO | INFORMATION |
| NOTICE | Provides information that might be helpful to users, e.g., notice of truncation of long identifiers. | NOTICE | INFORMATION |
| WARNING | Provides warnings of likely problems, e.g., COMMIT outside a transaction block. | NOTICE | WARNING |
| ERROR | Reports an error that caused the current command to abort. | WARNING | ERROR |
| LOG | Reports information of interest to administrators, e.g., checkpoint activity. | INFO | INFORMATION |
| FATAL | Reports an error that caused the current session to abort. | ERR | ERROR |
| PANIC | Reports an error that caused all database sessions to abort. | CRIT | ERROR |
pgaudit.log_parameter
指定审计日志记录应该包括与语句一起传递的参数。当参数出现时，它们将包含在语句文本之后的CSV格式中。
默认值为 off.
pgaudit.log_relation
指定会话审计日志记录是否应该为SELECT或DML语句中引用的每个关系(表、视图等)创建单独的日志条目。对于不使用对象审计日志记录的详尽日志记录，这是一个有用的快捷方式。
默认值为 off.
pgaudit.log_rows
指定审核日志记录应包括语句检索或影响的行。启用后，行字段将包含在参数字段之后。
默认值为关闭。
pgaudit.log_statement
指定日志记录是否将包括语句文本和参数（如果启用）。根据要求，审核日志可能不需要此项，这会使日志不那么详细。
默认值为打开。
pgaudit.log_statement_once
指定日志记录是包含带有语句/子语句组合的第一个日志条目的语句文本和参数，还是包含每个条目。禁用此设置将减少冗长的日志记录，但可能会使确定生成日志条目的语句变得更加困难，尽管语句/子语句对以及进程id应该足以识别与前一个条目一起记录的语句文本。
默认值 off.
pgaudit.role
指定用于对象审计日志记录的主角色。可以通过将多个审计角色授予主角色来定义它们。这允许多个组负责审计日志记录的不同方面。
该项没有默认值.
日志格式
审计条目被写入标准日志记录工具，并以逗号分隔的格式包含以下列。只有在删除每个日志条目的日志行前缀部分时，输出才符合CSV格式。
- AUDIT_TYPE - 会话或对象.
- 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: ' ，它将为每个审计日志提供日期/时间、用户名和数据库名。
参考
参考链接：
https://access.crunchydata.com/documentation/pgaudit/1.0.6/#object-audit-logging
https://severalnines.com/database-blog/postgresql-audit-logging-best-practices
