This is a blog of AmberBit - a Elixir and Ruby web development company. Hire us for your project!

Connecting Ecto to PostgreSQL with SSL

Hubert

Posted by Hubert Łępicki

Hubert is partner at AmberBit. Rails, Elixir and functional programming are his areas of expertise.
@hubertlepicki @hubertlepicki

PostgreSQL has native support for secure SSL connections, and - if available - it’s a good idea to use this feature. Moreover, some hosted PostgreSQL providers, such as Google’s Cloud SQL do require that you establish your connections not only with SSL enabled, but also in a way that verifies identity of both: client and server during the process.

Enabling SSL

In the most basic use case, all you have to do to enable secure SSL connection with Ecto’s PostgreSQL Adapter, is to flip on the :ssl switch:

config :myapp, MyApp.Repo, adapter: Ecto.Adapters.Postgres,
  username: "username",
  password: "password",
  database: "database",
  hostname: "example.com",
  ssl: true,
  pool: 10

This will only verify the server’s certificate against global database of certificate authorities installed on the client system. If the server is however using self-signed certificate, you will need to obtain and provide server’s certificate file. In case of Google’s Cloud SQL, this file is called server-ca.pem:

config :myapp, MyApp.Repo, adapter: Ecto.Adapters.Postgres,
  username: "username",
  password: "password",
  database: "database",
  hostname: "example.com",
  ssl: true,
  pool: 10,
  ssl_opts: [
    cacertfile: "priv/server-ca.pem"
  ]

If your provider requires you to use client-side certificates, in addition to server’s SSL certificate - to authenticate - you will also need a private key file and a cert file:

config :myapp, MyApp.Repo, adapter: Ecto.Adapters.Postgres,
  username: "username",
  password: "password",
  database: "database",
  hostname: "example.com",
  ssl: true,
  pool: 10,
  ssl_opts: [
    cacertfile: "priv/server-ca.pem",
    keyfile: "priv/client-key.pem,
    certfile: "priv/client-cert.pem"
  ]

Note on storing certificate files

It might not be greatest idea ever to store your *.pem files in the repository. Better idea is to put them on the server, away from Git repository and developers. Even better idea is to use a dedicated tool such as Vault to manage the certificate files and store them properly. This can be integrated with your deployment pipeline so you never have to manually upload, change or remove *.pem files when the configuration needs updating.

Hubert

Hi there!

I hope you enjoyed the blog post. Can we help you with Elixir or Ruby work? We are looking for new opportunities at the very moment, and we do have team available just for you.

Email me at: contact@amberbit.com or use the contact form below.

Want to get in touch about a project? Drop us a line!

When submitting the form, you are sending your personal information (including your name and e-mail as entered above) to contact@amberbit.com. AmberBit Sp. z o. o. is the receiving party, and a data controller, and will use the information you provided for the purpose of establishing relationship leading to possibly signing a services contract, and fulfillment of such contract only. We will not subscribe you to marketing lists, newsletters etc. You can read more about it in our Privacy Policy.