Re: [GENERAL] Configuration of pgaudit settings in postgreSQL.conf causes postgreSQL to fail to start

[Troy Hardin]

Jeff/Arthur,


Thanks.  I am testing on version 9.5.7 with pgaudit 1.0.6.  The production 
system is currently 9.5.7 but will receive updates to 9.5.9 and beyond as 
updates are made available.  I will paste in here info from the postgreSQL logs 
and then a couple entries from Event Viewer.  Sadly, those event viewer entries 
show little.


PostgreSQL logs showing startup, after which I ran SET command (SET pgaudit.log 
= 'READ, WRITE, DDL, ROLE';) and tested with a couple role changes (note that 
those entries in the log show pgaudit functioning).  I then uncommented the 
pgaudit.log setting in postgresql.conf and restarted the service.  Logs show 
the shutdown.


2017-09-14 14:59:11.148 UTC 59ba993f.3f0 2017-09-14 14:59:11 UTC 0 1008 
:LOG: database system was shut down at 2017-09-14 14:56:28 UTC

2017-09-14 14:59:11.192 UTC 59ba993f.3f0 2017-09-14 14:59:11 UTC 0 1008 
:LOG: MultiXact member wraparound protections are now enabled

2017-09-14 14:59:11.199 UTC 59ba993e.a64 2017-09-14 14:59:10 UTC 0 2660 
:LOG: database system is ready to accept connections

2017-09-14 14:59:11.376 UTC 59ba993e.a64 2017-09-14 14:59:10 UTC 0 2660 
:LOG: setsockopt(TCP_KEEPCNT) not supported

2017-09-14 14:59:11.857 UTC 59ba993f.da4 2017-09-14 14:59:11 UTC 0 3492 
:LOG: autovacuum launcher started

2017-09-14 14:59:12.218 UTC [unknown] 59ba9940.f1c 2017-09-14 14:59:12 UTC 
[unknown] 0 3868 ::1(49306) [unknown]:LOG: connection received: host=::1 
port=49306

2017-09-14 15:00:23.335 UTC 59ba993e.a64 2017-09-14 14:59:10 UTC 0 2660 
:LOG: setsockopt(TCP_KEEPCNT) not supported

2017-09-14 15:00:23.498 UTC [unknown] 59ba9987.b2c 2017-09-14 15:00:23 UTC 
[unknown] 0 2860 ::1(49307) [unknown]:LOG: connection received: host=::1 
port=49307

2017-09-14 15:00:32.668 UTC 59ba993e.a64 2017-09-14 14:59:10 UTC 0 2660 
:LOG: setsockopt(TCP_KEEPCNT) not supported

2017-09-14 15:00:32.827 UTC [unknown] 59ba9990.8b0 2017-09-14 15:00:32 UTC 
[unknown] 0 2224 ::1(49308) [unknown]:LOG: connection received: host=::1 
port=49308

2017-09-14 15:00:32.850 UTC postgres 59ba9990.8b0 2017-09-14 15:00:32 UTC 
postgres 0 2224 ::1(49308) [unknown]:LOG: connection authorized: 
user=postgres database=postgres

2017-09-14 15:05:37.975 UTC postgres 59ba9990.8b0 2017-09-14 15:00:32 UTC 
postgres 0 2224 ::1(49308) psql:LOG: AUDIT: SESSION,1,1,ROLE,CREATE 
ROLE,,,CREATE ROLE test;,

2017-09-14 15:07:31.922 UTC postgres 59ba9990.8b0 2017-09-14 15:00:32 UTC 
postgres 0 2224 ::1(49308) psql:LOG: AUDIT: SESSION,2,1,ROLE,DROP 
ROLE,,,DROP ROLE test;,

2017-09-14 15:08:16.821 UTC 59ba993e.a64 2017-09-14 14:59:10 UTC 0 2660 
:LOG: received fast shutdown request

2017-09-14 15:08:16.822 UTC 59ba993e.a64 2017-09-14 14:59:10 UTC 0 2660 
:LOG: aborting any active transactions

2017-09-14 15:08:16.827 UTC postgres 59ba9990.8b0 2017-09-14 15:00:32 UTC 
postgres 57P01 2224 ::1(49308) psql:FATAL: terminating connection due to 
administrator command

