SEO

Hi All-

We are going to start implementing SSL with Postgres v8.1.4 (finally got it working with Tomcat after a few long weeks!); and I've been doing some reading in the docs and on the mailing lists and I have a couple of questions before we start down this path:

- 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? Or can we use the certificates and keystore file we generated using the Jave keytool implementing SSL with Tomcat?

- 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)

Thanks in advance for any help and for your time!
-Jeanna

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/i...e/ssl-tcp.html
http://www.postgresql.org/docs/8.1/i...libpq-ssl.html
http://www.postgresql.org/docs/8.1/i...ntication.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

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Thanks for the reply Michael.

I'm getting started and will report back on any issues I run into; this
mailing list is excellent at responding and helping troubleshoot!! So
thanks to all for that!!!

----- 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/i...e/ssl-tcp.html
> http://www.postgresql.org/docs/8.1/i...libpq-ssl.html
> http://www.postgresql.org/docs/8.1/i...ntication.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
>


---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

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? Or can we use the
> certificates and keystore file we generated using the Jave keytool
> implementing SSL with Tomcat?


OpenSSL must be installed on the server and enabled at build time.
OpenSSL does not have to be installed on the client.

You are advised to use the OpenSSL tools to create the private/public key pair
for the server as it will then be in the correct format. Assuming you know how
to build/acquire a signed certificate that requires no parse phrase, place the
certificate pair into the root data directory of PostgreSQL. The key pair
should be named server.key and server.crt respectively. Make sure the
permissions on these files are only readable by the postgres user account that
runs the database.

The line ssl=true must appear in the postgresql.conf file.

A restart of the server will be required for ssl to be enabled.

Now for the Java side of things.

If your certificate was signed by a recognised authority you will need a copy
of the public certificate used by the authority that signed your certificate.
Place that public certificate into the Java key store. Most of the well known
ones are already provided in the key store for Sun's VM.

If you self signed the certificate on the server then simply place the public
part of the key pair (server.crt) into your Java key store.

The above will enable clients to connect using ssl.
If you require client authenticaton using ssl then you will need the public key
used to sign your client side certificates. You will need to place the public
key into the root data directory of PostgreSQL and it must be named root.crt.

User the pg_hba.conf file to force clients to use ssl or not.

Hope that helps.
Regards
Donald Fraser



---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

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/i...e/ssl-tcp.html
> http://www.postgresql.org/docs/8.1/i...libpq-ssl.html
> http://www.postgresql.org/docs/8.1/i...ntication.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
>


---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

On Wed, Sep 20, 2006 at 03:33:18PM -0500, Jeanna Geier wrote:
> Hopefully someone here has some OpenSSL expertise and can help me with a
> problem I'm running into...
[...]
> 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
> ........................................+++
> ......+++

That command should work; here's what it does on my FreeBSD system:

% openssl genrsa -des3 -out server.key 2048
Generating RSA private key, 2048 bit long modulus
...............+++
............................+++
e is 65537 (0x10001)
Enter pass phrase for server.key:
Verifying - Enter pass phrase for server.key:

Your prime number generation appears to have completed but the
command hangs before displaying the encryption exponent. How long
did you wait? The OpenSSL source code has only a few lines between
those two actions, one of which is:

app_RAND_write_file(NULL, bio_err);

I wonder if that's where the command is hanging. That function
generates cryptographically strong pseudo-random bytes and saves
them to a file for future use, so it's possible that you didn't
wait long enough. If your system doesn't have enough entropy then
it might be waiting to gather more, in which case wiggling the mouse
or banging on the keyboard might help (assuming your system gathers
entropy from "random" activity like interrupts). If not then you
could try commenting out that line (line 264) in apps/genrsa.c,
then rebuild and reinstall OpenSSL. That's not a good solution but
if key generation completes after making that change then at least
you'd have pinpointed the problem.

Incidentally, if you encrypt the private key (as you're doing with
the -des3 option) then the postmaster will prompt for the password
every time it starts. That'll prevent the postmaster from starting
unattended.

--
Michael Fuhr

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly