DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.fr;
	h=Message-ID:X-YMail-OSG:Received:X-Mailer:Date:From:Subject:To:MIME-Version:Content-Type;
	b=N7Ty4BB4S/3zhJSte9dyfAl910BX/BVtwn5bUzs1HyrRIrXz5ev6zUCjFqVjqKzDE14vC5f/kW74UDewGgkpqzpiuEOGFtBob3fH//7JE7cT4BnHSM8Egm8LZq3+Y9FfWYhT30ybKftJl62jXLjJPGo8r9U41EIdwAAJRFdhI2k=;
Message-ID: <728081.42149.qm@web29519.mail.ird.yahoo.com>
Date: Thu, 14 Oct 2010 11:59:04 +0100 (BST)
From: Sebastien <maxmanseb2713@yahoo.fr>
Subject: Connect to a server with SSL encrypted connection?
To: pgsql-admin@postgresql.org
MIME-Version: 1.0
Content-Type: multipart/alternative; boundary="0-1749150258-1287053944=:42149"

--0-1749150258-1287053944=:42149
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: quoted-printable

Hello!=0A=0AI'm writting to get some clues about PostgreSQL administration,=
 and more =0Aprecisely SSL connection from one server to another with certi=
ficate =0Aidentification. I must underline than I'm new to postgreSQL and s=
erver =0Aadministration.=0A=0AHere is the story:=0A=0AMy main server has an=
 IP XXX.XXX.XXX.XXX. I installed on it PostgreSQL 9.0.1 and =0AOpenSSL. I d=
id put a line in pg_hba.conf to allow connection from users on the =0Alocal=
 network (connection to the local server "MyMainServer").=0A=0AI have a sec=
ondary server located elsewhere ("MySecondServer"), on which I want =0Ato m=
aintain a copy of a database of my main server "MyDatabase" (through dumps)=
, =0Aand to have access from this place (with the IP YYY.YYY.YYY.YYY) to my=
 =0APostgreSQL on MyMainServer. I installed PostgreSQL 9.0.1 and OpenSSL on=
 this =0Aserver as well. Both servers run on windows.=0A=0AI need the conne=
ction to be as secured as possible, with only allowed connection =0Afrom "M=
ySecondServer" to "MyMainServer"=0A=0AFrom what I found in the documentatio=
n, it seems that a SSL encrypted connection =0Awith identification of clien=
t and server with certificates would fit my needs.=0A=0ANow, beginning of t=
he questions... Please tell me if anything is wrong or could =0Abe improved=
...=0A=0A=0A1) The line to add to my pg_hba.conf on "MyMainServer" :=0A=0Ah=
ostssl MyDatabase MyUserName YYY.YYY.YYY.YYY/32 cert=0A=0AThis is to only a=
llow SSL connections from YYY.YYY.YYY.YYY for user MyUserName =0Awith certi=
ficate.=0A=0AQuestion n=B0 1 : on page =0Ahttp://developer.postgresql.org/p=
gdocs/postgres/ssl-tcp.html they state "The =0Aclientcert option in pg_hba.=
conf is available for all authentication methods =0A(...)", and also "place=
 certificates of the certificate authorities (CAs) you =0Atrust in the file=
 root.crt in the data directory, and set the clientcert =0Aparameter to 1 o=
n the appropriate hostssl line(s) in pg_hba.conf". Is it =0Asomething diffe=
rent from the "cert" option that I added to the end of the line? =0AI've se=
en no option called "clientcert" for pg_hba.conf elsewhere.=0A=0ANow comes =
the big trouble... Generating the certificates. How should I sign them =0Ai=
n this case?=0A=0A=0A2) Creation of my server certificate:=0A=0A> openssl r=
eq -new -text -out server.req=0A=0AI enter for "Common Name": "MyMainServer=
", right? Then,=0A=0A> openssl rsa -in privkey.pem -out server.key=0A=0A"A =
self-signed certificate can be used for testing (...) If all the clients ar=
e =0Alocal to the organization, using a local CA is recommended.".=0A=0AOka=
y, from here it becomes more difficult. Does it mean that I should set up a=
 =0Alocal CA on "MySecondServer" to sign the certificate of "MyMainServer"?=
=0A=0ASo when I connect to "MyMainServer", (from YYY.YYY.YYY.YYY), it sends=
 me its =0Acertificate, and this one is checked from my local CA on "MyMain=
