PG_AUDITSome common use cases and features of ‘PG_AUDIT’:
Yum install postgresql-contrib
Yum install postgresql-15-pg-audit
STEP 2 : Create Audit Extension
Create Extension pg_audit;
STEP 3 : Edit postgresql.conf
Vi postgresql.conf
shared_preload_libraries = 'pg_audit'
------------------------------------------------------------------------------
CUSTOMIZED OPTIONS
-----------------------------------------------------------------------------
pgaudit.log = all
pgaudit.log_relation = 'all'
pgaudit.config = 'pgaudit.conf'
STEP 4 : Restart postgresql
/usr/pgsql-15/bin/pg_ctl -D /var/lib/pgsql/15/data restart
STEP 5: Monitor Audit logs
Create table t1 (id int, name text);
Insert into t1 values(1,’aaa’);
Insert into t1 values(2,’bbb’);
-bash-4.2$ cd /var/lib/pgsql/15/data/log
-bash-4.2$ ls -lrt
total 2836
-rw-------. 1 postgres postgres 272175 May 18 16:28 postgresql-Sat.log
-rw-------. 1 postgres postgres 1025872 May 20 18:07 postgresql-Mon.log
-rw-------. 1 postgres postgres 707600 May 21 18:08 postgresql-Tue.log
-rw-------. 1 postgres postgres 88929 May 22 18:07 postgresql-Wed.log
-rw-------. 1 postgres postgres 125357 May 23 16:34 postgresql-Thu.log
-rw-------. 1 postgres postgres 227314 May 24 12:48 postgresql-Fri.log
-bash-4.2$ tail -50f postgresql-Fri.log
2024-05-24 12:44:11.296 IST [7309] LOG: AUDIT: SESSION,1,1,READ,SELECT,,,"SELECT n.nspname as ""Schema"",
c.relname as ""Name"",
CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'm' THEN 'materialized view'
WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 't' THEN 'TOAST table' WHEN 'f'
THEN 'foreign table' WHEN 'p' THEN 'partitioned table' WHEN 'I' THEN 'partitioned index'
END as ""Type"",
pg_catalog.pg_get_userbyid(c.relowner) as ""Owner""
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
LEFT JOIN pg_catalog.pg_am am ON am.oid = c.relam
WHERE c.relkind IN ('r','p','')
AND n.nspname <> 'pg_catalog'
AND n.nspname !~ '^pg_toast'
AND n.nspname <> 'information_schema'
AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1,2;",< not logged>
2024-05-24 12:44:27.334 IST [7309] LOG: statement: create table t1(id int, name text);
2024-05-24 12:44:45.790 IST [7309] LOG: statement: insert into ta values (1,'aaa');
2024-05-24 12:44:45.790 IST [7309] ERROR: relation "ta" does not exist at character 13
2024-05-24 12:44:45.790 IST [7309] STATEMENT: insert into ta values (1,'aaa');
2024-05-24 12:44:52.736 IST [7309] LOG: statement: insert into t1 values (1,'aaa');
2024-05-24 12:44:52.736 IST [7309] LOG: AUDIT: SESSION,2,1,WRITE,INSERT,,,
"insert into t1 values (1,'aaa');",< not logged>
2024-05-24 12:45:01.740 IST [7309] LOG: statement: insert into t1 values (2,'bbb');
2024-05-24 12:45:01.741 IST [7309] LOG: AUDIT: SESSION,3,1,WRITE,INSERT,,,
"insert into t1 values (2,'bbb');",< not logged>
2024-05-24 12:47:22.952 IST [7364] LOG: statement: SELECT r.rolname, r.rolsuper, r.rolinherit,
r.rolcreaterole, r.rolcreatedb, r.rolcanlogin,
r.rolconnlimit, r.rolvaliduntil,
ARRAY(SELECT b.rolname
FROM pg_catalog.pg_auth_members m
JOIN pg_catalog.pg_roles b ON (m.roleid = b.oid)
WHERE m.member = r.oid) as memberof
, r.rolreplication
, r.rolbypassrls
FROM pg_catalog.pg_roles r
WHERE r.rolname !~ '^pg_'
ORDER BY 1;
2024-05-24 12:47:23.073 IST [7364] LOG: AUDIT: SESSION,1,1,READ,SELECT,,,
"SELECT r.rolname, r.rolsuper, r.rolinherit,
r.rolcreaterole, r.rolcreatedb, r.rolcanlogin,
r.rolconnlimit, r.rolvaliduntil,
ARRAY(SELECT b.rolname
FROM pg_catalog.pg_auth_members m
JOIN pg_catalog.pg_roles b ON (m.roleid = b.oid)
WHERE m.member = r.oid) as memberof
, r.rolreplication
, r.rolbypassrls
FROM pg_catalog.pg_roles r
WHERE r.rolname !~ '^pg_'
ORDER BY 1;",< not logged>
« Previous (pg_badger - log analysis tool) |