概念描述
PostgreSQL审计扩展(pgAudit)通过标准的PostgreSQL日志工具提供详细的会话或对象审计日志。
pgAudit的目标是为PostgreSQL用户提供生成审计日志的能力。
安装pgaudit
首先关于版本需要与pg版本对应，如下：
详细安装如下：
$ tar -zxvf pgaudit-1.4.0.tar.gz
$ cd pgaudit-1.4.0/
$ make install USE_PGXS=1注意，需要修改参数shared_preload_libraries：
postgres=# alter system set shared_preload_libraries='pgaudit';
## 需要重启pg最后再创建扩展：
postgres=# create extension pgaudit;
CREATE EXTENSION
postgres=#
postgres=# \dx
List of installed extensions
Name | Version | Schema | Description
-------------+---------+------------+-------------------------------------------------------
pageinspect | 1.7 | public | inspect the contents of database pages at a low level
pgaudit | 1.4 | public | provides auditing functionality
pgcrypto | 1.3 | public | cryptographic functions
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
sslinfo | 1.2 | public | information about SSL certificates
(5 rows)至此，pgaudit已经安装完成。
测试验证
- 首先测试会话级别的审计
postgres=# set pgaudit.log='write,ddl';
SET
postgres=# set pgaudit.log_relation=on;
SET
postgres=# set pgaudit.log_client=on;
SET
postgres=# select name,setting,source from pg_settings where name like 'pgaudit%';
name | setting | source
----------------------------+-----------+---------
pgaudit.log | write,ddl | session //重启pg后，失效
pgaudit.log_catalog | on | default
pgaudit.log_client | on | session
pgaudit.log_level | log | default
pgaudit.log_parameter | off | default
pgaudit.log_relation | on | session
pgaudit.log_statement_once | off | default
pgaudit.role | | default
(8 rows)然后通过创建测试表，可以在日志中看到相关条目信息。
postgres=# create table xxxx(id int);
CREATE TABLE
postgres=# insert into xxxx values(11);
INSERT 0 1如下，在日志中可以看到相关信息：
- 测试对象级别的审计
postgres=# create role audit_crw with password '1qaz@WSX';
CREATE ROLE
postgres=# set pgaudit.log ='';
SET
postgres=# set pgaudit.role='audit_crw';
SET
postgres=# select name,setting,source from pg_settings where name like 'pgaudit%';
name | setting | source
----------------------------+-----------+---------
pgaudit.log | | session
pgaudit.log_catalog | on | default
pgaudit.log_client | off | default
pgaudit.log_level | log | default
pgaudit.log_parameter | off | default
pgaudit.log_relation | off | default
pgaudit.log_statement_once | off | default
pgaudit.role | audit_crw | session
(8 rows)
postgres=# create table audit_tes(id int);
CREATE TABLE
postgres=# grant select,delete on public.audit_tes to audit_crw;
GRANT
postgres=# select * from information_schema.role_table_grants where grantee='audit_crw';
grantor | grantee | table_catalog | table_schema | table_name | privilege_type | is_grantable | with_hier
archy
---------+-----------+---------------+--------------+------------+----------------+--------------+----------
pg12 | audit_crw | postgres | public | audit_tes | SELECT | NO | YES
pg12 | audit_crw | postgres | public | audit_tes | DELETE | NO | NO
(2 rows)
postgres=# insert into audit_tes values(1);
INSERT 0 1
postgres=# insert into audit_tes values(2);
INSERT 0 1
postgres=# select * from audit_tes;
id
----
1
2
(2 rows)
postgres=# delete from audit_tes;
DELETE 2如下，在日志中可以看到相关信息：
