Обсуждение: Beginning SSL Questions
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/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
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/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 >
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
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 >
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