I hope so some auditing functionality will be core feature.
pgAudit provides basic functionality and usually is good enough. But it is not good enough for some applications in financial services.
It's certainly being used successfully in the financial sector, but I'm sure there are some applications where it won't work.
yes, it is used there. Probably there are not too much applications, where pgAudit is not enough. Unfortunately, these applications are usually business critical.
The requirements:
1. structured output - attached query is not good enough - column name, table name, schema, database, role should be separated
Have you tried using pgaudit.log_relation? That would at least get you table name, and schema. Database and role should really be handled by postgres. Role is actually pretty tricky - which one should be logged?
sure I did it.
Who got new rights, who lost rights, new user, dropped user, changes of some features per user (work_mem, logging, ..)
2. separated log (log file) with guaranteed write - fsync after every line means significant performance issue, but fsync every 1sec (or defined interval) is acceptable
This would be better as a feature of Postgres logging. Managing log files in individual backends doesn't seem like a good idea.
I agree. The auditing can be good use case for this enhanced log system.
3. security issues - not enough access rights to database object should be processed and logged in audit log too.
Postgres will generate errors on access violations. Unfortunately, there are currently no hooks that will allow pgAudit to log them. At least, that I'm aware of.
I have a customer, who want to collect all audit data (requires in structured format) and store it to fraud detection software.
I am not sure if one hook helps - It looks so some security related collector (like stats collector or log collector) it is necessary. Currently these informations are too spread over all postgres.
