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

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:

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

Can someone help me?

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

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
]

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.

1 Like

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?

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

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

I haven’t tried that way yet

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}’}}

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