pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedpgaudit and create postgis extension logs a lot inserts
7+ messages / 5 participants
[nested] [flat]
* pgaudit and create postgis extension logs a lot inserts
@ 2018-01-18 12:12 Svensson Peter <peter.svensson@smhi.se>
2018-01-18 16:54 ` Re: pgaudit and create postgis extension logs a lot inserts Joe Conway <mail@joeconway.com>
2018-01-19 13:51 ` Re: pgaudit and create postgis extension logs a lot inserts David Steele <david@pgmasters.net>
0 siblings, 2 replies; 7+ messages in thread
From: Svensson Peter @ 2018-01-18 12:12 UTC (permalink / raw)
To: pgsql-performance
Hi,
I have installed pgaudit, and configured as:
pgaudit.log = 'ddl,role'
pgaudit.log_level = 'log' (default)
Versions: postgresql96 (9.6.6) , pgaudit96 (1.0.4), postgis 2.3.2, Rhel 7.4
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.
I have tried different combinations of pgaudit.log settings (role,-functions), (role), and also changed pgaudit.log_level to warning, but it was not better.
Does anybody have a useful pgaudit settings which not overflow the log files, even when installing postgis or other extensions?
Also noticed that setting a session log to none (set pgaudit.log='none';) overrides parameter from postgresql.conf, but does not get logged, and then you can do whatever you want without any audit.
I supposed this changing of audit session log parameter should be logged to file?
Regards,
Peter
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: pgaudit and create postgis extension logs a lot inserts
2018-01-18 12:12 pgaudit and create postgis extension logs a lot inserts Svensson Peter <peter.svensson@smhi.se>
@ 2018-01-18 16:54 ` Joe Conway <mail@joeconway.com>
2018-01-19 11:05 ` Re: pgaudit and create postgis extension logs a lot inserts Magnus Hagander <magnus@hagander.net>
2018-01-19 12:24 ` RE: pgaudit and create postgis extension logs a lot inserts Karen Stone <kstone@eldocomp.com>
1 sibling, 2 replies; 7+ messages in thread
From: Joe Conway @ 2018-01-18 16:54 UTC (permalink / raw)
To: Svensson Peter <peter.svensson@smhi.se>; pgsql-performance
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?
Joe
--
Crunchy Data - http://crunchydata.com
PostgreSQL Support for Secure Enterprises
Consulting, Training, & Open Source Development
Attachments:
[application/pgp-signature] signature.asc (818B, ../../514a9172-df28-56ab-ee65-2c01e5cbeb6a@joeconway.com/2-signature.asc)
download
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: pgaudit and create postgis extension logs a lot inserts
2018-01-18 12:12 pgaudit and create postgis extension logs a lot inserts Svensson Peter <peter.svensson@smhi.se>
2018-01-18 16:54 ` Re: pgaudit and create postgis extension logs a lot inserts Joe Conway <mail@joeconway.com>
@ 2018-01-19 11:05 ` Magnus Hagander <magnus@hagander.net>
2018-01-19 13:41 ` Re: pgaudit and create postgis extension logs a lot inserts David Steele <david@pgmasters.net>
1 sibling, 1 reply; 7+ messages in thread
From: Magnus Hagander @ 2018-01-19 11:05 UTC (permalink / raw)
To: Joe Conway <mail@joeconway.com>; +Cc: Svensson Peter <peter.svensson@smhi.se>; pgsql-performance
On Thu, Jan 18, 2018 at 6:54 PM, Joe Conway <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?
--
Magnus Hagander
Me: https://www.hagander.net/ <http://www.hagander.net/;
Work: https://www.redpill-linpro.com/ <http://www.redpill-linpro.com/;
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: pgaudit and create postgis extension logs a lot inserts
2018-01-18 12:12 pgaudit and create postgis extension logs a lot inserts Svensson Peter <peter.svensson@smhi.se>
2018-01-18 16:54 ` Re: pgaudit and create postgis extension logs a lot inserts Joe Conway <mail@joeconway.com>
2018-01-19 11:05 ` Re: pgaudit and create postgis extension logs a lot inserts Magnus Hagander <magnus@hagander.net>
@ 2018-01-19 13:41 ` David Steele <david@pgmasters.net>
2018-01-20 13:05 ` Re: pgaudit and create postgis extension logs a lot inserts Magnus Hagander <magnus@hagander.net>
0 siblings, 1 reply; 7+ messages in thread
From: David Steele @ 2018-01-19 13:41 UTC (permalink / raw)
To: Magnus Hagander <magnus@hagander.net>; Joe Conway <mail@joeconway.com>; +Cc: Svensson Peter <peter.svensson@smhi.se>; pgsql-performance
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: pgaudit and create postgis extension logs a lot inserts
2018-01-18 12:12 pgaudit and create postgis extension logs a lot inserts Svensson Peter <peter.svensson@smhi.se>
2018-01-18 16:54 ` Re: pgaudit and create postgis extension logs a lot inserts Joe Conway <mail@joeconway.com>
2018-01-19 11:05 ` Re: pgaudit and create postgis extension logs a lot inserts Magnus Hagander <magnus@hagander.net>
2018-01-19 13:41 ` Re: pgaudit and create postgis extension logs a lot inserts David Steele <david@pgmasters.net>
@ 2018-01-20 13:05 ` Magnus Hagander <magnus@hagander.net>
0 siblings, 0 replies; 7+ messages in thread
From: Magnus Hagander @ 2018-01-20 13:05 UTC (permalink / raw)
To: David Steele <david@pgmasters.net>; +Cc: Joe Conway <mail@joeconway.com>; Svensson Peter <peter.svensson@smhi.se>; pgsql-performance
On Fri, Jan 19, 2018 at 3:41 PM, David Steele <david@pgmasters.net> wrote:
> 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.
>
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/;
^ permalink raw reply [nested|flat] 7+ messages in thread
* RE: pgaudit and create postgis extension logs a lot inserts
2018-01-18 12:12 pgaudit and create postgis extension logs a lot inserts Svensson Peter <peter.svensson@smhi.se>
2018-01-18 16:54 ` Re: pgaudit and create postgis extension logs a lot inserts Joe Conway <mail@joeconway.com>
@ 2018-01-19 12:24 ` Karen Stone <kstone@eldocomp.com>
1 sibling, 0 replies; 7+ messages in thread
From: Karen Stone @ 2018-01-19 12:24 UTC (permalink / raw)
To: Svensson Peter <peter.svensson@smhi.se>; Joe Conway <mail@joeconway.com>; pgsql-performance
Please remove me from this list. Thanks.
Karen Stone| Technical Services| Eldorado |a Division of MphasiS
5353 North 16th Street, Suite 400, Phoenix, Arizona 85016-3228
Tel (928) 892 5735 | www.eldoinc.com | www.mphasis.com |kstone@eldocomp.com
-----Original Message-----
From: Svensson Peter [mailto:peter.svensson@smhi.se]
Sent: Friday, January 19, 2018 4:04 AM
To: Joe Conway <mail@joeconway.com>; pgsql-performance@postgresql.org
Subject: SV: pgaudit and create postgis extension logs a lot inserts
A test to create postgis extension made 4 rsyslog processes run for several minutes with high cpu util, and when you have only 8 cpu:s this take lot of resources.
The create command also have to wait until all the log are written so there are great impact.
Log file got 16 GB big only for this.
We have several databases in the same server, some of them with postgis.
Those databases are maintained bye different people, and tell them to disable pgaudit every time they are doing something that can cause lot log will create a bad behaviour, especially when we cannot see in the logs that they have disabled pgaudit.
I think postgis extension is not the only extention that creates both tables, functions and insert data, but if there are a way to configure pgaudit so you get rid of the inserts maybe its a way to handle it.
/Peter
________________________________________
Från: Joe Conway [mail@joeconway.com]
Skickat: den 18 januari 2018 17:54
Till: Svensson Peter; pgsql-performance@postgresql.org
Ämne: Re: pgaudit and create postgis extension logs a lot inserts
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?
Joe
--
Crunchy Data - http://crunchydata.com
PostgreSQL Support for Secure Enterprises Consulting, Training, & Open Source Development
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: pgaudit and create postgis extension logs a lot inserts
2018-01-18 12:12 pgaudit and create postgis extension logs a lot inserts Svensson Peter <peter.svensson@smhi.se>
@ 2018-01-19 13:51 ` David Steele <david@pgmasters.net>
1 sibling, 0 replies; 7+ messages in thread
From: David Steele @ 2018-01-19 13:51 UTC (permalink / raw)
To: Svensson Peter <peter.svensson@smhi.se>; pgsql-performance
Hi Peter,
On 1/18/18 7:12 AM, Svensson Peter wrote:
>
> Also noticed that setting a session log to none (set
> pgaudit.log='none';) overrides parameter from postgresql.conf, but
> does not get logged, and then you can do whatever you want without any
> audit.
> I supposed this changing of audit session log parameter should be
> logged to file?
pgaudit is not intended to audit the superuser and only a superuser can
set pgaudit.log.
However, you can limit superuser access with the setuser extension:
https://github.com/pgaudit/set_user
Regards,
--
-David
david@pgmasters.net
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2018-01-20 13:05 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2018-01-18 12:12 pgaudit and create postgis extension logs a lot inserts Svensson Peter <peter.svensson@smhi.se>
2018-01-18 16:54 ` Joe Conway <mail@joeconway.com>
2018-01-19 11:05 ` Magnus Hagander <magnus@hagander.net>
2018-01-19 13:41 ` David Steele <david@pgmasters.net>
2018-01-20 13:05 ` Magnus Hagander <magnus@hagander.net>
2018-01-19 12:24 ` Karen Stone <kstone@eldocomp.com>
2018-01-19 13:51 ` David Steele <david@pgmasters.net>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox