1. Download pgaudit & pgauditlogtofile
https://download.postgresql.org/pub/repos/yum/16/redhat/rhel-8.2-x86_64/
yum install -y https://rpmfind.net/linux/centos-stream/9-stream/AppStream/x86_64/os/Packages/pgaudit-16.0-1.module_el9+807+b1de07f3.x86_64.rpm
yum install -y https://download.postgresql.org/pub/repos/yum/16/redhat/rhel-8.2-x86_64/pgauditlogtofile_16-1.5.12-1PGDG.rhel8.x86_64.rpm
2. check rpm & extension
[root@teguhth ~]# rpm -qa | grep -i postgres
postgresql16-server-16.0-1PGDG.rhel8.x86_64
postgresql16-contrib-16.0-1PGDG.rhel8.x86_64
postgresql16-16.0-1PGDG.rhel8.x86_64
postgresql16-libs-16.0-1PGDG.rhel8.x86_64
[root@teguhth ~]#
[root@teguhth ~]# rpm -qa | grep -i postgres
postgresql16-server-16.0-1PGDG.rhel8.x86_64
postgresql16-contrib-16.0-1PGDG.rhel8.x86_64
postgresql16-16.0-1PGDG.rhel8.x86_64
postgresql16-libs-16.0-1PGDG.rhel8.x86_64
[root@teguhth ~]#
[root@teguhth ~]# rpm -qa | grep -i audit
audit-3.0-0.17.20191104git1c2f876.el8.x86_64
pgaudit_16-16.0-1PGDG.rhel8.x86_64
pgauditlogtofile_16-1.5.12-1PGDG.rhel8.x86_64
audit-libs-3.0-0.17.20191104git1c2f876.el8.x86_64
[root@teguhth ~]#
teguhth=# SELECT * FROM pg_extension WHERE extname like '%pgaudit%';
oid | extname | extowner | extnamespace | extrelocatable | extversion | extconfig | extcondition
-------+------------------+----------+--------------+----------------+------------+-----------+--------------
16420 | pgaudit | 10 | 2200 | t | 16.0 | |
16465 | pgauditlogtofile | 10 | 2200 | t | 1.5 | |
(2 rows)
teguhth=#
3. setting pgaudit & pgauditlogtofile in postgresql.conf
shared_preload_libraries = 'pgaudit,pgauditlogtofile'
pgaudit.log = 'ddl, write, role'
pgaudit.log_directory = '/var/lib/pgsql/16/data/log'
pgaudit.log_filename = 'pgaudit-%a.log'
pgaudit.log_relation = on
[postgres@teguhth data]$ cat postgresql.conf | grep -i shared_preload_libraries
#shared_preload_libraries = '' # (change requires restart)
#shared_preload_libraries = 'pgaudit'
shared_preload_libraries = 'pgaudit,pgauditlogtofile'
[postgres@teguhth data]$ cat postgresql.conf | grep -i pgaudit.
#shared_preload_libraries = 'pgaudit'
shared_preload_libraries = 'pgaudit,pgauditlogtofile'
pgaudit.log = 'ddl, write, role'
pgaudit.log_directory = '/var/lib/pgsql/16/data/log'
pgaudit.log_filename = 'pgaudit-%a.log'
pgaudit.log_relation = on
[postgres@teguhth data]$
4. restart postgresql service
[root@teguhth ~]#
[root@teguhth ~]# systemctl restart postgresql-16
[root@teguhth ~]#
5. login to database and with specify user
psql -h 10.10.10.9 -U admin -d teguhth
[postgres@teguhth data]$ psql -h 10.10.10.9 -U admin -d teguhth
Password for user admin:
psql (16.0)
Type "help" for help.
teguhth=# SELECT current_user,version();
current_user | version
--------------+---------------------------------------------------------------------------------------------------------
admin | PostgreSQL 16.0 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-18), 64-bit
(1 row)
teguhth=#
6. test create table insert and delete drop
select * from barang;
delete from barang where kode_barang='ELK-06';
delete from barang where kode_barang='ELK-05';
select * from barang;
teguhth=# select * from barang;
kode_barang | nama_barang | satuan_barang | stok_barang
-------------+-------------+---------------+-------------
ELK-01 | RICE COOKER | BUAH | 20
ELK-02 | LEMARI ES | UNIT | 8
ELK-03 | TELEVISI | UNIT | 30
ELK-04 | RADIO/TAPE | BUAH | 35
ELK-05 | KOMPUTER | UNIT | 28
ELK-06 | KIPAS ANGIN | BUAH | 38
(6 rows)
teguhth=# delete from barang where kode_barang='ELK-06';
DELETE 1
teguhth=# delete from barang where kode_barang='ELK-05';
DELETE 1
teguhth=# select * from barang;
kode_barang | nama_barang | satuan_barang | stok_barang
-------------+-------------+---------------+-------------
ELK-01 | RICE COOKER | BUAH | 20
ELK-02 | LEMARI ES | UNIT | 8
ELK-03 | TELEVISI | UNIT | 30
ELK-04 | RADIO/TAPE | BUAH | 35
(4 rows)
teguhth=#
7. check log
[postgres@teguhth log]$ tail -f /var/lib/pgsql/16/data/log/pgaudit-Sun.log
2024-02-25 17:20:11.871 WIB,admin,teguhth,2219,10.10.10.9:49552,65db13e8.8ab,20,DELETE,2024-02-25 17:18:16 WIB,4/30,0,00000,SESSION,13,1,WRITE,DELETE,TABLE,public.barang,delete from barang where kode_barang ='ELK-06';,<not logged>,,,,,,,,,psql
2024-02-25 17:20:15.247 WIB,admin,teguhth,2219,10.10.10.9:49552,65db13e8.8ab,21,DELETE,2024-02-25 17:18:16 WIB,4/31,0,00000,SESSION,14,1,WRITE,DELETE,TABLE,public.barang,delete from barang where kode_barang ='ELK-05';,<not logged>,,,,,,,,,psql
2024-02-25 17:58:20.070 WIB,admin,teguhth,1504,10.10.10.9:48476,65db1cf4.5e0,1,INSERT,2024-02-25 17:56:52 WIB,3/20,0,00000,SESSION,1,1,WRITE,INSERT,TABLE,public.barang,"insert into barang(KODE_BARANG,NAMA_BARANG,SATUAN_BARANG,STOK_BARANG) values('ELK-01','RICE COOKER','BUAH',20);",<not logged>,,,,,,,,,psql
2024-02-25 17:58:20.073 WIB,admin,teguhth,1504,10.10.10.9:48476,65db1cf4.5e0,2,INSERT,2024-02-25 17:56:52 WIB,3/21,0,00000,SESSION,2,1,WRITE,INSERT,TABLE,public.barang,"insert into barang(KODE_BARANG,NAMA_BARANG,SATUAN_BARANG,STOK_BARANG) values('ELK-02','LEMARI ES','UNIT',8);",<not logged>,,,,,,,,,psql
2024-02-25 17:58:20.074 WIB,admin,teguhth,1504,10.10.10.9:48476,65db1cf4.5e0,3,INSERT,2024-02-25 17:56:52 WIB,3/22,0,00000,SESSION,3,1,WRITE,INSERT,TABLE,public.barang,"insert into barang(KODE_BARANG,NAMA_BARANG,SATUAN_BARANG,STOK_BARANG) values('ELK-03','TELEVISI','UNIT',30);",<not logged>,,,,,,,,,psql
2024-02-25 17:58:20.074 WIB,admin,teguhth,1504,10.10.10.9:48476,65db1cf4.5e0,4,INSERT,2024-02-25 17:56:52 WIB,3/23,0,00000,SESSION,4,1,WRITE,INSERT,TABLE,public.barang,"insert into barang(KODE_BARANG,NAMA_BARANG,SATUAN_BARANG,STOK_BARANG) values('ELK-04','RADIO/TAPE','BUAH',35);",<not logged>,,,,,,,,,psql
2024-02-25 17:58:20.075 WIB,admin,teguhth,1504,10.10.10.9:48476,65db1cf4.5e0,5,INSERT,2024-02-25 17:56:52 WIB,3/24,0,00000,SESSION,5,1,WRITE,INSERT,TABLE,public.barang,"insert into barang(KODE_BARANG,NAMA_BARANG,SATUAN_BARANG,STOK_BARANG) values('ELK-05','KOMPUTER','UNIT',28);",<not logged>,,,,,,,,,psql
2024-02-25 17:58:20.076 WIB,admin,teguhth,1504,10.10.10.9:48476,65db1cf4.5e0,6,INSERT,2024-02-25 17:56:52 WIB,3/25,0,00000,SESSION,6,1,WRITE,INSERT,TABLE,public.barang,"insert into barang(KODE_BARANG,NAMA_BARANG,SATUAN_BARANG,STOK_BARANG) values('ELK-06','KIPAS ANGIN','BUAH',38);",<not logged>,,,,,,,,,psql
2024-02-25 17:59:14.501 WIB,admin,teguhth,1504,10.10.10.9:48476,65db1cf4.5e0,7,DELETE,2024-02-25 17:56:52 WIB,3/27,0,00000,SESSION,7,1,WRITE,DELETE,TABLE,public.barang,delete from barang where kode_barang='ELK-06';,<not logged>,,,,,,,,,psql
2024-02-25 17:59:17.936 WIB,admin,teguhth,1504,10.10.10.9:48476,65db1cf4.5e0,8,DELETE,2024-02-25 17:56:52 WIB,3/28,0,00000,SESSION,8,1,WRITE,DELETE,TABLE,public.barang,delete from barang where kode_barang='ELK-05';,<not logged>,,,,,,,,,psql
^C
[postgres@teguhth log]$
8. for pgaudit log convert to table https://teguhth.blogspot.com/2024/04/convert-pgaudit-pgauditlogtofile-log.html
No comments:
Post a Comment