PG_AUDITSome common use cases and features of ‘PG_AUDIT’:
Yum install postgresql-contrib Yum install postgresql-15-pg-auditSTEP 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 restartSTEP 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) |