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)
标签：审计,rhel7,x86,pgauditlogtofile,64,pgaudit,安装,log From： https://www.cnblogs.com/hxlasky/p/16952512.html