How to connect an elixir application with Azure Database for PostgreSQL using SSL certificate?

Uchoa · December 14, 2021, 2:10pm

Hello everyone

I’m newcomer for here and mainly using elixir language. Currently I’m trying to connect my system (Elixir) with an Azure Database for PostgreSQL using ssl certificate. I downloaded the certificate from the blue documents page (f.e.: BaltimoreCyberTrustRoot.crt.pem), but every time I try to run the system it throws the following error:

12:50:29.007 [error] Postgrex.Protocol (#PID<0.165.0>) failed to connect: ** (DBConnection.ConnectionError) ssl connect: closed
12:50:29.019 [error] Postgrex.Protocol (#PID<0.164.0>) failed to connect: ** (DBConnection.ConnectionError) ssl connect: closed
12:50:30.981 [error] Postgrex.Protocol (#PID<0.165.0>) failed to connect: ** (DBConnection.ConnectionError) ssl connect: closed
12:50:31.254 [error] Postgrex.Protocol (#PID<0.164.0>) failed to connect: ** (DBConnection.ConnectionError) ssl connect: closed
12:50:31.445 [error] Could not create schema migrations table. This error usually happens due to the following:

The database does not exist
The “schema_migrations” table, which Ecto uses for managing
migrations, was defined by another library
There is a deadlock while migrating (such as using concurrent
indexes with a migration_lock)

To fix the first issue, run “mix ecto.create”.

To address the second, you can run “mix ecto.drop” followed by
“mix ecto.create”. Alternatively you may configure Ecto to use
another table and/or repository for managing migrations:

config :database, Database.Repo,
  migration_source: "some_other_table_for_schema_migrations",
  migration_repo: AnotherRepoForSchemaMigrations

The full error report is shown below.

** (DBConnection.ConnectionError) connection not available and request was dropped from queue after 2983ms. This means requests are coming in and your connection pool cannot serve them fast enough. You can address this by:

Ensuring your database is available and that you can connect to it
Tracking down slow queries and making sure they are running fast enough
Increasing the pool_size (albeit it increases resource consumption)
Allowing requests to wait longer by increasing :queue_target and :queue_interval

Can someone help me?

ruslandoga · December 14, 2021, 2:29pm

You can pass your certs in :ssl_opts when configuring the repo.

Uchoa · December 14, 2021, 2:32pm

I already passed. Here are the settings:

config :database, Database.Repo,
database: cgc_db_name,
username: cgc_db_username,
password: cgc_db_password,
hostname: cgc_db_hostname,
port: String.to_integer(cgc_db_port),
pool_size: String.to_integer(cgc_db_pool_size),
ssl: true,
ssl_opts: [
verify: :verify_peer,
versions: [:“tlsv1.2”],
ciphers: :ssl.cipher_suites(:all, :“tlsv1.2”),
cacertfile: cgc_db_cacertfile
]

ruslandoga · December 14, 2021, 2:43pm

Here’s my working setup with google’s postgres:

# in config/runtime.exs

decode_cert = fn cert ->
  [{:Certificate, der, _}] = :public_key.pem_decode(cert)
  der
end

decode_key = fn cert ->
  [{:RSAPrivateKey, key, :not_encrypted}] = :public_key.pem_decode(cert)
  {:RSAPrivateKey, key}
end

ca_cert = System.get_env("DATABASE_CA_CERT")
client_key = System.get_env("DATABASE_CLIENT_KEY")
client_cert = System.get_env("DATABASE_CLIENT_CERT")

ssl_opts =
  if ca_cert do
    [
      cacerts: [decode_cert.(ca_cert)],
      key: decode_key.(client_key),
      cert: decode_cert.(client_cert)
    ]
  end

config :app, App.Repo,
  ssl_opts: ssl_opts,
  # ...

Maybe it would help.

Uchoa · December 14, 2021, 2:53pm

Ok, but you have 3 certificates: ca_cert, client_key and client_cert. In my case, I’m using Azure and the documentation only provides one certificate (BaltimoreCyberTrustRoot.crt.pem). I would like to know if the variable “verify” with the value “verify_peer” obligatorily asks for all certificates.

My attempts so far?

Connect my system with azure database for postgres without ssl and it worked correctly. → OK!
Connect my system with azure database for postgres with ssl and it gave an error.
I’ve already changed the version to [:“tlsv1.1”], [:“tlsv1.2”], [:“tlsv1.3”] and all attempts gave error.
I already took the verify property, but it also gave an error.

ruslandoga · December 14, 2021, 2:57pm

You probably can’t do full verification (I guess that’s what verify_peer stands for, but I’m not sure) with just a cacert, have you been able to connect to the db with psql, it might have more info on what’s going wrong? Also try using sslmode=require and sslmode=verify-ca. I pass these in repo url:

# export DATABASE_URL=ecto://user:password@hostname:5432/db?sslmode=require
db_url = System.fetch_env!("DATABASE_URL")
config :app, App.Repo,
  url: db_url

Uchoa · December 14, 2021, 2:59pm

I haven’t tried that way yet

Uchoa · December 15, 2021, 2:36pm

I’m trying again, but now the following error is appearing:

14:32:06.948 [error] Postgrex.Protocol (#PID<0.2281.0>) failed to connect: ** (DBConnection.ConnectionError) ssl connect: TLS client: In state certify at ssl_handshake.erl:2017 generated CLIENT ALERT: Fatal - Handshake Failure
{bad_cert,hostname_check_failed} - {:tls_alert, {:handshake_failure, ‘TLS client: In state certify at ssl_handshake.erl:2017 generated CLIENT ALERT: Fatal - Handshake Failure\n {bad_cert,hostname_check_failed}’}}

ruslandoga · December 15, 2021, 2:49pm

That’s much better as it at least shows the error now instead of plain closed. Since the error reason is hostname_check_failed can you please verify that the name in the certificate is the same as the one you are connecting to? Maybe the host name there is something like my-db.azure.com and you are connecting to 13.48.123.123 or something like this.

You can do it with openssl: openssl - Get common name (CN) from SSL certificate? - Unix & Linux Stack Exchange

KristerV · November 13, 2024, 1:19pm

i am at this exact state now. any chance you have a working config you can share?

ruslandoga · November 13, 2024, 3:44pm

@KristerV

I think Postgrex should be able to handle it automatically in the recent versions (v0.18.0 and up).

For an older Postgrex this seems to work:

Mix.install [{:postgrex, "0.17.5"}]

Postgrex.start_link(
  hostname: "my-rds-db.c16quuxxxx.eu-north-1.rds.amazonaws.com",
  port: 5432,
  username: "my-rds-user",
  password: "xxx",
  database: "my-rds-db",
  show_sensitive_data_on_connection_error: true,
  ssl: true,
  ssl_opts: [
    verify: :verify_peer,
    cacertfile: ~c"/Users/ruslandoga/Desktop/eu-north-1-bundle.pem",
    depth: 10,
    server_name_indication: ~c"my-rds-db.c16quuxxxx.eu-north-1.rds.amazonaws.com",
    customize_hostname_check: [match_fun: :public_key.pkix_verify_hostname_match_fun(:https)]
  ]
)

The important bit that was missing from my previous replies is server_name_indication.

chgeuer · November 13, 2024, 9:40pm

How about this one:

gist.github.com

https://gist.github.com/chgeuer/b8d87f442846f9fe6aa1bde98b36fb7d

postgrex_azure.md



# A small demo to connect to an Azure PostgreSQL DB using Postgrex

```elixir
Mix.install(
  [
    {:postgrex, "~> 0.19.3"},
    {:req, "~> 0.5.7"}
  ],

This file has been truncated. show original

A small demo to connect to an Azure PostgreSQL DB using Postgrex

Mix.install(
  [
    {:postgrex, "~> 0.19.3"},
    {:req, "~> 0.5.7"}
  ],
  consolidate_protocols: false
)

A small utility to download certs into memory

defmodule MicrosoftCerts do
  # https://learn.microsoft.com/en-us/azure/postgresql/flexible-server/concepts-networking-ssl-tls#download-root-ca-certificates-and-update-application-clients-in-certificate-pinning-scenarios
  # https://www.digicert.com/kb/digicert-root-certificates.htm
  # https://dl.cacerts.digicert.com/DigiCertGlobalRootG2.crt.pem

  defp get_pem(url) do
    %Req.Response{status: 200, body: pem} = Req.get!(url: url)
    pem
  end

  defp get_cer(url) do
    %Req.Response{status: 200, body: cer} = Req.get!(url: url)

    cert_to_pem(cer)
  end

  defp cert_to_pem(cert) when is_binary(cert) do
    cert
    |> Base.encode64()
    |> String.replace(~r/.{64}/, "\\0\n")
    |> then(&"-----BEGIN CERTIFICATE-----\n#{&1}\n-----END CERTIFICATE-----\n\n")
  end

  def ca_pem_data do
    [
      "https://cacerts.digicert.com/DigiCertGlobalRootG2.crt.pem" |> get_pem(),
      "https://www.microsoft.com/pkiops/certs/microsoft%20azure%20rsa%20tls%20issuing%20ca%2004%20-%20xsign.crt"
      |> get_cer(),
      "https://www.microsoft.com/pkiops/certs/Microsoft%20RSA%20Root%20Certificate%20Authority%202017.crt"
      |> get_cer()
    ]
    |> Enum.join("\n\n\n")
  end

  def ssl_opts(hostname) do
    [
      # log_level: :all,
      protocol: :tls,
      protocol_version: :"tlsv1.3",
      verify: :verify_peer,
      # cacertfile: temp_ca_file,
      cacerts:
        ca_pem_data()
        |> :public_key.pem_decode()
        |> Enum.map(fn {:Certificate, der, :not_encrypted} -> der end),
      server_name_indication: String.to_charlist(hostname),
      depth: 3
    ]
  end
end

Connect

db_host = "foo.postgres.database.azure.com"

{:ok, conn} = Postgrex.start_link(
  hostname: db_host,
  port: 5432,
  username: "adminpostgrex",
  password: "xxx",
  database: "postgres",
  ssl: MicrosoftCerts.ssl_opts(db_host)
  # stacktrace: true,
  # show_sensitive_data_on_connection_error: true,
)

chgeuer · November 14, 2024, 3:12pm

Here’s a quick blog article

KristerV · November 15, 2024, 7:21am

oh man, I still get TLS :client: In state :wait_cert at ssl_handshake.erl:2177 generated CLIENT ALERT: Fatal - Unknown CA.

The only difference seems to be the cert itself. what kind of a cert are you using? the name says “bundle” - what is that? I just got the CA version from Microsoft docs, is that not correct?

edit: it works! those SSL settings were exactly correct! and “bundle” means you put the 3 certs together:

cat DigiCertGlobalRootCA.pem DigiCertGlobalRootG2.crt.pem MicrosoftRSARoot2017.pem > combined_ca.pem

thank you so much! what a pain this was.