Re: pgaudit - an auditing extension for PostgreSQL

From: Stephen Frost <sfrost(at)snowman(dot)net>
To: Abhijit Menon-Sen <ams(at)2ndquadrant(dot)com>
Cc: Ian Barwick <ian(at)2ndquadrant(dot)com>, pgsql-hackers(at)postgresql(dot)org
Subject: Re: pgaudit - an auditing extension for PostgreSQL
Date: 2014-05-04 12:52:42
Message-ID: 20140504125242.GB2556@tamriel.snowman.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Abhijit,

* Abhijit Menon-Sen (ams(at)2ndquadrant(dot)com) wrote:
> 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.

I'm not anxious to try and replace ereport() either, but I don't see
that as necessary to have multiple log targets (we already have that,
after all..). The design that I had discussed w/ Magnus and at the
hacker's meeting in 2011 was around the notion of 'tags' and a
structured interface to the logging collector. That fits in nicely with
the idea of using a DSM queue, I'd think.

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

This also addresses things like anonymous DO blocks and functions
then..? With enough information to be useful for forensics?

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

This seems like an independently useful thing (would be nice to have in
our logs and in pg_stat_statements, imv..).

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

We'd need to also consider permissions and how these are managed.
Presumably, the 'owner' of a relation would be able to define and modify
its audit parameters, but it would be useful to have that capability
independently grant'able and also be sure that any changes made to the
auditing are clearly logged.

This gets into a much larger area of discussion around what can be
granted and what must be owner-only or superuser-only.

Thanks,

Stephen

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Stephen Frost 2014-05-04 12:58:33 Re: pgaudit - an auditing extension for PostgreSQL
Previous Message Bruce Momjian 2014-05-04 12:46:07 9.4 release notes