Tuesday, July 18, 2017

Use standard logging for audit purpose in PostgreSQL 9.6

Configuration:

$ grep -i audit postgresql.conf
log_connections = on  # audit setting
log_disconnections = on # audit setting
log_line_prefix = '<%m:%r:%u@%d:[%p]:> '        # audit setting
log_statement = 'all'                   # audit setting

Sample pg_log output:

<2017-07-18 20:25:12.374 +08:127.0.0.1(57640):[unknown]@[unknown]:[3541]:> LOG:  connection received: host=127.0.0.1 port=57640
< 2017-07-18 20:25:12.375 +08:127.0.0.1(57640):admin1@testdb:[3541]:> LOG:  connection authorized: user=admin1 database=testdb
< 2017-07-18 20:25:13.037 +08:127.0.0.1(57640):admin1@testdb:[3541]:> LOG:  disconnection: session time: 0:00:00.662 user=admin1 database=testdb host=127.0.0.1 port=57640
< 2017-07-18 20:25:17.622 +08:127.0.0.1(57642):[unknown]@[unknown]:[3543]:> LOG:  connection received: host=127.0.0.1 port=57642
< 2017-07-18 20:25:17.623 +08:127.0.0.1(57642):admin1@testdb:[3543]:> LOG:  connection authorized: user=admin1 database=testdb
< 2017-07-18 20:25:32.728 +08:127.0.0.1(57642):admin1@testdb:[3543]:> LOG:  statement: create table t1(id integer);
< 2017-07-18 20:25:41.154 +08:127.0.0.1(57642):admin1@testdb:[3543]:> LOG:  statement: insert into t1 values (1);
< 2017-07-18 20:25:47.598 +08:127.0.0.1(57642):admin1@testdb:[3543]:> LOG:  statement: select * from t1;
< 2017-07-18 20:25:50.049 +08:127.0.0.1(57642):admin1@testdb:[3543]:> LOG:  statement: drop table t1;
< 2017-07-18 20:25:54.762 +08:127.0.0.1(57642):admin1@testdb:[3543]:> LOG:  disconnection: session time: 0:00:37.139 user=admin1 database=testdb host=127.0.0.1 port=57642

image