Re: pgaudit and create postgis extension logs a lot inserts

On Fri, Jan 19, 2018 at 3:41 PM, David Steele <david(at)pgmasters(dot)net> wrote:

> On 1/19/18 6:05 AM, Magnus Hagander wrote:
> >
> >
> > On Thu, Jan 18, 2018 at 6:54 PM, Joe Conway <mail(at)joeconway(dot)com
> > <mailto:mail(at)joeconway(dot)com>> wrote:
> >
> > On 01/18/2018 04:12 AM, Svensson Peter wrote:
> > > When I then install postgis extension in a database it writes a
> huge
> > > amount of logs which slow down the server a lot.
> > > Not only table creation and functions are logged, even all
> inserts in
> > > spatial_ref_sys are written to the audit-log.
> > >
> > > LOG: AUDIT: SESSION,1,1,DDL,CREATE FUNCTION,,,"
> > > ......
> > > INSERT INTO ""spatial_ref_sys"" (""srid"",""auth_name"
> > > ....
> > >
> > > This behaviour make pgaudit useless in our environment due to the
> > > overhead in log-file write.
> >
> > How often do you intend to install PostGIS? Disable pgaudit, install
> > PostGIS, enable pgaudit?
> >
> >
> > Would it make sense for pgaudit to, at least by option, not include DDL
> > statements that are generated as "sub-parts" of a CREATE EXTENSION? It
> > should still log the CREATE EXTENSION of course, but not necessarily all
> > the contents of it, since that's actually defined in the extension
> > itself already?
> That's doable, but I think it could be abused if it was always on and
> installing extensions is generally not a daily activity.
>

Probably true, yeah. It can certainly be part of a daily activity in say CI
environments etc, but those are not likely environments where pg_audit
makes that much sense in the first place.

--
Magnus Hagander
Me: https://www.hagander.net/ <http://www.hagander.net/>
Work: https://www.redpill-linpro.com/ <http://www.redpill-linpro.com/>