Request: pgAudit compatible

[dbajuliano]

Hi Darold

Would be amazing if we can get a friendly report or a tab from pgAudit entries, what do you think?

All your tools are amazing, thank you so much for the good work 🥇

[darold]

Can you post a log sample and you want to see in the report so that I can see how and what pgAudit is logging?

[dbajuliano]

Sorry about the very long delay.

I was thinking of having a pgAudit tab on pgBadger similar to the Overview/General Activity. Something displaying the WRITE Statements: INSERT, UPDATE, DELETE, TRUNCATE, and COPY operations grouped by user. Also, the ROLE Statements: related to roles and privileges: GRANT, REVOKE, CREATE/ALTER/DROP ROLE. You could include other pgAudit statements if you want, but they are not from my interest in auditing.

Or maybe something simple like displays pgAudit entries in the Events/most-frequent-errors-events similar to the replication logs.

Here is a sample from pgAudit documentation: https://github.com/pgaudit/pgaudit#example-1

[hedayat]

Unfortunately, pgAudit doesn't log the duration of a query. However, if you enable duration logging with log_duration = on; you'll get the duration in the following line as below:

2022-04-18 13:28:29.510 +0430 [80843] LOG:  AUDIT: SESSION,5,1,READ,SELECT,,,"select *
        from account;",<not logged>
2022-04-18 13:28:29.510 +0430 [80843] LOG:  duration: 0.418 ms

It'd be nice if pgBadger can parse such statements combined together too.

[darold]

Hi, pgBager is already able to do that, this is just that the pg_audit format is not supported.

[hedayat]

Yeah thanks, I just discovered the --log-duration option. :">

And note that the whole query is enclosed in "s and if there are "s in the query, they are specified as below:

Original query: select '"hi"';

pgAudit log:

2022-04-18 15:38:50.110 +0430 [87456] LOG:  AUDIT: SESSION,3,1,READ,SELECT,,,"select '""hi""';",<not logged>