How to Authenticate to Postgres from Rails using an SSL certificate.

In this post, I'll walk you through connecting Rails to Postgres, using an SSL/TLS certificate. We'll also cover how to keep the private key secure when using Rails on a cloud platform, like Heroku.

Certificates

We need three files for authentication to work. You should receive these from your Postgres Administrator, or 3rd party host.

1. The root CA certificate

To prove its identity, the Postgres server's SSL certificate is signed by a verifiable Certificate Authority. The root CA certificate is the public certificate for the Certificate Authority.

Rails uses this certificate to verify that the server it connects to is the real deal. This protects against man-in-the-middle attacks, eavesdropping or Impersonation.

Our root CA certificate filename is root.cert.

2. Your client certificate

This certificate was also signed by the Certificate Authority and verifies your identity. Postgres uses the Certificate Authority to verify your client certificate is valid and has not expired.

Our client certificate filename is client.cert

3. Your private key

This is the private key for your certificate, used to prove that you are its true owner, and not using a copy. Like any password, you MUST keep this file PRIVATE, never committing to any repository.

Our private key filename is client.key.

Running Rails Locally

When running Rails on a local machine, the private key can be kept with the other certificates, as long as it's not checked into any repository.

Step 1: Certificate placement

We need to add the files to the application, making sure our private key is not added to any commit. The obvious location would be within the applications config folder.

1. create a new folder to hold the certificates: config/certs

2. copy root.cert, client.cert and client.key to the folder

3. edit <path-to-app>/.gitignore to ignore any certificate keys by adding /config/certs/*.key

Step 2: Test the certificates and server URL are valid.

Before continuing let's ensure the certificates and server settings you received are valid. We can do this by using the Postgres interactive terminalpsql.

1. open a new terminal

2. check your Postgres installation psql -V

3. navigate to the certificates folder cd <path-to-app>/config/certs

4. type the command: psql "host=<host-URL> port=<port-number> dbname=<database-name> user=<username> sslmode=verify-full sslrootcert=root.cert sslcert=client.cert sslkey=client.key"

5. enter a SQL command such as listing all the tables: \dt

6. exit from psql: \q

Step 3: Configure Rails Database Settings

Edit the Rails database configuration to use the certificate:

1. edit config/datababase.yml

2. change the development section:

development:
    <<: *default
    database:     <database name>
    host:         <host URL>
    user:         <username>
    port:         <port>
    sslrootcert: 'config/certs/root.cert'
    sslcert:     'config/certs/client.cert'
    sslkey:      'config/certs/client.key'
    sslmode:     'verify-full'

Step 4: Start Rails

Start the Rails server and check it all works :-)

Running Rails on the Cloud

When running Rails on a cloud platform like Heroku we have a security issue with the private key.

1. we don't want it saved in the repository.

2. we can't add it as an environment variable because the PG gem expects an actual file.

3. we can't copy it to the dyno files, because they are re-created daily.

One solution is to have Rails re-create the file when needed. This is achievable by encoding the private key contents within an environment variable.

Step 1: Encode the Private Key

Convert the private key from multi-line text to a single line, with a join character replacing line breaks.

1. open the client.key file in your editor.

2. copy the text into a new tab and close the client. key.

3. join all the individual lines together with a | character replacing the line breaks.

4. add this string to your cloud environment variables as PG_CLIENT_KEY

Step 2: Generate the Private Key File On-Demand

As Rails boots, re-create the private key from the environment variable PG_CLIENT_KEY.

1. edit config/application.rb

2. add the following at the end of the file, after the module definition:

if ENV['PG_CLIENT_KEY'].present?
    # Ignore if the key file is already present, otherwise, create it
    if Dir["config/certs/*"].include?("config/certs/client.key")
        puts "PG_CLIENT_KEY file alread created ... skipping."
    else
        puts "PG_CLIENT_KEY present ... writing key file."
        key_text = ENV['PG_CLIENT_KEY'].gsub('|', "\n")
        key_file = File.new("config/certs/client.key", "w")

        key_file.puts(key_text)
        key_file.close

        File.chmod(0600, 'config/certs/client.key')
    end
else
    puts "PG_CLIENT_KEY undefined ... ignoring."
end

Step 3: Update the Rails Database Settings

When developing locally, we used the key database: <database name> in our database config. Heroku overwrites this setting, so we need an alternate approach.

To stop Heroku from doing this, we remove the database: key and use url: instead.

Our database config now becomes:

production:
    <<: *default
    url:         'postgres://<user>@<host>:<port>/<database>'
    sslrootcert: 'config/certs/root.cert'
    sslcert:     'config/certs/client.cert'
    sslkey:      'config/certs/client.key'
    sslmode:     'verify-full'

Step 4: Deploy Rails

Make sure you've set the environment variable from Step 1 above, then deploy the update.

If you have a connection problem, check the logs to make sure it says "PG_SSL_KEY present ... writing key file."

Conclusion

Provisioning your Postgres server on a private network behind a firewall is by far the best way to go. When it's connected to the public Internet, there is always a higher security risk.

Using certificates to authenticate makes it exponentially more secure than using the standard login & password authentication.

If you can sweet-talk your Postgres administrators into using certificate authentication, you now have the knowledge you need to connect from Rails.

I hope this post saved you some time, and you enjoyed the content.

CodeMeister

"Every day that you learn something new, is never a wasted day."