Re: [HACKERS] pg audit requirements

On 11/13/17 1:43 PM, Pavel Stehule wrote:
> 2017-11-13 19:19 GMT+01:00 David Steele <david(at)pgmasters(dot)net
>
> 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 <https://github.com/pgaudit/pgaudit>
>
> I hope so some auditing functionality will be core feature.

Well, that makes two of us!

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

Agreed, the logging for the ROLE class is not very good. Most detailed
information is pulled from event triggers which do not fire for global
objects like roles and databases.

SET operations should be logged with the MISC class, though.

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

You may want to take a look at
https://github.com/pgaudit/pgaudit_analyze. This a reference
implementation that demonstrates how to get pgAudit info into a
structured form. It includes logging errors and associating them with
the statement/transaction that caused the error.

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

I can't argue with that.

--
-David
david(at)pgmasters(dot)net