Re: pgaudit - an auditing extension for PostgreSQL

* Jim Nasby (Jim(dot)Nasby(at)BlueTreble(dot)com) wrote:
> On 2/17/15 1:10 PM, Stephen Frost wrote:
> >>What I'd prefer to see is a way to decouple the OS account from any
> >>>DB account. Clearly that doesn't protect us from the OS user doing
> >>>something bad, but at least then there's no way for them to just
> >>>silently run SQL commands.
> >If the DB account isn't a superuser then everything changes. There's no
> >point fighting with the OS user- they can run some other PG binary which
> >they've copied over locally and run SQL with that, or copy all the files
> >over to another server which they have complete access to. The fact
> >that they can also connect to the DB and run SQL isn't really an issue.
>
> I disagree. The difference here is that the OS can audit whatever
> commands they're running, but not what happens inside something like
> psql. Even if you did run a keylogger, trying to use that to
> interpret a psql session would be a real pain, if not impossible. So
> I don't think we can rely on the OS to audit SQL at all. But
> obviously if they did something like copy the files somewhere else,
> or bring in a new binary, the OS would at least have record that
> that happened.

From my experience, logging the commands is no where near enough.. psql
is hardly the only complex CLI process one can run. Further, I'm not
suggesting that we rely on the OS to audit SQL, merely stating that
anything which deals with auditing the connection to PG needs to be
outside of the PG process space (and that of processes owned by the user
which PG is running as).

> Though... I wonder if there's some way we could disallow *all*
> superuser access to the database, and instead create a special
> non-interactive CLI. That would allow us to throw the problem over
> the wall to the OS.

That sounds like a horrible approach. A non-interactive CLI would be
terrible and it's not clear to me how that'd really help. What happens
when they run emacs or vim?

> In any case, I think it's clear that there's a lot of value in at
> least handling the non-SU case, so we should try and do that now.
> Even if it's only in contrib.

Absolutely. Glad we agree on that. :)

> One thing that does occur to me though... perhaps we should
> specifically disable auditing of SU activities, so we're not
> providing a false sense of security.

I don't agree with this. Done properly (auditing enabled on startup,
using a remote auditing target, etc), it might be possible for such
auditing to catch odd superuser behavior. What we don't want to do is
claim that we provide full superuser auditing, as that's something we
can't provide. Appropriate and clear documentation is absolutely
critical, of course.

Thanks!

Stephen