From jgeier@apt-cafm.com Thu Sep 14 14:17:12 2006 X-Original-To: pgsql-admin-postgresql.org@postgresql.org Received: from localhost (wm.hub.org [200.46.204.128]) by postgresql.org (Postfix) with ESMTP id 9DD719FB1E0 for ; Thu, 14 Sep 2006 11:17:12 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.128]) (amavisd-new, port 10024) with ESMTP id 76620-04 for ; Thu, 14 Sep 2006 14:17:08 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey- Received: from socrates.thinkhost.com (mailpipe.socrates.thinkhost.com [64.40.100.194]) by postgresql.org (Postfix) with ESMTP id CD3739FA0FC for ; Thu, 14 Sep 2006 11:17:07 -0300 (ADT) Received: from geier (adsl-64-91-71-139-rb.lax.customer.centurytel.net [64.91.71.139]) by socrates.thinkhost.com (8.13.6/8.13.5) with SMTP id k8EEGKGK070051 for ; Thu, 14 Sep 2006 10:16:20 -0400 (EDT) (envelope-from jgeier@apt-cafm.com) Message-ID: <009801c6d808$7200a260$6700a8c0@geier> From: "Jeanna Geier" To: Subject: Beginning SSL Questions Date: Thu, 14 Sep 2006 09:17:00 -0500 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0095_01C6D7DE.88B0FD90" X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2900.2869 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2962 X-Antivirus: avast! (VPS 0637-1, 09/13/2006), Outbound message X-Antivirus-Status: Clean X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.135 tagged_above=0 required=5 tests=HTML_50_60, HTML_MESSAGE X-Spam-Level: X-Archive-Number: 200609/133 X-Sequence-Number: 23047 This is a multi-part message in MIME format. ------=_NextPart_000_0095_01C6D7DE.88B0FD90 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable 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! =3Do) Thanks in advance for any help and for your time! -Jeanna ------=_NextPart_000_0095_01C6D7DE.88B0FD90 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
Hi = All-
 
We are going = to start=20 implementing SSL with Postgres v8.1.4 (finally got it working with = Tomcat=20 after a few long weeks!); and I've been doing some reading in the docs = and on=20 the mailing lists and I have a couple of questions before we start down = this=20 path:
 
 - In the = docs, it says=20 that when using SSL in Postgres "This requires that OpenSSL is installed = on both=20 client and server systems and that support in PostgreSQL is enabled at = build=20 time" - is this correct?  Or can we use the certificates and = keystore=20 file we generated using the Jave keytool implementing SSL with=20 Tomcat?
 
 - In = perusing the=20 mailing list, it appears that this is not going to be a 'simple' = task...any=20 pointers that anyone can give to me before we start?  If possible, = I'd like=20 to avoid another hair-pulling three week task! =3Do)
 
