Re: SSL problems

Hi Team,

I have problems to setup SSL for PostgreSQL server. I did all the steps
which described in the documentation (17.8. Secure TCP/IP Connections
with SSL), but when I try to start the PostgreSQL server the pg_ctl gave
me: "could not start server". And nothing in the logs (I enabled all of
them). I googled around but did not find much.

My spec:

FreeBSD 7.0-RELEASE-p3 amd64

PostgreSQL 8.3.3 (installed from ports):

WITH_NLS=true
WITHOUT_PAM=true
WITHOUT_LDAP=true
WITHOUT_MIT_KRB5=true
WITHOUT_HEIMDAL_KRB5=true
WITHOUT_OPTIMIZED_CFLAGS=true
WITH_XML=true
WITHOUT_TZDATA=true
WITHOUT_DEBUG=true
WITH_ICU=true
WITH_INTDATE=true

Please help.

Andriy

Andriy Bakay <andriy(at)irbisnet(dot)com> writes:
> I have problems to setup SSL for PostgreSQL server. I did all the steps
> which described in the documentation (17.8. Secure TCP/IP Connections
> with SSL), but when I try to start the PostgreSQL server the pg_ctl gave
> me: "could not start server". And nothing in the logs (I enabled all of
> them). I googled around but did not find much.

There is *no* exit path from the PG server that does not spit out an
error message someplace. Re-examine the logging setup. I don't know
how FreeBSD's package sets it up exactly, but there have been packages
in the past that just sent the postmaster's stderr to /dev/null :-(.
See here for some documentation about the settings that determine where
messages go:
http://www.postgresql.org/docs/8.3/static/runtime-config-logging.html#RUNTIME-CONFIG-LOGGING-WHERE

regards, tom lane

Hello Andriy,

the reply-to settings are a bit uncomfortable here. Your mail went only
to me. But I'm not part of the developer or support team. It's strange
that pg_ctl doesn't say anything else. Is there any system sniffer on
FreeBSD like Process Monitor on Windows? I can only say that the docs
worked for me (removed the password as described) on Ubuntu and Windows.
I got complaints because of the rights on the certificates first. Does
the server really start if SSL is deactivated in postgresql.conf again?

Good luck,

Peter

> Yes of cause I compiled with OpenSSL support (FreeBSD port has this
> option enabled by default). And I have all certificates with proper CA
> signature, rest of applications (Postfix, Apache, etc.) work with this
> certificates very well.
>
> And to make sure I ran the following command 'pg_config':
>
> $ pg_config
> BINDIR = /usr/local/bin
> DOCDIR = /usr/local/share/doc/postgresql
> INCLUDEDIR = /usr/local/include
> PKGINCLUDEDIR = /usr/local/include/postgresql
> INCLUDEDIR-SERVER = /usr/local/include/postgresql/server
> LIBDIR = /usr/local/lib
> PKGLIBDIR = /usr/local/lib/postgresql
> LOCALEDIR = /usr/local/share/locale
> MANDIR = /usr/local/man
> SHAREDIR = /usr/local/share/postgresql
> SYSCONFDIR = /usr/local/etc/postgresql
> PGXS = /usr/local/lib/postgresql/pgxs/src/makefiles/pgxs.mk
> CONFIGURE = '--with-libraries=/usr/local/lib'
> '--with-includes=/usr/local/include' '--enable-thread-safety'
> '--with-docdir=/usr/local/share/doc/postgresql' '--with-openssl'
> '--with-system-tzdata=/usr/share/zoneinfo' '--enable-integer-datetimes'
> '--enable-nls' '--prefix=/usr/local' '--mandir=/usr/local/man'
> '--infodir=/usr/local/info/' '--build=amd64-portbld-freebsd7.0' 'CC=cc'
> 'CFLAGS=-O2 -fno-strict-aliasing -pipe ' 'LDFLAGS= -pthread
> -rpath=/usr/local/lib' 'build_alias=amd64-portbld-freebsd7.0'
> CC = cc
> CPPFLAGS = -I/usr/local/include
> CFLAGS = -O2 -fno-strict-aliasing -pipe -Wall -Wmissing-prototypes
> -Wpointer-arith -Winline -Wdeclaration-after-statement -Wendif-labels
> -fno-strict-aliasing -fwrapv
> CFLAGS_SL = -fPIC -DPIC
> LDFLAGS = -pthread -rpath=/usr/local/lib -L/usr/local/lib
> -Wl,-R'/usr/local/lib'
> LDFLAGS_SL =
> LIBS = -lpgport -lintl -lssl -lcrypto -lz -lreadline -lcrypt -lm
> VERSION = PostgreSQL 8.3.3
>
> It should be something else.
>
> Andriy
>
> Jan-Peter(dot)Seifert(at)gmx(dot)de wrote:
>> Hi,
>>
>>> Datum: Wed, 03 Sep 2008 08:43:29 -0400
>>> Von: Andriy Bakay <andriy(at)irbisnet(dot)com>
>>> An: pgsql-admin(at)postgresql(dot)org, pgsql-ru-general(at)postgresql(dot)org
>>> Betreff: [ADMIN] SSL problems
>>
>>> Hi Team,
>>>
>>> I have problems to setup SSL for PostgreSQL server. I did all the steps
>>> which described in the documentation (17.8. Secure TCP/IP Connections
>>> with SSL), but when I try to start the PostgreSQL server the pg_ctl gave
>>> me: "could not start server". And nothing in the logs (I enabled all of
>>> them). I googled around but did not find much.
>>>
>>> My spec:
>>>
>>> FreeBSD 7.0-RELEASE-p3 amd64
>>>
>>> PostgreSQL 8.3.3 (installed from ports):
>>>
>>> WITH_NLS=true
>>> WITHOUT_PAM=true
>>> WITHOUT_LDAP=true
>>> WITHOUT_MIT_KRB5=true
>>> WITHOUT_HEIMDAL_KRB5=true
>>> WITHOUT_OPTIMIZED_CFLAGS=true
>>> WITH_XML=true
>>> WITHOUT_TZDATA=true
>>> WITHOUT_DEBUG=true
>>> WITH_ICU=true
>>> WITH_INTDATE=true
>>
>> obviously configure hasn't been run with the option "--with-openssl"
>> before compiling the binaries.
>> With the PostgreSQL command pg_config you get the configure options
>> that have been used for making the binaries - so you can make sure. It
>> seems that you must recompile from sources. Are you sure you have
>> openssl itself installed on your system? Maybe you have to generate a
>> certificate as well. It has been a while since I had installed
>> SSL-support successfully on windows and Linux.
>>
>> Peter
>>
>

After I disable SSL option in postgresql.conf the server is starting
successfully.

Please, advise.

Jan-Peter Seifert wrote:
> Hello Andriy,
>
> the reply-to settings are a bit uncomfortable here. Your mail went only
> to me. But I'm not part of the developer or support team. It's strange
> that pg_ctl doesn't say anything else. Is there any system sniffer on
> FreeBSD like Process Monitor on Windows? I can only say that the docs
> worked for me (removed the password as described) on Ubuntu and Windows.
> I got complaints because of the rights on the certificates first. Does
> the server really start if SSL is deactivated in postgresql.conf again?
>
> Good luck,
>
> Peter
>
>> Yes of cause I compiled with OpenSSL support (FreeBSD port has this
>> option enabled by default). And I have all certificates with proper CA
>> signature, rest of applications (Postfix, Apache, etc.) work with this
>> certificates very well.
>>
>> And to make sure I ran the following command 'pg_config':
>>
>> $ pg_config
>> BINDIR = /usr/local/bin
>> DOCDIR = /usr/local/share/doc/postgresql
>> INCLUDEDIR = /usr/local/include
>> PKGINCLUDEDIR = /usr/local/include/postgresql
>> INCLUDEDIR-SERVER = /usr/local/include/postgresql/server
>> LIBDIR = /usr/local/lib
>> PKGLIBDIR = /usr/local/lib/postgresql
>> LOCALEDIR = /usr/local/share/locale
>> MANDIR = /usr/local/man
>> SHAREDIR = /usr/local/share/postgresql
>> SYSCONFDIR = /usr/local/etc/postgresql
>> PGXS = /usr/local/lib/postgresql/pgxs/src/makefiles/pgxs.mk
>> CONFIGURE = '--with-libraries=/usr/local/lib'
>> '--with-includes=/usr/local/include' '--enable-thread-safety'
>> '--with-docdir=/usr/local/share/doc/postgresql' '--with-openssl'
>> '--with-system-tzdata=/usr/share/zoneinfo' '--enable-integer-datetimes'
>> '--enable-nls' '--prefix=/usr/local' '--mandir=/usr/local/man'
>> '--infodir=/usr/local/info/' '--build=amd64-portbld-freebsd7.0' 'CC=cc'
>> 'CFLAGS=-O2 -fno-strict-aliasing -pipe ' 'LDFLAGS= -pthread
>> -rpath=/usr/local/lib' 'build_alias=amd64-portbld-freebsd7.0'
>> CC = cc
>> CPPFLAGS = -I/usr/local/include
>> CFLAGS = -O2 -fno-strict-aliasing -pipe -Wall -Wmissing-prototypes
>> -Wpointer-arith -Winline -Wdeclaration-after-statement -Wendif-labels
>> -fno-strict-aliasing -fwrapv
>> CFLAGS_SL = -fPIC -DPIC
>> LDFLAGS = -pthread -rpath=/usr/local/lib -L/usr/local/lib
>> -Wl,-R'/usr/local/lib'
>> LDFLAGS_SL =
>> LIBS = -lpgport -lintl -lssl -lcrypto -lz -lreadline -lcrypt -lm
>> VERSION = PostgreSQL 8.3.3
>>
>> It should be something else.
>>
>> Andriy
>>
>> Jan-Peter(dot)Seifert(at)gmx(dot)de wrote:
>>> Hi,
>>>
>>>> Datum: Wed, 03 Sep 2008 08:43:29 -0400
>>>> Von: Andriy Bakay <andriy(at)irbisnet(dot)com>
>>>> An: pgsql-admin(at)postgresql(dot)org, pgsql-ru-general(at)postgresql(dot)org
>>>> Betreff: [ADMIN] SSL problems
>>>> Hi Team,
>>>>
>>>> I have problems to setup SSL for PostgreSQL server. I did all the steps
>>>> which described in the documentation (17.8. Secure TCP/IP Connections
>>>> with SSL), but when I try to start the PostgreSQL server the pg_ctl gave
>>>> me: "could not start server". And nothing in the logs (I enabled all of
>>>> them). I googled around but did not find much.
>>>>
>>>> My spec:
>>>>
>>>> FreeBSD 7.0-RELEASE-p3 amd64
>>>>
>>>> PostgreSQL 8.3.3 (installed from ports):
>>>>
>>>> WITH_NLS=true
>>>> WITHOUT_PAM=true
>>>> WITHOUT_LDAP=true
>>>> WITHOUT_MIT_KRB5=true
>>>> WITHOUT_HEIMDAL_KRB5=true
>>>> WITHOUT_OPTIMIZED_CFLAGS=true
>>>> WITH_XML=true
>>>> WITHOUT_TZDATA=true
>>>> WITHOUT_DEBUG=true
>>>> WITH_ICU=true
>>>> WITH_INTDATE=true
>>> obviously configure hasn't been run with the option "--with-openssl"
>>> before compiling the binaries.
>>> With the PostgreSQL command pg_config you get the configure options
>>> that have been used for making the binaries - so you can make sure. It
>>> seems that you must recompile from sources. Are you sure you have
>>> openssl itself installed on your system? Maybe you have to generate a
>>> certificate as well. It has been a while since I had installed
>>> SSL-support successfully on windows and Linux.
>>>
>>> Peter
>>>
>

