Re: pgaudit - an auditing extension for PostgreSQL

On May 4, 2014, at 10:12 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Stephen Frost <sfrost(at)snowman(dot)net> writes:
>> * Abhijit Menon-Sen (ams(at)2ndquadrant(dot)com) wrote:
>>> 1. I wish it were possible to prevent even the superuser from disabling
>>> audit logging once it's enabled, so that if someone gained superuser
>>> access without authorisation, their actions would still be logged.
>>> But I don't think there's any way to do this.
>
>> Their actions should be logged up until they disable auditing and
>> hopefully those logs would be sent somewhere that they're unable to
>> destroy (eg: syslog). Of course, we make that difficult by not
>> supporting log targets based on criteria (logging EVERYTHING to syslog
>> would suck).
>
>> I don't see a way to fix this, except to minimize the amount of things
>> requiring superuser to reduce the chances of it being compromised, which
>> is something I've been hoping to see happen for a long time.
>
> Prohibiting actions to the superuser is a fundamentally flawed concept.
> If you do that, you just end up having to invent a new "more super"
> kind of superuser who *can* do whatever it is that needs to be done.

In getting approval for FDA validated systems, IIRC, they wanted to see the audit change permissions completely independent of the technical roles and responsibilities. Meaning that superuser or owner roles could not change the audit requirements once established and the audit role could not change any data or data definitions except add, change or remove auditing rules. Everything the auditor role did was logged, no exceptions.

If an owner or superuser dropped a table the auditors were completely fine with a log entry that the table/column was dropped or created by someone. The audit reporting system (external to the database) had notifications for these types of events. For example, by procedure these changes should have been done in conjunction with the auditors and the initial audit requirements should already have been improved by the auditors when the column/table was added back. Dropping a table/column without getting approval ahead of time was a procedure violation that could result in termination. Of course, there were a lot more details.

By monitoring creation/delete DDL events along with non changeable (by technical staff) audit rules the auditors were happy that they could manage the audit conformance.

And yes, the audit logs had to be written in a way they could not be easily tampered with. At the time we used an approved append only, read only hardware file/reporting system.

In considering how this might apply to PostgreSQL, it seems that once formal auditing is turned on, basic non-changeable level of audit reporting should be in place (i.e. log all create/drop/rename tables/columns/roles and log all superuser/audit role actions) and this basic audit reporting cannot be turned off or have the destination changed without considerable headache (something like init'ing the database?). Then data monitoring auditing rules can be added/changed/removed as necessary within the authorization framework. Formal auditing might also require other functionality like checksums.

Until these or similar requirements (for formal auditing) are in core, it makes no sense (to me) to not allow the superuser to manage auditing because any conformance requirements have to be procedure based, not system based. People often forget that procedure/people based audit conformance worked just fine before computers existed.

Neil