First time attempting PostgreSQL SSL

David Patricola

unread,

Jan 28, 2011, 1:18:02 PM1/28/11

to

I’m new as can be with creating SSL certificates on my own. I downloaded the openssl binary and installed it. The instructions and tutorials on the website don’t help me much in terms of steps A,B,C; this could also be due to a lack of familiarity with technical terms used for each part of this. The only thing I did accomplish is the following

openssl req -out CSR.csr -new -newkey rsa:2048 -nodes -keyout privateKey.key

I now have a privateKey and CSR file in the openssl/bin folder. Beyond this I do not know what goes where. I’m on Windows 2003 (server) and Windows XP (client). I know what to change in the PostgreSQL config but do not know if PostgreSQL implicitly knows that a certificate exists, and what the client box should have.

David Patricola | Senior Cold Fusion Developer | Web Applications & Services | Jefferson Information Technologies

Thomas Jefferson Universtiy | Philadelphia, PA | 215.503.1715 (Office)

Alan Buxey

unread,

Jan 29, 2011, 3:21:19 PM1/29/11

to

Hi,

> I’m new as can be with creating SSL certificates on my own. I downloaded
> the openssl binary and installed it. The instructions and tutorials on
> the website don’t help me much in terms of steps A,B,C; this could also be
> due to a lack of familiarity with technical terms used for each part of
> this. The only thing I did accomplish is the following
>
>
>
> openssl req -out CSR.csr -new -newkey rsa:2048 -nodes -keyout
> privateKey.key
>
>
>
> I now have a privateKey and CSR file in the openssl/bin folder. Beyond
> this I do not know what goes where. I’m on Windows 2003 (server) and
> Windows XP (client). I know what to change in the PostgreSQL config but
> do not know if PostgreSQL implicitly knows that a certificate exists, and
> what the client box should have.

you probably want to check the postgreSQL documentation for where and how to
use the certs - as this is not specific to OpenSSL.

anyway, in general terms...you now have a private key - good, the PSQL server
would have that.... you need to get your CSR signed by a CA that the client
knows..... you then would configure PSQL to use the public version of the
signed CSR - usually a DER or PEM file by that point. at this point,
its just like a client talking to an SSL'd web server (or any other service).
client connects, gets given the cert...which it trusts (because of CA) and
then SLS tunnel gets made. data is transferred over that tunnel.

alan
______________________________________________________________________
OpenSSL Project http://www.openssl.org
User Support Mailing List openss...@openssl.org
Automated List Manager majo...@openssl.org

David Patricola

unread,

Jan 31, 2011, 4:10:10 PM1/31/11

to

I found an excellent tutorial on this:
http://www.howtoforge.com/postgresql-ssl-certificates

I followed the instructions perfectly, and used my client computer's IP
address as the CA. Here's my setup so far:

On the server:

- In Postgre data/ I have the server.crt and server.key files
- pg_hba.conf has hostssl all all
myipaddress/32 cert
- postgresql.conf has ssl = on
- I restarted the service and it connected.

On the client:

- In my %appdata%/postgresql folder I have postgresql.crt,
postgresql.csr, postgresql.key and root.crt

After this, I try to connect to the server again from my client box and it
says "server does not support SSL, but SSL is required". Would this be a
Postgre issue or did I not put a certificate file correctly somewhere?

Lou Picciano

unread,

Jan 31, 2011, 4:55:10 PM1/31/11

to

David,

First step - well before placing your certificates correctly, is to be sure your PostgreSQL _server_ has been compiled with SSL support. From that message, it seems it isn't...

From there, you have to place all the certificates and keys in correct locations - but I think you're not quite there yet.

To get yourself going, why not first set up a pg_hba.conf configuration using hostssl, but skip the certificate usage for the moment... Probably a bit to handle all at once; SSL plus the certificate authentication all in one go...

Lou Picciano

unread,

Jan 31, 2011, 5:15:54 PM1/31/11

to

David, a couple of additional thoughts, as I re-read this...

