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

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.

Rails uses the PG gem for postgres to connect see here for the implementation:

https://github.com/rails/rails/blob/02a3c0e771b3e09173412f93d8699d4825a366d6/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb#L881

The PG gem uses libpg (c library) and the documentation on "PG::Connection.new" found here: http://deveiate.org/code/pg/PGconn.html

Suggests the following options:

host
server hostname

hostaddr
server address (avoids hostname lookup, overrides host)

port
server port number

dbname
connecting database name

user
login user name

password
login password

connect_timeout
maximum time to wait for connection to succeed

options
backend options

tty
(ignored in newer versions of PostgreSQL)

sslmode
(disable|allow|prefer|require)

krbsrvname
kerberos service name

gsslib
GSS library to use for GSSAPI authentication

service
service name to use for additional parameters

So this would indicate that the connection string will not work (since it is not recognised by the adapter, this might be a mysql adapter option)

Also this indicates that the sslmode=required option should work, as this is a basic feature of libpg.

So:

database.yml

staging:
  ...
  sslmode: "require"
  ...

should definitely do the trick, are you sure you use staging mode? // add sslmode to the other environments too to be sure.

Also libpg uses SSL by default as first try, maybe you see the error with SSL Off because SSL mode failed first, then libpq retried without ssl and eventually raised an error.