Re: Beginning SSL Questions - Mailing list pgsql-admin

Hi All-

Hopefully someone here has some OpenSSL expertise and can help me with a
problem I'm running into...

My main goal is to build Postgres with ssl enabled - building on Windows
using MinGW; to do that I need a server.crt and server.key file generated
from OpenSSL.  So here's what I've done over the past few days:

Downloaded and installed:
 - Mingw
 - msys
 - zlib-1.2.3  - installed under C:\msys\1.0 directory
 - postgresql-8.1.4 source - installed and compiled under C:\msys\1.0
directory (using --with-openssl option and "ssl=on" in postgresql.conf)
 - openssl-0.9.8c source - installed and compiled under C:\msys\1.0
directory

I've been able to successfully create the 'template0' and 'template1'
prototype db's in postgres, but cannot start postmaster without the key and
certificate files:
  $ postmaster -D /usr/local/pgsql/data/
2006-09-20 15:16:38 FATAL:could not load server certificate file
"server.crt": No such file or directory

So, I changed to the openssl-0.9.8c directory to build my keyfile and
certificate and am having no luck and could really use someone's expertise!!
When I enter the command line option to generate the keyfile, it says it's
generating the file, but it just hangs there....  I've left it running, but
it doesn't complete, it only outputs the two lines with '.......++++++' and
stops:

   $ openssl genrsa -des3 -out server.key 2048
   Loading 'screen' into random state - done
   Generating RSA private key, 2048 bit long modulus
   ........................................+++
   ......+++

In the 'C:\msys\1.0\openssl-0.9.8c' directory, it creates a 'server.key'
file, but it is empty (0 KB).  The only way I can get it to exit out of this
is to use ctl+c.

PLEASE HELP!!  I've been working on this all week with no luck and could
really use some help!!  I've tried uninstalling and re-installing and
re-compiling OpenSSL (in different locations) with the same results.  When I
compile it, it appears to compile without any problems...

Thanks much,
-Jeanna

----- Original Message -----
From: "Michael Fuhr" <mike@fuhr.org>
To: "Jeanna Geier" <jgeier@apt-cafm.com>
Cc: <pgsql-admin@postgresql.org>
Sent: Thursday, September 14, 2006 10:01 AM
Subject: Re: [ADMIN] Beginning SSL Questions


> On Thu, Sep 14, 2006 at 09:17:00AM -0500, Jeanna Geier wrote:
>> - In the docs, it says that when using SSL in Postgres "This requires
>> that OpenSSL is installed on both client and server systems and
>> that support in PostgreSQL is enabled at build time" - is this
>> correct?
>
> PostgreSQL must have been built with the --with-openssl configure
> option and the server needs "ssl = on" in postgresql.conf.
>
>> Or can we use the certificates and keystore file we generated using
>> the Jave keytool implementing SSL with Tomcat?
>
> You can use the same certificate and key but you'll need to copy
> them to your $PGDATA directory as server.crt and server.key (whether
> using the same certificate and key is a good idea is an administrative
> and/or security matter, but from a technical standpoint it should
> work).  If you want to require SSL client authentication then also
> install the CA certificate(s) as root.crt.  I'd suggest getting
> non-authenticated SSL working first and only then set up client
> authentication if you need it.
>
> If you want to require SSL connections (authenticated or not) then
> use "hostssl" in pg_hba.conf and make sure no other entry will match
> a non-SSL connection.
>
>> - In perusing the mailing list, it appears that this is not going
>> to be a 'simple' task...any pointers that anyone can give to me
>> before we start?  If possible, I'd like to avoid another hair-pulling
>> three week task! =o)
>
> Setting up SSL is simple.  Read "Secure TCP/IP Connections with
> SSL," "SSL Support," and "Client Authentication" in the documentation
> and follow the instructions therein.
>
> http://www.postgresql.org/docs/8.1/interactive/ssl-tcp.html
> http://www.postgresql.org/docs/8.1/interactive/libpq-ssl.html
> http://www.postgresql.org/docs/8.1/interactive/client-authentication.html
>
> If you have trouble then please report what you did, what you
> expected to happen, and what did happen (including client and server
> error messages).
>
> --
> Michael Fuhr
>