How do I make Rails use SSL to connect to PostgreSQL?

Question

When I try to connect to the remote PostgreSQL database with a Rails 3.2 project I get this error:

FATAL:  no pg_hba.conf entry for host "10.0.0.3", user "projectx", database "projectx", SSL off 

My configuration on Rails looks like this:

staging:   adapter: postgresql   database: projectx   username: projectx   password: 123456   host: 10.0.0.3   encoding: utf8   template: template0   min_messages: warning 

and on PostgreSQL looks like this:

hostssl    all             all             0.0.0.0/0            md5 hostssl    all             all             ::/0                 md5 

Both machines are running on an Ubuntu 12.04.

I found posts saying that it should work automatically, which clearly doesn't happen. I found some saying that libpq didn't have SSL enabled and enabling it solved the problem, but no explanation on how to enable it. I can see when I look at the dependencies of libpq that it depends on the some SSL packages, so I would assume SSL support is compiled.

Some posts recommended adding this:

sslmode: require 

or this:

sslmode: enabled 

to enable ssl mode, but it had no effect for me. I read that it's silently ignored.

I also tried the database string approach, ending up with:

staging:   adapter: postgresql   database: "host=10.0.0.3 dbname=projectx user=projectx password=123456 sslmode=require" 

and then I got the error:

could not connect to server: No such file or directory Is the server running locally and accepting connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"? 

which seems to indicate that Rails was trying to connect to localhost or rather, the local PostgreSQL (there's none) instead of 10.0.0.3.

Any ideas?

Answer 1

As you wrote, normally the Ubuntu 12.x packages are set up so that SSL is activated, works out of the box, and in addition is the first method tried by rails, or any client that lets libpq deal with this stuff, which means almost all clients.

This automatic enabling is not necessarily true with other PostgreSQL packages or with a self-compiled server, so the answers or advice applying to these other contexts don't help with yours.

As your setup should work directly, this answer is a list of things to check to find out what goes wrong. Preferably, use psql first to test a connection setup rather than rails, so that generic postgresql issues can be ruled out first.

Client-side

The client-side sslmode parameter controls the sequence of connect attempts.

To voluntarily avoid SSL, a client would need to put sslmode=disable somewhere in the connection string, or PGSSLMODE=disable in the environment, or mess up with one of the other PGSSL* variables. In the unlikely case your rails process had this in its environment, that would explain the error you're getting, given that pg_hba.conf does not allow non-SSL connections.

Another reason to not try SSL is obviously when libpq is not compiled with SSL support but that's not the case with the Ubuntu packages.

The default for sslmode is prefer, described as:

prefer (default)

first try an SSL connection; if that fails, try a non-SSL connection 

The SSL=off at the end of your error message relates to the last connect attempt that fails. It may be that SSL was tried and failed, or not tried at all, we can't know from this message alone. The connect attempt with SSL=off is rejected normally by the server per the policy set in pg_hba.conf (hostssl in the first column).

It's more plausible that the problem is server-side, because there are more things than can go wrong.

Server-side

Here are various things to check server-side:

• There should be ssl=on in postgresql.conf (default location: /etc/postgresql/9.1/main/)

• when connecting to localhost with psql, you should be greeted with a message like this:

psql (9.1.13)
SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256)
Type "help" for help.

• The ca-certificates package should be installed and up-to-date.

• The ssl-cert package should be installed and up-to-date.

• Inside the postgres data directory (/var/lib/postgresql/9.1/main by default), there should be soft links:
  server.crt -> /etc/ssl/certs/ssl-cert-snakeoil.pem or another valid certificate, and
  server.key -> /etc/ssl/private/ssl-cert-snakeoil.key or another valid key.

• /etc/ssl/certs and parent directories should be readable and cd'able by postgres.

• The postgres unix user should be in the ssl-cert unix group (check with id -a postgres) otherwise it can't read the private key.

• If changing postgresql.conf, be sure that postgresql gets restarted before doing any other test.

• There shouldn't be any suspicious message about SSL in /var/log/postgresql/postgresql-9.1-main.log at startup time or at the time of the failed connection attempt.