Server"?=0A=0AIf it's true, could you tell me how to do that? What are the =
steps to create my =0Alocal CA, then sign my server certificate with it?=0A=
=0A=0A3) Creation of my client certificate... I guess it's the same thing, =
=0A=0A> openssl req -new -text -out client.req=0A=0AI enter for "Common Nam=
e": "MyUserName", right? "The cn (Common Name) attribute =0Aof the certific=
ate will be compared to the requested database user name, and if =0Athey ma=
tch the login will be allowed. User name mapping can be used to allow cn =
=0Ato be different from the database user name.".=0A=0AThen, should I also =
remove the passphrase with "openssl rsa -in privkey.pem -out =0Aclient.key"=
. I guess not, so that this passphrase will protect the client key, =0Abut =
will be asked everytime I connect, is that true?=0A=0ASo, now can I also si=
gn the client certificate with the same local CA that you =0Ahelped me setu=
p just before? How, please?=0A=0A=0A4) If I understand well, I know have on=
e certificate for the server, and one for =0Athe client, both signed with m=
y local CA.=0A=0AThen, I should add the certificate of my local CA in "root=
.crt" of =0A"MyMainServer", true? What does it mean, copying the content of=
 the CA =0Acertificate file in "root.crt"????=0A=0A"server.crt" and "server=
.key" previously created on step 2 must also be put on =0Athe "data" folder=
 on "MyMainServer", true?=0A=0AOk, I guess that at this point "MyMainServer=
" is well configured, true?=0A=0A=0A5) Now, for the client... How does it w=
ork on this part? From what I understand:=0A=0AOn "MySecondServer", I must =
put my local CA certificate in "root.crt", what does =0Ait mean, copying th=
e content in this file?=0A=0AThen, in "postgresql.crt", put my client certi=
ficate created before =0A("client.crt"). I guess that's all I have to do, t=
rue? "MySecondServer" is ready =0Ato connect to "MyMainServer"?=0A=0ANow, h=
ow can I set sslmode to "verify-full" as indicated on =0Ahttp://developer.p=
ostgresql.org/pgdocs/postgres/libpq-ssl.html ? I couldn't find =0Athis opti=
on anywhere...=0A=0A=0A6) Okay, almost done... =0A=0ANow I want to make a d=
ump of "MyDatabase" on "MyMainServer" on "MySecondServer". =0AHow can I do =
this connection? What is the host that I should write to connect to =0A"MyM=
ainServer", IP XXX.XXX.XXX.XXX with user "MyUserName"?=0A=0A> pg_dump  -c -=
h ???????? -p 5432 -U "MyUserName" "MyDatabase" | psql -c -h =0A>"MySecondS=
erver" -p 5432 -U "MyUserName" "MyDatabase"=0A=0AAnd the same to connect wi=
th psql:=0A=0A> psql -h ?????? -p 5432 -d "MyDatabase" -U "MyUserName"=0A=
=0AI guess I'll also be able to connect with pgAdmin at this point.=0A=0AOn=
e last question, about how this authentication takes place, what is the ord=
er =0Aof the steps?=0A1) Client connects to server, and sends its certifica=
te=0A2) server checks if client IP is okay, if so checks certificate as def=
ined in =0Apg_hba.conf with root.crt=0A3) if okay, server sends its certifi=
cate=0A4) client checks if server's certificate is okay with root.crt=0A5) =
if okay, the connection is done, and encrypted?=0A=0APlease tell me if I un=
derstand well how it works.=0A=0AThank you very much if you can reply to my=
 questions, I'm sure other users =0Asearching on google will also find your=
 replies very useful!!=0A=0ABye!=0A=0A=0AS=E9bastien=0A=0A=0A=0A      
--0-1749150258-1287053944=:42149
Content-Type: text/html; charset=iso-8859-1
Content-Transfer-Encoding: quoted-printable

<html><head><style type=3D"text/css"><!-- DIV {margin:0px;} --></style></he=
ad><body><div style=3D"font-family:"arial", "helvetica", sans-serif;font-si=
ze:10pt;color:#000000;"><p>Hello!<br><br>I'm writting to get some clues abo=
ut PostgreSQL administration, and more precisely SSL connection from one se=
rver to another with certificate identification. I must underline than I'm =
new to postgreSQL and server administration.<br><br>Here is the story:<br><=
br>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").=
<br><br>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.<br><br>I =
need the connection to be as secured as possible, with only allowed connect=
ion from "MySecondServer" to "MyMainServer"<br><br>From what I found in the=
 documentation, it seems that a SSL encrypted connection with identificatio=
n of client and server with certificates would fit my needs.<br><br>Now, be=
ginning of the questions... Please tell me if anything is wrong or could be=
 improved...<br><br><br>1) The line to add to my pg_hba.conf on "MyMainServ=
