PostgreSQL in Ruby with SSL connections

[Kevin Brown]

Is SSL supported with the ruby postgres adapter? I'd like to SSL the
connections, but looking at the rdoc, I'm not seeing anything about
it, though there is a mysterious "options" parameter. I can already
connect to my postgres server with pgadmin3, so I know everything is
set up correctly, so how to I tell the ruby code to get on board?

 

[Dick Davies]

It's handled transparently by libpq. If you're using the C library,
you don't need to worry about it.

 

[Kevin Brown]

------=_Part_778_13746720.1138154403264
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

I'd like to use SSL certificate authentication, and that happens
transparently? Don't I need to supply a cert? It's not looking in the
~/.postgresql directory for it like psql does...?

It's handled transparently by libpq. If you're using the C library,
you don't need to worry about it.

------=_Part_778_13746720.1138154403264--

 

[Dick Davies]

Sorry, you didn't mention client auth. Haven't tried that
(but server auth worked fine for me before now).

 

[Dave Lee]

I'd like to use SSL certificate authentication, and that happens
transparently? Don't I need to supply a cert? It's not looking in the
~/.postgresql directory for it like psql does...?

If you are using postgres-pr, than I can't comment. If you are using
the ruby-postgres extension, then it too should be looking in the
~/.postgresql directory because, as mentioned by Dick Davies,
ruby-postgres makes use of libpq, which is what psql uses. The
documentation regarding ssl support in libpq is at:

http://www.postgresql.org/docs/current/interactive/libpq-ssl.html

which states:

"If the server demands a client certificate, libpq will send the
certificate stored in file ~/.postgresql/postgresql.crt within the
user's home directory. A matching private key file
~/.postgresql/postgresql.key must also be present, and must not be
world-readable."

Having said this, I haven't actually used ssl for postgresql
connections. The documentation regarding ssl with libpq is very
simple, I'm not sure what could be going wrong in your case. Which
OS? Which ruby? Which version of ruby-postgres? Maybe you're
actually using postgres-pr?

Dave

 

[Kevin Brown]

I'm using the compiled C extension on Linux. I'll do some more
playing and see what I can make out. Could very well have been world
readable.

Thanks for your help, and I'll post the result when I've played some more.

 

[Kevin Brown]

Alright, here it is. I'm not even client authing yet. Psql connects
just fine and lets me know it's SSLing the connection. Ruby code from
the same machine to the same machine says:

#<PGError: SSL error: sslv3 alert handshake failure>

which I can't find anything about using Google.

 

[Kevin Brown]

Ok, I've nailed it down further.

Without client authing, everything now works fine.

I turn on client authing, and psql connects just fine, while the ruby
code does not, reporting the same #<PGError: SSL error> stuff.

 

[Dave Lee]

Alright, here it is. I'm not even client authing yet. Psql connects
just fine and lets me know it's SSLing the connection. Ruby code from
the same machine to the same machine says:

#<PGError: SSL error: sslv3 alert handshake failure>

which I can't find anything about using Google.

I googled "sslv3 alert handshake failure" which resulted in almost 12k
hits. I don't think you'd find much if you include PGError in the
search. In any case, the fact that psql is working while
ruby-postgres is not, is confusing. The thing is, the ruby-postgres
extension contains no SSL logic, it leaves it all in the hands of
libpq.

is your ruby process and psql process being run under the same user?

Dave

 

[Kevin Brown]

I googled "sslv3 alert handshake failure" which resulted in almost 12k
hits. I don't think you'd find much if you include PGError in the
search. In any case, the fact that psql is working while
ruby-postgres is not, is confusing. The thing is, the ruby-postgres
extension contains no SSL logic, it leaves it all in the hands of
libpq.

is your ruby process and psql process being run under the same user?

Yes. And the postgres server reports in the logs that the ruby code
is not supplying a cert, hence the ssl handshake failure, while psql,
from the exact same user account, and terminal window, 2 seconds
apart, connects beautifully.

Did they link against an older version (7 point something) of lib-pq?=20
I'm on newest everything for psql and the server...

 

[Dave Lee]

Yes. And the postgres server reports in the logs that the ruby code
is not supplying a cert, hence the ssl handshake failure, while psql,
from the exact same user account, and terminal window, 2 seconds
apart, connects beautifully.

Did they link against an older version (7 point something) of lib-pq?
I'm on newest everything for psql and the server...

are you running on windows?

Dave

 

[Kevin Brown]

No. Server is Debian Sarge with 8.1.2 installed from source. Client
is Kubuntu Breezy Badger with ruby and postgres ruby adapter installed
via apt-get.

 

[Kevin Brown]

So, any idea what's going on here? I don't know the internals, and
haven't bothered to look, but should I?