Re: [HACKERS] pg audit requirements

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

> 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.

> 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?

> 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.

> 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.

Thanks,
-- 
-David
david@pgmasters.net