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

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.

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: https://unix.stackexchange.com/questions/103461/get-common-name-cn-from-ssl-certificate

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

@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.