【DB宝93】PG审计插件之pgaudit

小麦苗DBA宝典

发布于 2022-04-11 20:23:47

1.3K0

发布于 2022-04-11 20:23:47

文章被收录于专栏：小麦苗的DB宝专栏小麦苗的DB宝专栏

简介

https://www.pgaudit.org/

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’。

[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://www.pgaudit.org/

https://github.com/pgaudit/pgaudit

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%';

过程：

[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设置启用会话日志记录。

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;

日志输出：

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语句都将被记录:

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;

日志输出：

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

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.

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的语句参数。