Thanks in = advance for any=20 help and for your time!
-Jeanna
------=_NextPart_000_0095_01C6D7DE.88B0FD90-- From mike@fuhr.org Thu Sep 14 15:02:27 2006 X-Original-To: pgsql-admin-postgresql.org@postgresql.org Received: from localhost (wm.hub.org [200.46.204.128]) by postgresql.org (Postfix) with ESMTP id 78C6D9FB1F2 for ; Thu, 14 Sep 2006 12:02:27 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.128]) (amavisd-new, port 10024) with ESMTP id 98158-03 for ; Thu, 14 Sep 2006 15:02:07 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey- Received: from tigger.fuhr.org (tigger.fuhr.org [63.214.45.158]) by postgresql.org (Postfix) with ESMTP id B50C69FB1BC for ; Thu, 14 Sep 2006 12:02:06 -0300 (ADT) Received: from winnie.fuhr.org (winnie.fuhr.org [10.1.0.1]) by tigger.fuhr.org (8.13.8/8.13.8) with ESMTP id k8EF1wps064804 (version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=OK); Thu, 14 Sep 2006 09:02:01 -0600 (MDT) Received: from winnie.fuhr.org (localhost [127.0.0.1]) by winnie.fuhr.org (8.13.8/8.13.8) with ESMTP id k8EF1wY0096172; Thu, 14 Sep 2006 09:01:58 -0600 (MDT) (envelope-from mfuhr@winnie.fuhr.org) Received: (from mfuhr@localhost) by winnie.fuhr.org (8.13.8/8.13.8/Submit) id k8EF1wfV096171; Thu, 14 Sep 2006 09:01:58 -0600 (MDT) (envelope-from mfuhr) Date: Thu, 14 Sep 2006 09:01:57 -0600 From: Michael Fuhr To: Jeanna Geier Cc: pgsql-admin@postgresql.org Subject: Re: Beginning SSL Questions Message-ID: <20060914150157.GA76098@winnie.fuhr.org> References: <009801c6d808$7200a260$6700a8c0@geier> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <009801c6d808$7200a260$6700a8c0@geier> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=SPF_PASS, UNPARSEABLE_RELAY X-Spam-Level: X-Archive-Number: 200609/135 X-Sequence-Number: 23049 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 From jgeier@apt-cafm.com Thu Sep 14 15:11:51 2006 X-Original-To: pgsql-admin-postgresql.org@postgresql.org Received: from localhost (wm.hub.org [200.46.204.128]) by postgresql.org (Postfix) with ESMTP id 7E6C29FB1F2 for ; Thu, 14 Sep 2006 12:11:51 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.128]) (amavisd-new, port 10024) with ESMTP id 98158-06 for ; Thu, 14 Sep 2006 15:11:47 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey- Received: from socrates.thinkhost.com (mailpipe.socrates.thinkhost.com [64.40.100.194]) by postgresql.org (Postfix) with ESMTP id 33A6D9FA0FC for ; Thu, 14 Sep 2006 12:11:47 -0300 (ADT) Received: from geier (adsl-64-91-71-139-rb.lax.customer.centurytel.net [64.91.71.139]) by socrates.thinkhost.com (8.13.6/8.13.5) with SMTP id k8EFAq2v089963; Thu, 14 Sep 2006 11:10:52 -0400 (EDT) (envelope-from jgeier@apt-cafm.com) Message-ID: <00bb01c6d810$100fb8e0$6700a8c0@geier> From: "Jeanna Geier" To: "Michael Fuhr" Cc: References: <009801c6d808$7200a260$6700a8c0@geier> <20060914150157.GA76098@winnie.fuhr.org> Subject: Re: Beginning SSL Questions Date: Thu, 14 Sep 2006 10:11:31 -0500 MIME-Version: 1.0 Content-Type: text/plain; format=flowed; charset="iso-8859-1"; reply-type=original Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2900.2869 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2962 X-Antivirus: avast! (VPS 0637-1, 09/13/2006), Outbound message X-Antivirus-Status: Clean X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests= X-Spam-Level: X-Archive-Number: 200609/136 X-Sequence-Number: 23050 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" To: "Jeanna Geier" Cc: 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 > From postgres@kiwi-fraser.net Thu Sep 14 15:15:57 2006 X-Original-To: pgadmin-support-postgresql.org@postgresql.org Received: from localhost (wm.hub.org [200.46.204.128]) by postgresql.org (Postfix) with ESMTP id 59AA69FB1E0 for ; Thu, 14 Sep 2006 12:15:57 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.128]) (amavisd-new, port 10024) with ESMTP id 09503-02 for ; Thu, 14 Sep 2006 15:15:44 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey- Received: from smtp-out2.blueyonder.co.uk (smtp-out2.blueyonder.co.uk [195.188.213.5]) by postgresql.org (Postfix) with ESMTP id 7EAF59FB1F2 for ; Thu, 14 Sep 2006 12:15:43 -0300 (ADT) Received: from [172.23.170.147] (helo=anti-virus03-10) by smtp-out2.blueyonder.co.uk with smtp (Exim 4.52) id 1GNswH-0003BE-WC for pgadmin-support@postgresql.org; Thu, 14 Sep 2006 16:15:42 +0100 Received: from [82.44.23.191] (helo=DonAcer) by asmtp-out5.blueyonder.co.uk with smtp (Exim 4.52) id 1GNsw9-0004xm-Ru for pgadmin-support@postgresql.org; Thu, 14 Sep 2006 16:15:33 +0100 Message-ID: <002c01c6d810$9f514e60$6464a8c0@DonAcer> From: "Donald Fraser" To: "[pgADMIN]" References: <009801c6d808$7200a260$6700a8c0@geier> Subject: Re: [ADMIN] Beginning SSL Questions Date: Thu, 14 Sep 2006 16:15:32 +0100 MIME-Version: 1.0 Content-Type: text/plain; format=flowed; charset="iso-8859-1"; reply-type=original Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2900.2869 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2962 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests= X-Spam-Level: X-Archive-Number: 200609/17 X-Sequence-Number: 5747 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 From postgres@kiwi-fraser.net Thu Sep 14 15:16:59 2006 X-Original-To: pgsql-admin-postgresql.org@postgresql.org Received: from localhost (wm.hub.org [200.46.204.128]) by postgresql.org (Postfix) with ESMTP id 169579FB1E0 for ; Thu, 14 Sep 2006 12:16:59 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.128]) (amavisd-new, port 10024) with ESMTP id 06419-03 for ; Thu, 14 Sep 2006 15:16:54 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey- Received: from smtp-out4.blueyonder.co.uk (smtp-out4.blueyonder.co.uk [195.188.213.7]) by postgresql.org (Postfix) with ESMTP id 608B99FB1BC for ; Thu, 14 Sep 2006 12:16:54 -0300 (ADT) Received: from [172.23.170.139] (helo=anti-virus01-10) by smtp-out4.blueyonder.co.uk with smtp (Exim 4.52) id 1GNsxQ-0002WO-V0 for pgsql-admin@postgresql.org; Thu, 14 Sep 2006 16:16:53 +0100 Received: from [82.44.23.191] (helo=DonAcer) by asmtp-out3.blueyonder.co.uk with smtp (Exim 4.52) id 1GNsxP-00026K-Ri for pgsql-admin@postgresql.org; Thu, 14 Sep 2006 16:16:51 +0100 Message-ID: <003201c6d810$cdccb860$6464a8c0@DonAcer> From: "Donald Fraser" To: "[ADMIN]" References: <009801c6d808$7200a260$6700a8c0@geier> Subject: Re: Beginning SSL Questions Date: Thu, 14 Sep 2006 16:16:50 +0100 MIME-Version: 1.0 Content-Type: text/plain; format=flowed; charset="iso-8859-1"; reply-type=original Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2900.2869 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2962 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests= X-Spam-Level: X-Archive-Number: 200609/137 X-Sequence-Number: 23051 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 From jgeier@apt-cafm.com Wed Sep 20 20:34:35 2006 X-Original-To: pgsql-admin-postgresql.org@postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id EDD419FB289 for ; Wed, 20 Sep 2006 17:34:35 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 22893-06-5 for ; Wed, 20 Sep 2006 17:34:21 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from socrates.thinkhost.com (mailpipe.socrates.thinkhost.com [64.40.100.194]) by postgresql.org (Postfix) with ESMTP id 23B4B9FB313 for ; Wed, 20 Sep 2006 17:33:42 -0300 (ADT) Received: from geier (adsl-64-91-71-139-rb.lax.customer.centurytel.net [64.91.71.139]) by socrates.thinkhost.com (8.13.6/8.13.5) with SMTP id k8KKWX4Q057463; Wed, 20 Sep 2006 16:32:39 -0400 (EDT) (envelope-from jgeier@apt-cafm.com) Message-ID: <007a01c6dcf4$057dcfa0$6700a8c0@geier> From: "Jeanna Geier" To: "Michael Fuhr" Cc: References: <009801c6d808$7200a260$6700a8c0@geier> <20060914150157.GA76098@winnie.fuhr.org> Subject: Re: Beginning SSL Questions Date: Wed, 20 Sep 2006 15:33:18 -0500 MIME-Version: 1.0 Content-Type: text/plain; format=flowed; charset="iso-8859-1"; reply-type=original Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2900.2869 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2962 X-Antivirus: avast! (VPS 0638-0, 09/19/2006), Outbound message X-Antivirus-Status: Clean X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.025 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200609/222 X-Sequence-Number: 23136 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" To: "Jeanna Geier" Cc: 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 > From mike@fuhr.org Thu Sep 21 02:51:36 2006 X-Original-To: pgsql-admin-postgresql.org@postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 1C32B9FB304 for ; Wed, 20 Sep 2006 23:51:36 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 93284-10 for ; Wed, 20 Sep 2006 23:51:23 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from tigger.fuhr.org (tigger.fuhr.org [63.214.45.158]) by postgresql.org (Postfix) with ESMTP id A6E379FB2F6 for ; Wed, 20 Sep 2006 23:51:23 -0300 (ADT) Received: from winnie.fuhr.org (winnie.fuhr.org [10.1.0.1]) by tigger.fuhr.org (8.13.8/8.13.8) with ESMTP id k8L2pF2P073737 (version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=OK); Wed, 20 Sep 2006 20:51:17 -0600 (MDT) Received: from winnie.fuhr.org (localhost [127.0.0.1]) by winnie.fuhr.org (8.13.8/8.13.8) with ESMTP id k8L2pEbL074082; Wed, 20 Sep 2006 20:51:14 -0600 (MDT) (envelope-from mfuhr@winnie.fuhr.org) Received: (from mfuhr@localhost) by winnie.fuhr.org (8.13.8/8.13.8/Submit) id k8L2pCvU074081; Wed, 20 Sep 2006 20:51:12 -0600 (MDT) (envelope-from mfuhr) Date: Wed, 20 Sep 2006 20:51:12 -0600 From: Michael Fuhr To: Jeanna Geier Cc: pgsql-admin@postgresql.org Subject: Re: Beginning SSL Questions Message-ID: <20060921025112.GA73386@winnie.fuhr.org> References: <009801c6d808$7200a260$6700a8c0@geier> <20060914150157.GA76098@winnie.fuhr.org> <007a01c6dcf4$057dcfa0$6700a8c0@geier> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <007a01c6dcf4$057dcfa0$6700a8c0@geier> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.15 tagged_above=0 required=5 tests=AWL, SPF_PASS X-Spam-Level: X-Archive-Number: 200609/224 X-Sequence-Number: 23138 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