Re: ssl to more than one server - Mailing list pgsql-general

On Thu, 29 Jan 2009 21:56:05 +0100
Ivan Sergio Borgonovo <mail@webthatworks.it> wrote:

> On Thu, 29 Jan 2009 12:53:20 -0500
> Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> > Ivan Sergio Borgonovo <mail@webthatworks.it> writes:
> > > I succeded to connect to one postgresql server with ssl.
> > > Now it's the time of the second... but postgresql clients
> > > (pgsql) just look at ~/.postgresql/postgresql.(key|crt)
> > > So I can't put in ~/.postgresql/ another [].crt coming from
> > > another server.
>
> > Not an ssl expert, but I think you just concatenate all the keys
> > you need into the one text file.
>
> I did a cat new.(crt|key) >> postgresql.(crt|key) on the client.
> The old "server" still work. The new one still doesn't.
>
> I took notes on how I did the first time and I think they were
> enough detailed to repeat the process but I've to admit I really
> didn't understand what I did the first time, so I'm not absolutely
> sure if I really did it right.

Actually... due to a mistype on the original server it was working.
Generally mistype break things.
I'm listing the whole process because there should be something
missing to succeed in full ssl support.

The mistake was to name root.crt, root.crl so that actually one of
the certificate was not used.
Now as soon as the certificate are there... I get the message:
could not accept SSL connection: peer did not return a certificate
psql: FATAL:  no pg_hba.conf entry for host "192.168.1.12", user
"ivan", database "test", SSL off
If I don't use root.crt, I can connect through ssl on both server,
but the connection is not authenticated.
What's missing?

Client:
openssl req -new -text -out pg_client.req
openssl rsa -in privkey.pem -out pg_client.key
openssl req -x509 -in pg_client.req -text -key pg_client.key -out
pg_client.crt
chmod og-rwx pg_client.key
mv pg_client.key ~/.postgresql/postgresql.key
scp pg_client.cert server1:/etc/postgresql-common/root.crt
scp pg_client.cert server2:/etc/postgresql-common/root.crt

Server1:
openssl req -new -text -out pg_server.req
openssl rsa -in privkey.pem -out pg_server.key
openssl req -x509 -in pg_server.req -text -key pg_server.key -out
pg_server.crt
chmod og-rwx pg_server.key
mv pg_server.key /etc/postgresql-common/server.key
scp pg_server.crt client:/home/ivan/.postgresql/postgresql.crt
mv pg_server.crt /etc/postgresql-common/server.crt

Server2:
openssl req -new -text -out pg_server.req
openssl rsa -in privkey.pem -out pg_server.key
openssl req -x509 -in pg_server.req -text -key pg_server.key -out
pg_server.crt
chmod og-rwx pg_server.key
mv pg_server.key /etc/postgresql-common/server.key
scp pg_server.crt client:/home/ivan/.postgresql/postgresql2.crt
mv pg_server.crt /etc/postgresql-common/server.crt

Client:
cat postgresql2.crt >> postgresql.crt

thanks

--
Ivan Sergio Borgonovo
http://www.webthatworks.it