Re: Connect to a server with SSL encrypted connection?

On 10/14/10 3:59 AM, Sebastien wrote:
> Hello!
>
> I'm writting to get some clues about PostgreSQL administration, and more precisely SSL connection from one server to another with certificate identification. I must underline than I'm new to postgreSQL and server administration.
>
> Here is the story:
>
> My main server has an IP XXX.XXX.XXX.XXX. I installed on it PostgreSQL 9.0.1 and OpenSSL. I did put a line in pg_hba.conf to allow connection from users on the local network (connection to the local server "MyMainServer").
>
> I have a secondary server located elsewhere ("MySecondServer"), on which I want to maintain a copy of a database of my main server "MyDatabase" (through dumps), and to have access from this place (with the IP YYY.YYY.YYY.YYY) to my PostgreSQL on MyMainServer. I installed PostgreSQL 9.0.1 and OpenSSL on this server as well. Both servers run on windows.
>
> I need the connection to be as secured as possible, with only allowed connection from "MySecondServer" to "MyMainServer"
>
> From what I found in the documentation, it seems that a SSL encrypted connection with identification of client and server with certificates would fit my needs.
>
> Now, beginning of the questions... Please tell me if anything is wrong or could be improved...
>
>
> 1) The line to add to my pg_hba.conf on "MyMainServer" :
>
> hostssl MyDatabase MyUserName YYY.YYY.YYY.YYY/32 cert
>
> This is to only allow SSL connections from YYY.YYY.YYY.YYY for user MyUserName with certificate.
>
> Question n° 1 : on page http://developer.postgresql.org/pgdocs/postgres/ssl-tcp.html they state "The clientcert option in pg_hba.conf is available for all authentication methods (...)", and also "place certificates of the certificate authorities (CAs) you trust in the file root.crt in the data directory, and set the clientcert parameter to 1 on the appropriate hostssl line(s) in pg_hba.conf". Is it something different from the "cert" option that I added to the end of the line? I've seen no option called "clientcert" for pg_hba.conf elsewhere.
>
> Now comes the big trouble... Generating the certificates. How should I sign them in this case?
>
>
> 2) Creation of my server certificate:
>
> > openssl req -new -text -out server.req
>
> I enter for "Common Name": "MyMainServer", right? Then,
>
> > openssl rsa -in privkey.pem -out server.key
>
> "A self-signed certificate can be used for testing (...) If all the clients are local to the organization, using a local CA is recommended.".
>
> Okay, from here it becomes more difficult. Does it mean that I should set up a local CA on "MySecondServer" to sign the certificate of "MyMainServer"?
>
> So when I connect to "MyMainServer", (from YYY.YYY.YYY.YYY), it sends me its certificate, and this one is checked from my local CA on "MyMainServer"?
>
> If it's true, could you tell me how to do that? What are the steps to create my local CA, then sign my server certificate with it?
>
>
> 3) Creation of my client certificate... I guess it's the same thing,
>
> > openssl req -new -text -out client.req
>
> I enter for "Common Name": "MyUserName", right? "The cn (Common Name) attribute of the certificate will be compared to the requested database user name, and if they match the login will be allowed. User name mapping can be used to allow cn to be different from the database user name.".
>
> Then, should I also remove the passphrase with "openssl rsa -in privkey.pem -out client.key". I guess not, so that this passphrase will protect the client key, but will be asked everytime I connect, is that true?
>
> So, now can I also sign the client certificate with the same local CA that you helped me setup just before? How, please?
>
>
> 4) If I understand well, I know have one certificate for the server, and one for the client, both signed with my local CA.
>
> Then, I should add the certificate of my local CA in "root.crt" of "MyMainServer", true? What does it mean, copying the content of the CA certificate file in "root.crt"????
>
> "server.crt" and "server.key" previously created on step 2 must also be put on the "data" folder on "MyMainServer", true?
>
> Ok, I guess that at this point "MyMainServer" is well configured, true?
>
>
> 5) Now, for the client... How does it work on this part? From what I understand:
>
> On "MySecondServer", I must put my local CA certificate in "root.crt", what does it mean, copying the content in this file?
>
> Then, in "postgresql.crt", put my client certificate created before ("client.crt"). I guess that's all I have to do, true? "MySecondServer" is ready to connect to "MyMainServer"?
>
> Now, how can I set sslmode to "verify-full" as indicated on http://developer.postgresql.org/pgdocs/postgres/libpq-ssl.html ? I couldn't find this option anywhere...
>
>
> 6) Okay, almost done...
>
> Now I want to make a dump of "MyDatabase" on "MyMainServer" on "MySecondServer". How can I do this connection? What is the host that I should write to connect to "MyMainServer", IP XXX.XXX.XXX.XXX with user "MyUserName"?
>
> > pg_dump -c -h ???????? -p 5432 -U "MyUserName" "MyDatabase" | psql -c -h "MySecondServer" -p 5432 -U "MyUserName" "MyDatabase"
>
> And the same to connect with psql:
>
> > psql -h ?????? -p 5432 -d "MyDatabase" -U "MyUserName"
>
> I guess I'll also be able to connect with pgAdmin at this point.
>
> One last question, about how this authentication takes place, what is the order of the steps?
> 1) Client connects to server, and sends its certificate
> 2) server checks if client IP is okay, if so checks certificate as defined in pg_hba.conf with root.crt
> 3) if okay, server sends its certificate
> 4) client checks if server's certificate is okay with root.crt
> 5) if okay, the connection is done, and encrypted?
>
> Please tell me if I understand well how it works.

There's another way that might be easier. Use ssh(1) with a tunnel. All you have to do is create the usual .ssh/* files that allow you to login from one server to the other without a password, and then something like this:

ssh -L 5433:192.xxx.xxx.xxx:5432

Then in your Postgres connection, use port 5433. ssh will forward the connection to the other server on port 5432. You can test it like this:

psql -U user -p 5433 -h localhost dbname

I've done this a lot, and it works well. The only trick is that you may want the SSH tunnel created automatically (such as if the computer reboots), which requires some sort of /etc/init.d script.

Craig