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.

Post by Hubert Łępicki

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