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.
Thank you very much if you can reply to my questions, I'm sure other users
searching on google will also find your replies very useful!!
Bye!
Sébastien
