安装审计pgaudit
1.查看安装的版本pgaudit
[root@localhost ~]# yum list pgaudit*
Loaded plugins: fastestmirror, langpacks
Determining fastest mirrors
* base: mirrors.163.com
* epel: mirror-jp.misakamikoto.network
* extras: mirrors.163.com
* updates: mirrors.163.com
Available Packages
pgaudit12_10.x86_64 1.2.4-1.rhel7 pgdg10
pgaudit13_11.x86_64 1.3.4-1.rhel7 pgdg11
pgaudit14_12.x86_64 1.4.3-1.rhel7 pgdg12
pgaudit15_13.x86_64 1.5.2-1.rhel7 pgdg13
pgaudit16_14.x86_64 1.6.2-1.rhel7 pgdg14
pgaudit17_15.x86_64 1.7.0-1.rhel7 pgdg15
pgaudit_analyze.x86_64 1.0.7-1.rhel7 pgdg-common
pgaudit_analyze.noarch 1.0.8-1.rhel7 pgdg-common
pgauditlogtofile-10.x86_64 1.0-1.rhel7 pgdg10
pgauditlogtofile-11.x86_64 1.0-1.rhel7 pgdg11
pgauditlogtofile-12.x86_64 1.0-1.rhel7 pgdg12
pgauditlogtofile-13.x86_64 1.0-1.rhel7 pgdg13
pgauditlogtofile_10.x86_64 1.5.6-1.rhel7 pgdg10
pgauditlogtofile_11.x86_64 1.5.6-1.rhel7 pgdg11
pgauditlogtofile_11-llvmjit.x86_64 1.5.6-1.rhel7 pgdg11
pgauditlogtofile_12.x86_64 1.5.6-1.rhel7 pgdg12
pgauditlogtofile_12-llvmjit.x86_64 1.5.6-1.rhel7 pgdg12
pgauditlogtofile_13.x86_64 1.5.6-1.rhel7 pgdg13
pgauditlogtofile_13-llvmjit.x86_64 1.5.6-1.rhel7 pgdg13
pgauditlogtofile_14.x86_64 1.5.6-1.rhel7 pgdg14
pgauditlogtofile_14-llvmjit.x86_64 1.5.6-1.rhel7 pgdg14
pgauditlogtofile_15.x86_64 1.5.6-2.rhel7 pgdg15
pgauditlogtofile_15-llvmjit.x86_64 1.5.6-2.rhel7 pgdg15
2.安装相应的版本
yum install pgaudit14_12.x86_64
3.修改配置参数
#修改shared_preload_libraries参数,后面追加pgaudit后重启实例,其他插件请忽略
su - postgres
[postgres@localhost data]$ vi /opt/pg12/data/postgresql.conf
[postgres@localhost data]$ shared_preload_libraries = 'pg_repack,pg_squeeze,pgaudit' # (change requires restart)
然后重新启动pg
[root@localhost ~]# systemctl stop postgresql-12
[root@localhost ~]# systemctl start postgresql-12
4.创建插件
#进入数据库,使用超级用户创建插件
postgres=# create extension pgaudit;
CREATE EXTENSION
5.可配置参数
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)
6.设置会话级别
su - postgres
[postgres@localhost pg_wal]$ psql
set pgaudit.log = 'all';
set pgaudit.log_relation = on;
set pgaudit.log_client=on;
select name,setting,source from pg_settings where name like 'pgaudit%';
在当前库下的操作语句都会记录到log_directory参数定义的目录中,注意切换到其他库执行的这种情况不会记录.
7.数据库级别的设置
vi /opt/pg12/data/postgresql.conf
后面添加如下参数:
pgaudit.log = 'all'
pgaudit.log_relation = on
pgaudit.log_client=on
然后重新启动pg
[root@localhost ~]# systemctl stop postgresql-12
[root@localhost ~]# systemctl start postgresql-12
[postgres@localhost pg_wal]$ psql
psql (12.13)
Type "help" for help.
postgres=# select name,setting,source from pg_settings where name like 'pgaudit%';
name | setting | source
----------------------------+---------+--------------------
pgaudit.log | all | configuration file
pgaudit.log_catalog | on | default
pgaudit.log_client | on | configuration file
pgaudit.log_level | log | default
pgaudit.log_parameter | off | default
pgaudit.log_relation | on | configuration file
pgaudit.log_statement_once | off | default
pgaudit.role | | default
(8 rows)