does rails postgres adapter support ssl?

Question

i'm trying to configure a rails app to remotely connect to a postgres db. i've noticed that the connection adapters for mysql have options that specify the required info for setting up an ssl connection, but there is no equivalent options for the postgres/pg adapter.

after googling around, i haven't been able to find anything either (only connecting via an ssh tunnel).

so simply, is trying to get the rails postgres adapter to connect over ssl a dead end?

thanks. any help or direction is appreciated.

-h

Answer 1

Rails < 3.2 will not actually pass the database.yml ssl configs on to the PG gem. I hope my pain saves you hours of debugging.

Answer 2

Answer for Rails 4 using JRuby

I'm using Rails 4 with JRuby 1.7.8 (1.9.3p392) and activerecord-jdbcpostgresql-adapter 1.3.4

This solution will allow your Rails application to connect to a PostgreSQL server using SSL. In this solution I use a "NonValidatingFactory" which should only be used for testing. To securely setup for production, you should setup a trustStore, which goes beyond my experience thus far.

Steps to setup Postgres SSL

Add gem to gemfile

    gem 'activerecord-jdbcpostgresql-adapter', platform: :jruby 

Add parameters to your database.yml (for development)

    sslmode: require
    properties: { sslfactory: 'org.postgresql.ssl.NonValidatingFactory' }

For production you need to create a store and remove the "NonValidatingFactor' (briefly described in connection_methods.rb)

    # JRuby/JVM needs to be started with :
    #  -Djavax.net.ssl.trustStore=mystore -Djavax.net.ssl.trustStorePassword=...
    # or a non-validating connection might be used (for testing) :
    #  :sslfactory = 'org.postgresql.ssl.NonValidatingFactory'

Reference Info

The Postgres adapter is build on JDBC. The most useful info I found was the interface between Ruby and Java, and the actual JDBC documentation.

The Ruby to Java Interface in adapter: https://github.com/jruby/activerecord-jdbc-adapter/blob/master/lib/arjdbc/postgresql/connection_methods.rb

JDBC Postgres Connection page: http://jdbc.postgresql.org/documentation/80/connect.html

Example of database.yml

development:

    adapter: postgresql
    encoding: unicode
    database: SSL_Test
    pool: 5
    timeout: 5000
    username: postgres
    password: YourPassword!
    sslmode: require
    properties: { sslfactory: 'org.postgresql.ssl.NonValidatingFactory' }
    host: www.example.com
    port: 5432

Caveats

This may work with other configurations and versions. If you do succeed, go ahead and add a comment for others to know this worked in your specific configuration. Thanks.

Answer 3

I came to this after looking into the exact same question as the OP and wasn't quite satisfied with any of the answers because I am using the pg gem as well and it's the only one supported enough for Rails 2.X.

After some investigation by my co-worker he realized the following:

In Rails 4, you can just specify a variables hash to do this (http://api.rubyonrails.org/classes/ActiveRecord/ConnectionAdapters/PostgreSQLAdapter.html) but it doesn't exist in Rails 2 (http://api.rubyonrails.org/v2.3.8/classes/ActiveRecord/ConnectionAdapters/PostgreSQLAdapter.html)

Because of this we can simply remove the typical config and toss everything into the database argument and call it a day (much like the original answer whitehat101 posted with the jdbc adapter)

Below is the implementation that you would use to connect to a remote server and use the sslmode desired.

development:
    adapter: postgresql
    database: "host=db-serv dbname=admin_production user=XX password=XX sslmode=verify-ca"

Answer 4

In late 2012, things seem to have changed. Although documentation is still sparse, the pg gem seems to auto-negotiate SSL, and the jdbc drivers can be coerced to use SSL.

My app is a hybrid MRI-jRuby app, that accesses heroku-postgres, a cloud postgresql server that requires SSL.

# Gemfile.lock
pg (0.14.1)

activerecord-jdbc-adapter (1.2.2.1)
activerecord-jdbcpostgresql-adapter (1.2.2.1)
jdbc-postgres (9.1.901)

The pg gem, seemed to auto-negotiate SSL. However, the JDBC adapter did not. MRI connected with a typical database.yml (no mention of ssl), but JDBC threw:

(FATAL: no pg_hba.conf entry for host "xx.xx.xx.xx", user "username", database "database", SSL off)

I eventually tried specifying the connection details in JDBC-URL format, and the connection succeeded:

# jruby database.yml
production:
  adapter: jdbcpostgresql
  url: jdbc:postgresql://host/database?user=user&password=password&ssl=true&sslfactory=org.postgresql.ssl.NonValidatingFactory

(sslfactory may not be needed for all setups)

Answer 5

reading the rubyonrails api of the PostgreSQLAdapter i would just answer your question with NO http://api.rubyonrails.org/classes/ActiveRecord/ConnectionAdapters/PostgreSQLAdapter.html

but: there are three different postgresql gems out there:

1. postgres (written in C, discontinued)
2. pg (written in C, best maintained)
3. postgres-pr (pure ruby implementation, active maintained)

the gem "pg" seems to allow SSL connection (at least when taking a look at the sources). this seems to be documented nowhere but it looks like it works (redmine confirms this here: http://www.redmine.org/wiki/1/RedmineInstall ).

so i suggest you might want to take a look on how the database.yml is configured with MYSQL and also try that out with the pg gem. also make sure that you compiled postgresql with SSL support. see http://www.williambharding.com/blog/rails/guide-to-setup-rails-with-mysql-ssl/

if that all does not work, maybe you can try to monkey-patch the database connection from rails and add connection_parameters to the ssl connection. here is the information from the source from ruby-pg:

<var>sslmode=mode</var> : how to treat SSL(string) (one of disable, allow, prefer, require)

please also take a look at another stackoverflow discussion regarding that topic: Can ActiveRecord connect to PostgreSQL remotely and protect the DB password?