Re: pgaudit - an auditing extension for PostgreSQL

Neil,

Thanks for sharing- sounds very similar to what I've heard also. Your
input and experience with this is very much sought and appreciated-
please continue to help us understand, so we're able to provide
something concrete and useful. Further comments inline.

* Neil Tiffin (neilt(at)neiltiffin(dot)com) wrote:
> 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.

Any system where there exists a role similar to 'superuser' in the PG
sense (a user who is equivilant to the Unix UID under which the rest of
the system is run) would be hard-pressed to provide a solution to this
issue. With SELinux it may be possible and I'd love to see an example
from someone who feels they've accomplished it. That said, if we can
reduce the need for a 'superuser' role sufficiently by having the
auditing able to be managed independently, then we may have reached the
level of "considerable headache".

As many have pointed out previously, there is a certain amount of risk
associated with running without *any* superuser role in the system
(though it's certainly possible to do so), as it becomes much more
difficult to do certain kinds of analysis and forensics associated with
trying to recover a corrupt system. Still, that risk may very well be
acceptable in some environments. I'd certainly like to see us get to a
point where a superuser role isn't absolutely required once the system
is up and running.

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

I do understand this and I expect we will always allow the roles which
are 'superuser' to modify these procedures, but we'll get to a point
where such a role doesn't have to exist (or it's a considerable headache
to get one into place) and that'll get us to the point which is required
to check the "formal auditing" box for the organizations which are
interested and willing to accept those trade-offs.

Thanks,

Stephen