pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedConnect to a server with SSL encrypted connection?
2+ messages / 2 participants
[nested] [flat]
* Connect to a server with SSL encrypted connection?
@ 2010-10-14 10:59 Sebastien <maxmanseb2713@yahoo.fr>
2010-10-14 19:10 ` Re: Connect to a server with SSL encrypted connection? Craig James <craig_james@emolecules.com>
0 siblings, 1 reply; 2+ messages in thread
From: Sebastien @ 2010-10-14 10:59 UTC (permalink / raw)
To: pgsql-admin
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
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Connect to a server with SSL encrypted connection?
2010-10-14 10:59 Connect to a server with SSL encrypted connection? Sebastien <maxmanseb2713@yahoo.fr>
@ 2010-10-14 19:10 ` Craig James <craig_james@emolecules.com>
0 siblings, 0 replies; 2+ messages in thread
From: Craig James @ 2010-10-14 19:10 UTC (permalink / raw)
To: pgsql-admin
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
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2010-10-14 19:10 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-10-14 10:59 Connect to a server with SSL encrypted connection? Sebastien <maxmanseb2713@yahoo.fr>
2010-10-14 19:10 ` Craig James <craig_james@emolecules.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox