Re: [HACKERS] pg audit requirements

2017-11-13 19:19 GMT+01:00 David Steele <david(at)pgmasters(dot)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
> david(at)pgmasters(dot)net
>
>