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?

Schnorkle

i don't think ssl is automatic. i found this manual page describing how to set it up: postgresql.org/docs/9.3/static/ssl-tcp.html. i don't know anything about rails so can't comment on that side. – Fresher

Initially marked as a duplicate of https://mcmap.net/q/534739/-does-rails-postgres-adapter-support-ssl/398670, but the answer there isn't really a good fit as it refers to using the JDBC adapter. – Sirois

Removed my answer based on feedback that the suggestions didn't work. No plans to set up Rails for testing, so that's probably it for my input, except that I suggest testing your connstring with psql too: psql "host=10.0.0.3 ...." – Sirois

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.

Raynor

I kept on working on other deployment issues and now it works. I'm not sure what changed exactly to make it work, but your point about that only being the last failure, after failing trying with SSL was useful. – Schnorkle

@Pablo: maybe you forgot to reload the postgresql service after allowing remote connections in pg_hba.conf. This would be consistent with the error message and the question in general. – Swineherd

No, PostgreSQL has been configured like that for ages and I was using Navicat to connect to it using SSL for more than a year already. – Schnorkle

how do i check that ca-certificates and ssl-cert are installed? – Tachometer

@BenNelson: dpkg -l ca-certificates or apt-cache show ca-certificates for more details. dpkg -L ca-certificates for the list of files it has installed. – Swineherd

Answer 2

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.

Standin

Answer 3

Please check your psql version, older version do not support slmode=require.

It worked for me after upgrading psql to the latest version.

Glockenspiel