Re: PostgreSQL Audit Extension

From: Bruce Momjian <bruce(at)momjian(dot)us>
To: David Steele <david(at)pgmasters(dot)net>
Cc: Robert Haas <robertmhaas(at)gmail(dot)com>, Stephen Frost <sfrost(at)snowman(dot)net>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Alvaro Herrera <alvherre(at)2ndquadrant(dot)com>, "Joshua D(dot) Drake" <jd(at)commandprompt(dot)com>, Pg Hackers <pgsql-hackers(at)postgresql(dot)org>, "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com>, Jim Nasby <Jim(dot)Nasby(at)bluetreble(dot)com>
Subject: Re: PostgreSQL Audit Extension
Date: 2016-02-19 15:14:47
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

On Fri, Feb 19, 2016 at 09:19:58AM -0500, David Steele wrote:
> > I was suggesting we could track write events via logical replication and
> > then we only have to figure out auditing of read events, which would be
> > easier.
> I agree that DDL/DML audit logging requires a lot of the same
> information as logical replication but I don't think reading the logical
> WAL stream is a good way to do audit logging even for writes.
> For instance there are some "writes" that are not WAL logged such as SET
> or ALTER SYSTEM. Determining attribution would be difficult or
> impossible, such as which function called an update statement (or vice
> versa). Tying together the read and write information would be tricky
> as well since the WAL stream contains information on transactions but
> not user sessions, if I understand it correctly.
> The end result is that it would be very difficult to record a coherent,
> attributed, and sequential audit log and in fact represent a step
> backward from pgaudit's current capabilities.

Understood. My point is that there is a short list of read events, and
many DDL events. We have already hesitated to record DDL changes for
logical replication because of the code size, maintenance overhead, and
testing required. If we could use the same facility for both logical
replication and auditing, the cost overhead is less per feature. For
example, we don't need to read the WAL to do the auditing, but the same
facility could be used for both, e.g. output some JSON standard format
that both logical replication and auditing could understand.

The bottom line is we need to crack the record-DDL nut somehow, and at
least in this case, we have two use-cases for it.

Bruce Momjian <bruce(at)momjian(dot)us>

+ As you are, so once was I. As I am, so you will be. +
+ Roman grave inscription +

In response to


Browse pgsql-hackers by date

  From Date Subject
Next Message David Steele 2016-02-19 15:31:41 Re: PostgreSQL Audit Extension
Previous Message Fujii Masao 2016-02-19 15:06:09 Re: pg_ctl promote wait