2017-09-14 15:08:16.828 UTC postgres 59ba9990.8b0 2017-09-14 15:00:32 UTC 
postgres 0 2224 ::1(49308) psql:LOG: disconnection: session time: 
0:07:44.000 user=postgres database=postgres host=::1 port=49308

2017-09-14 15:08:16.829 UTC 59ba993f.da4 2017-09-14 14:59:11 UTC 0 3492 
:LOG: autovacuum launcher shutting down

2017-09-14 15:08:16.833 UTC 59ba993f.bec 2017-09-14 14:59:11 UTC 0 3052 
:LOG: shutting down

2017-09-14 15:08:17.141 UTC 59ba993f.bec 2017-09-14 14:59:11 UTC 0 3052 
:LOG: database system is shut down



The following entries from event viewer show the service attempting to restart 
and ultimately failing.  No further logs/entries occur in the pg_log.:


- http://schemas.microsoft.com/win/2004/08/events/event;>
- 
  
  0
  4
  0
  0x80
  
  10900
  Application
  JWIN2012-11_1_2
  
  
- 
  Waiting for server startup...
  
  


- http://schemas.microsoft.com/win/2004/08/events/event;>
- 
  
  0
  2
  0
  0x80
  
  10901
  Application
  JWIN2012-11_1_2
  
  
- 
  Timed out waiting for server startup
  
  



v/r

Troy R. Hardin


From: Jeff Janes 
Sent: Thursday, September 14, 2017 10:33:02 AM
To: Artur Zakirov
Cc: pgsql-general@postgresql.org; Troy Hardin
Subject: Re: [GENERAL] Configuration of pgaudit settings in postgreSQL.conf 
causes postgreSQL to fail to start

On Sep 14, 2017 7:07 AM, "Arthur Zakirov" 
> wrote:
On Wed, Sep 13, 2017 at 02:42:18PM +, Troy Hardin wrote:
> Putting either of these two lines in the .conf file cause it to fail to start.

Can you show error messages from logs?

And the version.

Re: [GENERAL] Configuration of pgaudit settings in postgreSQL.conf causes postgreSQL to fail to start

[Jeff Janes]

On Sep 14, 2017 7:07 AM, "Arthur Zakirov"  wrote:

On Wed, Sep 13, 2017 at 02:42:18PM +, Troy Hardin wrote:
> Putting either of these two lines in the .conf file cause it to fail to
start.

Can you show error messages from logs?


And the version.

Re: [GENERAL] Configuration of pgaudit settings in postgreSQL.conf causes postgreSQL to fail to start

[Arthur Zakirov]

On Wed, Sep 13, 2017 at 02:42:18PM +, Troy Hardin wrote:
> Putting either of these two lines in the .conf file cause it to fail to start.

Can you show error messages from logs?

-- 
Arthur Zakirov
Postgres Professional: http://www.postgrespro.com
Russian Postgres Company


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Configuration of pgaudit settings in postgreSQL.conf causes postgreSQL to fail to start

[Troy Hardin]

I support an existing product that utilizes postgreSQL in a Windows 
environment.  Recently we've been asked to make a series of security/auditing 
changes to the product that require pgaudit.  We built pgaudit.dll and have 
made the majority of the configuration changes and those seem to be working as 
designed.  The pgaudit extension does what it is being required to do, but we 
are running into issues with configuring some settings globally in the 
postgresql.conf.


The two lines we need to run in the .conf file that are causing postgresql to 
fail to start are, for example:


pgaudit.log = 'read, write, ddl, role'

pgaudit.log_level = 'log'


Putting either of these two lines in the .conf file cause it to fail to start.  
However, after starting postgreSQL, I can go to command line and run the SQL to 
set these and they run fine.  After running them, pgaudit performs as designed 
and configured so I believe we've compiled pgaudit.dll correctly.  At any rate, 
I'm at wits end right now and need to either fix the above or find someone with 
more db experience to assist in creating triggers that would run the SQL SET 
commands after startup.


Are there known reasons why I might be having this issue?  Anyone have 
experience getting past something similar?


v/r

Troy R. Hardin