合 PG审计插件之pgaudit
👉 本文共约4345个字，系统预计阅读时间或需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.
本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信dbaup66，谢谢！
