BUG #5008: Server Startup Problem - When server is configured for SSL

On Tue, Aug 25, 2009 at 08:11, Jalaj Negi<jalajsinghnegi@gmail.com> wrote:

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.

What's in the log? Check both the PostgreSQL logfile and the Windows eventlog.

--
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
============================================================================================
2009-08-26 11:58:32 ISTLOG:  SSL certificate revocation list file "root.crl"
not found, skipping: No such file or directory
2009-08-26 11:58:32 ISTDETAIL:  Certificates will not be checked against
revocation list.
2009-08-26 11:58:32 ISTLOG:  database system was shut down at 2009-08-26
11:56:56 IST
2009-08-26 11:58:32 ISTFATAL:  the database system is starting up
2009-08-26 11:58:34 ISTLOG:  database system is ready to accept connections
2009-08-26 11:58:34 ISTLOG:  SSL certificate revocation list file "root.crl"
not found, skipping: No such file or directory
2009-08-26 11:58:34 ISTDETAIL:  Certificates will not be checked against
revocation list.
2009-08-26 11:58:34 ISTFATAL:  the database system is starting up
2009-08-26 11:58:39 ISTLOG:  autovacuum launcher started
2009-08-26 11:58:39 ISTLOG:  SSL certificate revocation list file "root.crl"
not found, skipping: No such file or directory
2009-08-26 11:58:39 ISTDETAIL:  Certificates will not be checked against
revocation list.
2009-08-26 11:58:39 ISTFATAL:  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.

--
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 = disable.  Letting that default just provides an additional
way to fail ...

Um. If we modify it, it should be to sslmode=allow. 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.

--
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.  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?

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.

--
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.  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/

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:

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/