PostgreSQL Audit Extension (pgAudit)能够提供详细的会话和对象审计日志。是pg的一个扩展插件。本文简单测试和使用说明

1 安装

​ 使用 log_statement=all 提供基本语句日志记录。这是可接受的监测，但没有提供一般要求的审计所需的详细程度。标准日志设备显示了用户的要求，而pgAudit则关注数据库满足请求时所发生的情况。

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

  pgAudit versions relate to PostgreSQL major versions as follows:

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

下载地址

https://github.com/pgaudit/pgaudit

本次测试安装1.2.2 ，pg的版本是10.18版本

1.1 解压缩

[postgres@PGserver2 contrib]$ unzip pgaudit-1.2.2.zip -d /home/postgres/postgresql-10.18/contrib
Archive:  pgaudit-1.2.2.zip
8178fc31cf758ccec7c1c9f7c21d1f6f41a4753b
   creating: /home/postgres/postgresql-10.18/contrib/pgaudit-1.2.2/
  inflating: /home/postgres/postgresql-10.18/contrib/pgaudit-1.2.2/.gitignore
  inflating: /home/postgres/postgresql-10.18/contrib/pgaudit-1.2.2/LICENSE
  inflating: /home/postgres/postgresql-10.18/contrib/pgaudit-1.2.2/Makefile
  inflating: /home/postgres/postgresql-10.18/contrib/pgaudit-1.2.2/README.md
   creating: /home/postgres/postgresql-10.18/contrib/pgaudit-1.2.2/expected/
  inflating: /home/postgres/postgresql-10.18/contrib/pgaudit-1.2.2/expected/pgaudit.out
  inflating: /home/postgres/postgresql-10.18/contrib/pgaudit-1.2.2/pgaudit--1.2--1.2.1.sql
  inflating: /home/postgres/postgresql-10.18/contrib/pgaudit-1.2.2/pgaudit--1.2.1--1.2.2.sql
  inflating: /home/postgres/postgresql-10.18/contrib/pgaudit-1.2.2/pgaudit--1.2.2.sql
  inflating: /home/postgres/postgresql-10.18/contrib/pgaudit-1.2.2/pgaudit.c
 extracting: /home/postgres/postgresql-10.18/contrib/pgaudit-1.2.2/pgaudit.conf
  inflating: /home/postgres/postgresql-10.18/contrib/pgaudit-1.2.2/pgaudit.control
   creating: /home/postgres/postgresql-10.18/contrib/pgaudit-1.2.2/sql/
  inflating: /home/postgres/postgresql-10.18/contrib/pgaudit-1.2.2/sql/pgaudit.sql
   creating: /home/postgres/postgresql-10.18/contrib/pgaudit-1.2.2/test/
  inflating: /home/postgres/postgresql-10.18/contrib/pgaudit-1.2.2/test/Vagrantfile
[postgres@PGserver2 contrib]$ cd /home/postgres/postgresql-10.18/contrib/pgaudit-1.2.2/




复制

1.2 编译安装

cd /home/postgres/postgresql-10.18/contrib/pgaudit-1.2.2/
[postgres@PGserver2 pgaudit-1.2.2]$ make
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fPIC -I. -I. -I../../src/include  -D_GNU_SOURCE   -c -o pgaudit.o pgaudit.c
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fPIC -shared -o pgaudit.so pgaudit.o  -L../../src/port -L../../src/common    -Wl,--as-needed -Wl,-rpath,'/data/pg10.18/lib',--enable-new-dtags
[postgres@PGserver2 pgaudit-1.2.2]$ make install
/bin/mkdir -p '/data/pg10.18/lib/postgresql'
/bin/mkdir -p '/data/pg10.18/share/postgresql/extension'
/bin/mkdir -p '/data/pg10.18/share/postgresql/extension'
/bin/install -c -m 755  pgaudit.so '/data/pg10.18/lib/postgresql/pgaudit.so'
/bin/install -c -m 644 ./pgaudit.control '/data/pg10.18/share/postgresql/extension/'
/bin/install -c -m 644 ./pgaudit--1.2.2.sql ./pgaudit--1.2--1.2.1.sql ./pgaudit--1.2.1--1.2.2.sql  '/data/pg10.18/share/postgresql/extension/'
[postgres@PGserver2 pgaudit-1.2.2]$
复制

1.3 查看可用的扩展插件

查看可用的扩展插件

 select * from pg_available_extensions where name like '%audit%';
 
 [postgres@PGserver2 pgaudit-1.2.2]$ psql
psql (10.18)
Type "help" for help.

