← All articles
SSL for Postgrex and Supabase in Elixir

SSL for Postgrex and Supabase in Elixir

  • Written by Andrei Soroker on

As sometimes happens, I needed to create a Postgrex connection to a Supabase Postgres instance (hosted on supabase.com) from Elixir. After failing to get it working quickly, I embarked on a super annoying journey I’d love to help others avoid, so I thought I’d write the key findings down.

To find your connection parameters in Supabase, look for Project Settings / Configuration / Database / Connection Parameters.

As a separate minor challenge, I had to get my Supabase certificate from an application configuration variable (fed in via Doppler) to a file. To do this, I used the Application.put_env approach:

    # application.ex
    case MyApp.env(:db_ssl_crt) do
      nil ->
        :ok

      pem ->
        {:ok, path} = Briefly.create()
        File.write!(path, pem)
        Application.put_env(:my_app, :cacertfile_path, path)
    end

The certificate is written to a temporary file with Briefly. Note that Briefly will close the temporary file as soon as the temp file creator process exits—in our case, it’s the application process, which means the path to the certificate will remain available as long as the application is running.

  def db() do
    ssl = case MyApp.env(:cacertfile_path) do
      nil ->
        []

      path ->
        [ssl: true,
          ssl_opts: [
            verify: :verify_peer,
            cacertfile: path,
            server_name_indication: String.to_charlist(MyApp.env(:db_host)),
            customize_hostname_check: [
              match_fun: :public_key.pkix_verify_hostname_match_fun(:https)
            ]
          ]
        ]
    end

    opts = [
      hostname: MyApp.env(:db_host),
      port: MyApp.env(:db_port),
      username: MyApp.env(:db_user),
      database: MyApp.env(:db_name),
      password: MyApp.env(:db_pass)
    ] ++ ssl

    Postgrex.start_link(opts)
  end
Share