Обсуждение: BUG #5008: Server Startup Problem - When server is configured for SSL
The following bug has been logged online: Bug reference: 5008 Logged by: Jalaj Negi Email address: jalajsinghnegi@gmail.com PostgreSQL version: 8.4.0 Operating system: Windows XP with SP3 Description: Server Startup Problem - When server is configured for SSL Details: I am running PostgreSQL version 8.4.0 on Windows XP machine with SP3. I installed using Win32 Installer Package and the server runs as a service and a service account was automatically created by the Win32 installer package during server installation. With default configuration the server was running fine and took minimal time to start up (2-3 seconds). But when I configured server for SSL and restarted server it took long time to start up (3-4 minutes) and displayed the following startup message. Startup Message ===================================================== The PostgreSQL Server 8.4 service is starting................................... .. The PostgreSQL Server 8.4 service could not be started. The service did not report an error. More help is available by typing NET HELPMSG 3534. The start command returned an error (2) Press <return> to continue... ===================================================== Exact Steps ===================================================== 1) Stopped the server. 2) Made following changes in configuration files for enabling SSL support. In pg_hba.conf I have only following one entry - hostssl all all 127.0.0.1/32 md5 clientcert=1 In postgresql.conf I made following changes - ssl = on ssl_ciphers = 'ALL:!ADH:!LOW:!EXP:!MD5:@STRENGTH' Added server.crt, server.key, root.crt, root.crl in servers data directory. 3) Started the server. ===================================================== Few Observations ===================================================== 1) When server starts with default configuration (without SSL configuration changes) following process are created and running in system - pg_ctl.exe (1 instance), postgres.exe (6 instances). 2) When server starts with SSL configuration changes following process are created and running in system - postgres.exe (6 instances). This time pg_ctl.exe process is absent. 3) Server starts properly when "clientcert=1" is removed from pg_hba.conf file. But if we want server to request client certificates we cant remove clientcert parameter from pg_hba.conf file. =====================================================
On Tue, Aug 25, 2009 at 08:11, Jalaj Negi<jalajsinghnegi@gmail.com> wrote: > > The following bug has been logged online: > > Bug reference: =A0 =A0 =A05008 > Logged by: =A0 =A0 =A0 =A0 =A0Jalaj Negi > Email address: =A0 =A0 =A0jalajsinghnegi@gmail.com > PostgreSQL version: 8.4.0 > Operating system: =A0 Windows XP with SP3 > Description: =A0 =A0 =A0 =A0Server Startup Problem - When server is confi= gured for > SSL > Details: > > I am running PostgreSQL version 8.4.0 on Windows XP machine with SP3. I > installed using Win32 Installer Package and the server runs as a service = and > a service account was automatically created by the Win32 installer package > during server installation. > > With default configuration the server was running fine and took minimal t= ime > to start up (2-3 seconds). But when I configured server for SSL and > restarted server it took long time to start up (3-4 minutes) and displayed > the following startup message. > > > Startup Message > =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D > The PostgreSQL Server 8.4 service is > starting................................... > .. > The PostgreSQL Server 8.4 service could not be started. > > The service did not report an error. > > More help is available by typing NET HELPMSG 3534. > > The start command returned an error (2) > > Press <return> to continue... > =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D > > > Exact Steps > =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D > 1) Stopped the server. > > 2) Made following changes in configuration files for enabling SSL support. > > In pg_hba.conf I have only following one entry - > hostssl all =A0all =A0127.0.0.1/32 =A0md5 clientcert=3D1 > > In postgresql.conf I made following changes - > ssl =3D on > ssl_ciphers =3D 'ALL:!ADH:!LOW:!EXP:!MD5:@STRENGTH' > > Added server.crt, server.key, root.crt, root.crl in servers data director= y. > > 3) Started the server. > =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D > > > Few Observations > =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D > 1) When server starts with default configuration (without SSL configurati= on > changes) following process are created and running in system - pg_ctl.exe= (1 > instance), postgres.exe (6 instances). > > 2) When server starts with SSL configuration changes following process are > created and running in system - postgres.exe (6 instances). This time > pg_ctl.exe process is absent. > > 3) Server starts properly when "clientcert=3D1" is removed from pg_hba.co= nf > file. But if we want server to request client certificates we cant remove > clientcert parameter from pg_hba.conf file. What's in the log? Check both the PostgreSQL logfile and the Windows eventl= og. --=20 Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/
On Wed, Aug 26, 2009 at 08:47, Jalaj Negi<jalajsinghnegi@gmail.com> wrote: > Hello, following is PostgreSQL log file. > I have no certificate to revoke so I havent placed any "root.crl" file in > servers data directory. > > Log File > =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D > 2009-08-26 11:58:32 ISTLOG:=A0 SSL certificate revocation list file "root= .crl" > not found, skipping: No such file or directory > 2009-08-26 11:58:32 ISTDETAIL:=A0 Certificates will not be checked against > revocation list. > 2009-08-26 11:58:32 ISTLOG:=A0 database system was shut down at 2009-08-26 > 11:56:56 IST > 2009-08-26 11:58:32 ISTFATAL:=A0 the database system is starting up > 2009-08-26 11:58:34 ISTLOG:=A0 database system is ready to accept connect= ions > 2009-08-26 11:58:34 ISTLOG:=A0 SSL certificate revocation list file "root= .crl" > not found, skipping: No such file or directory > 2009-08-26 11:58:34 ISTDETAIL:=A0 Certificates will not be checked against > revocation list. > 2009-08-26 11:58:34 ISTFATAL:=A0 the database system is starting up > 2009-08-26 11:58:39 ISTLOG:=A0 autovacuum launcher started > 2009-08-26 11:58:39 ISTLOG:=A0 SSL certificate revocation list file "root= .crl" > not found, skipping: No such file or directory > 2009-08-26 11:58:39 ISTDETAIL:=A0 Certificates will not be checked against > revocation list. > 2009-08-26 11:58:39 ISTFATAL:=A0 connection requires a valid client > certificate <snip> I think this indicates that pg_ctl is trying to connect to the database just to see if it's running, but you have set it to require SSL certificate on connections from localhost. Could that be so? If so, try setting the requirement for certificates only on non-localhost addresses and see if it starts up properly in that case. --=20 Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/
Magnus Hagander <magnus@hagander.net> writes:
> I think this indicates that pg_ctl is trying to connect to the
> database just to see if it's running, but you have set it to require
> SSL certificate on connections from localhost. Could that be so? If
> so, try setting the requirement for certificates only on non-localhost
> addresses and see if it starts up properly in that case.
Hmm, seems like we should modify pg_ctl's probe code to set
sslmode = disable. Letting that default just provides an additional
way to fail ...
regards, tom lane
On Wed, Aug 26, 2009 at 15:30, Tom Lane<tgl@sss.pgh.pa.us> wrote: > Magnus Hagander <magnus@hagander.net> writes: >> I think this indicates that pg_ctl is trying to connect to the >> database just to see if it's running, but you have set it to require >> SSL certificate on connections from localhost. Could that be so? If >> so, try setting the requirement for certificates only on non-localhost >> addresses and see if it starts up properly in that case. > > Hmm, seems like we should modify pg_ctl's probe code to set > sslmode =3D disable. =A0Letting that default just provides an additional > way to fail ... Um. If we modify it, it should be to sslmode=3Dallow. Not "disable". (*ahem* to what would be a reasonable defualt for libpq in general :P) But that will still fail if the user has set it up to require a client certificate. --=20 Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/
Magnus Hagander <magnus@hagander.net> writes:
> But that will still fail if the user has set it up to require a client
> certificate.
But not till it gets to the pg_hba checks. We might need to have some
variant on PQrequiresPassword to detect that failure type, but we'll
already know what we need to.
This still points up the value of adding an actual "ping" interface to
the protocol, though.
regards, tom lane
On Wed, Aug 26, 2009 at 15:57, Tom Lane<tgl@sss.pgh.pa.us> wrote: > Magnus Hagander <magnus@hagander.net> writes: >> But that will still fail if the user has set it up to require a client >> certificate. > > But not till it gets to the pg_hba checks. =A0We might need to have some How would that be different from what we have now? sslmode=3Dprefer will still allow both ssl and non-ssl connection. It won't kick you out until you reach the hba processing, will it? > variant on PQrequiresPassword to detect that failure type, but we'll > already know what we need to. > > This still points up the value of adding an actual "ping" interface to > the protocol, though. Agreed. --=20 Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/
Magnus Hagander <magnus@hagander.net> writes:
> On Wed, Aug 26, 2009 at 15:57, Tom Lane<tgl@sss.pgh.pa.us> wrote:
>> Magnus Hagander <magnus@hagander.net> writes:
>>> But that will still fail if the user has set it up to require a client
>>> certificate.
>>
>> But not till it gets to the pg_hba checks. We might need to have some
> How would that be different from what we have now? sslmode=prefer will
> still allow both ssl and non-ssl connection. It won't kick you out
> until you reach the hba processing, will it?
Hm, will it retry if the ssl setup step fails? If so it'd be all right,
but it's still a waste of cycles ...
regards, tom lane
On Wed, Aug 26, 2009 at 22:47, Tom Lane<tgl@sss.pgh.pa.us> wrote: > Magnus Hagander <magnus@hagander.net> writes: >> On Wed, Aug 26, 2009 at 15:57, Tom Lane<tgl@sss.pgh.pa.us> wrote: >>> Magnus Hagander <magnus@hagander.net> writes: >>>> But that will still fail if the user has set it up to require a client >>>> certificate. >>> >>> But not till it gets to the pg_hba checks. =A0We might need to have some > >> How would that be different from what we have now? sslmode=3Dprefer will >> still allow both ssl and non-ssl connection. It won't kick you out >> until you reach the hba processing, will it? > > Hm, will it retry if the ssl setup step fails? =A0If so it'd be all right, > but it's still a waste of cycles ... Yes, that's the difference between prefer and require. I think the main issue is that test_postmaster_connection() only accepts two cases - successful login and password prompt. It would have similar issues with say an ident mismatch, or loopback connections configured for kerberos. --=20 Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/
Hi, I tried by setting the requirement for certificates only on non-localhost addresses. It works perfectly fine in that case. Whats the solution in case of requesting client certificates on localhost connection? One temporary solution I found for above question is to set PGSSLCERT, PGSSLKEY, PGSSLROOTCERT, PGSSLCRL as system environment variables and let them point to valid client certificate, key, root certificate and crl. Then I rebooted my windows machine and database server started perfectly fine. Is this solution fine or some fix is needed in code? regards, jalaj negi ======================================================== Magnus Hagander to me, pgsql-bugs show details Aug 26 (1 day ago) I think this indicates that pg_ctl is trying to connect to the database just to see if it's running, but you have set it to require SSL certificate on connections from localhost. Could that be so? If so, try setting the requirement for certificates only on non-localhost addresses and see if it starts up properly in that case. ======================================================== On Thu, Aug 27, 2009 at 12:30 PM, Magnus Hagander <magnus@hagander.net>wrote: > On Wed, Aug 26, 2009 at 22:47, Tom Lane<tgl@sss.pgh.pa.us> wrote: > > Magnus Hagander <magnus@hagander.net> writes: > >> On Wed, Aug 26, 2009 at 15:57, Tom Lane<tgl@sss.pgh.pa.us> wrote: > >>> Magnus Hagander <magnus@hagander.net> writes: > >>>> But that will still fail if the user has set it up to require a client > >>>> certificate. > >>> > >>> But not till it gets to the pg_hba checks. We might need to have some > > > >> How would that be different from what we have now? sslmode=prefer will > >> still allow both ssl and non-ssl connection. It won't kick you out > >> until you reach the hba processing, will it? > > > > Hm, will it retry if the ssl setup step fails? If so it'd be all right, > > but it's still a waste of cycles ... > > Yes, that's the difference between prefer and require. > > I think the main issue is that test_postmaster_connection() only > accepts two cases - successful login and password prompt. It would > have similar issues with say an ident mismatch, or loopback > connections configured for kerberos. > > > -- > Magnus Hagander > Me: http://www.hagander.net/ > Work: http://www.redpill-linpro.com/ >
2009/8/27 Jalaj Negi <jalajsinghnegi@gmail.com>: > Hi, > I tried by setting the requirement for certificates only on non-localhost addresses. It works perfectly fine in that case. > Whats the solution in case of requesting client certificates on localhost connection? > > One temporary solution I found for above question is to set PGSSLCERT, PGSSLKEY, PGSSLROOTCERT, PGSSLCRL > as system environment variables and let them point to valid client certificate, key, root certificate and crl. Then I rebooted > my windows machine and database server started perfectly fine. > Is this solution fine or some fix is needed in code? I think that qualifies as a workaround, but we need to find a proper solution. If that takes an API change (the discussions about a "ping" function), that's not going to be until 8.5 though. -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/