Subject: Re: pgaudit and create postgis extension logs a lot inserts
To: Magnus Hagander <magnus@hagander.net>, Joe Conway <mail@joeconway.com>
Cc: Svensson Peter <peter.svensson@smhi.se>,
 "pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
References: 
 <1CC6B04B8F153C4CBE486B1C7FD897430269E49962@WINVMSERV463.ad.smhi.se>
 <514a9172-df28-56ab-ee65-2c01e5cbeb6a@joeconway.com>
 <CABUevExuUaAg5tW0Mcg_YRRLSsh_zudNuYir=314xDJGrkyVSg@mail.gmail.com>
From: David Steele <david@pgmasters.net>
Message-ID: <d3351319-a6bb-6bf8-2d2b-7bcd0b731ea0@pgmasters.net>
Date: Fri, 19 Jan 2018 08:41:42 -0500
User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.13; rv:52.0)
 Gecko/20100101 Thunderbird/52.5.2
MIME-Version: 1.0
In-Reply-To: 
 <CABUevExuUaAg5tW0Mcg_YRRLSsh_zudNuYir=314xDJGrkyVSg@mail.gmail.com>
Content-Type: text/plain; charset=utf-8
Content-Language: en-US
Content-Transfer-Encoding: 8bit
Precedence: bulk

On 1/19/18 6:05 AM, Magnus Hagander wrote:
> 
> 
> On Thu, Jan 18, 2018 at 6:54 PM, Joe Conway <mail@joeconway.com
> <mailto:mail@joeconway.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.

It seems in this case the best action is to disable pgaudit before
installing postgis or install postgis first.

Regards,
-- 
-David
david@pgmasters.net