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

2017-11-13 19:19 GMT+01:00 David Steele <david@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

On 11/13/17 1:43 PM, Pavel Stehule wrote:

2017-11-13 19:19 GMT+01:00 David Steele <david@pgmasters.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&gt;

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@pgmasters.net

2017-11-15 16:21 GMT+01:00 David Steele <david@pgmasters.net>:

On 11/13/17 1:43 PM, Pavel Stehule wrote:

2017-11-13 19:19 GMT+01:00 David Steele <david@pgmasters.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/pg
audit>

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.

thank you for info

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.

I have a patch for pgaudit that does more structured informations to
output, but I waiting to customer to be able to publish it. The my patch
does little bit chaotic result because there are two concepts - using
generic variable - object name from original pgaudit, and using semantic
variables - table name, column name, ... It is not good mix, and when I
have possibility to start again, then probably I'll start from scratch. I
have not any problem with pgaudit design, but two different concepts of
output informations don't work well.

Note: PostgreSQL error systems allows to set additional fields for error
info like table name, column name. Unfortunately, there are not role name.
These fields can be filled by security exceptions and can be simply used by
some like pgaudit applications (without messages parsing)

Regards

Pavel