er" :<br><br>hostssl MyDatabase MyUserName YYY.YYY.YYY.YYY/32 cert<br><br>T=
his is to only allow SSL connections from YYY.YYY.YYY.YYY for user MyUserNa=
me with certificate.<br><br><span>Question n=B0 1 : on page <a target=3D"_b=
lank" href=3D"http://developer.postgresql.org/pgdocs/postgres/ssl-tcp.html"=
>http://developer.postgresql.org/pgdocs/postgres/ssl-tcp.html</a> they stat=
e "The clientcert option in pg_hba.conf is available for all
 authentication methods (...)", and also "place certificates of the certifi=
cate 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 ad=
ded to the end of the line? I've seen no option called "clientcert" for pg_=
hba.conf elsewhere.</span><br><br>Now comes the big trouble... Generating t=
he certificates. How should I sign them in this case?<br><br><br>2) Creatio=
n of my server certificate:<br><br>&gt; openssl req -new -text -out server.=
req<br><br>I enter for "Common Name": "MyMainServer", right? Then,<br><br>&=
gt; openssl rsa -in privkey.pem -out server.key<br><br>"A self-signed certi=
ficate can be used for testing (...) If all the clients are local to the or=
ganization, using a local CA is recommended.".<br><br>Okay, from here it be=
comes more difficult. Does it mean that I should set up a local CA
 on "MySecondServer" to sign the certificate of "MyMainServer"?<br><br>So w=
hen I connect to "MyMainServer", (from YYY.YYY.YYY.YYY), it sends me its ce=
rtificate, and this one is checked from my local CA on "MyMainServer"?<br><=
br>If it's true, could you tell me how to do that? What are the steps to cr=
eate my local CA, then sign my server certificate with it?<br><br><br>3) Cr=
eation of my client certificate... I guess it's the same thing, <br><br>&gt=
; openssl req -new -text -out client.req<br><br>I enter for "Common Name": =
"MyUserName", right? "The cn (Common Name) attribute of the certificate wil=
l be compared to the requested database user name, and if they match the lo=
gin will be allowed. User name mapping can be used to allow cn to be differ=
ent from the database user name.".<br><br>Then, should I also remove the pa=
ssphrase with "openssl rsa -in privkey.pem -out client.key". I guess not, s=
o that this passphrase will protect the client key, but will be
 asked everytime I connect, is that true?<br><br>So, now can I also sign th=
e client certificate with the same local CA that you helped me setup just b=
efore? How, please?<br><br><br>4) If I understand well, I know have one cer=
tificate for the server, and one for the client, both signed with my local =
CA.<br><br>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 c=
ertificate file in "root.crt"????<br><br>"server.crt" and "server.key" prev=
iously created on step 2 must also be put on the "data" folder on "MyMainSe=
rver", true?<br><br>Ok, I guess that at this point "MyMainServer" is well c=
onfigured, true?<br><br><br>5) Now, for the client... How does it work on t=
his part? From what I understand:<br><br>On "MySecondServer", I must put my=
 local CA certificate in "root.crt", what does it mean, copying the content=
 in this file?<br><br>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"?<br><br><spa=
n>Now, how can I set sslmode to "verify-full" as indicated on <a target=3D"=
_blank" href=3D"http://developer.postgresql.org/pgdocs/postgres/libpq-ssl.h=
tml">http://developer.postgresql.org/pgdocs/postgres/libpq-ssl.html</a> ? I=
 couldn't find this option anywhere...</span><br><br><br>6) Okay, almost do=
ne... <br><br>Now I want to make a dump of "MyDatabase" on "MyMainServer" o=
n "MySecondServer". How can I do this connection? What is the host that I s=
hould write to connect to "MyMainServer", IP XXX.XXX.XXX.XXX with user "MyU=
serName"?<br><br>&gt; pg_dump  -c -h ???????? -p 5432 -U "MyUserName" "MyDa=
tabase" | psql -c -h "MySecondServer" -p 5432 -U "MyUserName" "MyDatabase"<=
br><br>And the same to connect with psql:<br><br>&gt; psql -h ?????? -p 543=
2 -d "MyDatabase" -U "MyUserName"<br><br>I guess I'll also be able to
 connect with pgAdmin at this point.<br><br>One last question, about how th=
is authentication takes place, what is the order of the steps?<br>1) Client=
 connects to server, and sends its certificate<br>2) server checks if clien=
t IP is okay, if so checks certificate as defined in pg_hba.conf with root.=
crt<br>3) if okay, server sends its certificate<br>4) client checks if serv=
er's certificate is okay with root.crt<br>5) if okay, the connection is don=
e, and encrypted?<br><br>Please tell me if I understand well how it works.<=
br><br>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!!<br><br=
>Bye!<br></p><p><br></p><p>S=E9bastien<br></p>=0A=0A=0A</div><br>=0A=0A=0A=
=0A=0A      </body></html>
--0-1749150258-1287053944=:42149--