postgres@[local]:5432=# select * from pg_available_extensions where name like '%audit%';
  name   | default_version | installed_version |             comment
---------+-----------------+-------------------+---------------------------------
 pgaudit | 1.2.2           |                   | provides auditing functionality
(1 row)

postgres@[local]:5432=#



复制

1.4 在 postgresql.auto.conf 中配置 pgaudit

# Do not edit this file manually!
# It will be overwritten by the ALTER SYSTEM command.
shared_preload_libraries = 'pgaudit'



复制

1.5 重启

[postgres@PGserver2 pgdata]$  pg_ctl restart -D $PGDATA -l /tmp/logfile
waiting for server to shut down.... done
server stopped
waiting for server to start.... stopped waiting
pg_ctl: could not start server
Examine the log output.

复制

1.6 创建扩展

 create extension pgaudit;
 postgres@[local]:5432=# create extension pgaudit;
CREATE EXTENSION
postgres@[local]:5432=# select * from pg_available_extensions where name like '%audit%';
  name   | default_version | installed_version |             comment
---------+-----------------+-------------------+---------------------------------
 pgaudit | 1.2.2           | 1.2.2             | provides auditing functionality
(1 row)

postgres@[local]:5432=#\dx
                   List of installed extensions
  Name   | Version |   Schema   |           Description
---------+---------+------------+---------------------------------
 pgaudit | 1.2.2   | public     | provides auditing functionality
 plpgsql | 1.0     | pg_catalog | PL/pgSQL procedural language
(2 rows)

复制

1.7 查看可配置参数

postgres@[local]:5432=#select name,setting from pg_settings where name ~ '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)

postgres@[local]:5432=#
pgAudit扩展必须通过shared_preload_libraries载入。否则，将在加载时引发错误，并且不会发生审计日志记录。此外，在设置 pgaudit.log 之前必须调用 CREATE EXTENSION pgaudit 。如果删除了pgaudit扩展并需要重新创建pgaudit扩展，那么 pgaudit.log 必须先取消设置，否则会引发错误。

pgaudit.log
指定会话审计日志记录将记录哪类语句。可能的值是:

READ: SELECT 和COPY 当源是关系或查询时。
WRITE: INSERT, UPDATE, DELETE, TRUNCATE, 和COPY 当目标是一个关系时。
FUNCTION: 函数调用和 DO 块.
ROLE: 与角色和特权相关的语句: GRANT, REVOKE, CREATE/ALTER/DROP ROLE.
DDL: 不包含在 ROLE 类中的所有DDL.
MISC: 其他的一些命令, 比如 DISCARD, FETCH, CHECKPOINT, VACUUM.
可以使用逗号分隔的列表提供多个类，通过在类前面加 - 号可以减去类(参阅 会话审计日志记录).

默认值为 none.

pgaudit.log_catalog
指定如果语句中的所有关系都在pg_catalog中，则应该启用会话日志记录。禁用此设置将减少psql和PgAdmin等工具在日志中大量查询catalog的噪音。

默认值为 on.

pgaudit.log_level
指定将用于日志条目的日志级别 (详见有效级别的消息严重级别)，但注意不允许出现 ERROR, FATAL, 和PANIC 。此设置用于回归测试，对于测试或其他目的的最终用户也可能有用。

默认值为 log.

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

默认值为 off.

pgaudit.log_relation
指定会话审计日志记录是否应该为SELECT或DML语句中引用的每个关系(表、视图等)创建单独的日志条目。对于不使用对象审计日志记录的详尽日志记录，这是一个有用的快捷方式。

默认值为 off.

pgaudit.log_statement_once
指定日志记录是包含带有语句/子语句组合的第一个日志条目的语句文本和参数，还是包含每个条目。禁用此设置将减少冗长的日志记录，但可能会使确定生成日志条目的语句变得更加困难，尽管语句/子语句对以及进程id应该足以识别与前一个条目一起记录的语句文本。

默认值 off.

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

该项没有默认值.
复制

1.8 配置 pgaudit.role 审计角色

postgres@[local]:5432=#create  role pgaudit with password 'pgaudit' login;
CREATE ROLE
postgres@[local]:5432=# alter system set pgaudit.role  = 'pgaudit';
ALTER SYSTEM
postgres@[local]:5432=#select pg_reload_conf();
 pg_reload_conf
----------------
 t
(1 row)
postgres@[local]:5432=#show pgaudit.role ;
 pgaudit.role
--------------
 pgaudit
(1 row)

复制

2 测试使用

2.1 配置对象审计

postgres@[local]:5432=# create table tab_audit(id int,name varchar);
CREATE TABLE
postgres@[local]:5432=#grant select,insert,delete on public.tab_audit  to pgaudit ;
GRANT

复制

2.2 查看审计日志

select * from  tab_audit;
2021-09-03 17:18:08 CST [21120]: user=postgres,db=postgres,app=psql,client=[local] LOG:  AUDIT: OBJECT,1,1,READ,SELECT,TABLE,public.tab_audit,select * from tab_audit;,<not logged>


postgres@[local]:5432=#insert into public.tab_audit values(1,'yanwei');
INSERT 0 1

2021-09-03 17:19:15 CST [21120]: user=postgres,db=postgres,app=psql,client=[local] LOG:  AUDIT: OBJECT,2,1,WRITE,INSERT,TABLE,public.tab_audit,"insert into public.tab_audit values(1,'yanwei');",<not logged>
2021-09-03 17:19:15 CST [21120]: user=postgres,db=postgres,app=psql,client=[local] LOG:  duration: 0.384 ms



postgres@[local]:5432=#update public.tab_audit set id=2 where id=1;
UPDATE 1

2021-09-03 17:20:55 CST [21120]: user=postgres,db=postgres,app=psql,client=[local] STATEMENT:  update public.tab_audit id=2 where id=1;
2021-09-03 17:21:02 CST [21120]: user=postgres,db=postgres,app=psql,client=[local] LOG:  statement: update public.tab_audit set id=2 where id=1;
2021-09-03 17:21:02 CST [21120]: user=postgres,db=postgres,app=psql,client=[local] LOG:  AUDIT: OBJECT,3,1,WRITE,UPDATE,TABLE,public.tab_audit,update public.tab_audit set id=2 where id=1;,<not logged>


2021-09-03 17:21:34 CST [21120]: user=postgres,db=postgres,app=psql,client=[local] LOG:  statement: delete from public.tab_audit ;
2021-09-03 17:21:34 CST [21120]: user=postgres,db=postgres,app=psql,client=[local] LOG:  AUDIT: OBJECT,4,1,WRITE,DELETE,TABLE,public.tab_audit,delete from public.tab_audit ;,<not logged>

复制

2.3 其他测试

使用pgaudit.log设置启用会话日志记录。
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.


启用所有DML和DDL的会话日志记录，并记录DML语句中的所有关系:
set pgaudit.log = 'write, ddl';
set pgaudit.log_relation = on;

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;
    
2021-09-03 17:27:08 CST [21120]: user=postgres,db=postgres,app=psql,client=[local] LOG:  AUDIT: SESSION,5,1,DDL,CREATE TABLE,TABLE,public.account,"create table account
        (
            id int,
            name text,
            password text,
            description text
        );",<not logged>
2021-09-03 17:27:08 CST [21120]: user=postgres,db=postgres,app=psql,client=[local] LOG:  duration: 10.406 ms





2021-09-03 17:27:23 CST [21120]: user=postgres,db=postgres,app=psql,client=[local] LOG:  statement: insert into account (id, name, password, description)
                     values (1, 'user1', 'HASH1', 'blah, blah');
2021-09-03 17:27:23 CST [21120]: user=postgres,db=postgres,app=psql,client=[local] LOG:  AUDIT: SESSION,6,1,WRITE,INSERT,TABLE,public.account,"insert into account (id, name, password, description)
                     values (1, 'user1', 'HASH1', 'blah, blah');",<not logged>



postgres@[local]:5432=#select *
    from account;
 id | name  | password | description
----+-------+----------+-------------
  1 | user1 | HASH1    | blah, blah
(1 row)

postgres@[local]:5432=#


2021-09-03 17:29:21 CST [21971]: user=postgres,db=postgres,app=psql,client=[local] LOG:  statement: select *
            from account;
2021-09-03 17:29:21 CST [21971]: user=postgres,db=postgres,app=psql,client=[local] LOG:  AUDIT: SESSION,1,1,READ,SELECT,,,"select *
            from account;",<not logged>
2021-09-03 17:29:21 CST [21971]: user=postgres,db=postgres,app=psql,client=[local] LOG:  duration: 0.151 ms


复制

2.4 检查参数和卸载

postgres@[local]:5432=#alter system reset shared_preload_libraries ;
ALTER SYSTEM

[postgres@PGserver2 ~]$ pg_ctl restart -l /tmp/logfile
waiting for server to shut down.... done
server stopped
waiting for server to start.... done
server started
[postgres@PGserver2 ~]$

postgres@[local]:5432=#show shared_preload_libraries
;
 shared_preload_libraries
--------------------------

(1 row)


复制