Re: pgaudit - an auditing extension for PostgreSQL

At 2014-05-02 14:04:27 -0400, sfrost(at)snowman(dot)net wrote:
>
> I'd really like to see us be able to, say, log to a table and have
> more fine-grained control over what is logged, without needing an
> extension.

There were several factors we considered in our work:

1. We did the minimum possible to produce something that gives us
demonstrably more than «log_statement=all» in 9.3/9.4/9.5.

2. We wanted to produce something that could be used *now*, i.e. with
9.3 and soon 9.4, to get wider feedback based on actual usage. I'm
hoping that by the time we make a submission for 9.5, we'll have a
clearer picture of what Postgres auditing should look like.

3. We steered clear of implementing different log targets. We know that
ereport() doesn't cut it, but decided that doing anything else would
be better after some feedback and wider discussion. Any suggestions
in this regard are very welcome.

(Stephen, I can see from your mail that you've already inferred at least
some of the above, so it's more a general statement of our approach than
a response to what you said.)

> > 2. pgaudit creates a log entry for each affected object […]
>
> Interesting- I'm a bit on the fence about this one. Perhaps you can
> elaborate on the use-case for this?

Who accessed public.x last month?

Answering that question would become much more difficult if one had to
account for every view that might refer to public.x. And did the view
refer to public.x before the schema change on the first Wednesday of
last month?

We don't have a "deparsed" representation of DML, so "select * from x"
is logged differently from "select * from other.x". Same with potential
complications like how exactly a join is written.

The way pgaudit does it, you can just "grep public.x" in your audit log
and be sure (modulo bugs, of course) you're seeing everything relevant.

> This kind of auditing is often about specific information (and
> therefore specific objects) and it'd be ideal to have that set
> up and managed alongside the table definition.

Yes, exactly.

-- Abhijit