PostgreSQL in Ruby with SSL connections


#1

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?


#2

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


#3

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…?


#4

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


#5

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.


#6

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.


#7

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.


#8

On 1/24/06, Kevin B. removed_email_address@domain.invalid wrote:

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 D.,
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


#9

On 1/26/06, Kevin B. removed_email_address@domain.invalid wrote:

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


#10

On 1/28/06, Kevin B. removed_email_address@domain.invalid wrote:

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


#11

On 1/26/06, Dave L. removed_email_address@domain.invalid wrote:

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?
I’m on newest everything for psql and the server…


#12

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


#13

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.