合 PG审计插件之pgaudit
简介
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设置启用会话日志记录。