Re: pgaudit - an auditing extension for PostgreSQL

On May 4, 2014, at 3:17 PM, Stephen Frost <sfrost(at)snowman(dot)net> wrote:

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

Not sure I understand which issue you are referring to. If you are referring to 'cannot be turned off', I would think a reasonable first pass would be to handle it similar to '--data-checksums' in 'initdb'. For example, "This option can only be set during initialization, and cannot be changed later. If set, basic auditing is on for all objects, in all databases."

> 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

If all of the superuser's actions are logged and it's not possible to turn off the logging (without considerable headache) then it may not matter what the superuser can do. If the superuser makes changes and they are logged then the auditors have sufficient information to see if the correct procedures were followed. Validated systems are based on tracking, not necessarily prohibiting. Select individuals that should be able to be trusted (which should apply to superusers) should be able to perform the actions necessary to support the organization.

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