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.
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
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
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
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
create a new folder to hold the certificates:
client.keyto the folder
<path-to-app>/.gitignoreto ignore any certificate keys by adding
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 terminal
open a new terminal
check your Postgres installation
navigate to the certificates folder
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"
enter a SQL command such as listing all the tables:
exit from psql:
Step 3: Configure Rails Database Settings
Edit the Rails database configuration to use the certificate:
change the development section:
development: <<: 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.
we don't want it saved in the repository.
we can't add it as an environment variable because the
PGgem expects an actual file.
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.
client.keyfile in your editor.
copy the text into a new tab and close the
join all the individual lines together with a
|character replacing the line breaks.
add this string to your cloud environment variables as
Step 2: Generate the Private Key File On-Demand
As Rails boots, re-create the private key from the environment variable
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
Our database config now becomes:
production: <<: 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."
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.
"Every day that you learn something new, is never a wasted day."