2017-11-13 19:19 GMT+01:00 David Steele <da...@pgmasters.net>: > Hi Pavel, > > On 11/10/17 2:33 AM, Pavel Stehule wrote: > >> >> I am sending some notes, experience about usage of pgAudit. >> > > Thanks for the input! I'm not sure this is the best forum for comments, > however, since pgAudit is not part of Postgres. > > Issues can be opened at the github site: > https://github.com/pgaudit/pgaudit >
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. Regards Pavel > Thanks, > -- > -David > da...@pgmasters.net > >