First, in your client certs location - %appdata%/postgresql - you do _not_ need the CSR. This is the Certificate Signing Request. It won't hurt you, but save this elsewhere.

Next: A common gotcha on Windows is that the %APPDATA% is not always exactly what you think it is. It's worth experimenting with your local vs. roaming profiles, for example...

Having said all that, here's some Good Advice(!): Skip certificates for the moment, and be sure you have SSL working _at all_ on your server.

You must be sure of a couple of things:

1) The server _must_ be built with SSL support

2) The client lib you are using - typically pq_lib, if you haven't compiled your own code, must ALSO be built with SSL support.

3) The pg_hba.conf access line must 'activate' SSL for a given connection.

HINT: A 'simple' pg_hba.conf setting will be adequate to testing SSL; remove the variables one by one in your logic. IE, use a 'hostssl' line for user 'test' from 0.0.0.0/0 with an md5 password.

The whole SSL-build-plus-certificates chain above, all at the same time, is not for the faint of heart, if you have never done it before. It's a bit bigger than just setting the options in pg_hba.conf. Once you've verified SSL as a baseline, then you're ready to move on to certificates!

Let me know! Lou Picciano

----- Original Message -----
From: "David Patricola" <david.p...@jefferson.edu>
To: openss...@openssl.org

Sent: Monday, January 31, 2011 4:31:08 PM
Subject: FW: First time attempting PostgreSQL SSL

Whoops, I forgot to include root.crt in the Postgre /data directory. Now
that I fixed that, I am still getting the same connection error on the
client side.

David Patricola

unread,

Feb 1, 2011, 8:50:12 AM2/1/11

to

My server pg_hba.conf file already has the following to connect to the client:

hostssl all all myipaddress/32 cert

And my postgre configuration file has ssl = on set already.

I installed pgAdmin on my client computer with the Binary file. And I have no idea what the difference is between a local and roaming profile, as my appdata folder is under c:\Documents and Settings\--account--\Application Data\postgresql

When you say the server must be built with SSL support, it has a dropdown option when setting up a database for SSL Mode, and I selected (for now) Required. If there’s some other feature I missed in this regard then I am unaware of it.

Lou Picciano

unread,

Feb 1, 2011, 9:36:20 AM2/1/11

to

David,

My server pg_hba.conf file already has the following to connect to the client:

hostssl all all myipaddress/32 cert

Trust me.

Make your life simple. Change that line to generate some 'empirical' testing:

hostssl all all 0.0.0.0/0 md5

Forget the locked-down CIDR addressing for the moment - given the routing and multi-net environment of any large institution, can you be sure of the IP address you're coming in on? (Presumably you're tailing your logs?)

Forget the cert. Establish a password-only connection, using SSL, as I've suggested earlier. Have you created a simple test user, with an md5-hashed password? (There will be plenty of time for 'Fun with Certs', believe me).

And my postgre configuration file has
ssl = on set already

Well, if you've successfully restarted the server with that option, this may be diagnostic: That you DO have SSL built in to the server.

When you say the server must be built with SSL support, it has a dropdown option when setting up a database for SSL Mode, and I selected (for now) Required

Not relevant. Sounds like you are setting the _client-side_ connection options - perhaps in pgAdminIII? This only set pqlib's behavior.

BTW, Friends don't let friends use pgAdminIII...(!) It's a basis for an 'intervention'; you'll want to get onto the PostgreSQL CLI ASAP!

Last: You realize this is the OpenSSL list; not really the right place to get the advice you seek? (Am happy to help nonetheless... !)

Let me know how you get on! (I will want to know the resolution of all this...)

Lou Picciano

---

David Patricola

unread,

Feb 1, 2011, 9:38:39 AM2/1/11

to

I _just_ realized that I installed PostgreSQL before I installed OpenSSL, so --with-openssl was not populated with the directory location. I need to do this first. How can I modify this in the postgre setup (Windows Binary used to install).

*facepalm*