Re: pgaudit - an auditing extension for PostgreSQL

On May 4, 2014, at 10:12 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Stephen Frost <sfrost@snowman.net> writes:
>> * Abhijit Menon-Sen (ams@2ndquadrant.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
independentof the technical roles and responsibilities.  Meaning that superuser or owner roles could not change the
auditrequirements once established and the audit role could not change any data or data definitions except add, change
orremove 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
droppedor created by someone.  The audit reporting system (external to the database) had notifications for these types
ofevents.  For example, by procedure these changes should have been done in conjunction with the auditors and the
initialaudit requirements should already have been improved by the auditors when the column/table was added back.
Droppinga 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
happythat 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
approvedappend 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-changeablelevel of audit reporting should be in place (i.e. log all create/drop/rename tables/columns/roles and log
allsuperuser/audit role actions) and this basic audit reporting cannot be turned off or have the destination changed
withoutconsiderable headache (something like init'ing the database?).  Then data monitoring auditing rules can be
added/changed/removedas necessary within the authorization framework.  Formal auditing might also require other
functionalitylike checksums. 

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

Neil