correlation https://teguhth.blogspot.com/2024/02/enable-pgaudit-pgauditlogtofile-in.html
https://teguhth.blogspot.com/2024/04/convert-pgaudit-pgauditlogtofile-log.html
1. config postgresql.conf & restart systemctl restart postgresql-18
listen_addresses='*'
shared_preload_libraries = 'pgaudit,pgauditlogtofile'
pgaudit.log_exclude = 'pg_catalog,information_schema'
pgaudit.log_directory = '/var/lib/pgsql/18/data/log'
pgaudit.log_filename = 'pgaudit-teguhth.log'
pgaudit.log = 'write,ddl,role'
#pgaudit.log = 'all'
pgaudit.log_catalog = off
pgaudit.log_relation = on
pgaudit.log_statement = on
pgaudit.log_parameter = off
pgaudit.log_rows = off
pgaudit.log_execution_time = off
pgaudit.log_execution_memory = off
# pgaudit.log_format = csv
2. enable pgaudit
CREATE EXTENSION pgaudit;
CREATE EXTENSION pgauditlogtofile;
3. check sample log
[postgres@teguhth-all log]$ cat /var/lib/pgsql/18/data/log/pgaudit-teguhth.log
"2026-08-10 15:00:27.206442683 WIB","postgres","teguhth","4488","[local]","6a798517.1188","DROP TABLE","0/2","2605","00000","SESSION","1","1","DDL","DROP TABLE","","","drop table suplier,<not logged>",,,,,,,,,"psql",,,,,,,
"2026-08-10 15:01:33.933630264 WIB","postgres","teguhth","4488","[local]","6a798517.1188","CREATE TABLE","0/3","2606","00000","SESSION","2","1","DDL","CREATE TABLE","","","\"create table suplier(\nKODE_SUPLIER char(5) not null,\nNAMA_SUPLIER varchar(30),\nALAMAT_SUPLIER varchar(30),\nKOTA_SUPLIER varchar(15),\nTELEPON_SUPLIER varchar(15),\nprimary key(KODE_SUPLIER))\",<not logged>",,,,,,,,,"psql",,,,,,,
"2026-08-10 15:01:49.017779942 WIB","postgres","teguhth","4488","[local]","6a798517.1188","SELECT","0/4","0","00000","SESSION","3","1","READ","SELECT","TABLE","public.suplier","select * from suplier,<not logged>",,,,,,,,,"psql",,,,,,,
"2026-08-10 15:01:49.019101379 WIB","postgres","teguhth","4488","[local]","6a798517.1188","INSERT","0/5","0","00000","SESSION","4","1","WRITE","INSERT","TABLE","public.suplier","\"insert into suplier(KODE_SUPLIER,NAMA_SUPLIER,ALAMAT_SUPLIER,KOTA_SUPLIER,TELEPON_SUPLIER) values ('EJ-01','PT ACTRON','JL THAMRIN 12','JAKARTA','(021) 850-2301')\",<not logged>",,,,,,,,,"psql",,,,,,,
"2026-08-10 15:01:49.020524723 WIB","postgres","teguhth","4488","[local]","6a798517.1188","INSERT","0/6","0","00000","SESSION","5","1","WRITE","INSERT","TABLE","public.suplier","\"insert into suplier(KODE_SUPLIER,NAMA_SUPLIER,ALAMAT_SUPLIER,KOTA_SUPLIER,TELEPON_SUPLIER) values ('EJ-02','PT MULYA ELEKTRONIK','JL SUDIRMAN 45','JAKARTA','(021) 855-4262')\",<not logged>",,,,,,,,,"psql",,,,,,,
"2026-08-10 15:01:49.021570061 WIB","postgres","teguhth","4488","[local]","6a798517.1188","INSERT","0/7","0","00000","SESSION","6","1","WRITE","INSERT","TABLE","public.suplier","\"insert into suplier(KODE_SUPLIER,NAMA_SUPLIER,ALAMAT_SUPLIER,KOTA_SUPLIER,TELEPON_SUPLIER) values ('EB-01','PT ULTRASOUND','JL SUKARNO HATTA 103','BANDUNG','(021) 522-3305')\",<not logged>",,,,,,,,,"psql",,,,,,,
[postgres@teguhth-all log]$
4. create table in dbatools
CREATE TABLE IF NOT EXISTS pgauditfile (
id SERIAL PRIMARY KEY,
log_time TEXT,
user_name TEXT,
database_name TEXT,
process_id INTEGER,
connection_info TEXT,
session_id TEXT,
command_tag TEXT,
sqlstate TEXT,
audit_type TEXT,
statement_class TEXT,
object_type TEXT,
object_name TEXT,
statement TEXT,
application_name TEXT,
raw_log TEXT
);
5. create phyton script load_pgaudit
[root@teguhth-all phyton]# pwd
/data/phyton
[root@teguhth-all phyton]#
[root@teguhth-all phyton]# cat /data/phyton/load_pgaudit.py
import csv
import psycopg2
from psycopg2.extras import execute_values
# Naikkan batas ukuran field menjadi 10 MB (cukup untuk statement panjang)
csv.field_size_limit(10 * 1024 * 1024)
# Koneksi ke server 10.10.10.90 dengan user admin
conn = psycopg2.connect(
host="10.10.10.90",
port=5432, # port default, sesuaikan jika berbeda
database="dbatools",
user="admin",
password="admin"
)
cur = conn.cursor()
log_file = "/var/lib/pgsql/18/data/log/pgaudit-teguhth.log"
data = [] # untuk batch insert
with open(log_file, 'r') as f:
reader = csv.reader(f, quotechar='"')
for row in reader:
# Lewati baris yang tidak lengkap
if len(row) < 18:
continue
timestamp = row[0]
user_name = row[1]
database_name = row[2]
try:
process_id = int(row[3])
except ValueError:
process_id = None
connection_info = row[4]
session_id = row[5]
command_tag = row[6]
sqlstate = row[9] if len(row) > 9 else None
audit_type = row[10] if len(row) > 10 else None
statement_class = row[13] if len(row) > 13 else None
object_type = row[15] if len(row) > 15 else None
object_name = row[16] if len(row) > 16 else None
statement = row[17] if len(row) > 17 else None
# Cari application_name
application_name = None
if len(row) > 20 and row[20] in ('psql', 'pgbench', 'pgadmin'):
application_name = row[20]
else:
for field in row:
if field in ('psql', 'pgbench', 'pgadmin', 'psql'):
application_name = field
break
data.append((
timestamp, user_name, database_name, process_id, connection_info,
session_id, command_tag, sqlstate, audit_type, statement_class,
object_type, object_name, statement, application_name,
','.join(row) # raw_log
))
# Insert batch
if data:
insert_sql = """
TRUNCATE TABLE pgauditfile;INSERT INTO pgauditfile
(log_time, user_name, database_name, process_id, connection_info,
session_id, command_tag, sqlstate, audit_type, statement_class,
object_type, object_name, statement, application_name, raw_log)
VALUES %s
"""
execute_values(cur, insert_sql, data)
conn.commit()
cur.close()
conn.close()
print(f"Data berhasil dimasukkan: {len(data)} baris.")
print(f"Copyright by : Teguh Triharto")
print(f"Website : https://www.linkedin.com/in/teguhth")
[root@teguhth-all phyton]#
6. insert log to table using phyton
python3 /data/phyton/load_pgaudit.py
7. select data mentah
SELECT
id,
log_time,
user_name,
database_name,
process_id,
connection_info,
session_id,
command_tag,
sqlstate,
audit_type,
statement_class,
object_type,
object_name,
statement,
application_name,
raw_log
FROM pgauditfile;
8. select data final
SELECT
id,
log_time,
user_name,
database_name,
process_id,
connection_info,
session_id,
command_tag,
sqlstate,
audit_type,
statement_class,
object_type,
object_name,
application_name,
trim(
replace(
replace(
regexp_replace(
regexp_replace(
raw_log,
'^.*?,(?:DDL|WRITE|READ|ROLE|MISC|MISC_SET|FUNCTION),[^,]*,[^,]*,[^,]*,',
''
),
',<not logged>.*$',
''
),
'\', ''
),
'"', ''
)
) || ';' AS query_text
FROM pgauditfile;
9. select data final simple without timezone
SELECT replace(trim(log_time), ' WIB', '')::timestamp AS log_time,database_name,
trim(
replace(
replace(
regexp_replace(
regexp_replace(
raw_log,
'^.*?,(?:DDL|WRITE|READ|ROLE|MISC|MISC_SET|FUNCTION),[^,]*,[^,]*,[^,]*,',
''
),
',<not logged>.*$',
''
),
'\', ''
),
'"', ''
)
) || ';' AS query_text
FROM pgauditfile order by log_time asc;
No comments:
Post a Comment