Thread: ssl to more than one server

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.

What should I do to keep stuff separate?

thanks

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

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.

            regards, tom lane

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.

Somehow I haven't been able to find an howto that really explain how
to do it and grasp enough to be confident to bend it enough to a bit
broader context.

I just know that eg. auto-signing a certificate for apache is much
easier and doesn't involve moving files across client and server.

I think a clearer guide from some pg/ssl guru will be certainly very
welcome by all the users.

The server is not complaining... actually it is the client that is
not able to reply with a proper key.

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

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