> Datum: Thu, 04 Sep 2008 22:01:51 -0400
> Von: Andriy Bakay <andriy(at)irbisnet(dot)com>
> An: Jan-Peter Seifert <Jan-Peter(dot)Seifert(at)gmx(dot)de>
> CC: pgsql-admin(at)postgresql(dot)org, pgsql-hackers(at)postgresql(dot)org
> Betreff: Re: [ADMIN] SSL problems

> After I disable SSL option in postgresql.conf the server is starting
> successfully.

Okay - this was to make sure, that SSL actually really IS the problem. As Tom Lane already mentioned - get your installation to talk to you. pg_ctl should always throw an explaining error message if the server can't be started. In my case with SSL often incorrect privileges on files and/or missing files.
I guess you already have "log_destination = 'stderr'" and "logging_collector = on" enabled in your postgresql.conf ...
If I remember correctly sometimes non-matching versions of PostgreSQL and OpenSSL might be a reason too.

Peter

>
> Please, advise.
>
> Jan-Peter Seifert wrote:
> > Hello Andriy,
> >
> > the reply-to settings are a bit uncomfortable here. Your mail went only
> > to me. But I'm not part of the developer or support team. It's strange
> > that pg_ctl doesn't say anything else. Is there any system sniffer on
> > FreeBSD like Process Monitor on Windows? I can only say that the docs
> > worked for me (removed the password as described) on Ubuntu and Windows.
> > I got complaints because of the rights on the certificates first. Does
> > the server really start if SSL is deactivated in postgresql.conf again?
> >
> > Good luck,
> >
> > Peter
> >
> >> Yes of cause I compiled with OpenSSL support (FreeBSD port has this
> >> option enabled by default). And I have all certificates with proper CA
> >> signature, rest of applications (Postfix, Apache, etc.) work with this
> >> certificates very well.
> >>
> >> And to make sure I ran the following command 'pg_config':
> >>
> >> $ pg_config
> >> BINDIR = /usr/local/bin
> >> DOCDIR = /usr/local/share/doc/postgresql
> >> INCLUDEDIR = /usr/local/include
> >> PKGINCLUDEDIR = /usr/local/include/postgresql
> >> INCLUDEDIR-SERVER = /usr/local/include/postgresql/server
> >> LIBDIR = /usr/local/lib
> >> PKGLIBDIR = /usr/local/lib/postgresql
> >> LOCALEDIR = /usr/local/share/locale
> >> MANDIR = /usr/local/man
> >> SHAREDIR = /usr/local/share/postgresql
> >> SYSCONFDIR = /usr/local/etc/postgresql
> >> PGXS = /usr/local/lib/postgresql/pgxs/src/makefiles/pgxs.mk
> >> CONFIGURE = '--with-libraries=/usr/local/lib'
> >> '--with-includes=/usr/local/include' '--enable-thread-safety'
> >> '--with-docdir=/usr/local/share/doc/postgresql' '--with-openssl'
> >> '--with-system-tzdata=/usr/share/zoneinfo' '--enable-integer-datetimes'
> >> '--enable-nls' '--prefix=/usr/local' '--mandir=/usr/local/man'
> >> '--infodir=/usr/local/info/' '--build=amd64-portbld-freebsd7.0' 'CC=cc'
> >> 'CFLAGS=-O2 -fno-strict-aliasing -pipe ' 'LDFLAGS= -pthread
> >> -rpath=/usr/local/lib' 'build_alias=amd64-portbld-freebsd7.0'
> >> CC = cc
> >> CPPFLAGS = -I/usr/local/include
> >> CFLAGS = -O2 -fno-strict-aliasing -pipe -Wall -Wmissing-prototypes
> >> -Wpointer-arith -Winline -Wdeclaration-after-statement -Wendif-labels
> >> -fno-strict-aliasing -fwrapv
> >> CFLAGS_SL = -fPIC -DPIC
> >> LDFLAGS = -pthread -rpath=/usr/local/lib -L/usr/local/lib
> >> -Wl,-R'/usr/local/lib'
> >> LDFLAGS_SL =
> >> LIBS = -lpgport -lintl -lssl -lcrypto -lz -lreadline -lcrypt -lm
> >> VERSION = PostgreSQL 8.3.3
> >>
> >> It should be something else.
> >>
> >> Andriy
> >>
> >> Jan-Peter(dot)Seifert(at)gmx(dot)de wrote:
> >>> Hi,
> >>>
> >>>> Datum: Wed, 03 Sep 2008 08:43:29 -0400
> >>>> Von: Andriy Bakay <andriy(at)irbisnet(dot)com>
> >>>> An: pgsql-admin(at)postgresql(dot)org, pgsql-ru-general(at)postgresql(dot)org
> >>>> Betreff: [ADMIN] SSL problems
> >>>> Hi Team,
> >>>>
> >>>> I have problems to setup SSL for PostgreSQL server. I did all the
> steps
> >>>> which described in the documentation (17.8. Secure TCP/IP Connections
> >>>> with SSL), but when I try to start the PostgreSQL server the pg_ctl
> gave
> >>>> me: "could not start server". And nothing in the logs (I enabled all
> of
> >>>> them). I googled around but did not find much.
> >>>>
> >>>> My spec:
> >>>>
> >>>> FreeBSD 7.0-RELEASE-p3 amd64
> >>>>
> >>>> PostgreSQL 8.3.3 (installed from ports):
> >>>>
> >>>> WITH_NLS=true
> >>>> WITHOUT_PAM=true
> >>>> WITHOUT_LDAP=true
> >>>> WITHOUT_MIT_KRB5=true
> >>>> WITHOUT_HEIMDAL_KRB5=true
> >>>> WITHOUT_OPTIMIZED_CFLAGS=true
> >>>> WITH_XML=true
> >>>> WITHOUT_TZDATA=true
> >>>> WITHOUT_DEBUG=true
> >>>> WITH_ICU=true
> >>>> WITH_INTDATE=true
> >>> obviously configure hasn't been run with the option "--with-openssl"
> >>> before compiling the binaries.
> >>> With the PostgreSQL command pg_config you get the configure options
> >>> that have been used for making the binaries - so you can make sure. It
> >>> seems that you must recompile from sources. Are you sure you have
> >>> openssl itself installed on your system? Maybe you have to generate a
> >>> certificate as well. It has been a while since I had installed
> >>> SSL-support successfully on windows and Linux.
> >>>
> >>> Peter
> >>>
> >

--
Der GMX SmartSurfer hilft bis zu 70% Ihrer Onlinekosten zu sparen!
Ideal für Modem und ISDN: http://www.gmx.net/de/go/smartsurfer