How about this one:
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,
)
